azure - KQL。如何制作列来显示一天中每小时连接的虚拟机数量？

Question

我有 KQL，可以显示某个时间范围内有多少个虚拟机连接。并显示连接的开始时间、停止时间和持续时间。我的代码:

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

我需要的是显示每小时有多少用户连接到某个 session 主机，如图所示。 有可能做到吗？感谢您的回答:)

Answer 1

这应该可以解决问题。

// 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

<表类=“s-表”><标题> session 主机名2022.11.23_182022.11.23_192022.11.23_202022.11.23_212022.11.23_222022.11.23_232022.11.24_002022.11.24_012022.11.24_022022.11.24_032022.11.24_042022.11.24_052022.11.24_062022.11.24_072022.11.24_082022.11.24_092022.11.24_102022.11.24_112022.11.24_122022.11.24_132022.11.24_142022.11.24_152022.11.24_162022.11.24_17 <正文>VM1224433454555555754444446VM2222457669766566544442432VM3113344444445532334553443VM4333344443110000001111222VM5444543333333211222111111VM6111111111222212222333223VM7244565432332221000122222

Fiddle