IDM / Oracle / CRW - A Tall Order

[Former Member]

Hello all. My name is Brandon and I would like to review over a recent problem I had to solve in my IDM consulting life. To give you just a bit of environmental background, the company I am working with currently runs IDM 7.2 on a Oracle database.

The task at hand was to write a report in CRW that would pull all the identities that had membership in a given role or roles. The user running the report would select the role or roles they wanted and also needed to be able to filter by active or terminated employees or both. I felt the Oracle SQL query I wrote would be worthy of a blog entry as the data had to be pulled from three different places so maybe this will help someone out someday. Here's what I came up with:

select distinct display_name as Employee_Name, HR_Status, PS_DEPT_DESC as Department_Description, mcdisplayname as Manager from

(select mskey, display_name, HR_Status, PS_SETID, PS_DEPT_DESC, MCDISPLAYNAME,

RANK() OVER (PARTITION BY mskey

ORDER BY ps_setid DESC) "RANK" from

(select * from (select mskey, mcattrname, mcvalue from MXMC_OPER.IDMV_VALLINK_EXT)

pivot (max(mcvalue) for mcattrname in ('DISPLAYNAME' as Display_Name,'MX_FS_PS_STATUS' as HR_Status,'MX_MANAGER' as Manager,'Z_PS_DEPARTMENT' as Dept_ID))

join MXMC_RT.Z_PS_DEPARTMENTS D on D.PS_DEPT = Dept_ID

join MXMC_OPER.MXI_ENTRY M on M.MCMSKEY = Manager

where mskey in

(select mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'DISPLAYNAME' and mskey in

(select mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'MX_FS_PS_STATUS' and mcsearchvalue in ('ACTIVE','TERMINATED') and mskey in

(select distinct mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'MXREF_MX_ROLE' and mcvalue in (261336,27,261369))))))

where rank = 1

order by display_name

I would love to show you some output from that query but, due to client confidentiality, I can't. However, I can make a quick table below to give you an idea as to what comes up:

Employee_Name	HR_Status	Department_Description	Manager
Brandon Bollin	Active	IT - Security	Matt Pollicove
Clark Kent	Active	Legal	Jerry Siegel
Lex Luthor	Terminated	Finance	Joe Shuster

These three employees are members of one of the roles with MSKEYs of 261336, 27 or 261369 and both active or terminated is being displayed. In order to get this simple output, I decided to start with the most basic parts of this query, pulling identities from IDM and filtering on role membership and active / terminated. Lines 10 thru 15 was where this whole thing started.

select mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'DISPLAYNAME' and mskey in

(select mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'MX_FS_PS_STATUS' and mcsearchvalue in ('ACTIVE','TERMINATED') and mskey in

(select distinct mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'MXREF_MX_ROLE' and mcvalue in (261336,27,261369)))

This query will simply pull display names of the identities I wanted in the given roles and HR status or statuses. When importing this into CRW, the ACTIVE / TERMINATED portion and 261336,27,261369 would be substituted with variables which would be set via user input. For anyone who's ever worked with IDM, this part of the query should be pretty straight forward. At this point, I noted the number of results I received. For the sake of example, let's say I got 500. This way, I knew that, going forward, that would be my baseline. Anything I compounded onto this query would have to return the same number of results for it to be correct.

So now I have to add the identities' HR Statuses, department descriptions and managers' names. To do this, I added lines 5 thru 9 onto the query:

select * from (select mskey, mcattrname, mcvalue from MXMC_OPER.IDMV_VALLINK_EXT)

pivot (max(mcvalue) for mcattrname in ('DISPLAYNAME' as Display_Name,'MX_FS_PS_STATUS' as HR_Status,'MX_MANAGER' as Manager,'Z_PS_DEPARTMENT' as Dept_ID))

join MXMC_RT.Z_PS_DEPARTMENTS D on D.PS_DEPT = Dept_ID

join MXMC_OPER.MXI_ENTRY M on M.MCMSKEY = Manager

where mskey in

