ViewCLI | Find users & assigned VM that did not login for more than 90 days

I didn’t find this anywhere else so i decided to create my own ViewCLI script.

Just run it against a View Event Database to get all information you need.

Please note:

If there is more than one machine assigned to a user it will show “System.Object[]” instead of a machine name.

You can still use “get-desktopVM | where { $_.user_displayname -eq “domain\username” } to list all machines in that case.

It actually results users that did login to the broker. This is enough for me as i don’t need the agent_login itself.

Just make sure you edit the sql query if you want to see agent connects.

Let me know if you have any questions or problems:

 

<#
.SYNOPSIS
Check environment for users login times and get machines with no login since more than 90days.
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory=$False)]
    [string]$choice
)

#generate a timestamp that can be used in filename
Function logstamp {
$now=get-Date
$yr=$now.Year.ToString()
$mo=$now.Month.ToString()
$dy=$now.Day.ToString()
$hr=$now.Hour.ToString()
$mi=$now.Minute.ToString()
if ($mo.length -lt 2) {
$mo="0"+$mo #pad single digit months with leading zero
}
if ($dy.length -lt 2) {
$dy="0"+$dy #pad single digit day with leading zero
}
if ($hr.length -lt 2) {
$hr="0"+$hr #pad single digit hour with leading zero
}
if ($mi.length -lt 2) {
$mi="0"+$mi #pad single digit minute with leading zero
}
Write-Output $yr$mo$dy$hr$mi
}

#variables - modify accordingly
$dbserver = ""
$user = ""
$pwd = read-host 'View Event DB Password' -AsSecureString
$database = "VIEWEVENTS"
$connectionString = "Server=$dbserver;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
$domain = ""
#this query actually gets all users that did not login between today and -90 days but before -90 days until -180 days.
#you could choose to increase or decrease the -180 as you want.
$query = 
"
SELECT ModuleAndEventText 
FROM VE_event_historical 
WHERE (EventType = 'BROKER_USERLOGGEDIN') AND (Time BETWEEN dateadd(day,-180,getdate()) AND dateadd(day,-90,getdate())) 
EXCEPT 
SELECT ModuleAndEventText 
FROM VE_event_historical 
WHERE (EventType = 'BROKER_USERLOGGEDIN') AND (Time BETWEEN dateadd(day,-90,getdate()) AND dateadd(day,0,getdate()))
"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText  = $query
$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$connection.Close()

$properties = @{UserID = ''; VM=''}
$object = New-Object -TypeName PSObject -Property $properties

$collection=@()

$table | foreach-object {
    $objloop = $object.PSObject.Copy()
    #make sure to edit the 2nd -replace to match your domain
    $objloop.UserID = $_.ModuleAndEventText -replace " has logged in", "" -replace "User domain\\", ""
    $objloop.VM = get-desktopvm | where { $_.user_displayname -eq "$domain\$($objloop.UserID)" } | Select-Object -exp Name
    $collection += $objloop
    }

write-output "Logging output to InactiveDesktops_$timestamp.csv"

$timestamp = logstamp
$filename = "InactiveDesktops_$timestamp.csv"
$collection | Export-Csv $filename -NoTypeInformation