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)
FROM OPENROWSET(BULK 'C:\FuzzyLogic\ADExport\ADExport.xml',SINGLE_BLOB) AS AD
DECLARE @ADXMLData XML
SELECT @ADXMLData = XMLFROMCS
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]
SELECT dn, attrname, attrvalue
FROM OPENXML(@docHandle, N'/cs-objects/cs-object/pending-import-hologram/entry/attr/value',2)
(dn nvarchar(450) '../../@dn'
,primaryobjectclass nvarchar(450) '../../primary-objectclass'
,attrname nvarchar(450) '../@name'
,attrvalue nvarchar(450) '.'
,multivalued nvarchar(450) '../@multivalued'
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])
EXEC sp_xml_removedocument @docHandle