Kusto query to get correct users counts connected to the server

Question

there is WVDConnections table in Log Analytics with such data:

DateTime	User	Session status
09:20:39	user3	Completed
09:05:41	user3	Connected
09:05:07	user3	Started
06:51:58	user1	Completed
06:46:22	user2	Completed
06:12:40	user2	Connected
06:12:18	user2	Started
05:36:11	user1	Connected
05:35:37	user1	Started

Session status:
Started - user initiated connection.
Connected - user connected.
Completed - user disconnected.

I need a timechart that displays time gaps then there is no connected users (display as 0) and user counts on the server in certain periods.

So in this case if taking 24 hours period from above table:

0 connections between 09:20:39 - 24:00:00  
1 connection between 09:05:07 - 09:20:39  
0 connections between 06:51:58 - 09:05:07  
1 connection between 06:46:22 - 06:51:58  
2 connections between 06:12:18 - 06:46:22  
1 connection between 05:35:37 - 06:12:18  
0 connections between 00:00:00 - 05:35:37

Now I have only this query

WVDConnections
| where TimeGenerated between (datetime(2024-02-26,00:00) .. datetime(2024-02-26,23:59))
| summarize dcount(UserName) by bin(TimeGenerated, 1h)
| render timechart

But it just counts number of users in 1 hours periods and never display 0 connection on the chart.

How to write the correct kusto query that displays 0 connections and correct user counts then they connects/disconnects?

Answer 1

this is another interesting one where KQL like all languages like this isn't great at showing you what isn't there. So you need to give it things that you know from the start, in this case I've assumed a StartWindow and EndWindow.

I have taken your code and question, added some additional variables and some extra testing source data. I've added in the source data an entry with just "Connected" (with //*** for visibility), you may want to comment that line out for testing.

Otherwise hope it helps.

let WDConnectionsFaux = datatable(DateTime:datetime, User:string, SessionStatus:string) [
datetime('2024-03-29T09:20:39'), 'user3', 'Completed',
datetime('2024-03-29T09:05:41'), 'user3', 'Connected',
datetime('2024-03-29T09:05:07'), 'user3', 'Started',
datetime('2024-03-29T06:51:58'), 'user1', 'Completed',
datetime('2024-03-29T06:46:22'), 'user2', 'Completed',
datetime('2024-03-29T06:12:40'), 'user2', 'Connected',
datetime('2024-03-29T06:12:18'), 'user2', 'Started',
datetime('2024-03-29T05:36:11'), 'user1', 'Connected',
datetime('2024-03-29T05:35:37'), 'user1', 'Started',
datetime('2024-03-29T15:00:00'), 'user1', 'Started', //Have added in a little extra data for testing
datetime('2024-03-29T15:00:00'), 'user4', 'Connected', //***Only log entry is Connected, assumed 24 hour connection? ***
datetime('2024-03-29T15:30:00'), 'user1', 'Connected', //Duplicate User
datetime('2024-03-29T16:45:00'), 'user1', 'Completed',
datetime('2024-03-29T23:00:00'), 'user5', 'Started', //Session starts late in the window
];
let Units = 1m; //eg 1 minute 'slices'
let StartWindow = floor(datetime('2024-03-29T00:00:00'), Units);
let EndWindow = floor(datetime('2024-03-29T23:59:59'), Units);
let ConstantConnection = WDConnectionsFaux
| where DateTime between (StartWindow .. EndWindow)
| summarize AllSessionStates = make_set(SessionStatus) by User
| where AllSessionStates has_all ('Connected') and array_length(AllSessionStates) == 1 //If all we have is connected assume 24 hour connection
| summarize by User;
let AssumedStarts = WDConnectionsFaux
| where DateTime between (StartWindow .. EndWindow)
| summarize arg_min(DateTime, User) by SessionStatus //Get the first change
| where SessionStatus == 'Completed' //Only care if the first log is Completed
| union (ConstantConnection) //Add in Connected sessions
| extend SessionStatus = 'Started', DateTime = StartWindow; //Create an entry at the beginning of our window as Started
let AssumedCompleteds = WDConnectionsFaux
| where DateTime between (StartWindow .. EndWindow)
| summarize arg_max(DateTime, User) by SessionStatus //Get the last change
| where SessionStatus == 'Started' //Only care if the last log is Started
| union (ConstantConnection) //Add in Connected sessions
| extend SessionStatus = 'Completed', DateTime = EndWindow; //Create an entry at the beginning of our window as Started;
range DateTime from StartWindow to EndWindow step Units //Generate the entireity of possible times in our window
| join kind = leftouter ( //Join in all our assumed and known data
WDConnectionsFaux
| where DateTime between (StartWindow .. EndWindow)
| union (AssumedStarts), (AssumedCompleteds) //Mix our actual data and assumed data back together
| extend DateTime = floor(DateTime, Units)
| where SessionStatus != 'Connected' //If we have Started and Completed does Connected matter?
| partition by User ( //For each User create individual sessions
  order by DateTime asc
  | scan with_match_id=SessionId with (
  step Started: SessionStatus == 'Started' ;
  step Completed: SessionStatus == 'Completed';)
)
| summarize StartedTime = min(DateTime), CompletedTime = max(DateTime) by SessionId = strcat(User, '-', SessionId) //User our SessionId a bit like a key
| mv-expand TimeFrame = range(StartedTime, CompletedTime, Units) to typeof(datetime) //Create the range for the entire Session
| summarize AllSessions = make_set(SessionId) by TimeFrame
) on $left.DateTime == $right.TimeFrame
| project-away TimeFrame
| summarize by NumberOfSessions = array_length(AllSessions), DateTime //Use the SessionIds to count number of concurrent connections
| extend NumberOfSessions = coalesce(NumberOfSessions, 0) //Add zeros in our data
| order by DateTime asc
| render timechart