Exclude weekends from KQL query

Question

Hello,

I have a simple query below looking for the total number of events in Sentinel for CAPAM for the last 30mins. I'm setting up a playbook to report if that number reaches 0. However, since CAPAM is an IAM technology it often reaches 0 during the weekends.

How can I exclude the weekends from the query (include only weekdays)?

CAPAMAuditLog

| where TimeGenerated > ago(30m)

| count

EDIT: Or is there a way to schedule the playbook to run only during weekdays?

Answer 1

Hi,

The simplest I can think of is by using the dayofweek() function:

CAPAMAuditLog
| where TimeGenerated > ago(30m)
| where dayofweek(TimeGenerated) != time(6.00:00:00) // Saturday
| where dayofweek(TimeGenerated) != time(0.00:00:00) // Sunday
| count

Alternatively you could move these into variables for a cleaner query:

let Saturday = time(6.00:00:00);
let Sunday = time(0.00:00:00);
CAPAMAuditLog
| where TimeGenerated > ago(30m)
| where dayofweek(TimeGenerated) != Saturday
| where dayofweek(TimeGenerated) != Sunday
| count

Or for an even cleaner query use the between comparator:

let Saturday = time(6.00:00:00);
let Sunday = time(0.00:00:00);
CAPAMAuditLog
| where TimeGenerated > ago(30m)
| where dayofweek(TimeGenerated) between (Sunday .. Saturday)
| where dayofweek(TimeGenerated) != between
| count

Documentation for the dayofweek() function is here.

BR,

Peter