Windows logon failures parsed from event logs. This query is based on the information in this article: https://www.ultimatewindowssecurity.com/securitylog/book/page.aspx?spid=chapter5
SELECT datetime, eventid,
json_extract(windows_eventlog.data,'$.EventData.TargetUserSid') AS 'sid',
json_extract(windows_eventlog.data,'$.EventData.TargetUserName') AS 'username',
json_extract(windows_eventlog.data,'$.EventData.Status') AS 'failure_reason',
CASE lower(json_extract(windows_eventlog.data,'$.EventData.SubStatus'))
WHEN '0xc0000064' THEN 'user name does not exist'
WHEN '0xc000006a' THEN 'user name is correct but the password is wrong'
WHEN '0xc0000234' THEN 'user is currently locked out'
WHEN '0xc0000072' THEN 'account is currently disabled'
WHEN '0xc000006d' THEN 'reason not specified'
WHEN '0xc000006f' THEN 'user tried to logon outside his day of week or time of day restrictions'
WHEN '0xc0000070' THEN 'workstation restriction'
WHEN '0xc0000193' THEN 'account expiration'
WHEN '0xc0000071' THEN 'expired password'
WHEN '0xc0000133' THEN 'clocks between DC and other computer too far out of sync'
WHEN '0xc0000224' THEN 'user is required to change password at next logon'
WHEN '0xc0000225' THEN 'evidently a bug in Windows and not a risk'
END 'failure_status_description',
json_extract(windows_eventlog.data,'$.EventData.LogonType') AS 'logon_type',
CASE json_extract(windows_eventlog.data,'$.EventData.LogonType')
WHEN '2' THEN 'INTERACTIVE'
WHEN '3' THEN 'NETWORK'
WHEN '4' THEN 'BATCH'
WHEN '5' THEN 'SERVICE'
WHEN '7' THEN 'UNLOCK'
WHEN '8' THEN 'NETWORK_CLEAR_TEXT'
WHEN '9' THEN 'NEW_CREDENTIALS'
WHEN '10' THEN 'REMOTE_INTERACTIVE'
WHEN '11' THEN 'CACHED_INTERACTIVE'
END 'logon_type_description',
json_extract(windows_eventlog.data,'$.EventData.IpAddress') AS 'ip_address',
json_extract(windows_eventlog.data,'$.EventData.IpPort') AS 'ip_port'
FROM windows_eventlog
WHERE channel = 'Security'
AND eventid ='4625'
AND (data NOT LIKE '%"LogonType":"4"%' AND data NOT LIKE '%"LogonType":"5"%');