(select mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'DISPLAYNAME' and mskey in

(select mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'MX_FS_PS_STATUS' and mcsearchvalue in ('ACTIVE','TERMINATED') and mskey in

(select distinct mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'MXREF_MX_ROLE' and mcvalue in (261336,27,261369))))

The select on line 1 above pulls all the pertinent information from IDMV_VALLINK_EXT. However, since the identity center database isn't normalized like most databases, I now need to turn it on it's side, if you will. That's where the PIVOT command comes in. All of the values selected from line 1 above will become rows instead of columns. While this isn't an exactly accurate description of what PIVOT does, it's good enough for this description. Google PIVOT sometime if you want to know more as this command does a number of other things depending what what operator you user (MAX, MIN, AVG, etc.). Since the department and manager IDs are saved as PS_DEPT number and MSKEYs on a user's identity respectively, I now add the two joins to pull out the human friendly names of the departments and managers. At this point, I'm thinking I'm done. My required four columns for the final report should be there so I run the query. I get back something like 591 results. What?! That's way more than my 500 baseline.

Upon some further digging, I discovered that, at some point in this company's past, they reorganized their departments when they were acquired by another company. All the old department names were still in the database so the joining of PS_DEPT on to the Z_PS_DEPARTMENTS table was pulling all department names for any user that existed during this transition. Users that had two, in some cases more, departments were getting more than one line in the results. Now what? How do I only pull the current information?

Thankfully, there was a column in Z_PS_DEPARTMENTS that allowed for this, PS_SETID. Once I was told that which SETID was current, it just so happened that the current set was always in first one listed in my results. Now all I needed to do was filter by RANK. That's where the rest of the query comes into play:

select distinct display_name as Employee_Name, HR_Status, PS_DEPT_DESC as Department_Description, mcdisplayname as Manager from

(select mskey, display_name, HR_Status, PS_SETID, PS_DEPT_DESC, MCDISPLAYNAME,

RANK() OVER (PARTITION BY mskey

ORDER BY ps_setid DESC) "RANK" from

(select * from (select mskey, mcattrname, mcvalue from MXMC_OPER.IDMV_VALLINK_EXT)

pivot (max(mcvalue) for mcattrname in ('DISPLAYNAME' as Display_Name,'MX_FS_PS_STATUS' as HR_Status,'MX_MANAGER' as Manager,'Z_PS_DEPARTMENT' as Dept_ID))

join MXMC_RT.Z_PS_DEPARTMENTS D on D.PS_DEPT = Dept_ID

join MXMC_OPER.MXI_ENTRY M on M.MCMSKEY = Manager

where mskey in

(select mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'DISPLAYNAME' and mskey in

(select mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'MX_FS_PS_STATUS' and mcsearchvalue in ('ACTIVE','TERMINATED') and mskey in

(select distinct mskey from MXMC_OPER.IDMV_VALLINK_EXT

where mcattrname = 'MXREF_MX_ROLE' and mcvalue in (261336,27,261369))))))

where rank = 1

order by display_name

The RANK command adds a "RANK" column onto my results. Any result with multiple PS_SETIDs would now be ranked 1, 2, or 3. I only want that first result so at the bottom of the query, I only return rank = 1. The first line in this query selects only the columns I wanted returned to be displayed and the last line orders them by their display name. When I ran this, I got back 500 results. Victory!! Plugging into CRW was a breeze after that. I'll still never forgot the e-mail I got back from my project contact when I sent him this query for approval before plugging in into CRW. It was essentially a two sentence long, "WOW!"

In writing this blog entry, I know I cut some corners on explanations so if you wish any clarifications, comment below and I can answer back. Again, as stated above, maybe this will help someone out when trying to pull IDM data someday, Additionally, if you have any suggestions on how I could have gotten the same result back simpler, PLEASE let me know! I am a huge lover of constructive criticism.

Thanks all and feel free to rate the quality of this post as well. It's my first blog entry so hopefully I got it right in the eyes of the readers. Tschüss!