Powershell - Lync Map a SIP User to the IP Address it came from

i need testers on this one.... please try it if you want and let me know the results....

basically it will discover what sip users are registered against the particular FE server and report on its client version / sip address and the originating IP address..

can be helpful if you want to identify multiple  clients and where they are logging in from...

$computername = $env:COMPUTERNAME
$domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain()
$strdomain = $domain.name
$lyncserver = $computername + "." + $strdomain
Import-Module lync

$SqlServer = $lyncserver + "\RTCLOCAL"
$SqlCatalog = "RTCDYN"
$SqlQuery = "select * from RegistrarEndpoint"
 
#*************** 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()
#Clear

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

foreach ($t in $tempstore)
{
 if ($t.isserversource -ne "False")
 {
        $bytearray0 = $t.clientapp
        $bytearray1 = $t.contactinfo
 $bytearray2 = $t.sipheaderfrom
 $EncodingType = "System.Text.ASCIIEncoding"
 $Encode = new-object $EncodingType
 $clientapp = $Encode.GetString($ByteArray0)
        $contactinfo = $encode.getstring($bytearray1)
 $sipheaderfrom = $encode.getstring($bytearray2)
 
 $c = $contactinfo.split(';')
 $t = $c[0].split(':')
 $clientip = $t[1]
 write-host "SIP USER   : `t`t" $sipheaderfrom
   write-host "Client IP  : `t`t" $clientip    
 write-host "Client App : `t`t" $clientapp 
        
 }
}

Comments

• Anonymous
  February 05, 2013
  rtcdyn.dbo.RegistrarEndpoint.SipHeaderFrom is the users SIP address not an IP address.

• Anonymous
  May 06, 2013
  IP address comes from contactinfo table.  $contactinfo = $encode.getstring($bytearray1)

• Anonymous
  August 07, 2013
  this is very useful...but is there any way to export the results in a file?

• Anonymous
  October 14, 2013
  The comment has been removed

• Anonymous
  October 15, 2013
  I didn't get the IP addresss. And i can get the same information for the contactinfo for the same user signed in differenent places. i don't think this colume stores the IP address.

• Anonymous
  October 12, 2014
  I'm not getting IP information either. The ContactInfo variable in the rtcdyn.dbo.RegistrarEndpoint table only stores in the SipAddress. I'm running 2013, could this be a 2010 vs 2013 thing?

• Anonymous
  August 07, 2015
  As stated above by Anup Swain
  
  IP address comes from contactinfo table.
  
  $contactinfo = $encode.getstring($bytearray1)

• Anonymous
  August 07, 2015
  It would be good though to understand how to compile this code in something usable :)

• Anonymous
  September 15, 2015
  Thank you for the Script.
  I also don't see the IP listed. Yes, I know it comes from $contactinfo = $encode.getstring($bytearray1) but it is not listed after running the script. Is there a way to see the IP of the user session?
  
  Thanks
  

• Anonymous
  February 09, 2016
  I'm not seeing any internal IP addresses, but I do see the external addresses. What are the criteria that Lync uses to populate this field? Is there possibly a different field for internal IP's? I like being able to pull the external IP addresses, but I am also looking for the internal IP's. Nice work John. I'll talk to someone who knows SQL and see if they can point me to the correct field for the internal IP's.