Export address book directly from the server in Lync 2010/2013

While working with address book in Lync Server, sometimes its very important and convinient to know, what exactly Lync Server have in its database regarding address book. You will need this, for example, when you working with Active Directory attributes that will be shown in contact card in Lync client.
Below you will find the script that will dig into the SQL database and get the "raw" address book (remember that Lync Server update its address book every 5 minutes by default, and it stores the data in SQL DB). The script is very slow, but anyway that better that nothing.

$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
 
}

Tags: script, powershell (ru), lync (ru), lync 2013 (ru), lync 2010 (ru)

PrintEmail

Add comment


Security code
Refresh