XQL - "After hours" query

[Jesse_Siegrist]

This is a fairly dataset agnostic query snippet to look for events "after hours". You'll need to define what that means and also convert the time zone to your local time. This might not work if you're using UTC in the console, I'm not sure there. 

It took me some doing to get this working correctly and it's a common thing someone might want to use for an investigation or dashboard.. so here you go

| alter Local_Time = parse_timestamp("%Y/%m/%d %H:%M:%S", format_timestamp("%Y/%m/%d %H:%M:%S", _time,"America/Los_Angeles"),"UTC")
| alter event_hour = extract_time(Local_Time , "HOUR"), event_day = extract_time(Local_Time , "DAYOFWEEK")
| filter (
   (event_day in (1, 7)) // Saturday (7) or Sunday (1)
   or
   (event_day in (2, 3, 4, 5,6) and (event_hour < 6 or event_hour >= 17)) // M–F but outside 6am-5pm
 )//end filter

|fields _time, Local_Time , event_hour , event_day

That field set should help you confirm its return the correct results before you drive on. Comment out the filter above and make sure the local time matches your _time and the hour and day also lines up. From what I can tell Sunday starts at 1 and Sat is 7, but ymmv. 

[eluis]

Hi @Jesse_Siegrist 

Glad to hear that this is working. We really appreciate the contribution of every user here in this forum

KR, 

Luis