Courion Tip – Clean up your IdentityMap automatically

The issue is that, as users move from one department to another, their identitymap information gets out of date. Our implementation ADDS to the identitymap, but never deletes from it. So, I developed the following to remove bad entries from the identitymap. (This could very easily be altered to simply flag them as well.) We have moved our ticketing for password changes/resets to password_ticketing table. If you haven’t done that, I recommend it, but you’ll have to add (and, this is from memory, so it’s going to be close, maybe not exact) “and workflowaction =’pwdreset'”

Step 1: Create a view. Basically, the Native Status error messages tell us what we want to know.. When it fails because a user doesn’t exist, for example. So, why not use that information??? Also note the use of GETDATE()-1 .. In english, this basically means “during the last 24 hours”. You can adjust this according to your needs… I actually use GETDATE()-3, in spite of the view being called “Past 1 day”..

ALTER VIEW
[dbo].[Password_Failures_Past_1_Day_User_Doesnt_Exist]
AS SELECT TOP (100) PERCENT
Provisionee, TargetID, Workflow, Date, TargetStatus
FROM dbo.Password_Ticketing
WHERE 
((Date > GETDATE() – 1) AND (Workflow <> ‘Password_Retry’))
AND
((TargetStatus LIKE ‘%user%does not exist%’) OR (TargetStatus LIKE ‘%The user name could not be found%’) OR (TargetStatus LIKE ‘%Invalid user profile%’))
ORDER BY Date DESC

Step 2: Create a delete query for your identitymap table… I will show 2 versions here, the first one does a clean up of ALL identity map entries where a password change failed (because of an account not existing). Of course, this could be modified to set an inactive flag, we opted for deletion.

delete from identitymap where exists
(select 1 from Password_Failures_Past_1_Day_User_Doesnt_Exist where Password_Failures_Past_1_Day_User_Doesnt_Exist.TargetID = IdentityMap.TargetID AND
Password_Failures_Past_1_Day_User_Doesnt_Exist.Provisionee = IdentityMap.ProfileUID)

This one will only clean up active directory entries (note that you will need to change the activedirectory text to whatever uniquely matches your active directory target(s). If you have only one, you could easily hard code it. However, I have mutiple domains – so I use the wildcards and a unique string to match it correctly.

delete from identitymap where targetid like ‘%activedirectory%’ and exists
(select 1 from Password_Failures_Past_1_Day_User_Doesnt_Exist where Password_Failures_Past_1_Day_User_Doesnt_Exist.TargetID = IdentityMap.TargetID AND
Password_Failures_Past_1_Day_User_Doesnt_Exist.Provisionee = IdentityMap.ProfileUID)

Step 3: Create a delete query for your staging table, in this case, Active Directory. If you truncate your staging_ad table every night, like we used to, you probably don’t need to do this. However, what was a temporary table for us slowly became permanent.

delete from Staging_AD where exists
(select 1 from Password_Failures_Past_1_Day_User_Doesnt_Exist where Password_Failures_Past_1_Day_User_Doesnt_Exist.TargetID = Staging_AD.TargetID AND
Password_Failures_Past_1_Day_User_Doesnt_Exist.Provisionee = Staging_AD.ProfileUID)

You should run these manually (from TOAD or from SQL Server Management Studio) for several days before you add them into SSIS packages.

Step 3: Incorporate these queries into your SSIS package… BUT, I strongly recommend incorporating these into your SSIS package as one of the first steps or near the first steps – why? Because if your code or logic is incorrect, and too many entries are removed, then the subsequent steps will simply put them back in!!

Advertisements

Posted July 15, 2010 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: