User Vs Role Mapping in 7.2

[former_member373435]

Hi Everybody , I have requirement to create an excel file with all the users and the roles assigned to them in IDM 7.2

I am using below query to obtain the user and role information -

select my.mcThisMSKEY as UserMskey,

            my.mcThisMSKEYVALUE as UniqueIdOfUser,

            usr.mcDisplayName as DisplayNmaeOfUser,

            my.mcOtherMSKEY as RoleMskey,

            my.mcOtherMSKEYVALUE as UniqueIdOfRole,

            rle.mcDisplayName as DisplayNameOfRole

                 from idmv_link_ext my, idmv_entry_simple usr, idmv_entry_simple rle

                      where my.mcThisOcName = 'MX_PERSON'

                      and my.mcOtherOcName = 'MX_ROLE'

                      and my.mcAttrName = 'MXREF_MX_ROLE'

                      and my.mcThisMSKEY = usr.mcMSKEY

                      and my.mcOtherMSKEY = rle.mcMSKEY

            order by my.mcThisMSKEY

The query is working fine, but I am not sure what should be in the output , I mean ATTRIBUTE and VALUE in the destination tab of the "to ASCII pass". As a result when I run the job it is executing but the output file is generated with just some ;; (almost 15000 lines).

I just need userIDs and Role Unique IDs or Role Names

Any help would be appreciated.

[Steffi_Warnecke · Accepted solution]

Hello Ani,

what does your destination tab look like right now? Nothing in there?

You should be able to use everthing after an "as". So in your case I'd guess UniqueIdOfUser and UniqueIdOfRole would be interesting. And DisplayNmaeOfUser (there's a typo in there ), if you want to. It's pretty much your choice.

I have a similar (wild) looking query with ASCII output and that's how I handle it.

Regards,

Steffi.

[former_member373435 · Accepted solution]

Hi Steffi, wonderful !!

You got it right, I did not have anything and was trying to execute the query through a script passing the mskey. I realised your advise would be much more easier.

That's what I was looking for indeed.

Let me try and get back in case issues.

[former_member373435]

Thanks Matt, do I need to modify the query? Indeed, I have a lot of user and roles assigned to them.

Also my question is what should be the Attributes in the destination tab of the "to ASCII pass" against user ID and role names.

Like in the destination tab of the job ---

Value                    Attributes

-------------------------------------------------------

UserID               %MSKEYVALUE%

RoleName          ????

The output csv should contain "user Ids" and the "roles" assigned to them, users display name would be good to have.

[terovirta]

Attribute (name) will be your column heading and (attribute) Value will be the data repeated as rows in the CSV.

In the destination tab click on the "Insert template" button and select "data source template" and you get the fields defined in the select statement to your attribute mapping.

If you're not happy with column headings change them in the attribute mapping in destination tab or the alias you have typed in the select-statement.

Or type in the column names of you select statement with appropriate column heading for the "attribute name" column.

regards, Tero

[former_member2987]

When I try this in my test environment, that's exactly what you get.  If you have a lot of users and roles, this list could indeed be quite large.