cancel
Showing results for 
Search instead for 
Did you mean: 

Query to Identity Centre for User Attributes

0 Kudos

Hi,

I need create a job to query IDStore and then export data to csv in IDM 7.2 - the report should have below fields --

userID, Firstname,  Lastname,  email,  user_dcu,  company_code,  department,  location_city,  location_address,  manager id,  manager_firstname,  manager_lastname,  manager_email.

I am able to find some of the attributes with below query <not tested - may be you can rectify also if you find some> but stuck for some of the attributes  like location_address, manager id, manager_firstname, manager_lastname and manager_email.

Can anybody help me with a query for this ?

select

c.searchvalue as mskeyvalue

,div.SearchValue as division

,coalesce(dcu1.searchvalue,'') as dcu

,coalesce(dcu2.searchvalue,'') as dcu_name

,coalesce(m.avalue,'') as mail

from idmv_value_basic_active a with(nolock)

inner join idmv_value_basic_active b with(nolock)

on b.mskey = a.mskey

and b.attrname = 'P_TYPE'

left outer join idmv_value_basic_active div with(nolock)

on div.mskey = a.mskey

and div.attrname = 'P_DIVISION'

inner join idmv_value_basic_active c with(nolock)

on c.mskey = b.mskey

and c.attrname = 'MSKEYVALUE'

left outer join idmv_value_basic_active m with(nolock)

on m.mskey = b.mskey

and m.attrname = 'MX_MAIL_PRIMARY'

left outer join idmv_link_basic_active dcu with(nolock)

on dcu.mcthismskey = a.mskey

and dcu.mcattrname = 'MXREF_COMPANY'

left outer join idmv_value_basic_active dcu1 with(nolock)

on dcu1.mskey = dcu.mcothermskey

and dcu1.attrname = 'C_DCU'

left outer join idmv_value_basic_active dcu2 with(nolock)

on dcu2.mskey = dcu.mcothermskey

and dcu2.attrname = 'DISPLAYNAME'

where a.attrname =  'MX_ENTRYTYPE'

and a.searchvalue = 'MX_PERSON'

Accepted Solutions (1)

Accepted Solutions (1)

former_member201064
Active Participant
0 Kudos

Hi Ani,

doesn't look too bad at first sight.

First things first, you have MXREF_COMPANY wrong, that should be MXREF_MX_COMPANY_ADDRESS.

You use a lot of joins so every attribute has to be filled to get all person. If someone is missing e.g. the mail address you won't see them in the report. That can be wished or not

I prefer something like this:

select

e.mcMSKEYVALUE as mskeyvalue,

(select dcu.mcOtherMSKEYVALUE from idmv_link_ext dcu with (nolock) where dcu.mcthismskey = e.mcmskey and dcu.mcattrname = 'MXREF_MX_COMPANY_ADDRESS') company,

(select avalue from idmv_value_basic_active m with(nolock) where m.mskey = e.mcMSKEY and m.attrname = 'MX_MAIL_PRIMARY') mail

--, add the rest of the attributes here with a separate subquery

from idmv_entry_simple e with(nolock)

where e.mcEntryType =  'MX_PERSON'

I am not too sure about the coalesce you use. Are these attributes multivalues? But DISPLAYNAME and MX_MAIL_PRIMARY shouldn't be multivalues. I always used own created ones or the MX_MAIL_ADDITIONAL.

If you want to write out a csv file with a ToASCIIFile pass you simply can use select mcmskey from idmv_entry_simple with (nolock) where mcentrytype = 'MX_PERSON'.

Then set the "Use identity store" on the source tab, set the checkbox "Generate CSV file" and use pairings of mail %MX_MAIL_PRIMARY% and so on on the destination tab.

Within a ToGeneric it's similar, but you have to use Javascript to built up the file and then the uToFile function.

Did all variants a lot of times, no difference in the outcome. It all depended on where the data originated.

Best regards

Dominik

0 Kudos

Hi Dominik, I like your approach. Much straightforward.

I am not too sure which views would give me all the values of userID, Firstname,  Lastname,  email,  user_dcu,  company_code,  department,  location_city,  location_address,  manager id,  manager_firstname,  manager_lastname,  manager_email.

I tried for several times but I was lost in all relations and seems my query not going anywhere.

Do you consider this query?

select

e.mcMSKEYVALUE as mskeyvalue,

(select dcu.mcOtherMSKEYVALUE from idmv_link_ext dcu with (nolock) where dcu.mcthismskey = e.mcmskey and dcu.mcattrname = 'MXREF_MX_COMPANY_ADDRESS') company,

(select avalue from idmv_value_basic_active m with(nolock) where m.mskey = e.mcMSKEY and m.attrname = 'MX_MAIL_PRIMARY') mail

