idm sql help

[former_member569171]

Hi,

I add some customized attributes (ATTR1, ATTR2, ATTR3) to MX_PERSON. how to get all MX_PERSON (users) informations including the values of these 3 customized attributes ?

Thanks,

Victoria

[ChrisPS · Accepted solution]

Hi Victoria,
                this topic has been raised before in the threads so try a quick search. There is already a very good blog by IdM developer Per Krabsetsve that you can find here

this details the views where you can find attributes of any entry type like MX_PERSON. In your case just add the required attributes to the examples in the blog.

Thanks,

Chris

[former_member569171 · Accepted solution]

Hi Chris,

Thank you for your help, this blog is very helpful, after modifying the query I am getting:

select mcMSKEY,mcAttrName,mcValue from idmv_vallink_basic where mcMSKEY =  (select mcmskey from idmv_entry_simple where mcMSKEYVALUE = 'MYUSER')

mcMSKEY     mcAttrName     mcValue

1                    ATTR1               Val1

2                    ATTR2               Val2

3                    ATTR3               Val3

4                    mskeyvalue       MYUSER  

My need is how to get this information not as result but as header like!

MSKEYVALUE     ATTR1     ATTR2     ATTR3

MYUSER             Val1          Val2        Val3

Thank your very much,

Victoria

[ChrisPS · Accepted solution]

Hi Victoria,
                are you trying to output this information to a report or what is the goal in this case ?

thanks,

Chris

[former_member569171 · Accepted solution]

Hi Chris,

I have one HR file, which I upload to IDM, this HR file contains the somes rules. According to these rules the users will gets privileges, roles and groups. In this file I added some additional custom attributes.

What I am trying to do , is to extract all these users with assigned privileges but also the additional attributes (ATTR1, ATTR2, ATTR3).

So, I need this report for ALL users and not only user. I tried to create a To ASCII File and there I can extract ATTR1: %ATTR1%, ATTR2: %ATTR2% etc but no sucess to them for all users.

So I guess, If I can get the custom attributes to myuser I can do it for all,

Thank your,

Victoria

[former_member2987 · Accepted solution]

Victoria,

Take a look at 's excellent blog entry:  . He talks a bit about how to "pivot" IDM data.

Reagrds,

Matt

[former_member569171 · Accepted solution]

Hi Matt,

Thanks for your help, I am using mx sql server and not Oracle, still looking how to use this Pivot for my query ?

Any help,

Victoria

[Former Member · Accepted solution]

My pivot query was actually originally developed in an MS-SQL environment. It will work on both Microsoft and Oracle databases.

[Former Member · Accepted solution]

If these are singlevalue non-reference attributes you can try something simple with a join. Perhaps not the most efficient way to do it, but easy to write and understand atleast.

select E.mcMskeyValue userid, FN.mcValue firstname, LN.mcValue lastname, M.mcValue email
from mxi_entry E
  left outer join idmv_vallink_basic FN on E.mcMSKEY = FN.mcMSKEY and FN.mcAttrName='MX_FIRSTNAME'
  left outer join idmv_vallink_basic LN on E.mcMSKEY = LN.mcMSKEY and LN.mcAttrName='MX_LASTNAME'
  left outer join idmv_vallink_basic M on E.mcMSKEY = M.mcMSKEY and M.mcAttrName='MX_MAIL_PRIMARY'
where E.mcMSKEY in (<select that picks up mskeys of people assigned the privilege>)

Change the attribute names. Left outer joins give null values where the joined attribute is missing, change to inner join if you want to exclude entries that don't have all the attributes.

Br,

Per Christian

[Former Member · Accepted solution]

Two other approaches, the pivot already being mentioned by Brandon.

Using case and group by:

select mskey,
  max (case when AttrName = 'MSKEYVALUE' then aValue end ) mskeyvalue,
  max (case when AttrName = 'DISPLAYNAME' then aValue end ) DisplayName,
  max (case when AttrName = 'MX_ENTRYTYPE' then aValue end ) EntryType
from idmv_value_basic
where attrname in ('MSKEYVALUE','DISPLAYNAME','MX_ENTRYTYPE')
group by mskey

Using pivot:

select * from 
(
  select mskey,avalue,attrname from idmv_value_basic where attrname in ('MSKEYVALUE','DISPLAYNAME','MX_ENTRYTYPE')
) s
pivot
(
  max(avalue) for attrname in (MSKEYVALUE,DISPLAYNAME,MX_ENTRYTYPE) 
) p

All three tested and working on SQL Server.

[Former Member · Accepted solution]

Hi Victoria,

If the pivot thing was the correct answer then the right answer should go to Brandon and I'll have to go through his postings and click like on enough of them to make up for the points 🙂

Br,

Chris

[Former Member · Accepted solution]

Your "case & group" answer might have been the deal maker. I got a helpful star for my answer so maybe that helped but didn't work for her. 

[former_member569171]

Hi Brandon,

I am not able to adapt your query for my need, I appreciate your help.

Any workaround ?

Victoria

[Former Member]

I'm surprised the pivot won't work for you. When I use it, it does exactly what you're describing, which is make the attribute names into column headers and list their values by rows. The only IDM supported database I've never tried it in is DB2, and I hope I never have to. If I think of any other way to do this, I'll let you know but unfortunately, that's all the help I can give. 

[former_member569171]

Hi Brandon,

Please find here the equivalent of your query 01. to 11. lines which does not work:

select * from

(select mskey, mcattrname, mcvalue from IDMV_VALLINK_EXT)

pivot

(max(mcValue) for mcAttrName in ('ATTR1','ATTR2','ATTR3'))

join MXI_ENTRY M on M.MCMSKEY = '123456'

where mskey in (

select MSKEY, mcAttrName, mcSearchValue from IDMV_VALLINK_EXT

where mcAttrName in('ATTR1','ATTR2','ATTR3')

Thank your for your help,

Victoria.

[Former Member]

You cannot join on an attribute value. Your join will have to match one of the mcAttrNames you're referring to in the pivot, i.e. 'ATTR1','ATTR2', or 'ATTR3'. In my sample query, I have:

(max(mcValue) for mcAttrName in ('MX_MANAGER' as Manager)

MX_MANAGER on a user's record is the manager's MSKEY. I then needed the actual name of the manager so my join takes that manager's MSKEY and joins his name from the MXI_ENTRY table:

join MXI_ENTRY M on M.MCMSKEY = Manager