KQL. How can I make columns to show how many VM's connected every hour in a day?

Question

I have KQL that shows how many VM's connect in a time range. And shows Start time of connection, Stop time and Duration. My code:

WVDConnections
| where SessionHostName contains "VM"
//| where UserName contains ""
| where State contains "Started"
| extend Started = TimeGenerated
| join kind= inner ( WVDConnections
    | where SessionHostName contains "VM"
    //| where UserName contains ""
    | where State contains_cs "Completed"
    | extend StopTime = TimeGenerated)
on CorrelationId
| extend Duration= StopTime - Started
| where Duration > 5m
| project Started, StopTime, SessionHostName, Duration

What I need is to show how many users was connected to some session host every hour, like it's shown in a picture. Is it possible to make it? Thanks for answers :)

Answer 1

That's should do the trick.

// Sample data generation. Not part of the solution.
let _timeframeStart                 = 1d;
let _sessionsStartBeforeTimeframe   = 4h;
let _maxSessionDuration             = 12h;
let _vms                            = 7;
let _session                        = 100;
let WVDConnections = materialize
(
    range CorrelationId from 1 to _session step 1 
    | extend TimeGenerated      = ago((_timeframeStart + _sessionsStartBeforeTimeframe) * rand())
            ,SessionHostName    = strcat("VM", tostring(1 + toint(rand(_vms))))
    | mv-expand State           = dynamic(["Started", "Completed"]) to typeof(string)
               ,TimeGenerated   = pack_array(TimeGenerated, TimeGenerated + _maxSessionDuration * rand()) to typeof(datetime)
    | where TimeGenerated between (ago(_timeframeStart) .. now())
);
// Solution Starts here.
let t = materialize
(
    WVDConnections
    | where SessionHostName hasprefix "VM"
    | where State in ("Started", "Completed")
    | project TimeGenerated
             ,CorrelationId
             ,SessionHostName
             ,delta = iff(State == "Started", 1, -1)
);
let SessionsStartedPriorToTimeframe =
(
    t
    | summarize sum(delta) by CorrelationId, SessionHostName
    | summarize delta = countif(sum_delta == -1) by SessionHostName
    | where delta > 0 
);
let minTimeGenerated = toscalar(t | summarize min(TimeGenerated));
let maxTimeGenerated = toscalar(t | summarize max(TimeGenerated));
let VMsHoursProductJoin =
(
    range TimeGenerated from bin(minTimeGenerated, 1h) to maxTimeGenerated step 1h
    | extend dummy = 1
    | join kind=inner (t | distinct SessionHostName | extend dummy = 1) on dummy
);
union (t | project-away CorrelationId), (SessionsStartedPriorToTimeframe | extend TimeGenerated = minTimeGenerated)
| summarize hour_delta = sum(delta) by SessionHostName, bin(TimeGenerated, 1h)
| join kind=rightouter VMsHoursProductJoin on SessionHostName, TimeGenerated
| project SessionHostName   = SessionHostName1
         ,TimeGenerated     = TimeGenerated1
         ,hour_delta
| partition hint.strategy=native by SessionHostName
  (
    order by TimeGenerated asc
    | extend open_sessions = row_cumsum(hour_delta)
  )
| extend TimeGenerated_HH = format_datetime(TimeGenerated, "yyyy.MM.dd_HH")
| evaluate pivot(TimeGenerated_HH, take_any(open_sessions), SessionHostName)
| order by SessionHostName asc

SessionHostName	2022.11.23_18	2022.11.23_19	2022.11.23_20	2022.11.23_21	2022.11.23_22	2022.11.23_23	2022.11.24_00	2022.11.24_01	2022.11.24_02	2022.11.24_03	2022.11.24_04	2022.11.24_05	2022.11.24_06	2022.11.24_07	2022.11.24_08	2022.11.24_09	2022.11.24_10	2022.11.24_11	2022.11.24_12	2022.11.24_13	2022.11.24_14	2022.11.24_15	2022.11.24_16	2022.11.24_17
VM1	2	2	4	4	3	3	4	5	4	5	5	5	5	5	5	7	5	4	4	4	4	4	4	6
VM2	2	2	2	4	5	7	6	6	9	7	6	6	5	6	6	5	4	4	4	4	2	4	3	2
VM3	1	1	3	3	4	4	4	4	4	4	4	5	5	3	2	3	3	4	5	5	3	4	4	3
VM4	3	3	3	3	4	4	4	4	3	1	1	0	0	0	0	0	0	1	1	1	1	2	2	2
VM5	4	4	4	5	4	3	3	3	3	3	3	3	2	1	1	2	2	2	1	1	1	1	1	1
VM6	1	1	1	1	1	1	1	1	1	2	2	2	2	1	2	2	2	2	3	3	3	2	2	3
VM7	2	4	4	5	6	5	4	3	2	3	3	2	2	2	1	0	0	0	1	2	2	2	2	2

Fiddle