CODEWITHSUNDEEP
azureazure-devopskqlazure-sentinel
Stuart
Stuart

Reputation: 43

Multiple dates within KQL query

I am trying to create a sentinel query using KQL which would only search for information on certain dates such as bank holidays. I have seen this can be done on other query languages however best I have created so far is having multiple dates in a row such as

let startdate = (datetime(23/09/2022));
let enddate = (datetime(26/09/2022));

where timegenerated between (startdate .. enddate)

However this does not allow multiple different date slots. I have tried below creating an array of dates however '!contains' is invalid with timegenerated field. Does anyone know a fix for this?

let HolidayDates = datatable(HDates: string)

[

'24/12/2022',

'25/12/2022',

//first holiday

'01/01/2023',

'02/01/2023',

'03/01/2023',

//New years

'07/04/2023',

'08/04/2023',

'09/04/2023',

'10/04/2023'

//easter holiday

];



| where TimeGenerated !contain (HolidayDates)

Upvotes: 1

Views: 831

Answers (1)

Peter Bons
Peter Bons

Reputation: 29780

You can do this like this:

let HolidayDateTimes = datatable(HDates: datetime )
[
datetime(2022-12-24),
datetime(2022-09-27),
datetime(2022-12-25)];
traces | where startofday(timestamp) in (HolidayDateTimes)

Intead of using contains, which is a string operator, use the in operation to check whether an item is in a collection. Then I use startofday to strip away the time information.

Upvotes: 2

Related Questions