Export OCS Conferencing Users with a Registered Conference ID & PIN

If you are exploiting the full capabilities of Office Communicator Server 2007 (now called Lync 2010), you have it connected to a telephony gateway and have enabled users to register for audio conferencing. To determine who has signed up and registered a PIN, the following PowerShell script will return a comma separated value file with some basic information. In this example, it uses System.Data.SQLClient to assist in the SQL query against the rtc database to pull the e-mail address associated to the registered row. Using that e-mail address, I craft an LDAP query using Microsoft ActiveX Data Object (there are other methods) and targeting a Global Catalog (GC://) to perform the query. The purpose of using the Global Catalog to perform the query relates to a multi-domain forest environment and the proxyAddresses attribute is a member of the partial attribute set contained within the Global Catalog. All the attributes provided in this script also reside in the partial attribute set. If you were to change up the returned attributes, please be aware that you need to consult the partial attribute set to ensure you are able to retrieve the attributes you want. If they are not, you can either chase referrals or use the returned distinguisedName to perform an LDAP:// query against a specific domain.

$ocsConferencingUsers = @()
$ocsConferencingEntries = @()

$sqlConnection = New-Object System.Data.SQLClient.SQLConnection
$sqlConnection.ConnectionString = "server=sqlserver.ad.mydomain.local;database=rtc;trusted_connection=true;"
$sqlConnection.Open()

$sqlCommand = New-Object System.Data.SQLClient.SQLCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = "SELECT UserAtHost FROM dbo.Resource, dbo.UserPinMembership WHERE dbo.Resource.ResourceId = dbo.UserPinMembership.ResourceId"

$sqlDataReader = $sqlCommand.ExecuteReader()

if($sqlDataReader.HasRows -eq  $true) {
 while($sqlDataReader.Read()) {
  $ocsConferencingEntries += $sqlDataReader.GetValue(0)
 }
} else {
 Write-Warning "Unable to obtain OCS data from SQL"
 exit
}

$objectConnection = New-Object -comObject "ADODB.Connection"
$objectCommand = New-Object -comObject "ADODB.Command"
$objectConnection.Open("Provider=ADsDSOObject;")
$objectCommand.ActiveConnection = $objectConnection

$forestRootDn = ([ADSI]("LDAP://" + (([System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()).name) + "/rootDSE")).defaultNamingContext

$ldapBase = "GC://$forestRootDn"
$ldapAttr = "distinguishedName,sAMAccountName,givenName,sn,mail,telephoneNumber,streetAddress,l,st,postalCode,c"
$ldapScope = "subtree"

foreach($ocsConferencingEntry in $ocsConferencingEntries) {

 $ldapFilter = "(&(objectClass=user)(proxyAddresses=smtp:$ocsConferencingEntry))"
 $ldapQuery = "<$ldapBase>;$ldapFilter;$ldapAttr;$ldapScope"
 $objectCommand.CommandText = $ldapQuery
 $objectRecordSet = $objectCommand.Execute()

 while(!$objectRecordSet.EOF) {
  $userObjectDn = $objectRecordSet.Fields.Item('distinguishedName').Value
  $userObjectDomain = ((($userObjectDn -replace "(.*?)DC=(.*)",'$2') -replace "DC=","") -replace ",",".")
   
  $ocsConferencingUser = New-Object -typeName PSObject
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "domain" -value $userObjectDomain.Split(".")[0]
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "sAMAccountName" -value $objectRecordSet.Fields.Item('sAMAccountName').Value
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "firstName" -value $objectRecordSet.Fields.Item('givenName').Value
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "lastName" -value $objectRecordSet.Fields.Item('sn').Value
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "eMail" -value $objectRecordSet.Fields.Item('mail').Value
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "telephoneNumber" -value $objectRecordSet.Fields.Item('telephoneNumber').Value
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "streetAddress" -value ($objectRecordSet.Fields.Item('streetAddress').Value -replace "`r`n",", ")
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "city" -value $objectRecordSet.Fields.Item('l').Value
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "state" -value $objectRecordSet.Fields.Item('st').Value
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "zipCode" -value $objectRecordSet.Fields.Item('postalCode').Value
  Add-Member -inputObject $ocsConferencingUser -type NoteProperty -name "country" -value $objectRecordSet.Fields.Item('c').Value
  $ocsConferencingUsers += $ocsConferencingUser

  $objectRecordSet.MoveNext()
 }
}

$ocsConferencingUsers | Export-Csv -path "ocsConferencingUsers.csv" -noTypeInformation