Getting Lync User Connection Information

I posted in the past about how to identify where users are connecting to in Lync. I have used this script several times this week to find out what version users were using and what computer they were using. The troubleshooting process is a great deal easier when you don’t have to depend on users to give you the information.

I spent some time adding to the previous script and added an nslookup to get the client computer name along with the IP, and then I added some historical logon information.

Between the two pieces of the script, I ended up using the local SQL on Front-End servers and then the LcsCDR database. It was actually a fun couple of hours of testing and playing.

You can download the latest script here, or you can read it and take what you want as shown below, and then modify it as needed to fit your environment. At the very bottom of this post is an example of the output.

CLS
# User Name to Search
$User = “russ@infrastructurehelp.com”
# SQL Server for historical connection information
$SqlServer = “SqlServerName\InstanceName”

Write-Host ” “
Write-Host “Retrieving Current Lync Client Connections for $User” -foregroundcolor red
#Find the Front-End server that is supposed to be used by the user
$FirstPriority = Get-CsUserPoolInfo $User | Select –ExpandProperty PrimaryPoolMachinesInPreferredOrder | Select fqdn -First 1
# The SQL server in this next variable is for the SQL express on the Front-End
$ServerName = $FirstPriority.fqdn

# This query is for the SQL Express to get current connection information
$SQLQuery = “
    Select
        IsServerSource,
        ClientApp,
        ContactInfo,
        SipHeaderFrom
    From RegistrarEndpoint
    WHERE SipHeaderFrom LIKE ‘%$User%'”
                                               
$Connection = New-Object system.data.sqlclient.sqlconnection
$Connection.connectionString=”Data Source=$ServerName\RTCLOCAL;Initial Catalog=RTCDyn;Integrated Security=SSPI”
$Connection.open()
$Command = $Connection.CreateCommand()
$Command.Commandtext = $SqlQuery
$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $Command
$Dataset = New-Object System.Data.Dataset
$DataAdapter.Fill($Dataset)
# $Dataset.Tables[0] | Export-CSV UserIP.csv -notype
$Connection.close()
$Connection = $null

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

ForEach ($r in $Results1){

    If ($r.IsServerSource -ne “False”){
        $ClientApp = $r.ClientApp
        $ContactInfo = $r.ContactInfo
        $SipHeaderFrom = $r.SipHeaderFrom
        $EncodingType = “System.Text.ASCIIEncoding”
        $Encode = new-object $EncodingType
        $ClientApp = $Encode.GetString($ClientApp)
        $ContactInfo = $encode.getstring($ContactInfo)
        $SipHeaderFrom = $encode.getstring($SipHeaderFrom)
        # Strip garbage from $ContactInfo to get IP
            $CI = $ContactInfo.split(‘;’)
            $CI2 = $CI[0]
            $CI3 = $CI2.split(‘:’)
            $ClientIp = $CI3[1]
        #Strip garbage from $Sip User to get SIP address in SMTP format
            $Sip = $SipHeaderFrom.split(‘:’)
            $Sip2 = $Sip[1]
            $Sip3 = $Sip2.split(‘>’)
            $SipAddress = $Sip3[0]
        #Find computer name
            $Name = nslookup $ClientIp
            ForEach ($n in $Name){
                If($n -ilike “name*”){
                    $Name = $n.split(‘ ‘)
                    $Name2 = $Name[4]
                    $Name3 = $Name2.split(‘.’)
                    $CompName = $Name3[0]
                }
            }
        Write-host “UserURI         : $SipAddress”
        Write-host “IPAddress       : $ClientIp”
        Write-Host “ComputerName    : $CompName”
        Write-host “Version         : $ClientApp” 
    }

    Write-Host ” “
}

Write-Host ” “
Write-Host “Retrieving Recent Lync Client Connections for $User” -foregroundcolor red

# The second query is used to get recent connection information, changing to TOP 10 or more will retrieve more results
$SQLQuery = “
SELECT TOP 5
    u.UserUri,
    r.RegisterTime,
    r.DeRegisterTime,
    v.Version,
    s.ServerFQDN as Server,
    p.PoolFQDN as Pool
