Manipulating the msRTCSIP-UserRoutingGroupID

pointless-button-large-msg-127353534404First off, if you haven’t yet read this article then you should – Understanding how Windows Fabric Works (with regards to Lync). This article is a very minor ancillary to that article so out of context it will make very little sense.

In the Mastering Lync article, the following is written:
As you can see here, the msRTCSIP-UserRoutingGroupID in active directory corresponds to a routing group defined within Lync.  Some of the numbers are reversed from Active Directory to what is seen in the RTCLocal database.

It’s the “Some of the numbers are reversed” bit that the below script handles.

For no apparent reason, I decided to write a script that would take the routing group ID’s in Active directory and manipulate the “reversed numbers” so that they match what is seen in Lync’s SQL Server tables. Note that it doesn’t edit or change anything in AD; rather it just outputs a list of User ID’s and routing groups that you can view and manually compare with what is in SQL Server.

So is there a real point to this script? Probably not but hey – I learned some fun PowerShell manipulation. Plus, you can get all of this information right out of SQL Server.

The first line of the script is a Get-ADUser command which grabs the msRTCSIP-UserRoutingGroupID attribute from all of your users.

$user = get-aduser -filter * -Properties name,MsRTCSIP-UserRoutingGroupID | where {"MsRTCSIP-UserRoutingGroupID" -ne $Null}

After that is gathered, a simple foreach loop is run that loops through all of the users and manipulates the numbers. The first thing that is done is to convert the value returned from hexadecimal to a string format so we can easily manipulate it.

ForEach ($StringValue in $item."MsRTCSIP-UserRoutingGroupID")
  $HexValue = "{0:x}" -f $StringValue
  $HexRoutingGroupID = $HexRoutingGroupID + $HexValue

Once this is done, we can now do a bunch of string manipulations to flip the values to match what is seen in SQL Server. In short, what happens is that the string is “counted backward” from one value to another. In the first case we are selecting the values from -13 to -16. The cool thing here is that it also adds those values to our variable in “negative order” so we don’t have to do any string manipulation once we’ve picked off a pair of values we want to reverse. It’s pretty slick.

$UserRoutingGroupID1Inverse = $HexRoutingGroupID[-13..(-16)]
$UserRoutingGroupID2Inverse = $HexRoutingGroupID[-11..(-12)]
$UserRoutingGroupID3Inverse = $HexRoutingGroupID[-9..(-10)]
$UserRoutingGroup4 = $HexRoutingGroupID[8..16]

Finally the string gets concatenated together and output to the screen.

$outputvalue = $ +","+ $UserRoutingGroupID1Inverse +" "+ $UserRoutingGroupID2Inverse + " " + $UserRoutingGroupID3Inverse + " " + $UserRoutingGroup4

So there it is. No clue if this script will ever provide any major value as you can also just output this information directly from SQL. Check into the rtclocal.rtc.dbo.Resource,  rtclocal.rtc.dbo.FrontEnd and rtclocal.rtc.dbo.RoutingGroupAssignment tables. Then do SQL magic I don’t know how to do.

Download the full script here.


Skip to comment form

  1. Thanks for your work. It was useful to me.

  2. Thanks for your work. It was useful to me.

  3. I made two changes: save output to file & change format of output to match my SQL query:
    $LogFile = “c:tempUserRoutingGroupID.csv”

    $user = get-aduser -filter * -Properties name,MsRTCSIP-UserRoutingGroupID | where {$_.”MsRTCSIP-UserRoutingGroupID” -ne $Null}

    foreach ($item in $user){

    $HexRoutingGroupID = @()

    ForEach ($StringValue in $item.”MsRTCSIP-UserRoutingGroupID”)

    $HexValue = “{0:X}” -f $StringValue
    $HexRoutingGroupID = $HexRoutingGroupID + $HexValue


    $UserRoutingGroupID1Inverse = $HexRoutingGroupID[-13..(-16)]
    $UserRoutingGroupID2Inverse = $HexRoutingGroupID[-11..(-12)]
    $UserRoutingGroupID3Inverse = $HexRoutingGroupID[-9..(-10)]
    $UserRoutingGroup4 = $HexRoutingGroupID[8..9]
    $UserRoutingGroup5 = $HexRoutingGroupID[10..16]
    [string]$outputvalue =$UserRoutingGroupID1Inverse +”-” + $UserRoutingGroupID2Inverse + “-” + $UserRoutingGroupID3Inverse + “-” + $UserRoutingGroup4+ “-” + $UserRoutingGroup5
    $outputvalue=$outputvalue.Replace(” “,””)
    $outputvalue = $ +”,”+$outputvalue

    $outputvalue | Out-File -FilePath $LogFile -Append

    1. Thank you FBot and thank you John for the tweaked version. Both of you have been VERY helpful.

Leave a Reply

Your email address will not be published.