KQL - Azure Sentinel Workbook - Splitting work hours and off hours sign in logs... not getting the results I'm expecting

Question

I'm creating a Sentinel workbook showing a bar chart of the device log ins (Event ID 4624) each day during expected working hours AND a separate query showing a list of the log ins that happen outside of expected working hours. Working hours are Monday - Friday 5AM - 7PM EST. As seen in the results below I'm getting results that don't match what I had expected from these queries. Can someone spot where I've gone wrong? I would greatly appreciate it.

SecurityEvent
| extend Time = datetime_utc_to_local(TimeGenerated, 'America/New_York')
| where EventID == 4624
| where TargetUserName in ('XXX', 'XXX', 'XXX', 'XXX', 'XXX', 'XXX', 'XXX', 'XXX')
| where hourofday(Time) between (5 .. 19) and dayofweek(Time) between (1d .. 5d)
| make-series EventCount = count() default = 0 on Time from ago(30d) to now() step 1d by TargetUserName
| render barchart

Bar Chart Results There should be entries shown for Fridays, but that isn't showing.

Here's the off hours query:

let A = dynamic(["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"]);
SecurityEvent
| extend Time = datetime_utc_to_local(TimeGenerated, 'America/New_York')
| extend WeekDay = A[toint(dayofweek(Time)/1d)]
| where EventID == 4624
| where TargetUserName in ('XXX', 'XXX', 'XXX', 'XXX', 'XXX', 'XXX', 'XXX', 'XXX')
| where hourofday(Time) !between (5 .. 19) or dayofweek(Time) !between (1d .. 5d)
| summarize by TargetUserName, tostring(WeekDay), Time, Computer

Grid Results The results are showing log ins on weekdays within the expected working hours time range. My understanding is that !between is supposed to omit results within the specified range. Also, The day of the week function doesn't seem to be working properly as both 12/13 and 12/14 are labeled as Wednesday.

I've tinkered with this for quite some time, but I feel like at this point I'm stumped. Probably some small error I'm not seeing. I'm new to KQL, so any help would be great!

Answer 1

As always with query languages, the best suggestions are always:

1. validate your assumptions - make a version of your query that does no filtering other than a time range, and look at what you get back.

   • if you do | summarize count() by bin(TimeGenerated, 1d) do you actually get what you expect?
   • do you have a regular distribution across days like you think? are there no items on certain days in the raw data?
   • you're filtering to specific users, so maybe some users don't do some things on some days?

2. simplify - you are doing a bunch of date/time math up front, mapping to days of the week, etc. what if you do all of that last ? do things like bin on 1d and get the results you want, and THEN start adjusting time math?

3. mock it up - use the datatable operator to make a fake set of initial rows with the exact columns/rows you want. then run your query against that datatable instead of the real table. does the query produce what you expect? (this is almost like "unit testing" the query)

The rest of us won't have the same data in the tables as you do, so it could be that running those queries on our data give you totally different results than your data would.