Categories
Uncategorized

Get sessions from horizon even database

This is a work in progress but it does work, but is not fully automated. The following scripts will get 

Step-by-step guide

  1. Connect to the horizon sql server with sql managment studio(currently euc-sql-wp03)
  2. Run the following sql command against the horizon event database to get all of the eventsSELECT [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],null From 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 here for the 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){#Edit this line between the ** after like to search for desktop pools by idif($event.EventType -like '*BROKER_DESKTOP_REQUEST*' -and $event.ModuleAndEventText -like '*ECM*'){$eventData=$event.ModuleAndEventText -split ' '$userName=$eventData[1]:outer for($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}break outer} }}}                    }} }$index++}#edit this to 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

Leave a Reply

Your email address will not be published. Required fields are marked *