Stale / Ghosted / Orphaned SQL Objects in Lync RTCLOCAL

In one of the large enterprise size Lync environments I work with each day I occasionally see an issues with reusing phones numbers / DID’s / LineURIs for Lync Enterprise Voice enabled users causes problems.  To fix this problem there’s a manual method and a scripted method via an Excel template that’s provided below.

User Symptom:

The end user may receive an alert when trying to making a PSTN, Peer to peer or conference call stating,

“Cannot complete the call. There is more than one contact with your phone number. If you cannot resolve this problem, contact your support team with this information.”

Additionally if you check SIPStack on the incoming call to the user you may see a SIP/2.0 485 Ambiguous error.  ms-diagnostics: 4199;reason=”Multiple users associated with the target phone number” followed up with your FE Pool/server name.  If checking local UCCAPILOG’s from another Lync user attempting to P2P the impacted user you could see almost identical error being, “ms-diagnostics: 4002;reason=”Multiple users associated with the source phone number”

Normally in this scenario I would just use my script “Search Specific Lync Phone Number(s)” to check for any other Lync accounts (Users, CAPs, Analog Devices, Exchange UM Contacts, etc) that have the LineURI in questioned assigned as well.  And normally, that will discover the issue and allow a quick fix.  However, sometimes there are no active Lync accounts that have this number so it’s a little confusing as to how it’s flagged as Ambiguous.

After much troubleshooting I identified the cause is stale objects within every copy of RTCLOCAL in our environment, which has dozens and dozens now that Lync 2013 keeps this on each Front End.

Resolution:

If you have a small environment or want to manually check via SQL Server Management Studio run the below queries.  In this example we’ll pretend the impacted user has a LineURI of Tel:+13025551234;ext=1234 and a ResourceID of 29521.  The first SQL query to run would be;

SELECT * From [rtc].[dbo].[ResourcePhone] where PhoneNum like ‘%13025551234%’

orphanedsql_query1

This will provide the Resource ID’s of likely two or more accounts and the reason why there’s an ambiguous issue.  Take the resulted ResourceID and run the following query against each to discover the SIP address of each Lync account that RTCLOCAL still believe exists.

SELECT * FROM [rtc].[dbo].[Resource] where ResourceId = ‘29521’

orphanedsql_query2

Once you determine the SIP address of the account that was likely removed from Lync but is orphaned in SQL run the following query to rip it out once and for all.

execute dbo.RtcDeleteResource ‘Disabled_UsersSIPAddress@LyncLead.com’

orphanedsql_query3

If you have but one copy of RTCLOCAL in your Lync infrastructure then good for you and you’re finished.  However, if you have 50+ doing this on many servers would be quite annoying.  Additionally, running a ForEach-Object script on your RTCLOCAL databases may also give you a few extra grey hairs in the event you missed a quote or dash or something and end up killing all your user accounts.  Due to this, I setup an Excel sheet that auto generates the individual powershell commands to run against each server/RTCLOCAL copy.  It allows you to run one at a time or in bulk and visually watch what’s happen.  At least for me, this is much easier on my stress level when potential impacting 130,000 enabled users.

Bulk Resolution Process:

LyncLeadcom Clean Lync Orphaned Objects In SQL RTCLOCAL – EXCEL TEMPLATE

Use the above Excel template to auto generated powershell commands to discover what ResourceID’s have the same phone number, what SIP address is tied to said ResourceID and then delete the offended SIP address from RTCLOCAL on multiple servers.

More than likely you’ll only need to enter the phone number in the first row under LineURI and copy the command under the “Discover What Account Has The Phone Number (should only need to run once)” column.

orphanedsql_excel1

Open Powershell on your SQL Server and run the follow command

Import-Module “sqlps” -DisableNameChecking

Then run the command from field E8:

Invoke-Sqlcmd -Query “SELECT * FROM [rtc].[dbo].[ResourcePhone] where PhoneNum like ‘%15558675309%'” -ServerInstance ‘Lync_FE01\RTCLOCAL’

The result will tell you the ResourceID’s which you then paste into the Excel file under the “Resource ID” column.  If you only have two, then you would simply paste the two commands generated from the “Discover the SIP Address based on Resource ID (run only once)”.  *NOTE: for this step make sure you chnage the server hostname to be the same on both commands.  As ResourceID’s are different in each copy of RTCLOCAL you’ll set yourself up for failure if you dont check the two ResourceID’s on the same server.

Invoke-Sqlcmd -Query “SELECT * FROM [rtc].[dbo].[Resource] where ResourceId = ‘12345’” -ServerInstance ‘Lync_FE01\RTCLOCAL’
Invoke-Sqlcmd -Query “SELECT * FROM [rtc].[dbo].[Resource] where ResourceId = ‘23456’” -ServerInstance ‘Lync_FE01\RTCLOCAL’

Once you determine the stale, orphaned, ghosted, whatever you call it object take the SIP address and enter it as many times as needed in the “SIP Address” column for the number of FE servers you have.  Then update the “Server Hostname” column with whatever your host names are.

orphanedsql_excel2

 

Now copy all of commands from the last column, “Delete Discovered SIP Address from All copies of RTCLOCAL (Run against all Front Ends that contain RTCLOCAL)” and run them in powershell.

Invoke-Sqlcmd -Query “execute rtc.dbo.RtcDeleteResource ‘disabled_SIP_Address@LyncLead.com'” -ServerInstance ‘Lync_FE01\RTCLOCAL’
Invoke-Sqlcmd -Query “execute rtc.dbo.RtcDeleteResource ‘disabled_SIP_Address@LyncLead.com'” -ServerInstance ‘Lync_FE02\RTCLOCAL’
Invoke-Sqlcmd -Query “execute rtc.dbo.RtcDeleteResource ‘disabled_SIP_Address@LyncLead.com'” -ServerInstance ‘Lync_FE03\RTCLOCAL’
Invoke-Sqlcmd -Query “execute rtc.dbo.RtcDeleteResource ‘disabled_SIP_Address@LyncLead.com'” -ServerInstance ‘Lync_FE04\RTCLOCAL’
Invoke-Sqlcmd -Query “execute rtc.dbo.RtcDeleteResource ‘disabled_SIP_Address@LyncLead.com'” -ServerInstance ‘Lync_FE05\RTCLOCAL’
Invoke-Sqlcmd -Query “execute rtc.dbo.RtcDeleteResource ‘disabled_SIP_Address@LyncLead.com'” -ServerInstance ‘Lync_FE06\RTCLOCAL’
Invoke-Sqlcmd -Query “execute rtc.dbo.RtcDeleteResource ‘disabled_SIP_Address@LyncLead.com'” -ServerInstance ‘Lync_FE07\RTCLOCAL’

 

The above is what fixes it for me.  Hopefully it’ll do the same for you.  Enjoy!

-McBride

Leave a Reply