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 json_extract(windows_eventlog.data,'$.EventData.Status')
    when lower('0xC0000064') then 'user name does not exist'
    when lower('0xC000006A') then 'user name is correct but the password is wrong'
    when lower('0xC0000234') then 'user is currently locked out'
    when lower('0xC0000072') then 'account is currently disabled'
    when lower('0xC000006D') then 'reason not specified'
    when lower('0xC000006F') then 'user tried to logon outside his day of week or time of day restrictions'
    when lower('0xC0000070') then 'workstation restriction'
    when lower('0xC0000193') then 'account expiration'
    when lower('0xC0000071') then 'expired password'
    when lower('0xC0000133') then 'clocks between DC and other computer too far out of sync'
    when lower('0xC0000224') then 'user is required to change password at next logon'
    when lower('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)
6 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!