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!

Scripting / SysAdmin Survey

In repsonse to the "tagging" of my friend Laura Hunter I now respond with the answers to these deep mysteries. But first "tagging"? Is this equivalent to the gang tagging? I hope not!

How old were you when you started using computers?
I was 4 or 5 when ...
What was your first machine?
My Dad brought home an Atari 800 computer -- not the game console all though we did have games they just weren't as cool as the ones on the game console.
What was the first real script you wrote?
For me the breakthrough came when I was 7 or 8 and I was puzzling through a the Atari 800 book on BASIC and I was able to accept the abtract concept of a variable! Years later in 7th grade when I was introduced to algebra I realized that I had already done the hard part -- wrap my brain around this concept of a variable!

So the first script I wrote was a number guessing game in BASIC on the Atari 800 computer.

What scripting languages have you used?
BASIC. C-Brief (the C like macro language available in the most powerful of editors Brief from Underware -- later bought by Borland -- Basic Reconfigurable Interactive Editing Facility (BRIEF), AccessBasic, Rexx, Knozall's NLMerlin Basic (now Automation Wizard), Turbo C Shell Scripting, Bash Shell Scripting, C, C++, Java, JavaScript, VBScript, VBA, DOS batch files, VB.NET, C#.NET, Powershell, command-line automation with the ds*.

What was your first professional sysadmin gig?
By the time I was 16 my Dad had started his own software business. He dragged me into the office, weekly, to muck out the toilets and haul out the trash. After I eclipsed all of the Computer Science courses at my high school (thanks Mrs Crane), one summer day my Dad sat me down at his computer handed me the software manual to FileWizard and said test the software to see that it does what the manual says.

So I began testing, then my Dad's business partner and one of their other developers starting working me as I would test the software and I would also help them manage the Netware 2.x, 3.x and 4.x LAN. Eventually, I also started writing some code -- some small portions of which made it into the products. I even wrote one of the software manuals.

If there is one thing you learned along the way that you would tell new sysadmins, what would it be?
The fun thing about IT is the learning does not ever need to stop, but watch out for those for whom the learning has stopped.

What's the most fun you've ever had scripting?
Very early in my career, I loved taking a very manual process (print out the payroll numbers for temps and fax it to corporate) and automating it -- export the data, transform it, and email it to corporate automatically. I guess I have always been a nut for automating stuff -- which is one reason why I enjoy Identity Management. Let's automate the creation of user accounts! Let's automate their deactivation!

Who am I calling out?
Chris Calderon
Gil Kirkpatrick

I like my passwords Plain --in plaintext that is

Bug in ILM2 Beta 3 -- go vote on MSConnect to register your taste!

Look for Bug ID 354953

Do you like your passwords plain or with encrypted butter?

As for me and my house we will choose the encrypted butter! I mean passwords.

ILM 2 codeless provisioning looks great! You can add complex rules without code and then you can even see these rules as they get synchronized into the ILM synch engine (what we know and love from the MIIS 2003 and ILM 2007 days). But then oops! you can see my default password in plaintext!

7/3/2008

In the ILM2 Portal when I configure an initial outbound attribute flow for unicodepwd (an initial password for AD users) and then in Identity Manager looking at the resulting Synchronization rule object in the connector space I can see the password in plaintext!
Repro Steps
1) Assume use of VPC image from ILM product group2) Open Identity Manager3) Search the ILM 2 MA connector space -- look for the synchronization rule for provisioning users in my system the id begins with 102df8d94) Click on the elipsis button in the string column of the initial flow attribute then look for Pass@word15) Realize that your password is stored in plaintext!

Bigger screenshot of Identity Manager here: