Parallel Universe - MS Tech Blog

The ramblings of a PFE

Powershell Script - Lync Dump Address Book From SQL Database

Powershell Script - Lync Dump Address Book From SQL Database

  • Comments 1
  • Likes

i have had lots of address book issues the last few days and put together this quickly (and i know there are cleaner ways of doing it!)

 

basically it probes the sql backend server dumps out what information the update-csuserdatabase populates from AD

This for me anyway gave a stepping stone to figuring out how far the replication was actually happening and which part of the address book services was failing and why!

 

here is the script

and run it from a front end server and then you can view the ABReport.txt for all the entries it has

 

*******************************************************************************************************************************************************************************************************

 

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

$userdb = (get-csservice -userdatabase).poolfqdn
$dbinst = (get-csservice -userdatabase).sqlinstancename

$SqlServer = $userdb + "\" + $dbinst
$SqlCatalog = "RTCab"
$SqlQuery = "select * from AbAttribute"
$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
write-host "Populating SQL Data From AbAttribute..."
$sql = $SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$tempstoreABAttribute = $dataset.tables[0].rows

$abattribute = new-object psobject

for ($count=0;$count -lt $tempstoreAbattribute.count;$count++)

{
  
   $id = $tempstoreAbattribute[$count].id
   $name = $tempstoreAbattribute[$count].name
   $abattribute | Add-member -Name $id -MemberType Noteproperty -value $name

}

$userdb = (get-csservice -userdatabase).poolfqdn
$dbinst = (get-csservice -userdatabase).sqlinstancename

$SqlServer = $userdb + "\" + $dbinst
$SqlCatalog = "RTCab"
$SqlQuery = "select * from AbUserEntry"
$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
write-host "Populating SQL Data for AbUserEntry..."
$sql = $SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$tempstoreAbuserEntry = $dataset.tables[0].rows

 

 

$userdb = (get-csservice -userdatabase).poolfqdn
$dbinst = (get-csservice -userdatabase).sqlinstancename

$SqlServer = $userdb + "\" + $dbinst
$SqlCatalog = "RTCab"
$SqlQuery = "select * from AbAttributeValue"
$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
write-host "Populating SQL Data for AbAttributevalue..."
$sql = $SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$tempstoreAbAttributeValue = $dataset.tables[0].rows

 

foreach ($abuserentry in $tempstoreabuserentry)
{
 
 
  $userabentry = new-object psobject

  $userid = $abuserentry.userid
  $guid = $abuserentry.userguid

  foreach ($abattribute in $tempstoreabattributevalue)
  {
 
 

    if ($abattribute.userid -eq $userid)
    {
 
 #$abattribute

 switch ($abattribute.attrid)
 {
   1 {$attr = "GivenName"}
   2 {$attr = "sn"}
   3 {$attr = "displayname"}
          4 {$attr = "title"}
      5 {$attr = "mailnickname"}
     6 {$attr = "company"}
     7 {$attr = "physicaldeliveryofficename"}
     8 {$attr = "msrtcsip-primaryuseraddress"}
     9 {$attr = "telephonenumber"}
   10 {$attr = "homephone"}
   11 {$attr = "mobile"}
   12 {$attr = "othertelephone"}
   13 {$attr = "ipphone"}
   14 {$attr = "mail"}
   15 {$attr = "grouptype"}
   16 {$attr = "department"}
   17 {$attr = "description"}
   18 {$attr = "manager"}
   19 {$attr = "proxyaddresses"}
   20 {$attr = "msexchhidefromaddresslists"}
   99 {$attr = "entryid"}
        } 
 
 $userabentry |Add-member -Name $attr -membertype noteproperty -value $abattribute.value -force

    }
 
  }
 $userabentry |out-file -append ABreport.txt
 
 
       

}

 

Comments
  • Thanks for this, really useful.

    Worked on Lync 2013 RTM too.

    Tom

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