FIM Sets, XPATH, finding nulls with Strings

 

A little while ago I encountered some rather strange behavior of a Set vs. the XPATH query in FIM 2010.

Using the Export-FIMConfig with the -onlyBaseResources -CustomConfig switches I run the following query to see if there are any users without a DisplayName

/Person[not(starts-with(DisplayName,''))]

It showed 20

So then I created a set, called “~ People with no displayname”, with that as the custom filter. I checked it doesn't violate any of the limitations listed in the Business Policy Modeling doc (which I must say is a pretty good doc)

Then when I look at the Set and click view members on the criteria tab it shows 20 users. So far so good

But when I go to Search for users and it Resource ID in “~ People with no displayname” it shows me over 10,000

Indeed using the commandlet to run this query /Person[ObjectID=/Set[DisplayName='~ People with no displayname']/ComputedMember]") I get over 10,000

Jeremy made a suggestion:

/Person[not(starts-with(DisplayName,'%'))]  

Sure enough it works both as the SET filter and as the XPATH query and showed 20 records.

So to test a string for null, use:

not(starts-wth(Attribute,'%'))

in the XPATH predicate.

Why does this work?

The starts-with function works just like using the LIKE operator in T-SQL with an implied % at the end. not(starts-wth() does a NOT LIKE '%' with the implied %. Since % will match anything as long as it is not null this effectively tests for null.

All of the wildcards available in the LIKE operator work

I have tested it using the single wildcard character _ as well as ranges like [a-c] and other more complex patterns.

This also means that you can effectively do a contains in a set or group filter by doing:

starts-wth(attribute,'%searchvalue')

That’s right just prefix your searchvalue with % and what happens behind the scenes is a LIKE '%searchvalue%' which will find searchvalue anywhere in the string.

Warning about View Members on a set (possibly groups too)

Apparently when I click on view members on the Set’s Criteria tab it runs the XPATH query right then. But when you save the set with its new filter it runs the query in a slightly different fashion by first persisting the SET criteria to the database and then reloading the criteria and running the query and then persisting the membership results in the database to speed up look ups. (Naturally, the penalty is every create, delete, modify, add and remove request requires each set to be examined for impact and possible recalculation). So when you use the filter do do /Person[not(starts-with(DisplayName, '%'))] it stores that piece of the criteria as the literal string %% and the operator as LIKE. But when you use /Person[not(starts-with(DisplayName, ''))] it seems to delete that piece of the criteria effectively making it a set of all Persons. If you are implementing a ROPU (pronounced Rope You – Run on Policy Update) enabled workflow and this kind of thing happens to you it can me that a workflow is being applied to 10,000 objects instead of 20.

Pending Exports Report in ILM

Hopefully this topic will stir up some excitement among those wondering how to query objects in the connector space. The technique I am about to explicate for you works for both exports and imports.

As many of you aware, my colleague and fellow ILM MVP Brad Turner created the community reporting pack for MIIS/ILM some time ago. This is a package of reports written in SQL Server Reporting Services (SSRS).

Most of you are also aware that you can tell an import or export run profile step to drop an audit file. The audit file is in DSML format (an XML format). You can use XML files as the source for SSRS reports, they can also be used.

A later report he created was for Pending Exports, to show clients what records are about to be exported (drop audit file and stop) or what records were just exported (drop audit file).

1) Turn on the drop audit file for the export run profile step.

2) Create a virtual directory in IIS that points to the MAData subfolder location and allows you to see the file

3) Create a data source in SSRS for that file and only that file. This means you have to create a data source for each audit file. Wow -- doable but painful!

A short while ago I took this process and made it even slicker. I present the background of all this to show why Brad and I form the nucleus of a great team. I had not thought of creating a report based on the audit file. I viewed the audit file as a troubleshooting technique, not as a great way to be able to report on exports or imports. My inspiration was how to make this more flexible.

I created a stored procedure (only works on SQL 2005) that uses SQLXML (specifically the sp_xml_preparedocument and OpenXML to shred the XML data to relational data). 

Additionally, I created the stored procedure so that it can accept a lot of parameters, allowing us to report the pending exports (or just exported) for any of the MAs).

That means that we only need one data source -- pointing to the database that houses the stored procedure.

First allow me to demonstrate the basic technique:

SET NOCOUNT ON

DECLARE @ADXMLData XML

SELECT @ADXMLData = BulkColumn 
FROM OPENROWSET(BULK 'C:\Ensynch_projects\Reports\ILMReports\copy of admaexports.xml',SINGLE_NCLOB) AS AD

DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @ADXMLData, '<mmsml xmlns:a="http://www.microsoft.com/mms/mmsml/v2"/>'

            SELECT * 
            FROM OPENXML(@docHandle, N'//a:mmsml/a:directory-entries/a:delta/a:dn-attr/a:dn-value/a:dn',2) 
             With (
                    DeltaOp varchar(100) '../../../@operation'
                    ,DNAOp varchar(100) '../../@operation'
                    ,DNVOp varchar(100) '../@operation'
                    --,ObjType varchar(50) '../../../primary-objectclass'
                    ,ObjectDN varchar(1000) '../../../@dn'
                    ,AttrName varchar(100) '../../@name'
                    ,dn        varchar(1000) '.'
            ) Export
ORDER BY GroupDN

EXEC sp_xml_removedocument @docHandle

For this query I was first focused on some group updates. I need to show the client how we were going to update their distribution lists.

This query takes the XML from the DSML file and shreds it back to relational data like so

 

update	add		CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=MontyHALL, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add		CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Joe Montana, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add		CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Steve Young, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add		CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Fred Idaho, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add	add	CN=Group2, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Fred Idaho, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add	add	CN=Group2, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=MontyHALL, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add	add	CN=Group2, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Joe Montana, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org

 

Next week I will show how to add the parameters and then I will show how to make the report. If you are lucky I might even make a video and post it!