FROM Registration as r
    JOIN Users as u on r.UserId = u.UserId
    JOIN ClientVersions as v on r.ClientVersionId = v.VersionId
    JOIN Servers as s on r.RegistrarId = s.ServerId
    JOIN Pools as p on r.PoolId = p.PoolId
WHERE u.UserUri = ‘$User’
ORDER BY r.RegisterTime DESC”

$Connection = new-object system.data.sqlclient.sqlconnection
$Connection.connectionString=”Data Source=$SqlServer;Initial Catalog=LcsCDR;Integrated Security=SSPI”
$Connection.open()
$Command = $Connection.CreateCommand()
$Command.Commandtext = $SqlQuery
$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $Command
$Dataset = New-Object System.Data.Dataset
$DataAdapter.Fill($Dataset)
# $Dataset.Tables[0] | Export-CSV TempList.csv -notype
$Connection.close()
$Connection = $null

$Results2 = $Dataset.Tables[0].rows

$Results2

I played with the output a bit to make it all align. Basically, what you get is the first section of the output shows the two locations where I was currently logged in at the time I ran the script. The second part shows the last five logons. The number can easily be changed in the script by modifying the TOP 5 part of the SELECT statement.

Retrieving Current Lync Client Connections for russ@infrastructurehelp.com
2
UserURI         : russ@infrastructurehelp.com
IPAddress       : 10.10.10.41
ComputerName    : Desktop1
Version         : UCCAPI/15.0.4675.1000 OC/15.0.4675.1000 (Microsoft Lync)

UserURI         : russ@infrastructurehelp.com
IPAddress       : 10.10.10.42
ComputerName    : Desktop2
Version         : UCCAPI/15.0.4675.1000 OC/15.0.4675.1000 (Microsoft Lync)

Retrieving Recent Lync Client Connections for russ@infrastructurehelp.com
10

UserUri        : russ@infrastructurehelp.com
RegisterTime   : 2/24/2015 6:12:58 PM
DeRegisterTime : 2/24/2015 6:13:30 PM
Version        : UCCAPI/4.0.7577.4409 GCAT/4.0.7577.4398
Server         : fe1.infrastructurehelp.com
Pool           : pool01.infrastructurehelp.com

UserUri        : russ@infrastructurehelp.com
RegisterTime   : 2/24/2015 6:05:35 PM
DeRegisterTime : 2/24/2015 6:06:08 PM
Version        : UCCAPI/4.0.7577.4409 GCAT/4.0.7577.4398
Server         : fe1.infrastructurehelp.com
Pool           : pool01.infrastructurehelp.com

UserUri        : russ@infrastructurehelp.com
RegisterTime   : 2/24/2015 6:05:14 PM
DeRegisterTime : 2/24/2015 6:14:40 PM
Version        : UCCAPI/4.0.7577.4409 GCC/4.0.7577.4398
Server         : fe1.infrastructurehelp.com
Pool           : pool01.infrastructurehelp.com

UserUri        : russ@infrastructurehelp.com
RegisterTime   : 2/24/2015 2:19:49 AM
DeRegisterTime : 2/24/2015 2:28:02 AM
Version        : UCCAPI/4.0.7577.0 GCAT/4.0.7577.0
Server         : fe1.infrastructurehelp.com
Pool           : pool01.infrastructurehelp.com

UserUri        : russ@infrastructurehelp.com
RegisterTime   : 2/23/2015 7:15:11 PM
DeRegisterTime :
Version        : UCCAPI/15.0.4675.1000 OC/15.0.4675.1000 (Microsoft Lync)
Server         : fe1.infrastructurehelp.com
Pool           : pool01.infrastructurehelp.com

Advertisements
This entry was posted in Lync. Bookmark the permalink.

2 Responses to Getting Lync User Connection Information

  1. Marek says:

    Hi Russ, would you mind to remake it for all users, from sql DB (all history)?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s