This is a work in progress but it does work, but is not fully automated. The following scripts will get
Step-by-step guide
- Connect to the horizon sql server with sql managment studio(currently euc-sql-wp03)
- Run the following sql command against the horizon event database to get all of the events
SELECT [Module],[EventType],[ModuleAndEventText],Convert(varchar,[Time]) as Time,[Node],[DesktopId]FROM (SELECT [Module],[EventType],[ModuleAndEventText],[Time],[Node],[DesktopId] From eventUNION ALLSELECT [Module],[EventType],[ModuleAndEventText],[Time],[Node],nullFrom event_historical) alleventsWHERE EventType in ('BROKER_DESKTOP_REQUEST','BROKER_MACHINE_ALLOCATED','AGENT_CONNECTED','AGENT_DISCONNECTED')ORDER by Convert(datetime,[Time])3.Right click on the results and save it to a csv file4.)Copy the below code into powershell, and edit the Import-CSV file to point to the saved file from sql. Then also edit the Export-Csv to point to where to save the csv file, you need to open this in excel next. The also find this line “if($event.EventType -like ‘*BROKER_DESKTOP_REQUEST*’ -and $event.ModuleAndEventText -like ‘*ECM*’)” change the ‘*ECM*’ to how you want to search for desktop pool ids. You need to leave the * as wildcards, but in the below examples I looked for any ECM desktop pool id.#edit the file name hereforthe csv from the event database$events=Import-CSV C:\users\sjesse\Desktop\ecm_history.csv$begindate=$(Get-Date -Date"2021-01-01").AddDays(-1)$events=$events | Where-Object {$_.Time -as [datetime] -gt $begindate}$index=0$sessions=@()foreach($event in $events){#Editthisline between the ** after like to searchfordesktop pools by idif($event.EventType -like'*BROKER_DESKTOP_REQUEST*'-and $event.ModuleAndEventText -like'*ECM*'){$eventData=$event.ModuleAndEventText -split' '$userName=$eventData[1]:outerfor($i=$index;$i -lt $events.Count;$i++){#Write-Host"Checking for BROKER_MACHINE_ALLOCATED"if($events[$i].EventType -like'BROKER_MACHINE_ALLOCATED'-and $events[$i].ModuleAndEventText -like"*$userName*"){$machine=$($events[$i].ModuleAndEventText -split' ')[7]for($h=$i;$h -lt $events.Count; $h++){#Write-Host"Checking for AGENT_CONNECTED"if($events[$h].EventType -like'AGENT_CONNECTED'-and $events[$h].ModuleAndEventText -like"*$userName*"-and $events[$h].Node -like $machine){$loginTime=$events[$h].Timefor($j=$h; $j -lt $events.Count; $j++){#Write-Host"Checking for AGENT_DISCONNECTED"if($events[$j].EventType -like'AGENT_DISCONNECTED'-and $events[$j].Node -like $machine){$logoutTime=$events[$j].TimeWrite-Host"user $userName machine $machine login $loginTime logout $logoutTime"$sessions += New-Object PSObject -Property @{UserName=$userNameMachine=$machineLoginTime=$loginTimeLogoutTime=$logoutTime}breakouter}}}}}}}$index++}#editthisto save the sessions to a file$session | Export-Csv C:\users\sjesse\Desktop\ecm-sessions.csv
5.)Open the sessions file in excel. Remove any dupllicates, make sure to check all the columes. Look at https://support.microsoft.com/en-us/office/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d
6.)Copy this second powershell code into powershell. Edit the import-csv line to point to the sessions extracted from the event database, and edit this to export-csv to save the concurrent user report to the name you want.
$newsessions=""#edit this to point to the sessions saved the event database$newsessions=Import-Csv C:\users\sjesse\Desktop\ecm-ordered-sessions.csv$begindate=Get-Date -Date "2021-01-01"$report=@()$dateCount=0;$count=0 #$newsessions=$sessions | Where-Object {$(Get-Date -Date $_.LoginTime) -gt $begindate.AddDays(-1)}:outer for($i=0;$i -lt 8760 ;$i++){$date=$begindate.AddHours($i)$dateEnd=$date.AddHours(1)foreach($session in $newsessions){if($session.LoginTime -as [datetime] -gt $date -and $session.LogoutTime-as [datetime] -lt $dateEnd){ $session$count++}}$date$count$report+=New-Object PSObject -Property @{Time=$dateCount=$count}$count=0}$report | Export-Csv C:\users\sjesse\Desktop\ecm-report.csv |