Parallel Universe - MS Tech Blog

The ramblings of a PFE

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

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

  • Comments 4
  • Likes

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
  • works with 2010 and 2013

  • Good One.

  • 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

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

    Lync Srv 2010 Std

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment