Built off the open source project Osquery
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',
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'
json_extract(windows_eventlog.data,'$.EventData.LogonType') AS 'logon_type',
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'
json_extract(windows_eventlog.data,'$.EventData.IpAddress') AS 'ip_address',
json_extract(windows_eventlog.data,'$.EventData.IpPort') AS 'ip_port'
WHERE channel = 'Security'
AND eventid ='4625'
AND (data NOT LIKE '%"LogonType":"4"%' AND data NOT LIKE '%"LogonType":"5"%');
Tried this query, but CB says:
Windows: no such function: split
@Rush111 I just used it without issue in the CBC. Can you please email me a screenshot of the error at email@example.com?
Updated to leverage the json_extract function that I just learned about!
In playing around with this query and some failed RDP connections I found out that the ip_port is not reported in the Security logs. It is however in Sysmon logs if you are capturing them. Here is a post on an example of querying Symon logs:
Fixed an error in the query. Was querying 'Status' when it should have been 'SubStatus'.
Fixed logic with lower() function. Still same results, just a better algorithm.