Advanced XQL
This webinar covers how to utilize XQL to build out use cases, including an extensive XQL building demo.
For the best experience, we recommend watching the videos in a full-screen view.
Make sure to review the XQL queries under the videos:
Intro & Extraction Demo:
Scenario Introduction:
User Group Demo:
XQL queries:
Extraction demo:
Extraction Using arrayindex() and regextract()
config case_sensitive = false |
dataset = xdr_data // Using the xdr dataset
| fields action_evtlog_message, event_type, action_evtlog_event_id, agent_hostname , action_evtlog_data_fields
| filter event_type = ENUM.EVENT_LOG
| filter action_evtlog_event_id in(4720)
| alter SID_RegExtract = regextract(action_evtlog_message, "[\n\r][ \t]*Security ID:[ \t]*([^\n\r]*)")
| alter AccountName_RegExtract = regextract(action_evtlog_message, "[\n\r][ \t]*Account Name:[ \t]*([^\n\r]*)")
| alter AccountDomain_RegExtract = regextract(action_evtlog_message, "[\n\r][ \t]*Account Domain:[ \t]*([^\n\r]*)")
| alter Subject_SID = arrayindex(SID_RegExtract , 0)
| alter Target_SID = arrayindex(SID_RegExtract , 1)
| alter Subject_AccountName = arrayindex(AccountName_RegExtract , 0)
| alter Target_AccountName = arrayindex(AccountName_RegExtract , 1)
| alter Subject_DomainName = arrayindex(AccountDomain_RegExtract , 0)
| alter Target_DomainName = arrayindex(AccountDomain_RegExtract , 1)
| alter Subject_SID_directEXT = arrayindex(regextract(action_evtlog_message, "[\n\r][ \t]*Security ID:[ \t]*([^\n\r]*)") , 0)
| alter Subject_AccountName_directExt = arrayindex(regextract(action_evtlog_message, "[\n\r][ \t]*Account Name:[ \t]*([^\n\r]*)"), 0)
| alter Target_AccountName_directExt = arrayindex(regextract(action_evtlog_message, "[\n\r][ \t]*Account Name:[ \t]*([^\n\r]*)"), 1)
===================================
Extraction Using json_extract_scalar()
config case_sensitive = false |
dataset = xdr_data // Using the xdr dataset
| fields action_evtlog_message, event_type, action_evtlog_event_id, agent_hostname , action_evtlog_data_fields
| filter event_type = ENUM.EVENT_LOG
| filter action_evtlog_event_id in(4720)
| alter Subject_Account_Name = json_extract_scalar(action_evtlog_data_fields , "$.SubjectUserName")
| alter Target_Account_Name = json_extract_scalar(action_evtlog_data_fields , "$.TargetUserName")
User group data JASON extraction demo:
config case_sensitive = false
| dataset = xdr_data
| filter event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4720
| fields action_evtlog_message , action_evtlog_data_fields , action_evtlog_event_id , agent_hostname , agent_ip_addresses , action_evtlog_description , agent_hostname
| alter Creator_Account_Name = json_extract_scalar(action_evtlog_data_fields , "$.SubjectUserName")
| alter Creator_Account_SID = json_extract_scalar(action_evtlog_data_fields , "$.SubjectUserSid")
| alter Created_Account_Name = json_extract_scalar(action_evtlog_data_fields , "$.TargetUserName")
| alter Created_Account_SID = json_extract(action_evtlog_data_fields , "$.TargetSid")
| alter Creator_Domain_name = json_extract_scalar(action_evtlog_data_fields , "$.SubjectDomainName")
| alter Creator_User_Type = if ((Creator_Domain_name = agent_hostname ), "Local_User", "Domain_User")
| join (
preset = xdr_event_log | filter action_evtlog_event_id = 4732
| alter Group_Member_SID = json_extract(action_evtlog_data_fields , "$.MemberSid")
| alter User_Group_Name = json_extract_scalar(action_evtlog_data_fields , "$.TargetUserName")
| alter Action_Account_Name = json_extract_scalar(action_evtlog_data_fields , "$.SubjectUserName")
| alter User_Group_SID = json_extract_scalar(action_evtlog_data_fields , "$.TargetSid"))
as DemoXQL demoxql.Group_Member_SID = Created_Account_SID
| fields Creator_Account_Name , Created_Account_Name , Action_Account_Name , agent_hostname, Creator_Domain_name , Creator_User_Type ,User_Group_Name , User_Group_SID , action_evtlog_event_id , action_evtlog_message , action_evtlog_data_fields
| filter (User_Group_Name = """Administrators""")
| sort desc _time
| join (
preset = host_inventory_users
| fields endpoint_name , disabled as Is_Created_Account_Disabled , password_expired as Is_Created_Account_password_expired, name , full_name as Created_Account_full_name ) as AddedHostInventory addedhostinventory.endpoint_name = agent_hostname
| filter name = Created_Account_Name
| fields Creator_Account_Name , Action_Account_Name ,Created_Account_Name , Is_Created_Account_Disabled , Is_Created_Account_password_expired , agent_hostname , Creator_Domain_name ,Creator_User_Type , User_Group_Name , User_Group_SID , action_evtlog_event_id , action_evtlog_message , action_evtlog_data_fields
Recommended guides:
Cortex XDR XQL Language Reference
Cortex XDR XQL Schema Reference
Have a question? Post it on our Discussions forum
Cortex XDR
View full article