How to depict two line graphs on a timechart which represend two different time periods one on top of the other

Question

Hi all,

I am trying to create a timechart on a Sentinel workbook on which I will be representing the comparison of count per hour per application between 2 different days.

The main goal and the challenge here is, how to stack the counts for each application for the two different days on top of each other. Since the time period is different, naturally, the counts will be shifted to that axis which maps to that time.

I am thinking, how it would be that I take the results for the other day, and hack them so they seem as if they came on the same time period, and renaming the app count results so I can differentiate them.

Below is the initial code that will do the count per app, but not the stacking over the same timeperiod as I wish.

union isfuzzy = true
(
CommonSecurityLog
| where DeviceVendor == "myapp" and TimeGenerated >= startofday(now(),-2) and TimeGenerated <= endofday(now(),-2) 
// results from 2 days ago
| summarize count() by ApplicationProtocol, bin(TimeGenerated,1h)
| render timechart
)
,
(
CommonSecurityLog
| where DeviceVendor == "myapp" and TimeGenerated >= startofday(now(),-1) and TimeGenerated <= endofday(now(),-1) // results from previous day
| summarize count() by ApplicationProtocol, bin(TimeGenerated,1h)
| render timechart
)

Any help towards the right direction is appreciated.

Answer 1

One approach is this

union isfuzzy = true
(
SigninLogs
| where TimeGenerated between ( startofday(now(),-2) .. endofday(now(),-2) ) // results from previous day -2
| summarize prev_2=count() by UserPrincipalName, bin(TimeGenerated,1h)
)
,
(
SigninLogs
| where TimeGenerated between ( startofday(now(),-1) .. endofday(now(),-1) ) // results from yesterday
| summarize prev_1=count() by UserPrincipalName, bin(TimeGenerated,1h)
)
| order by TimeGenerated asc 
// manipulate TimeGenerated, so that only one day is used
| extend TimeGenerated = iif (isnotempty(prev_2), TimeGenerated = TimeGenerated +1d, TimeGenerated)
| summarize by prev_1, prev_2, TimeGenerated
| render timechart 

I used Signinlogs as an example

I think I prefer a columnchart, but a timechart works almost as well