:::: MENU ::::

VMware Horizon Reporting

This script should be run on against the Vmware events database. Reports highest number of concurrent users, with email instructions. –Replace indicates changes that must be made for use in your environment.

Use MyDatabase -- replace with database
Declare @Numberofdays INT = 90 -- replace with desired time range, default is 90 days.

Select ROW_NUMBER() OVER(ORDER BY TIME ASC) AS ROW,ModuleAndEventText,Time,EventID,EventType
INTO #ALLEVENTSTEMP
From MyEventsTable -- replace with table
Where (EventType='Broker_USERLOGGEDIN'OR EventType='BROKER_USERLOGGEDOUT') And (Time>= (GetDate() - @Numberofdays))
Order by Time ASC

Declare @NumberofRowsLeft INT = (Select Count(*) from #ALLEVENTSTEMP)

ALTER TABLE #ALLEVENTSTEMP ADD Processed INT, ConnectionCount INT 
Declare @ConnectionCount INT = 0


While @NumberofRowsLeft	 > 0
Begin
	Declare @ModuleText nVarChar(255) = (Select Top 1 EventType from #ALLEVENTSTEMP Where Processed is NULL)
if @ModuleText = 'Broker_USERLOGGEDIN'
	Begin
	Set @ConnectionCount = (@ConnectionCount + 1)
	End
ELSE
	BEGIN
	if  @ModuleText = 'Broker_USERLOGGEDOUT'
		Begin
		Set @ConnectionCount = (@ConnectionCount - 1)
		End
	END
Set @NumberofRowsLeft = (@NumberofRowsLeft - 1)
Update #ALLEVENTSTEMP 
set Processed = 1, ConnectionCount = @ConnectionCount
Where EventID = (Select top 1 EventID from #ALLEVENTSTEMP where Processed is NULL)
END


Declare @MinimumConnection INT = (SELECT MIN(ConnectionCount) FROM #ALLEVENTSTEMP)

IF @MinimumConnection < 0
Begin
Update #ALLEVENTSTEMP
Set ConnectionCount = ConnectionCount + @MinimumConnection*-1
END
Declare @MaxNumberofConcurrentUsers INT = ( Select MAX(ConnectionCount) from #ALLEVENTSTEMP)

Declare @PeakUseage TABLE (Row INT, EventID INT, Time DateTime, EventText NVARCHAR(255), ConnectionCount INT, TheDayofTheWeek NVARCHAR(255))

Insert INTO @PeakUseage (Row, EventID, Time, EventText,ConnectionCount)
Select Row,EventID, Time, ModuleAndEventText,ConnectionCount FROM #alleventstemp 
Where ConnectionCount = @MaxNumberofConcurrentUsers

Declare @NumberofSessions INT = (Select COUNT(*) from #ALLEVENTSTEMP Where EventType='Broker_USERLOGGEDIN')
	Declare @NumberofPeaks FLOAT = (Select COUNT(EventID) from @PeakUseage)
	Declare @PercentageofPeaks Decimal(4,2) = (Cast (@NumberofPeaks AS FLOAT)/CAST(@NumberofSessions AS FLOAT))*100
	Declare @PercentageofPeaksPercent VARCHAR(255)= Cast(@PercentageofPeaks AS NVARCHAR(255)) + '%'

Update @PeakUseage
Set TheDayofTheWeek = DATENAME(weekday,Time)
Select * from @PeakUseage

---Anything Below this line is optional ---

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)


SET @xml = CAST(( SELECT Row AS 'td','',EventID AS 'td','',
       (CAST(Time as VARCHAR(50))) AS 'td','', EventText AS 'td','', ConnectionCount AS 'td','', (CAST(TheDayofTheWeek as VARCHAR(50))) AS 'td'
FROM @PeakUseage Order BY Time ASC 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))


SET @body ='<html><body>For the past ' + (Cast (@Numberofdays as nvarchar(max)))+ ' days…<p>The greatest number of concurrent users is: <b>'
Set @body = @body + (Cast(@MaxNumberofConcurrentUsers AS nvarchar(max)))+ '</b>'
Set @body = @body + '<br>This happened ' + '<b>' + (Cast(@NumberofPeaks AS nvarchar(max)))+ '</b> times.'
Set @body = @body + '<br>The total number of login events for sessions is: <b>' + Cast(@NumberofSessions AS nvarchar(max))+ '</b>'
Set @body = @body + '<br>The percentage of sessions that reach peak useage is: <b>'+ @PercentageofPeaksPercent + '</b>'
Set @body = @body + '<table border = 1>
<tr>
<th> Row </th> <th> EventID </th> <th> Time </th> <th> EventText </th> <th> ConnectionCount </th> <th> DayofWeek </th> </tr>'    

SET @body = @body + '<Center>' + @xml + '</Center>' +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Server Mail', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = 'Test@MyEmail.com', -- replace with intended recipients, separate each with a semicolon
@subject = 'Vmware Horizon Report' ;


/*
For troubleshooting:
select * from #ConcurrentConnections
Select * from #ALLEVENTSTEMP
drop table #alleventstemp
 */