Adding Horizon Session data to SexiGraf Dashboard

Well... its not a secret anymore that I like Grafana and the prebuild appliance Sexigraf.

One of my favourite dashboards is VMware VM/ VMware All Cluster Top N VM stats.

Altough this dashboard gives me a lot of usefull data it only shows me the hypervisor layer.

As an EUC consultant one of the things I always wonder... how are these numbers relating to the number of session at that specific time window.. like CPU ready, memory etc etc....

What if... I can capture the session data from Horizon within the same interval as Sexigraf pulls the data from vCenter and put this data in a database... and show this within this dashboard?

Datasource

First I need a datasource

Grafana has support for many datasources... one of my personal favourites is InfluxDB.

The appliance is preinstalled with Graphite but I prefer InfluxDB

Why? Easy to setup. Formatting is easy and upload is done with a simple html post containing data in the correct format and tags.

So let's install InfluxDB.

start SSH session to the appliance. I use Putty for this.. (I guess most people do)

sudo curl -sL https://repos.influxdata.com/influxdb.key | sudo apt-key add -sudo curl -sL https://repos.influxdata.com/influxdb.key | sudo apt-key add - sudo curl -sL https://repos.influxdata.com/influxdb.key | sudo apt-key add - sudo apt update sudo apt install influxdb sudo systemctl start influxdb

when done, in the console type "influx"

create a user admin with password, in this example I use the default Sexigraf password.. change to your own needs.

CREATE USER admin WITH PASSWORD 'Sex!Gr@f' WITH ALL PRIVILEGES

When done, edit file /etc/influxdb/influxdb.conf search for the line containing (http) auth-enabled and set value to true. I Used WinSCP for this

in the cli of the appliance restart Influx with the following command: sudo systemctl restart influxdb

open influx again by typing influx

type auth to login

enter credentials as set above.

now lets create the database by typing Create DATABASE Horizon

and done!

PWSH modules

to install the required powershell modules type the following commands:

pwsh

Install-Module -Name VMware.VimAutomation.HorizonView -RequiredVersion 12.2.0.17463427 -Force
Install-Module -Name VMware.VimAutomation.HorizonView -Force

Script to gather the session data and upload to Influxdb:

Below is the script I created to gather the session data and upload to Influx. (to my best powershell effort ;-))

I created this for an Horizon infrastructure based on 2 PODS with 1 resource block each.

Edit the value of the variables to your need (connection servers and Influx username and password)

# Edwin de Bruin
# Debruinonline.net

$INFLUXDBserver="http://localhost:8086"

$INFLUXDBUser="admin" 
$INFLUXDBPW='Sex!Gr@f'
 
$INFLUXDBNAME="Horizon"
$INFLUXDBURI="$INFLUXDBserver/write?&db=$INFLUXDBNAME"

$HorizonCSServerPod1="connectionCSVIPpod1.example.lab"
$HorizonCSServerPod2="connectionCSVIPpod1.example.lab"

$HorizonCSadmin='administrator@example.lab'
$HorizonCSPassword='password' 

$INFLUXCRED = $INFLUXDBUser+':'+$INFLUXDBPW
$authheader = "Basic " + ([Convert]::ToBase64String([System.Text.encoding]::ASCII.GetBytes("$INFLUXCRED")))

#POD 1
$POD="1"
$cs = Connect-HVServer -Server $HorizonCSServerPod1 -User $HorizonCSadmin -Pass $HorizonCSPassword

#sessiondata
$sessionData=$cs.ExtensionData.SessionStatistics.SessionStatistics_GetLocalSessionStatistics().DesktopSessionStatistics

#Pooldata
$ViewAPI = $cs.ExtensionData
$query_service = New-Object "Vmware.Hv.QueryServiceService"
$query = New-Object "Vmware.Hv.QueryDefinition"
$query.queryEntityType = 'DesktopSummaryView'
$PoolsPOD1 = $query_service.QueryService_Query($ViewAPI,$query)
$PoolsPOD1.Results.DesktopSummaryData

Disconnect-HVServer $HorizonCSServerPod1 -Force -Confirm:$false

$NumActiveSessionsPOD1=$sessionData.NumActiveSessions
$NumIdleSessionsPOD1=$sessionData.NumIdleSessions
$NumDisconnectedSessionsPOD1=$sessionData.NumDisconnectedSessions
$NumPendingSessionsPOD1=$sessionData.NumPendingSessions
$NumTotalPOD1=$NumActiveSessionsPOD1+$NumIdleSessionsPOD1+$NumDisconnectedSessionsPOD1+$NumPendingSessionsPOD1

#upload Session data
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod1,POD=$POD,SessionState=Active NumActiveSessions=$NumActiveSessionsPOD1"
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod1,POD=$POD,SessionState=Idle NumIdleSessions=$NumIdleSessionsPOD1"
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod1,POD=$POD,SessionState=Disconnected NumDisconnectedSessions=$NumDisconnectedSessionsPOD1" 
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod1,POD=$POD,SessionState=Pending NumPendingSessions=$NumPendingSessionsPOD1" 
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod1,POD=$POD,SessionState=Total NumTotalSessions=$NumTotalPOD1" 

#Pooldata
$NumProvMachinesPOD1=$PoolsPOD1.Results.DesktopSummaryData.NumMachines| ForEach-Object -begin {$sum=0 }-process {$sum+=$_} -end {$sum}

#upload Pooldata
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "Pooldata,hostname=$HorizonCSServerPod1,POD=$POD,Pool=Total NumProvMachines=$NumProvMachinesPOD1"

