My quest to bring Best Practices to Identity Management especially with Microsoft FIM / ILM

Tuesday, November 11, 2008

CSExport -- Getting the ILM Connector Space into SQL

How can I query the ILM Connector Space?

"You can't." -- Yes you can.

"You have to WMI." --  But WMI is limited in what you can query and slow

"You have to use CSExport and then use XML tools." -- that works, but this may be better

The above are various answers you may receive. However, thanks to the power of SQLXML we can issue SQL queries against the ILM Connector Space (after it has been exported using CSExport).

The following code will only work on SQL 2005. Some of the SQLXML tricks involved here have been described previously

So I will discuss some of the new tricks.

The csexport file spits out XML that looks a lot like entity attribute value schema and this means that each attribute gets its own row when you bring it into SQL. Since that doesn't work well for me to write further queries I use a pivot operator to make the data relational.

The code below will retrieve data from the pending import holograms of an AD connectorspace and doesn't handle multi-valued attributes.

 

SET NOCOUNT ON


DECLARE @ADXML TABLE (ADID int identity (1,1), XMLFROMCS XML)


INSERT @ADXML (XMLFROMCS)


SELECT * 


FROM OPENROWSET(BULK 'C:\FuzzyLogic\ADExport\ADExport.xml',SINGLE_BLOB) AS AD


 


DECLARE @ADXMLData XML


 


SELECT @ADXMLData = XMLFROMCS


FROM @ADXML


 


DECLARE @docHandle int


EXEC sp_xml_preparedocument @docHandle OUTPUT, @ADXMLData


 


TRUNCATE TABLE dbo.adUsers 


 


INSERT dbo.adUsers (dn, [cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title])


 


SELECT dn, [cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title]


FROM


    (


            SELECT dn, attrname, attrvalue  


            FROM OPENXML(@docHandle, N'/cs-objects/cs-object/pending-import-hologram/entry/attr/value',2)  


             WITH 


                (dn nvarchar(450) '../../@dn'


                ,primaryobjectclass nvarchar(450) '../../primary-objectclass'


                ,attrname nvarchar(450) '../@name'


                ,attrvalue nvarchar(450) '.'


                ,multivalued nvarchar(450) '../@multivalued'


                ) adusers


            WHERE adusers.primaryobjectclass = 'user' AND adusers.multivalued = 'false'


                AND attrname in ('cn','department','displayname', 'employeeid', 'givenName', 'mail', 'sn','title') 


        ) AS ADList1


        


    PIVOT (MIN(attrvalue) FOR attrname in ([cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title])


 )AS ADUserPivot


 


EXEC sp_xml_removedocument @docHandle 


0 Comments:

Post a Comment

Links to this post:

<< Home