Powershell Script - Lync Dump Address Book From SQL Database

  • [アーティクル]

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

  • Anonymous
    February 08, 2013
    Thanks for this, really useful. Worked on Lync 2013 RTM too. Tom