Compare today's results to yesterday's and plot them on a graph

Question

I'm trying to build a KQL query that compares today's data to data from the previous day at the same time of day.

https://aka.ms/lademo

let thisWeek = SigninLogs
    | where TimeGenerated > ago(1h)
    | summarize ThisWeek = count() by bin(TimeGenerated, 1m)
    | serialize row = row_number();
let lastWeek= SigninLogs
    | where TimeGenerated between((ago(1h) - totimespan(1d)) .. (now() - totimespan(1d)))
    | summarize LastWeek = count() by bin(TimeGenerated, 1m)
    | serialize row = row_number();
thisWeek
| join // Join data from today and yesterday together
    (
    lastWeek
    )
    on row

There are a few challenges I am running into:

1. Since the hour timestamps won't match exactly between the days, the results won't align exactly as shown in the results below. For example, the hour:minute of 10/31/2023, 10:13:00.000 PM won't match 10/30/2023, 10:07:00.000 PM. Is there a way to still bucket per minute, and if timestamps do not exist, then simply use 0?

2. If I use a time chart instead of a grid, like in the picture below, I want to make the Y axis bigger without changing the numbers on it. In simple words, I'd like a zoom effect because the chart looks very crowded right now. Also, is there a way to get rid of columns like row1 and row from the chart?

Answer 1

Question 1

There are many ways to do this. The version I like is coalesce which lets you check if a value exists and if not use another one.

But there has to be something there to link to in the first place. For time base queries I've found that range could be useful to start off the base data. But you have to generate this data and from there you can have the "standard" time windows.

Question 2

Perhaps using a large bin window than a minute could be useful? Say 15 minutes? bin(TimeGenerated, 15m) Or even an hour? bin(TimeGenerated, 1h)

project-away is probably what you're looking for to remove row1 or project to keep the columns you want to keep.

How I might approach what I think is your use case would be the folowing:

range TimeWindow from startofweek(ago(7d)) to endofday(now()) step 15m
| join kind=leftouter (
SigninLogs
| where TimeGenerated >= startofweek(ago(7d))
| summarize count() by TimeWindow = floor(TimeGenerated, 15m)
) on TimeWindow
| extend count_ = coalesce(count_, 0)
| extend OverallWindow = iif(TimeWindow >= startofweek(now()), 'This Week', 'Last Week')
| extend TimeWindow = iif(OverallWindow == 'Last Week', datetime_add('day', 7, TimeWindow), TimeWindow)
| project TimeWindow, count_, OverallWindow
| render timechart