Courion Tip – Getting Started: Know your data

Know your data.

Let’s start with the basics. Courion (to me) isn’t a solution, it’s a solution framework. So, it (probably) doesn’t have canned answers for what you are trying to do, you have to come up with them yourself. I’ve had requests to base things on the user’s department number – only to find out that HR was creating new department numbers on a regular basis, but not letting anyone know. So, after a while, processes failed. The decision was made to base those same things on the state an employee was in, and we found that over 60% of the user’s didn’t have that populated.

IdentityMap (TM, courion, all that)

This is one of those super critical tables in courion that you really need to understand. It basically ties all the accounts that a user has together. Usually, it’s 3 columns – side note, seems like it’s grown A LOT since I wrote this. But the main 3 columns are still ProfileUID, TargetID, and UserName.


This is what ties all the user accounts together. You really really need this column, it needs to be unique to each employee, and it needs to exist everywhere. I was at one company where everyone had employee ID’s to log in, and it was already set on every system. No firstname  lastname, or first initial lastname, combinations!! I was A340359 there and everywhere. This made our implementation a dream!! However, at another place, this wasn’t so. Everyone had an employeeID, but no one knew what it was and it wasn’t set on all the target systems.

At this point, you might be pondering if employee id’s are set on your SAP system, or your AS/400’s or …. Find out.  If your systems don’t have an employee id on them, and you’re thinking that firstinitial & lastname will do just fine, good luck.  What about Aaron Adams, and Abigale Adams, or even just the first 2 John Smith’s you hire.

When you import data from your target systems, it is a LOT easier to find all records where employee id is ‘888944’ than it is to determine if that record is from John Smith or Jonathan Smith.

By finding all records in the identitymap where profileuid = some value, you immediately know every system that a uwer has accounts on (that have imports into courion).

Know how long your employee id’s are, and what constitutes a valid employee id. For instance, if all employee id’s are 6 characters long, you should know that. (This also gives you options – perhaps the user accounts you don’t want to touch should be more than 6 characters?). Know how this column is defined in the IdentityMap table. (it’s probably varchar(50), just cos that’s what SQL defaults to). Don’t change it yet, just know what’s what.


This is the user name on the target system. These will probably be all over the map, and here’s where your users are probably set up as first initial last name…  Hopefully, you have naming standards set up on all of your target systems. Employee ID’s pay off well here, because if you are importing jsmith and jsmith1 how do you know which user is responsible for that account??? If you have employee id’s in place, it is a resolvable situation since you would be keying off of the employee id, not the user’s name on that system.


These are probably carved in stone at this point, but if you’re just getting started, stop and think!!  There are plenty of places that this information is used. I strongly recommend that you use target id’s that make sense, mean something to end users, and convey information. For instance, SAP_ABC_110_T tells you right up front that it’s an SAP system, it’s your ABC box, client 110, and it’s a test system.    The target id will come into play when configuring workflow targets, configuring SSIS import processes,  automatically generating emails that go to the end user, and potentially other places (identitymap selection screen in the workflows themselves).

To Do’s

Date time stamp The only change I might recommend here is to add a date time stamp column to the identitymap.  That way, you will be able to keep track of when a user was given an account on a particular system. Auditors will love you for it, and it can help in debugging (for instance, if you search for all entries in the idenitymap for a given target id sorted by the date time stamp, you’d quickly know when the last time a user was added, or when the last time the process worked successfully)

Data cleanup – Before too long, you’ll find that your system is great at importing data about new users on systems, but not so good at removing information. As an example, let’s say you have an SAP account on our SAP_ABC_110_T system today. Next month, your account gets removed (and, let me digress – if ALL of that were to be done inside of courion (adding the account and then deleting it – then it will keep track of the changes, and you need not worry about it. That’s one of the beauties of the identity map). Your identitymap will still show an entry for that system, and you will keep getting password change failure emails every time you change your password.

I would strongly suggest that you encourage the owners of the target systems to send you information about not only ADDITIONS, but also account DELETIONS so that you can avoid this. Why not just delete all the entries that are not in the newest feed? Because, if you are not careful, and the feed fails, you will delete ALL the records for that target system.

Update…. I thought I would throw this in as an example of how not to do something. And note, it’s a real life example – I didn’t make this up!
CREATE TABLE [dbo].[Staging_AD](
[objectGUID] [nvarchar](2500) NULL,
[sAMAccountName] [nvarchar](2500) NULL,
[GivenName] [nvarchar](2500) NULL,
[sn] [nvarchar](2500) NULL,
[mail] [nvarchar](2500) NULL,
[employeeID] [nvarchar](2500) NULL,
[employeeType] [nvarchar](2500) NULL,
[pwdLastSet] [nvarchar](2500) NULL,
[description] [nvarchar](2500) NULL,
[displayName] [nvarchar](2500) NULL,
[department] [nvarchar](2500) NULL,
[streetAddress] [nvarchar](2500) NULL,
[l] [nvarchar](2500) NULL,
[st] [nvarchar](2500) NULL,
[postalCode] [nvarchar](2500) NULL,
[telephoneNumber] [nvarchar](2500) NULL,
[facsimileTelephoneNumber] [nvarchar](2500) NULL,
[memberof] [nvarchar](2500) NULL,
[distinguishedName] [nvarchar](2500) NULL,
[modifyTimeStamp] [nvarchar](2500) NULL,
[useraccountcontrol] [nvarchar](2500) NULL,
[accountExpires] [nvarchar](2500) NULL,
[Disabled] [nvarchar](2500) NULL

Why on earth you would want to store Disabled (a boolean value) as nvarchar(2500) is beyond me. In addition, all the timestamps are also nvarchar(2500). This shows a complete dis-regard for the nature of the data.


Posted March 21, 2012 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: