Submitted by Scott Stubberfield and Nick Smith, Microsoft

So just who is connected to your Registrar pool, and how many people are connected to Pool A vs. Pool B? Don’t ask us; we have no idea whatsoever. Instead, you should ask Scott Stubberfield and Nick Smith, who’ve written a script that can contact your front-end pools and return this distribution information. Their script connects to the backend Microsoft Lync Server 2010 databases and retrieves information about the endpoints currently connected to two different Registrar pools. The retrieved information, sorted by client version and by user name, is then saved to a comma-separated values file named PoolDistribution.csv.

Here’s the code:

#Defined Connection String

$connstring = "server=p10-CSFE01\rtclocal;database=rtcdyn;`
    trusted_connection=true;"

$connstring2 = "server=p10-CSFE02\rtclocal;database=rtcdyn; `
    trusted_connection=true;"

#Define SQL Command

$command = New-Object System.Data.SqlClient.SqlCommand

$command.CommandText = "Select (cast (RE.ClientApp as `
    varchar (100))) as ClientVersion, `

    R.UserAtHost as UserName, `

    Reg.Fqdn `

    From `

    rtcdyn.dbo.RegistrarEndpoint RE `

    Inner Join `

    rtc.dbo.Resource R on R.ResourceId = RE.OwnerId `

    Inner Join `

    rtcdyn.dbo.Registrar Reg on Reg.RegistrarId =   `
    RE.PrimaryRegistrarClusterId `

    Order By ClientVersion, UserName "

#Make the connection to Server 1

$connection = New-Object System.Data.SqlClient.SqlConnection

$connection.ConnectionString = $connstring

$connection.Open()

$command.Connection = $connection

$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter

$sqladapter.SelectCommand = $command

$results = New-Object System.Data.Dataset

$recordcount=$sqladapter.Fill($results)

$connection.Close()

$overallrecords = $overallrecords + $Results.Tables[0]

#Make the connection to Server 2

$connection.ConnectionString = $connstring2

$connection.Open()

$command.Connection = $connection

$results = New-Object System.Data.Dataset

$recordcount=$sqladapter.Fill($results)

$connection.Close()

$overallrecords = $overallrecords + $Results.Tables[0]

#End Section 2

$overallrecords | Export-Csv "PoolDistribution.csv"

Write-Host -ForegroundColor Green "Query complete"

To use this script, copy the code shown above, paste it into a text editor (like Notepad) and then save the file with a .PS1 extension (for example, C:\Scripts\PoolDistribution.ps1). After that, you can run the script from within the Lync Server Management Shell simply by typing the full path to the .PS1 file and then pressing ENTER:

C:\Scripts\PoolDistribution.ps1

As written, the script is designed to connect to two different Registrar pools: p10-CSFE01 and p10-CSFE02. If you only want to connect to one Registrar pool, remove one of the pool names listed at the beginning of the script, and remove (or comment out) the following block of code:

#Make the connection to Server 2

$connection.ConnectionString = $connstring2

$connection.Open()

$command.Connection = $connection

$results = New-Object System.Data.Dataset

$recordcount=$sqladapter.Fill($results)

$connection.Close()

$overallrecords = $overallrecords + $Results.Tables[0]

#End Section 2

To connect to additional Registrar pools you must do two things. First, add the new pools to the set of pools listed at the beginning of the script. For example, if you need to add the pool p10-CSFE03 the first part of your script should look similar to this:

#Defined Connection String

$connstring = "server=p10-CSFE01\rtclocal;database=rtcdyn;`
    trusted_connection=true;"

$connstring2 = "server=p10-CSFE02\rtclocal;database=rtcdyn; `
    trusted_connection=true;"

$connstring3 = "server=p10-CSFE03\rtclocal;database=rtcdyn; `
    trusted_connection=true;"

Note the variable name: $connstring3.

Next, copy the following block of code, changing the comments so they refer to Server/Section 3, and using the variable $connstring3 instead of $connstring2. In other words:

#Make the connection to Server 3

$connection.ConnectionString = $connstring3

$connection.Open()

$command.Connection = $connection

$results = New-Object System.Data.Dataset

$recordcount=$sqladapter.Fill($results)

$connection.Close()

$overallrecords = $overallrecords + $Results.Tables[0]

#End Section 3

To add additional pools just repeat those steps as needed.

Submitted by Scott Stubberfield and Nick Smith, Microsoft

Windows PowerShell makes it easy for you to enable a new user for Microsoft Lync Server 2010, and makes it just as easy for you to move a single user account from one Registrar pool to another. But what if you need to perform these user management tasks on a whole bunch of users, some of whom need to be enabled for Lync Server and some of whom need to have their accounts moved to a different Registrar pool? What do you do then?

Scott Stubberfield and Nick Smith have come up with one solution: you simply put all this information in a text file and then use a PowerShell script to read that text file and then take the appropriate action. The script they put together reads a comma-separated values file (a file containing user information), loops through the collection of users listed on that file and then does one of two things:

·         If the MoveOrEnable field for a user is marked as Move, the script uses the Move-CsUser cmdlet to move the user account from its current Registrar pool to a new Registrar pool (the Target field).

·         If the MoveOrEnable field for a user is marked as Enable, the script uses the Enable-CsUser cmdlet to enable the user account for Lync Server. The script assigns the user to the Registrar pool indicated by the Target field, and gives the user the SIP address listed in the SipUri field.

For example, suppose the first user listed in the .CSV file has these property values:

Because MoveOrEnable is set to Enable, the script will enable the user for Lync Server, and will do so using the following command:

Enable-CsUser –Identity "seeuser11@p10.local" –RegistrarPool "cspool.p10.ca" –SipAddress "sip:seeuser11@p10.ca"

Nice, huh? And the best part is this: the script will take care of everything whether you have one user listed in the .CSV file or 100,000 users listed in the .CSV file.

Here’s what the code looks like:

param( [string] $importfile = $(Read-Host -prompt `
    "Please enter a file name"))

$importedusers = Import-CSV $importfile

$transcriptname = "MoveorEnableUsers" + `
    (Get-Date -format s).Replace(":","-") +".txt"

Start-Transcript $transcriptname

foreach ($importeduser in $importedusers)

    {

        if ($importeduser.MoveorEnable -eq "Move")

            {

                Move-CsUser $importeduser.SipUri -target `
                   $importeduser.Target -verbose

            }

        else

    {

        Enable-CsUser $importeduser.UPN -SipAddress `
            $importeduser.SipUri -RegistrarPool `
            $importeduser.Target -Verbose

    }

    }

Stop-Transcript

To make use of this script, copy the code to Notepad (or any other text editor), and then save the file using a .PS1 file extension (for example, C:\Scripts\MoveOrEnableUsers.ps1). In addition, you must create a .CSV file similar to this, and save that file to your hard drive as well:

SipUri,MoveorEnable,Target,UPN

sip:seeuser11@p10.ca,Enable,"cspool.p10.ca",seeuser11@p10.local

sip:seeuser12@p10.ca,Move,"cspool.p10.ca",seeuser12@p10.local

sip:seeuser13@p10.ca,Enable,"cspool.p10.ca",seeuser13@p10.local

sip:seeuser14@p10.ca,Enable,"cspool.p10.ca",seeuser14@p10.local

sip:seeuser15@p10.ca,Enable,"cspool.p10.ca",seeuser15@p10.local

sip:seeuser16@p10.ca,Enable,"cspool.p10.ca",seeuser16@p10.local

sip:seeuser17@p10.ca,Enable,"cspool.p10.ca",seeuser17@p10.local

sip:seeuser18@p10.ca,Move,"cspool.p10.ca",seeuser18@p10.local

sip:seeuser19@p10.ca,Enable,"cspool.p10.ca",seeuser19@p10.local

To run the script from within the Lync Server Management Shell, just type the full path to the .PS1 file and then press ENTER:

C:\Scripts\MoveOrEnableUsers.ps1

When the script starts, it will prompt you to enter the location of the .CSV file. Type in the file path (e.g., C:\Scripts\Users.csv) and press ENTER; in turn, the script will read the data from the .CSV and, as requested, either move or enable each user listed in the file.

As an added bonus, the script also maintains a detailed log of everything it does. That log will be stored in the current working directory, and will have a file name based on the current date and time. For example:

MoveorEnableUsers2010-06-10T09-33-03.txt

Submitted by Scott Stubberfield and Nick Smith, Microsoft

In order to take advantage of Enterprise Voice (Microsoft’s implementation of Voice over IP) users need, among other things, to be assigned a dial plan and a voice policy. Dial plans and voice policies can easily be assigned to individual users by using Lync Server PowerShell. But is the same thing true when it comes to assigning dial plans and voice policies to multiple users?

As it turns out, it is true: all you have to do is save the required information (user identity, dial plan name, and voice policy name) in a comma-separated values file and then write a script that reads in that information and assigns the appropriate dial plans and policies.

Check that: you don’t have to write a script to do this. That’s because Scott Stubberfield and Nick Smith have already written such a script for you. Their script reads a comma-separated values file and then takes each user listed in that file and assigns the user the appropriate dial plan and voice policy. For example, suppose the first user in the file has the following property values:

For this first user (sip:seeuser11@p10.ca) the script will assign the dial plan listed in the DialPlan field and the voice policy listed in the VoicePolicy field. In effect, that means the script will run the following two commands:

Grant-CsDialPlan –Identity "sip:seeuser11@p10.ca" –PolicyName "SEDE.see.local"

Grant-CsVoicePolicy –Identity "sip:seeuser11@p10.ca" –PolicyName "SeeUserVoicePolicy"

And then the process will repeat with the next user in the file.

Here’s what the script looks like:

param( [string] $importfile = $(Read-Host -prompt `

    "Please enter a file name"))

$importedusers = Import-Csv $importfile

$transcriptname = "GrantPolicies" + (Get-Date `

    -Format s).Replace(":","-") +".txt"

Start-Transcript $transcriptname

foreach ($importeduser in $importedusers)

    {

        Grant-CsDialPlan $importeduser.SipUri -PolicyName `
            $importeduser.DialPlan -Verbose

        Grant-CsVoicePolicy $importeduser.SipUri -PolicyName `
            $importeduser.VoicePolicy -Verbose

    }

Stop-Transcript

To make use of this script, copy the code to Notepad (or any other text editor), and then save the file using a .PS1 file extension (for example, C:\Scripts\GrantDialPlanAndVoicePolicy.ps1). In addition, you must create a .CSV file similar to this, and save that file to your hard drive as well:

sipuri,dialplan,voicepolicy

sip:seeuser11@p10.ca,"SEDE.see.local","SeeUserVoicePolicy"

sip:seeuser12@p10.ca,"SEDE.see.local","SeeUserVoicePolicy"

sip:seeuser13@p10.ca,"SEDE.see.local","SeeUserVoicePolicy"

sip:seeuser14@p10.ca,”SEDE.see.local","SeeUserVoicePolicy"

sip:seeuser15@p10.ca,"SEDE.see.local","SeeUserVoicePolicy"

sip:seeuser16@p10.ca,"SEDE.see.local","SeeUserVoicePolicy"

sip:seeuser17@p10.ca,"SEDE.see.local","SeeUserVoicePolicy"

sip:seeuser18@p10.ca,"SEDE.see.local","SeeUserVoicePolicy"

sip:seeuser19@p10.ca,"SEDE.see.local","SeeUserVoicePolicy"

To run the script from within the Lync Server Management Shell, simply type the full path to the .PS1 file and then press ENTER:

C:\Scripts\GrantDialPlanAndVoicePolicy.ps1

When the script starts, it will prompt you to enter the location of the .CSV file. Type in the file path (e.g., C:\Scripts\Users.csv) and press ENTER; in turn, the script will read the data from the .CSV and then assign each user the appropriate dial plan and voice policy.

As if that isn’t enough, the script also maintains a detailed log of everything it does. That log will be stored in the current working directory, and will have a file name based on the current date and time. For example:

GrantDialPlanAndVoicePolicy2010-06-10T09-33-03.txt

Submitted by Scott Stubberfield and Nick Smith, Microsoft

When you enable a user for Enterprise Voice (Microsoft’s implementation of Voice over IP) you also need to assign that user a line URI (i.e., a telephone number). Assign a line URI to one user? Here’s a command that can do that:

Set-CsUser –Identity "Ken Myer" –LineUri "tel:+14255551298"

Well, that looks easy enough. But what if you need to assign line URIs to 597 users? That looks like trouble.

But guess what: looks can be deceiving. Scott Stubberfield and Nick Smith have put together a script that can quickly and easily assign line URIs to any number of users, from one to, well, however many users you have in your organization. To do that, their script reads a comma-separated values file and then assigns each user listed in that file a line URI. For example, suppose the first user in the file has the following property values:

For this first user (sip:seeuser11@p10.ca) the script will assign the line URI listed in the LineUri field. That means that, effectively, the script will run the following command:

Set-CsUser –Identity "sip:seeuser11@p10.ca" –LineUri "tel:+10101"

After assigning the corresponding line URI to the first user listed in the file, the script will then proceed to assign the appropriate line URI to the next user listed in the file. Etc., etc.

The script itself looks a little like this:

param( [string] $importfile = $(Read-Host -prompt `
    "Please enter a file name"))

$importedusers = Import-Csv $importfile

$transcriptname = "AssignLineUri" + (Get-Date `
    -Format s).replace(":","-") +".txt"

Start-Transcript $transcriptname

foreach ($importeduser in $importedusers)

    {

        Set-CsUser $importeduser.SipUri -LineUri `
            $importeduser.LineUri –Verbose

    }

Stop-Transcript

To make use of this script, copy the code to your favorite text editor (e.g., Notepad), and then save the file using a .PS1 file extension (for example, C:\Scripts\AssignLineUris.ps1). In addition, you must create a .CSV file similar to this, and save that file to your hard drive as well:

SipUri,LineUri

sip:seeuser11@p10.ca,tel:+10101

sip:seeuser12@p10.ca,tel:+10102

sip:seeuser13@p10.ca,tel:+10103

sip:seeuser14@p10.ca,tel:+10104

sip:seeuser15@p10.ca,tel:+10105

sip:seeuser16@p10.ca,tel:+10106

sip:seeuser17@p10.ca,tel:+10107

sip:seeuser18@p10.ca,tel:+10108

sip:seeuser19@p10.ca,tel:+10109

To run the script from within the Lync Server Management Shell, simply type the full path to the .PS1 file and then press ENTER:

C:\Scripts\AssignLineUris.ps1

When the script starts, it will prompt you to enter the location of the .CSV file. Type in the file path (e.g., C:\Scripts\Users.csv) and press ENTER; in turn, the script will read the data from the .CSV and then assign each user the appropriate line URI.

Incidentally, the script also maintains a detailed log of everything it does. That log will be stored in the current working directory, and will have a file name based on the current date and time. For example:

AssignLineUri2010-06-10T09-33-03.txt