How to edit LDAP Query filter of Active Directory Connectors

Yesterday I participated in the Service Manager Customer LyncUp call and even typed two messages. I expected to see a road map for the Service Manager vNext but my expectations remain my expectations. However, I noticed that some people asked the product team about a feature request/bug fix that would allow editing the LDAP query filters of Active Directory connectors in Microsoft System Center 2012 Service Manager. Currently you have to create a new connector because those settings are grayed out.

We have spent lots of time with Service Manager connectors to develop SCUtils Email Connector. That’s why I decided to make my modest input to help people survive unless the SCSM product team develops the requested change.

For your information, the connectors in SCSM 2012 usually contain of two parts - settings and workflow. To edit the LDAP query filter we don’t need to change the workflow. It’s enough to adjust the settings.

The Active Directory connectors’ settings are stored in the ServiceManager database of the SCSM management server in the table named “MT_System$LinkingFramework$ActiveDirectorySource”. So we need to change some of the values.

Start SQL Server Management Studio and connect to the management server, find “ServiceManager” database, and then list the table “MT_System$LinkingFramework$ActiveDirectorySource”. Select in any raw any non-empty cell from UserList_758D7575_2420_4AF0_72D4_C531113CFCE7 column and copy the content.

Open Notepad and paste the copied XML text. Delete all text between <FilterString> and </FilterString>.

Now it’s time to prepare a new string with the LDAP Query filter. I think that is a good idea to test your query using LDAP query tools before inserting. After you get a properly tested LDAP query, you have to “escape” it using XML Escape formatter or another similar tool.

For instance, with the source line like this:
(&(ObjectCategory=User)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))

we will get a new line like that: 
(&amp;(ObjectCategory=User)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))

Insert the line between <FilterString> and </FilterString>.

Now you have an XML text and we can change UserList value of the AD connector.

We will use SMlets installed on the management server. Run PowerShell console using an administrative account, and then run the following PS commands (replace AD Connector with the name of your AD connector):

Import-module smlets
$adclass = Get-SCSMClass -name System.LinkingFramework.ActiveDirectorySource
$connector = Get-SCSMObject -Class $adclass -Filter "DisplayName -like 'AD Connector'"

Now we have to prepare our new value. Type the following command:

$newvalue = @"

Press Enter. Select and copy all the XML text in the notepad. Paste it in PowerShell console.

Press Enter. Type “@. Press Enter twice.

Type $newvalue and press Enter to check the result.

And now we are ready to set UserList value.
Set-SCSMObject -SMObject $connector -Property UserList -Value $newvalue

Repeat the steps for GroupList value (copy the XML text from GroupList_8B6F004C_06A7_10D2_2254_64E97AC5DE69 column, replace the text for <FilterString>, set $newvalue).
Set GroupList using the following command:
Set-SCSMObject -SMObject $connector -Property GroupList -Value $newvalue

Now open the SCSM console to see and check the new LDAP Query filter.

Of course, you can use the same procedure for PrinterList and ComputerList.