IDM Reporting - Output the IDM Role Unique ID

[Former Member]

Hello Experts,

I've got an IDM report that i'm wanting to extend to include the IDM role unique id.

Here's an extract of my sql:

select a.avalue as firstname,b.avalue as lastname,c.avalue as displayname,e.mskey,p.avalue as role,null as privilege from

mxiv_sentries a

inner join mxiv_sentries b on a.mskey=b.mskey

inner join mxiv_sentries c on b.mskey=c.mskey

inner join customPersonRoles e on c.mskey=e.mskey

inner join mxiv_sentries p on e.mskey=p.mskey

inner join mxiv_sentries f on p.mskey=f.mskey where

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND p.attrname='MXREF_MX_ROLE' AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND a.attrname ='MX_FIRSTNAME' AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND b.attrname ='MX_LASTNAME'  AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND c.attrname ='DISPLAYNAME'

This is a screenshot of my destination attempt but the ROLEID attribute is not returning the unique IDM Role number.

Can anybody please advise whether i need to include some additional SQL lines or whether there it's something i can resolve easily in the Destination attributes?

Thanks very much for your help.

Lee

[Former Member · Accepted solution]

Steffi/Peter thanks both for your responses. It probably goes to show how little I know about getting this information out off IDM but I'm learning a little at a time.

The purpose of the report is to get a list of all users and all their access assignments (IDM Role, Auto Privilege and Direct Privileges).

My report has 2 steps, the 1st step you can see from the screenshot retrieves the MSKEY for all users in our system and holds them in the customPersonRoles table. This table is used by the second step which is the Combine privileges to report task.

My full sql looks like this:

select a.avalue as firstname,b.avalue as lastname,c.avalue as displayname,e.mskey,p.avalue as role,null as privilege from

mxiv_sentries a

inner join mxiv_sentries b on a.mskey=b.mskey

inner join mxiv_sentries c on b.mskey=c.mskey

inner join customPersonRoles e on c.mskey=e.mskey

inner join mxiv_sentries p on e.mskey=p.mskey

inner join mxiv_sentries f on p.mskey=f.mskey where

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND p.attrname='MXREF_MX_ROLE' AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND a.attrname ='MX_FIRSTNAME' AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND b.attrname ='MX_LASTNAME'  AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND c.attrname ='DISPLAYNAME'

union

select a.avalue as firstname,b.avalue as lastname,c.avalue as displayname,e.mskey,N'Auto_Priv' as role,p.avalue as privilege from

mxiv_sentries a

inner join mxiv_sentries b on a.mskey=b.mskey

inner join mxiv_sentries c on b.mskey=c.mskey

inner join customPersonRoles e on c.mskey=e.mskey

inner join mxiv_sentries p on e.mskey=p.mskey where

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND p.attrname='MX_AUTOPRIVILEGE' AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND a.attrname ='MX_FIRSTNAME' AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND b.attrname ='MX_LASTNAME'  AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND c.attrname ='DISPLAYNAME'

union

select a.avalue as firstname,b.avalue as lastname,c.avalue as displayname,e.mskey,N'Direct_Priv' as role,p.avalue as privilege from

mxiv_sentries a

inner join mxiv_sentries b on a.mskey=b.mskey

inner join mxiv_sentries c on b.mskey=c.mskey

inner join customPersonRoles e on c.mskey=e.mskey

inner join mxiv_sentries p on e.mskey=p.mskey where

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND p.attrname='MXREF_MX_PRIVILEGE' AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND a.attrname ='MX_FIRSTNAME' AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND b.attrname ='MX_LASTNAME'  AND

b.mskey IN (SELECT mskey FROM MXIV_SENTRIES WHERE attrname='MX_ENTRYTYPE' AND searchvalue = 'MX_PERSON')

AND c.attrname ='DISPLAYNAME'

order by mskey,role,privilege

My current output looks like this:

Where it is showing "202!!ROLE" at the moment I want this to show the IDM Unique Role id

Thanks again I appreciate your help.

Lee

[Steffi_Warnecke · Accepted solution]

Hello Lee,

I know what your going through (and Peter probably, too), because everyone started somewhere with the reporting.

Better use the IDMVs, those are the IdM-views. The ones I use the most are

• idmv_value_basic
• idmv_vallink_basic
• idmv_vallink_ext
• idmv_entry_simple

I can really just point you to the blog

That one will help you a lot (and the others of the series, too).

You don't need the first pass, btw. Where you read all users into a temp-table. You can just do that in the ToASCII-pass.

If I were you, I'd start from scratch and work my way through the report (but first the blog and the views). That way you'll see how everything is connected and you can "clean up" this big SQL statement and the job.

Regards,

Steffi.

[Former Member · Accepted solution]

Thanks Steffi for taking the time to reply. I've had a quick look at the blog link it it looks incredibly useful, just the sort of thing i've been looking for.

I was sort of hoping that there was a quick fix to the existing report as it's already doing 99% of what i need, but you're probably right that it would be better to start from scratch and actually understand what i'm doing rather than trying via trial and error to get it to do what i need.

All i need now is the time to do it, i'm sure you know what it's like .

Thanks again.

Lee

[Steffi_Warnecke · Accepted solution]