#POD 2
$POD="2"
$cs = Connect-HVServer -Server $HorizonCSServerPod2 -User $HorizonCSadmin -Pass $HorizonCSPassword

#sessiondata
$sessionData=$cs.ExtensionData.SessionStatistics.SessionStatistics_GetLocalSessionStatistics().DesktopSessionStatistics

#Pooldata
$ViewAPI = $cs.ExtensionData
$query_service = New-Object "Vmware.Hv.QueryServiceService"
$query = New-Object "Vmware.Hv.QueryDefinition"
$query.queryEntityType = 'DesktopSummaryView'
$PoolsPOD2 = $query_service.QueryService_Query($ViewAPI,$query)
$PoolsPOD2.Results.DesktopSummaryData

Disconnect-HVServer $HorizonCSServerPod2 -Force -Confirm:$false

#sessiondata
$NumActiveSessionsPOD2=$sessionData.NumActiveSessions
$NumIdleSessionsPOD2=$sessionData.NumIdleSessions
$NumDisconnectedSessionsPOD2=$sessionData.NumDisconnectedSessions
$NumPendingSessionsPOD2=$sessionData.NumPendingSessions
$NumTotalPOD2=$NumActiveSessionsPOD2+$NumIdleSessionsPOD2+$NumDisconnectedSessionsPOD2+$NumPendingSessionsPOD2

#upload sessiondata
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod2,POD=$POD,SessionState=Active NumActiveSessions=$NumActiveSessionsPOD2"
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod2,POD=$POD,SessionState=Idle NumIdleSessions=$NumIdleSessionsPOD2"
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod2,POD=$POD,SessionState=Disconnected NumDisconnectedSessions=$NumDisconnectedSessionsPOD2" 
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod2,POD=$POD,SessionState=Pending NumPendingSessions=$NumPendingSessionsPOD2" 
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,hostname=$HorizonCSServerPod1,POD=$POD,SessionState=Total NumTotalSessions=$NumTotalPOD2"

#Pooldata
$NumProvMachinesPOD2=$PoolsPOD2.Results.DesktopSummaryData.NumMachines| ForEach-Object -begin {$sum=0 }-process {$sum+=$_} -end {$sum}

#upload Pooldata
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "Pooldata,hostname=$HorizonCSServerPod1,POD=$POD,Pool=Total NumProvMachines=$NumProvMachinesPOD2"


#Total

#sessiondata
$NumActiveSessionsTotal=$NumActiveSessionsPOD1+$NumActiveSessionsPOD2
$NumIdleSessionsTotal=$NumIdleSessionsPOD1+$NumIdleSessionsPOD2
$NumDisconnectedSessionsTotal=$NumDisconnectedSessionsPOD1+$NumDisconnectedSessionsPOD2
$NumPendingSessionsTotal=$NumPendingSessionsPOD1+$NumPendingSessionsPOD2
$NumSessionsTotal=$NumTotalPOD1+$NumTotalPOD2

#upload sessiondata 
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,SessionState=Active NumActiveSessionsTotal=$NumActiveSessionsTotal"
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,SessionState=Idle NumIdleSessionsTotal=$NumIdleSessionsTotal"
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,SessionState=Disconnected NumDisconnectedSessionsTotal=$NumDisconnectedSessionsTotal" 
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,SessionState=Pending NumPendingSessionsTotal=$NumPendingSessionsTotal" 
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "SessionData,SessionState=Total NumTotalSessionsTotal=$NumSessionsTotal"

#Pooldata
$NumProvMachinesTotal=$NumProvMachinesPOD1+$NumProvMachinesPOD2

#upload Pooldata
Invoke-RestMethod -Headers @{Authorization=$authheader} -Uri $INFLUXDBURI -Method Post -Body "Pooldata,Pool=Total NumProvMachinesTotal=$NumProvMachinesTotal"

Save this to HorizonSession.ps1

Upload to /usr/sbin I Used WinSCP for this

Now we need to let this script run at the interval. SexiGraf gathers the data each 5 minutes.

To do this, edit the folowing file:/etc/contrab

*/5  *  * * *   root    pwsh -f /usr/sbin/HorizonSession.ps1

type the following command to restart cron (Putty)

 /etc/init.d/cron restart

Configure Grafana to use Influx as datasource:

login to SexiGraf and click on the little wheel on the left. Next click on add datasource

(below screenshot i already added the datasource, yours should only show the default Graphite datasource that comes with the appliance

Select InfluxDB

Enter data

Name: InfluxDB

URL: http://localhost:8086

Access: Server (default)

InfluxDB Details:

Database: Horizon

User: Admin

Password: password you created earlyer within Influx...

Save and test:

Now create the dashboard!

Nah I'm gonna save you the trouble... a lot of clicking and editing here.. of course you can contact me if you want to know the details...

here's the JSON of the dashboard I created

horizonSessionDebruinonline.net

.rar

Download RAR • 4KB

You can download above RAR (JSON not allowed on Wix...)

Press the + button and Grafana and next Import

and done! There should be a new dashboard available.

on top: Total sessions of both PODS

Total provsiones machines of both PODS

then below it splits in 2.. left side POD 1, right side POD 2.

in this screenshot POD 1 is in maintenance and not in use, so no session data showing.

as you can see the horizon session data is drawn in the same interval as the hypervisor data. So when you zoom, change the timeframe etc etc it all correlates!

Little tip: Change the dashboard variables to reflect the correct vCenters..

Well this was fun, hope it helps someone!

I created this blog based on my notes.. Please contact me if there are any questions or remarks.