XQL Query UTC Time Help

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Announcements
Please sign in to see details of an important advisory in our Customer Advisories area.

XQL Query UTC Time Help

L2 Linker

We're capturing windows event 4800 and 4801 (Windows Locked and Windows Unlocked) and are working to build a report on it for the previous day, midnight to midnight.  I've got the query setup exactly as we want, however are struggling to get around UTC.  Does anyone have a good method to get around UTC?  Obviously, when I Run the report in XQL, results are correct because the platform is adjusting for the times, but when scheduling it to run, it's pulling UTC Time in the emailed report.  Surely someone has a method to adjust?

 

 

dataset = xdr_data
| alter username = lowercase(json_extract_scalar(action_evtlog_data_fields, "$.TargetUserName"))
| join conflict_strategy = left type = inner (preset = ad_users ) as users lowercase(users.sam_account_name) = username

| filter event_type = ENUM.EVENT_LOG
| filter action_evtlog_event_id = 4800 or
action_evtlog_event_id = 4801
| alter Workstation_Status = If (action_evtlog_event_id = 4800,"Workstation Locked",
if (action_evtlog_event_id = 4801, "Workstation Unlocked", "Null"))
| fields _time, agent_hostname, display_name, Workstation_Status, action_evtlog_event_id
| sort asc display_name, desc _time

 

1 accepted solution

Accepted Solutions

L2 Linker

@BPry 

 

Yeah, that's kind of what we did.  Below is the final query we've scheduled...Pretty nice report for those that have Remote Employees that aren't really working.

 

 

 

config timeframe = 2d
|dataset = xdr_data
| alter username = lowercase(json_extract_scalar(action_evtlog_data_fields, "$.TargetUserName"))
| alter Actual_Time = format_timestamp("%Y/%m/%d %H:%M:%S", _time, "America/Indiana/Indianapolis")
| join conflict_strategy = left type = inner (preset = ad_users ) as users lowercase(users.sam_account_name) = username
| filter _time >= to_timestamp(subtract(to_epoch(date_floor(current_time(), "d", "America/Indiana/Indianapolis"), "SECONDS"), 86400), "SECONDS")
| filter _time <= date_floor(current_time(), "d", "America/Indiana/Indianapolis")
| filter event_type = ENUM.EVENT_LOG
| filter action_evtlog_event_id = 4800 or
action_evtlog_event_id = 4801 or
action_evtlog_event_id = 4634
| filter security_group_list contains "***" or
security_group_list contains "***"
| filter (agent_ip_addresses contains """10.100.5""")
| alter Workstation_Status = If (action_evtlog_event_id = 4800,"Workstation Locked",
if (action_evtlog_event_id = 4801, "Workstation Unlocked",
if (action_evtlog_event_id = 4634, "Windows Logon", "Null")))
| fields -_time
| fields Actual_Time, display_name, Workstation_Status
| sort asc display_name, desc Actual_Time

 

 

 

*** Enter the Security Group from AD

View solution in original post

2 REPLIES 2

Cyber Elite
Cyber Elite

@Brad.Herbert,

Should be able to use 'timezone = "America/Chicago" ' to return the result in the given timezone instead of UTC. Obviously adjust the timezone as needed. 

L2 Linker

@BPry 

 

Yeah, that's kind of what we did.  Below is the final query we've scheduled...Pretty nice report for those that have Remote Employees that aren't really working.

 

 

 

config timeframe = 2d
|dataset = xdr_data
| alter username = lowercase(json_extract_scalar(action_evtlog_data_fields, "$.TargetUserName"))
| alter Actual_Time = format_timestamp("%Y/%m/%d %H:%M:%S", _time, "America/Indiana/Indianapolis")
| join conflict_strategy = left type = inner (preset = ad_users ) as users lowercase(users.sam_account_name) = username
| filter _time >= to_timestamp(subtract(to_epoch(date_floor(current_time(), "d", "America/Indiana/Indianapolis"), "SECONDS"), 86400), "SECONDS")
| filter _time <= date_floor(current_time(), "d", "America/Indiana/Indianapolis")
| filter event_type = ENUM.EVENT_LOG
| filter action_evtlog_event_id = 4800 or
action_evtlog_event_id = 4801 or
action_evtlog_event_id = 4634
| filter security_group_list contains "***" or
security_group_list contains "***"
| filter (agent_ip_addresses contains """10.100.5""")
| alter Workstation_Status = If (action_evtlog_event_id = 4800,"Workstation Locked",
if (action_evtlog_event_id = 4801, "Workstation Unlocked",
if (action_evtlog_event_id = 4634, "Windows Logon", "Null")))
| fields -_time
| fields Actual_Time, display_name, Workstation_Status
| sort asc display_name, desc Actual_Time

 

 

 

*** Enter the Security Group from AD

  • 1 accepted solution
  • 559 Views
  • 2 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!