The VMware Carbon Black Tech Zone is live! Checkout this great resource: Mastering Carbon Black Audit & Remediation.

Windows logon failures with the failure reason and logon type decoded

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"%');

 

Tags (2)
9 Comments
jnelson
Carbon Black Employee
Status changed to: Approved
 
Rush111
New Contributor

Tried this query, but CB says:

Windows: no such function: split
jnelson
Carbon Black Employee

@Rush111 which product did you run this query in: CB Response or the CBC?

Rush111
New Contributor

@jnelson Hi! I'm using Carbon Black Cloud

jnelson
Carbon Black Employee

@Rush111 I just used it without issue in the CBC. Can you please email me a screenshot of the error at njon@vmware.com?

jnelson
Carbon Black Employee

Updated to leverage the json_extract function that I just learned about!

jnelson
Carbon Black Employee

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: 

https://community.carbonblack.com/t5/Query-Exchange/Querying-Sysmon-logs/idi-p/109672

jnelson
Carbon Black Employee

Fixed an error in the query. Was querying 'Status' when it should have been 'SubStatus'.

jnelson
Carbon Black Employee

Fixed logic with lower() function. Still same results, just a better algorithm.