(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MSKEYVALUE' and MSKEY = M.mskey) as MSKEYVALUE,

(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_FIRSTNAME' and MSKEY = M.mskey) as FIRSTNAME,

(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_LASTNAME' and MSKEY = M.mskey) as LASTNAME,

(select mcvalue from idmv_link_basic_active with (nolock) where mcAttrName='C_DCU' and MSKEY = M.mskey) as DCU,

(select mcvalue from idmv_link_basic_active with (nolock) where mcAttrName='P_DIVISION' and MSKEY = M.mskey) as DIVISION,

from idmv_entry_simple e with(nolock)

where e.mcEntryType =  'MX_PERSON'

<<Please correct if you find something wrong>>

What about location_city,  location_address,  manager id,  manager_firstname,  manager_lastname,  manager_email ? How do I get those ?


Would you please help me to build the query ?

former_member201064
Active Participant
0 Kudos

I will try. First approach will be to ignore the multivalue attributes and add that functionality later on. I always avoided to handle them in SQL.

I actually only use two other views besides the idmv_entry_simple. idmv_value_ext and idmv_link_ext. Only sometimes I use the idmv_value_basic_active instead of the idmv_value_ext. The basic view is really faster if one just makes a select *, but there's no real difference when using a join on the entry_simple (at least I haven't noticed any differences).

The vallink views I try to avoid as the link view is faster and better to use. Only when mixing normal and reference attributes they are really useful to me.

idmv_value_ext:

Firstname,  Lastname,  email, department

These can you select like I stated

idmv_entry_simple:

userID, user_dcu / displayname

Directly using them from the entry simple view is best I guess

idmv_link_ext + idmv_value_ext:

location_city,  location_address,  manager id,  manager_firstname,  manager_lastname, manager_email, company_code (I guess you store an own attribute on the company?)

Here it gets a bit tricky. Example of a select:

(select avalue from idmv_value_ext s with (nolock) join idmv_link_ext l with (nolock) on s.MSKEY = l.mcOtherMSKEY and e.mcMSKEY = l.mcThisMSKEY and l.mcAttrName = 'MX_MANAGER' and s.AttrName = 'MX_FIRSTNAME') managerFirstname,

For the ManagerId you could either use attrname = 'MSKEYVALUE' or the entry simple view like this:

(select s.mcMSKEYVALUE from idmv_entry_simple s with (nolock) join idmv_link_ext l with (nolock) on s.mcMSKEY = l.mcOtherMSKEY and e.mcMSKEY = l.mcThisMSKEY and l.mcAttrName = 'MX_MANAGER') managerId,

former_member201064
Active Participant
0 Kudos

Corrected your query so it at least runs:

select

e.mcMSKEYVALUE as mskeyvalue,

(select dcu.mcOtherMSKEYVALUE from idmv_link_ext dcu with (nolock) where dcu.mcthismskey = e.mcmskey and dcu.mcattrname = 'MXREF_MX_COMPANY_ADDRESS') company,

(select avalue from idmv_value_basic_active m with(nolock) where m.mskey = e.mcMSKEY and m.attrname = 'MX_MAIL_PRIMARY') mail,

(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MSKEYVALUE' and MSKEY = e.mcMSKEY) as MSKEYVALUE,

(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_FIRSTNAME' and MSKEY = e.mcMSKEY) as FIRSTNAME,

(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_LASTNAME' and MSKEY = e.mcMSKEY) as LASTNAME,

(select mcOtherMSKEYVALUE from idmv_link_ext l with (nolock) where l.mcAttrName='C_DCU' and l.mcThisMSKEY = e.mcMSKEY) as DCU,

(select mcOtherMSKEYVALUE from idmv_link_ext l with (nolock) where l.mcAttrName='P_DIVISION' and l.mcThisMSKEY = e.mcMSKEY) as DIVISION

from idmv_entry_simple e with(nolock)

where e.mcEntryType =  'MX_PERSON'

Not too sure about C_CDU and P_DIVISION though. As they are really mutlivalue reference I try out testing with MXREF_MX_PRIVILEGE and coalescing now.

former_member201064
Active Participant
0 Kudos

Coalescing multivalues:

Couldn't get it working directly. I have a function though, which you could use as a pattern.

CREATE FUNCTION [dbo].[coalesceAttribute]

(

      @mskeyGiven int,

      @attrname nvarchar(max)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

      DECLARE @retVal NVARCHAR(MAX)

      SELECT @retVal = COALESCE(@retVal + '|', '') + mcValue

      FROM idmv_vallink_ext s

      WHERE  mskey = @mskeyGiven and mcattrname = @attrname

      RETURN @retVal

END

It works like this then:

select dbo.coalesceAttribute(mcMSKEY, 'P_DIVISION') privs, e.* from idmv_entry_simple e with (nolock)

For MXREF_MX_PRIVILEGE I get results like: 12345|23456|34567

Answers (3)

Answers (3)

0 Kudos

It helped !! Thanks Dominik

0 Kudos

Hi, thanks for all help !!

Got it working except one.

How do I find the "company code" and "company code description" for an user? Which view has description and how is the mapping ?

former_member201064
Active Participant
0 Kudos

Depends on where is your companyCode stored.

If at the company then you could use the mentioned scripts with %MXREF_MX_COMPANY_ADDRESS% and the attrname of the companyCode and companyCodeDescription.

I store the companyCode on both, company and person (event task and nightly batch job which checks if everything went ok), so I could use it directly as a person attribute

devaprakash_b
Active Contributor
0 Kudos

Hi Ani,

If i understand correct you want to provide the identity store as input and output you are expecting the details of the identity. If my understanding is correct please follow the below method.

Instead righting many joins, i would suggest you to create an export job with to Ascii Pass.

in the source tab right the below query


select * from idmv_vallink_basic where mcattrname = 'MX_ENTRY_TYPE' AND mcsearchvalue = 'MX_PERSON' AND mcidstore = 'your identity store'


in the the destination tab define the attributes which all you would like to be added in the report.


For manager details for that identity, you need to create a script which takes manager mskey as input and returns the values based on teh attribute passed as a parameter to it.



for example, $FUNCTION.getIdentityDetails(%MX_MANAGER%###MX_FIRST_NAME)


internally in the script, separate the parameters and use the below function


uIS_GetValue(MSKey, IStore, String AttName)


This would be the simple method, as if any attributes doesnt hold information you would get null value and it would be easy to verify too.


Regards,

DP