Ciao
I find on the internet this powershell script that find the info in EVENT VIEW DB
#DOMAIN: Change it with your domain name.
#domain.org: Change it with your domain FQDN.
#Session Type: Desktop is default but you can remove it or add another condition to that.
#Output File Address: The last line must be changed.
$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
This script create a similar CSV file :
