Powershell - Lync Get All Users Last Registration Time From Front End

this takes doug's concept (https://blogs.technet.com/b/dodeitte/archive/2011/05/11/how-to-get-the-last-time-a-user-registered-with-a-front-end.aspx)  and just runs it in powershell query and outputs the results to a text file to allow you to search

its simple but effective :)

 

param ([parameter(mandatory=$true)] [string]$lyncsrvfqdn)

$outputfile = "LyncUserLastLogon.txt"
$outputdir = $env:Userprofile + "\Desktop"
$output = $outputdir + "\" + $outputfile

$SqlServer = $lyncsrvfqdn + "\RTCLOCAL"
$SqlCatalog = "RTCDYN"
$SqlQuery = "SELECT rtc.dbo.Resource.UserAtHost, rtcdyn.dbo.HomedResourceDynamic.LastNewRegisterTime FROM rtcdyn.dbo.HomedResourceDynamic INNER JOIN rtc.dbo.Resource on rtc.dbo.Resource.ResourceId = rtcdyn.dbo.HomedResourceDynamic.OwnerId INNER JOIN rtcdyn.dbo.RegistrarEndpoint on rtcdyn.dbo.RegistrarEndpoint.OwnerId = rtcdyn.dbo.HomedResourceDynamic.OwnerId WHERE IsServerSource = 0 ORDER BY UserAtHost"
 
#*************** Query Above *******************************************************************************
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; 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
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

$logondata = $dataset.tables[0].rows

$logondata |out-file $output