Query Details
Work Week
Query
//Query to show events in just the workweek
let workweek = datatable(dayOfWeekTimespan:int, dayOfWeekDayDisplayName:string, firstH:int, lastH:int) [ 0, "Sunday", 9, 18, 1, "Monday", 9, 18, 2, "Tuesday", 9, 18, 3, "Wednesday", 9, 18, 4, "Thursday", 9, 18, 5, "Friday", 9, 18, 6, "Saturday", 9, 18, ];
let startDate = ago(60d); let endDate = now();
SecurityEvent
| where TimeGenerated between (startDate .. endDate)
| extend dayOfWeekTimespan = toint(substring(tostring(dayofweek(TimeGenerated)), 0, 1))
| where dayOfWeekTimespan in (1, 2, 3, 4, 5)
| lookup kind=leftouter workweek on dayOfWeekTimespan
| where datetime_part("Hour",TimeGenerated) between (firstH .. lastH)
| project TimeGenerated, Computer, dayOfWeekDayDisplayName
| sort by TimeGenerated asc
Explanation
This query is used to show events that occurred during the workweek. It defines the workweek as Monday to Friday, from 9 AM to 6 PM. The query filters the SecurityEvent table based on the time range specified by the startDate and endDate variables. It then determines the day of the week for each event and filters out events that occurred on weekends. The query joins the workweek table to map the day of the week to its display name. Finally, it projects the TimeGenerated, Computer, and dayOfWeekDayDisplayName columns and sorts the results by TimeGenerated in ascending order.
Details
Rod Trent
Released: July 7, 2021
Tables
SecurityEventworkweek
Keywords
Query,Events,Workweek,Datatable,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,StartDate,EndDate,SecurityEvent,TimeGenerated,Computer,DayOfWeekDayDisplayName,Sort
Operators
letdatatableagonowSecurityEventwherebetweenextendtointsubstringtostringdayofweekinlookupkindleftouterdatetime_partHourprojectsortbyasc