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 :)

CodePudding user response：

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

Fiddle