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

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

Script for automating desktop logoffs in Horizon.

This script will let users know that there desktop will restart after 3 days if they don’t logout. It checks after 3 days and anyone who’s session is less than 5 days old won’t be restarted and everyone else will.

This requires the AD powershell module that gets installed with the AD RSAT tools and vmware hv helper module

Get-Module -ListAvailable *VM* | Import-Module
Import-Module VMware.hv.helper
$emailBody="Message to Users"
$emailSubject="Planned restart of all Virtual Desktops"
$server=Connect-HVServer -Server $hvServer -User $hvUser -Password $hvPassword -Domain $hvDomain

#Warn of Logoff in 3 days
foreach($sessionSummary in $sessionSummaries)
    $sessionLength=$(get-date) - $sessionSummary.SessionData.StartTime 
    if($sessionLength.Days -gt 4)
        $sessionUserName=$sessionSummary.NamesData.UserName -creplace '^[^\\]*\\', ''
        $sessionEmail=(((Get-ADUser $sessionUserName -Properties mail | select mail) -replace "@{mail=","") -replace "}","")
        Send-MailMessage -To $sessionEmail -From $emailFrom -SmtpServer $emailSmtpServer -Subject $emailSubject  -Body $emailBody

#Wait 3 Days
Start-Sleep -Seconds 259200

#Logoff all desktops that have sessions longer then 5 days
foreach($sessionSummary in $sessionSummaries)
    $sessionLength=$(get-date) - $sessionSummary.SessionData.StartTime 
    if($sessionLength.Days -gt 5)


Troubleshooting Slow Logins with the Windows Performance Tool Kit

1.) First install the performance toolkit if not already, its a sub component of the Windows Assessment and Deployment Kit.

2.)Then open windows performance Recorder and show extra options.

3.)For boot/login issues change the performance senerio to fullboot. Pick the number of iterations you want to record, this is the number of startups its going to record. The default is 3.

4.)Click start . Its going to ask you where you want to save the file, make sure its somewhere your user can access

5.)Click save. It will ask you if your want to reboot click Ok to continue

6.)Login again, let if finish what its doing, and repeat for as many iterations you picked . Each login will show it tracing whats going on

7.) Open the etl file where you save it with the performance analyzer app or just double clicking on it. To start you will see a window like

8.)Click Trace and Load Symbols(This may take time)

9.)Click Profiles and Apply

10.)Click browser catalog and pick FullBoot.Boot

11.)This will open a few Windows. Start with the Time line. Here you can drill into what happened in during the full boot. For logon times we are interested in the Winlogon-Phase and the ExplorerInit. If you look when you highlight a phase it highlights it everywhere. This way you can see the process started and running at that time. What we are looking for is large gaps between start time and stop time, and determine if whats its doing it needed and should take that long. 

12.)The deep Analysis will show you a graph of system processes during startup. In this case cylance is using alot of processing. If you right click in on a peak and zoom in, you can see sylance for 10 seconds or so during login was using over a full core. Over all if you look at the idle percentage there is only about 10% cpu left . You can unzoom by right clicking and click unzoom.


Using procmon for troubleshooting virtual desktop

Saw this today on the vmware communites page, DEMDev wrote a short explanation on how to use psexec.exe and procmon.exe, to capture things by running procmon as the system user and logging in as another user.

The steps are

  *   Log on to the console with an admin account

  *   Copy PSExec.exe and ProcMon.exe to folder C:\X

  *   Run C:\X\PSExec.exe -accepteula -sd C:\X\ProcMon.exe -accepteula -quiet -backingfile C:\X\Log.PML

  *   Log off

  *   Log on “the normal way”, with your test user

  *   From an elevated prompt, run C:\X\PSExec.exe -accepteula -s C:\X\ProcMon.exe -accepteula -quiet -terminate