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


this takes doug’s concept (http://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

 

 

Comments (4)

  1. Anonymous says:

    Good One.

  2. Anonymous says:

    works with 2010 and 2013

  3. Avi says:

    Good one, i’ve made some improvement in case you have SRV records with couple of nodes this will go over every node local DB and append to an array, which you can either output to file or use gridview

    param ([parameter(mandatory=$true)] [string]$lyncsrvfqdn)
    $DNS_Lookup = [System.Net.Dns]::GetHostAddresses($lyncsrvfqdn)
    Write-Host Server $lyncsrvfqdn leads to the following servers`:
    foreach ($Record in $DNS_Lookup) {Write-Host $Record.ipaddresstostring}
    $outputfile = “LyncUserLastLogon.csv”
    $outputfile = @”
    LyncUserLastLogon.csv
    “@
    $outputdir = $env:Userprofile + “Desktop”
    $output = $outputdir + “” + $outputfile
    $AllNodesDataARR = @()

    foreach ($Record in $DNS_Lookup)
    {
    $Node = $Record.ipaddresstostring
    $SqlServer = $Node + “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) | Out-Null
    Write-Host ($dataset.tables[0].rows).count Records found at $lyncsrvfqdn node $Node
    $AllNodesDataARR += ($DataSet.Tables | select -ExpandProperty rows)
    Write-Host Added ($dataset.tables[0].rows).count Records to Array`, Current count of items in Array is $AllNodesDataARR.Count
    $SqlConnection.Close()

    }
    Write-Host Completed Total users connect is $AllNodesDataARR.count on all nodes
    $AllNodesDataARR | Out-GridView -Title “User Connected to $lyncsrvfqdn :”
    #$AllNodesDataARR | Export-Csv -Append -Path $output

  4. Diego says:

    This script won’t show me all users, only around 80 out of 300+

    Lync Srv 2010 Std