VMware Horizon View Connection (Session) Duration Report

Horizon_View_Connection_Session_Duration.ps1

$SQLServer = 'localhost'
$SQLDBName = 'VDIEvents'
$SQLQuery = "select [EventID],
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([EventType],'AGENT_',''),'ENDED','DISCONNECT'),'RECONNECTED','CONNECT'),'DISCONNECTED','DISCONNECT'),'CONNECTED','CONNECT') as 'Type',
[Time],
REPLACE(REPLACE (REPLACE([UserDisplayName],'domain.org',''),'\',''),'DOMAIN','') as UserName,
[DesktopDisplayName]
from dbo.user_events_hist
where DATEDIFF(Day,Time,GETDATE()) = 1 and SessionType = 'DESKTOP'
order by [UserName],[Time]"
 
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.Connection = $SQLConnection
$SQLAdapter = New-Object System.Data.SQLClient.SQLDataAdapter
$SQLAdapter.SelectCommand = $SQLCmd
$SQLAdapter.SelectCommand.CommandTimeout=600
$DataSet = New-Object System.Data.DataSet
$SQLAdapter.Fill($DataSet)
$SQLConnection.Close()
 
$DataTable=$DataSet.Tables[0]
$UserNames=$DataTable.UserName | Select-Object -Unique | Where-Object {$_ -notlike "*Administrator"}
$DataView=New-Object System.Data.DataView($DataSet.Tables[0])
 
#General Definitions
[DateTime]$BeginDay=Get-Date -Hour 0 -Minute 00 -Second 00
$BeginDay=$BeginDay.AddDays(-1)
[DateTime]$EndDay=$BeginDay.AddDays(1).AddSeconds(-1)
 
#Result Data Table Definition
$DataTableResult=New-Object System.Data.DataTable
$ReportDate=New-Object system.Data.DataColumn Date,([String])
$ReportName=New-Object system.Data.DataColumn UserName,([String])
$ReportStart=New-Object system.Data.DataColumn Start,([String])
$ReportEnd=New-Object system.Data.DataColumn End,([String])
$ReportDuration=New-Object system.Data.DataColumn Duration,([Int])
$DataTableResult.Columns.Add($ReportDate)
$DataTableResult.Columns.Add($ReportName)
$DataTableResult.Columns.Add($ReportStart)
$DataTableResult.Columns.Add($ReportEnd)
$DataTableResult.Columns.Add($ReportDuration)
$DataTableResult.Clear()
 
 
ForEach ($UserName in $UserNames)
{
    $DataView.RowFilter = "UserName = '$UserName'"
    [Int]$DataViewCount=$DataView.Count
    [Int]$Counter=0
    $Row=$DataTableResult.NewRow()
    $Row.Date=$BeginDay.ToShortDateString()
    $Row.UserName=$UserName
    Do
    {
##If first record is disconnect or connect.
        if ($Counter -eq 0 )
        {
            if ($DataView[0].Type -eq "Disconnect")
            {
                $Row.Duration=0
                [DateTime]$TempTime=$DataView[0].Time
                $TimeDiff=New-TimeSpan -Start $BeginDay -End $TempTime
                $Row.Duration=$TimeDiff.TotalMinutes
                $Row.Start=$BeginDay.ToShortTimeString()
            }
            ElseIf ($DataView[0].Type -eq "Connect")
            {
                If (($DataViewCount -1) -gt 0)
                {
                    $Row.Duration=0
                    [DateTime]$TempTime1=$DataView[$Counter].Time
                    [DateTime]$TempTime2=$DataView[$Counter +1].Time
                    $TimeDiff=New-TimeSpan -Start $TempTime1 -End $TempTime2
                    $Row.Duration=$TimeDiff.TotalMinutes + $Row.Duration
                }
                Else
                {
                    $Row.Duration=0
                }
                $Row.Start=$DataView[0].Time.ToShortTimeString()
            }
        }
##If first record is disconnect or connect and it's last record as well.
        if ($Counter -eq 0 -And ($DataViewCount -1) -eq 0)
        {
            if ($DataView[0].Type -eq "Disconnect")
            {
                $Row.End=$DataView[0].Time.ToShortTimeString()
            }
            ElseIf ($DataView[0].Type -eq "Connect")
            {
                [DateTime]$TempTime=$DataView[0].Time
                $TimeDiff=New-TimeSpan -Start $TempTime -End $EndDay
                $Row.Duration=$TimeDiff.TotalMinutes + $Row.Duration
                $Row.End=$EndDay.ToShortTimeString()
            }
        }
##If the record is the last record of some records.
       if ($Counter -eq ($DataViewCount -1) -And $Counter -ne 0 -And ($DataViewCount -1) -gt 0 )
        {
            if ($DataView[$DataViewCount -1].Type -eq "Disconnect")
            {
                $Row.End=$DataView[$DataViewCount -1].Time.ToShortTimeString()
            }
            ElseIf ($DataView[$DataViewCount -1].Type -eq "Connect")
            {
                [DateTime]$TempTime=$DataView[$DataViewCount -1].Time
                $TimeDiff=New-TimeSpan -Start $TempTime -End $EndDay
                $Row.Duration=$TimeDiff.TotalMinutes + $Row.Duration
                $Row.End=$EndDay.ToShortTimeString()
            }
        }
##If the record is not first or last or single record and it's value is connect.
        If ($Counter -gt 0 -And ($DataViewCount -1) -gt 0 -And ($Counter -lt ($DataViewCount -1)))
        {
            if ($DataView[$Counter].Type -eq "Connect")
            {
                [DateTime]$TempTime1=$DataView[$Counter].Time
                [DateTime]$TempTime2=$DataView[$Counter +1].Time
                $TimeDiff=New-TimeSpan -Start $TempTime1 -End $TempTime2
                $Row.Duration=$TimeDiff.TotalMinutes + $Row.Duration
            }
         }         
        $Counter++
    }While ($Counter -ne $DataViewCount -And $DataViewCount -ne 0)
               
    $DataTableResult.Rows.Add($Row)
}
$FileName=$BeginDay.ToShortDateString()
$FileName=$FileName -replace "/","."
$DataTableResult | Export-CSV -Path "C:\Users\Administrator\Desktop\Reports\VDIReport_$FileName.CSV" -NoTypeInformation -Force