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
*/