topic Re: XQL Query UTC Time Help in General Topics

XQL Query UTC Time Help

Brad.Herbert — Fri, 28 Jul 2023 19:41:46 GMT

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

Re: XQL Query UTC Time Help

BPry — Mon, 31 Jul 2023 15:28:54 GMT

@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.

Re: XQL Query UTC Time Help

Brad.Herbert — Mon, 31 Jul 2023 15:36:03 GMT

@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