1

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. Want to have something like that Is it possible to make it? Thanks for answers :)

TMAC
  • 45
  • 4
  • 1
    [Why should I not upload images of code/data/errors?](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors) – David דודו Markovitz Nov 24 '22 at 11:21
  • This question is not completely baked. **(1)** The number of open sessions can vary during an hour. What are you looking for, min / max / number on start/end of hour? **(2)** Does this report spans over more than a day? Does it look on the last 24 hours and if so, I would you order the columns? Does it start on midnight and if so, is it UTC or local time? **(3)** What if a VM does not have connections at all in the examined period? Are you good with not displaying it? – David דודו Markovitz Nov 24 '22 at 12:29
  • Thanks for asking. 1) I need to know how many connections was at every hour of a day. 2) It can be more than a day. 3) If VM does not have connections at all at some hour then it can be 0 – TMAC Nov 24 '22 at 14:39
  • **(1)** As I said, there are multiple options here **(2)** In that case your required results should look differently, containing date and not only time **(3)** If you have no records for a VM, then you are not aware of its existence and you can't display anything for it, including 0s. You need a list of relevant VMs. It can be achieved by looking on the entire / part of the historical data. – David דודו Markovitz Nov 24 '22 at 14:56
  • This is trickier than seems at 1st sight, e.g., a scenario where100 sessions were opened 10 days ago and still running. If you look for events from the last day / week you get none, because nothing happened for the last day / week. You have no indication that you actually have open sessions. – David דודו Markovitz Nov 24 '22 at 16:39
  • 1
    User would be disconnected auto after not using it for some time. So max session time in this data that I get is near 24 hours but never more. – TMAC Nov 24 '22 at 16:51
  • There is a good chance that you are misusing the *contains* operator. How do the actual values of `State` look like? – David דודו Markovitz Nov 24 '22 at 18:04

1 Answers1

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

David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88