-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCortex SQL
61 lines (54 loc) · 5.34 KB
/
Cortex SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
General Event Log
dataset = xdr_data
| filter event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4771
| fields action_evtlog_message
Failed Event Log
dataset = xdr_data // Using the xdr dataset
| filter event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4625 // Filtering by windows event log and id 4625
| alter User_Name =arrayindex(regextract(action_evtlog_message, "Account For Which Logon Failed:\r\n.*\r\n.*Account Name:.*?(\w.*)\r\n"),0), Logon_Type = arrayindex(regextract(action_evtlog_message, "Logon Type:.*?(\d+)\r\n"),0), Failure_Reason = arrayindex(regextract(action_evtlog_message,"Failure Reason:.*?(\w.*)\r\n"),0), Domain = arrayindex(regextract(action_evtlog_message, "Account For Which Logon Failed:\r\n.*\r\n.*.*\r\n.*Account Domain:.*?(\w.*?)\r\n"),0), Source_IP = arrayindex(regextract(action_evtlog_message, "Source Network Address:.*?(\d+\.\d+\.\d+\.\d+)\r\n"),0), Caller_Process_Name = arrayindex(regextract(action_evtlog_message, "Caller Process Name:.*?(\w.*)\r\n"),0), Host_Name = arrayindex(regextract(action_evtlog_message, "Workstation Name:.*?(\w.*)\r\n"),0), FailCode = arrayindex(regextract(action_evtlog_message, "Sub Status:.*?(\w.*)\r\n"),0)
| alter Failure_Reason = if(FailCode CONTAINS "0xC0000064", "User name does not exist", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC000006A", "Password is wrong", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC0000234", "User is currently locked out", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC0000072", "account is currently disabled", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC000006F", "User tried to logon outside his day of week or time of day restriction", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC0000070", "Workstation restriction", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC0000193", "Account expiration", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC0000071", "Expired password", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC0000133", "Clock between DC and other computer too far out of sync", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC0000224", "User is required to change password at next logon", Failure_Reason)
| alter Failure_Reason = if(FailCode CONTAINS "0xC000015b", "The user has not been granted the requested logon type (aka logon right) at this machine", Failure_Reason)
| fields User_Name, Host_Name, Domain, Logon_Type, FailCode, Failure_Reason, Source_IP, Caller_Process_Name // Select all the fields to show them
Account Created:
dataset = xdr_data
| filter event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4741
| filter action_evtlog_message ~= ".*A computer account was created.*"
| alter AccountName = arrayindex(regextract(action_evtlog_message, ".*Account Name:.*?(\w.*)\r\n"),0), AccountDomain = arrayindex(regextract(action_evtlog_message, "Account Domain:.*?(\w.*)\r\n"),0), SAM = arrayindex(regextract(action_evtlog_message, ".*SAM Account Name:.*?(\w.*)\r\n"),0), PrivilegeList = arrayindex(regextract(action_evtlog_message, ".*Privileges.*?(\w.*)\r\n"),0)
| fields AccountName as Creator_Account, AccountDomain as Domain , SAM as Account_created, PrivilegeList, action_evtlog_event_id, action_evtlog_message as raw // Select all the fields to show them
Account Locked Out:
dataset = xdr_data
| filter event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4740
| alter EventFromComputer =arrayindex(regextract(action_evtlog_message, ".*Account Name:.*?(\w.*)\r\n"),0), SecurityID = arrayindex(regextract(action_evtlog_message, ".*Security ID:.*?(\w.*)\r\n"),0)
| alter Computer = json_extract(action_evtlog_data_fields,"$.TargetDomainName")
| alter AccountName = json_extract(action_evtlog_data_fields,"$.TargetUserName")
| alter AccountName = trim(AccountName,"\"")
| alter Computer = trim(Computer,"\"")
| fields AccountName, Computer, EventFromComputer , SecurityID
Successfull Logon:
dataset = xdr_data // Using the xdr dataset
| filter event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4624
| alter User_Name = replace(json_extract(action_evtlog_data_fields, "$.TargetUserName"),"\"","")
| alter Domain_Name = replace(json_extract(action_evtlog_data_fields, "$.TargetDomainName"),"\"","")
| alter Logon_Type = replace(json_extract(action_evtlog_data_fields, "$.LogonType"),"\"","")
| filter (Domain_Name not in ("Font Driver Host", "Window Manager"))
| filter Logon_Type != "11"
| alter Logon_type_desc = ""
| alter Logon_type_desc = if(Logon_Type = "2", "Interactive", Logon_type_desc)
| alter Logon_type_desc = if(Logon_Type = "3", "Network", Logon_type_desc)
| alter Logon_type_desc = if(Logon_Type = "4", "Batch", Logon_type_desc)
| alter Logon_type_desc = if(Logon_Type = "5", "Service", Logon_type_desc)
| alter Logon_type_desc = if(Logon_Type = "7", "Unlock", Logon_type_desc)
| alter Logon_type_desc = if(Logon_Type = "8", "NetworkClearText", Logon_type_desc)
| alter Logon_type_desc = if(Logon_Type = "9", "NewCreds like RunAs", Logon_type_desc)
| alter Logon_type_desc = if(Logon_Type = "10", "RemoteInteractive like RDP", Logon_type_desc)
| alter Logon_type_desc = if(Logon_Type = "11", "CachedInteractive", Logon_type_desc)
| fields agent_hostname , agent_ip_addresses , actor_effective_username , User_Name, Domain_Name, Logon_Type, Logon_type_desc