Include only office hours in KQL query

Georgi Palazov 286 Reputation points
2023-01-27T07:25:33.6166667+00:00

Hello,

I'm working on a playbook to report on zero events from CAPAMAuditLog.The query below looks in the CAPAMAuditLog table and provides the count of events for the last 2 hours excluding Saturday and Sunday.

I also want to include only office hours(9am-6pm) in the query. How can I best achieve this?

Thank you!

let Saturday = time(6.00:00:00);
let Sunday = time(0.00:00:00);
CAPAMAuditLog
| where TimeGenerated > ago(2h)
| where dayofweek(TimeGenerated) != Saturday
| where dayofweek(TimeGenerated) != Sunday
| count
Microsoft Sentinel
Microsoft Sentinel
A scalable, cloud-native solution for security information event management and security orchestration automated response. Previously known as Azure Sentinel.
971 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Wilko van de Velde 2,146 Reputation points
    2023-01-27T07:29:28.0233333+00:00

    Add an extra filter:

    | where datetime_part("hour", TimeGenerated) between (9 .. 18)

    1 person found this answer helpful.