Yes, I do. *g* And I can already tell, that if you got that report running, every new one will be so much easier for you to create and maintain.

Have fun and a great day!

Steffi.

[Former Member · Accepted solution]

Since you're using 7.1 my blogs might not be that useful.

In answer to the original question, it seems like you should just replace the script thing with

ROLEID=%ROLE%

in the destinationgid if its the MSKEY you're after. Or atleast pass %ROLE%!!ROLE as input to the custom_getMskeyvalue script as Peter mentioned.

Another approach you can take is to extend the use of temporary tables by doing select into them rather than use the runtime to move it to a new table across the network and runtime client. This usually gives much better performance. I dont have a 7.1 system anymore so I have no idea how this works. You really should add with(nolock) as well on SQL Server. And consider some indexes on the temporary tables. This could be usefull if you extract the same information over and over again in multiple reports.

Part 1, Usually a To Database pass, no source, "SQL Updating checked" in destination

-- Remove contents of temporary tables if existing

drop table temp_users

drop table temp_rolepriv

-- Create temp_users table

select f.mskey,f.avalue firstname,l.avalue lastname,d.avalue displayname

into temp_users

from mxiv_sentries f

  inner join mxiv_sentries l on l.mskey = f.mskey and l.attrname = 'MX_LASTNAME'

  inner join mxiv_sentries d on d.mskey = f.mskey and d.attrname = 'DISPLAYNAME'

where f.attrname = 'MX_FIRSTNAME' and f.mskey in (

select mskey from mxiv_sentries  where attrname = 'MX_ENTRYTYPE' and searchvalue = 'MX_PERSON')

-- Create roles and privileges table, convert mskey to varchar for easy compare to searchvalue

-- Privileges:

select cast(p.mskey as varchar) mskey,p.avalue assignmentname

into temp_rolepriv

from mxiv_sentries p

where p.attrname = 'MSKEYVALUE' and p.mskey in (

select mskey from mxiv_sentries  where attrname = 'MX_ENTRYTYPE' and searchvalue = 'MX_PRIVILEGE')

-- Roles:

insert into temp_rolepriv

select cast(r.mskey as varchar) mskey,r.avalue assignmentname

from mxiv_sentries r

where r.attrname = 'MSKEYVALUE' and r.mskey in (

select mskey from mxiv_sentries  where attrname = 'MX_ENTRYTYPE' and searchvalue = 'MX_ROLE')

Part 2: Source of To Ascii pass:

-- Use temporary tables joined with IdS assignments. Could also read this into a temptable.

select u.firstname,u.lastname,u.displayname, L.searchvalue mskey, A.assignmentname,

Case L.attrname

  WHEN 'MXREF_MX_ROLE' then 'DIRECT ROLE'

  WHEN 'MX_AUTOROLE' then 'INHERITED ROLE'

  WHEN 'MXREF_MX_PRIVILEGE' then 'DIRECT PRIVILEGE'

  WHEN 'MX_AUTOPRIVILEGE' then 'INHERITED PRIVILEGE'

END assignmentType

from mxiv_sentries L

inner join temp_users U on L.mskey = U.MSKEY

inner join temp_rolepriv A on A.mskey = L.SearchValue

where attrname in ('MXREF_MX_ROLE','MXAUTOROLE', 'MXREF_MX_PRIVILEGE', 'MX_AUTOPRIVILEGE')

Br,

Chris

[former_member2987 · Accepted solution]

Steffi, that won't really work for him since by the look of his query, he's still on 7.1.

Matt

[Former Member · Accepted solution]

Thanks Chris for your very comprehensive answer.

Cheers,

Lee

[Former Member]

Hi All,

I did as Chris suggested and put ROLEID=%ROLE% into the destination tab against the ROLEID and it returned exactly what I needed. I made a coupld of minor tweaks to the sql to restrict some of the output and it's job done.

When I next need to write a report i'll take on board some of the other suggestions to develop a more effecient extract but for now I've got what I need.

Thanks again for all your help.

Lee

[Steffi_Warnecke]

You're welcome, Lee! Thank you for the update.

Regards,

Steffi.

[Former Member]

Thanks very much all for your help and input. I need some time to digest some of the information and maybe give some of Chris' suggestions a try.

Just wanted to say thanks for all your input up to now. As soon as i've had chance to try out some of the suggestions i'll post an update.

Thanks again.

Lee

[Steffi_Warnecke]

That's why Chris and you are the pros, because you can tell from a look at his query.

Regards,

Steffi.

[former_member2987]

Pros? No, well maybe   More like old.  Been looking at this schema for almost 10 years now. 

[Former Member]

I think you're making things to hard for yourself.  I think we're going to need more info to solve this (script details, source tab, what you're attempting to get as the output).  We'll also need to know what 'customPersonRoles' is...

By the look of it, you're passing the MSKEY of the person? to custom_getMskeyvalue and telling the script its a role and expecting to get the mskey of the role back?

Is that right?

The MSKEY of the role is held against the MXREF_MX_ROLE attribute - you don't need to do additional work to get it - however it won't get subroles.  Is that what you're after?

Peter

[Steffi_Warnecke]

Hello Lee,

wow, I think, I have never seen so many aliases in one statement. Is there a reason you don't just take all the information for the person from one? Are you trying to list the roles for the person?

And what gets returned as ID now?

Regards,

Steffi.