Generating Reports of All users with Several Attributes using Single SQL Query
Many times we get business requirements to generate a customized report of all users with several attributes like MSKEYVALUE, DISPLAYNAME, MAIL, MOBILE, COUNTRY etc.
If we want to keep all attributes of each user in a single row then it generally requires running a multiple sql-queries and then combining them together in a single file. It is time-consuming and manual work involved may lead to error.
Following single query can be used to generate such custom reports.
Select distinct
(select mskey from idmv_vallink_basic with (nolock) where mcAttrName=’MSKEYVALUE’ and MSKEY = M.mskey) as MSKEY,
(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=’DISPLAYNAME’ and MSKEY = M.mskey) as DISPLAYNAME,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_MAIL_PRIMARY’ and MSKEY = M.mskey) as MAIL,
(select mcValue from idmv_vallink_basic with (nolock) where mcAttrName =’MX_ADDRESS_COUNTRY’ and MSKEY = M.mskey) as COUNTRY,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_PHONE_PRIMARY’ and MSKEY = M.mskey) as Phone,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_MOBILE_PRIMARY’ and MSKEY = M.mskey) as Mobile,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_VALIDFROM’ and MSKEY = M.mskey) as ValidFrom,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_VALIDTO’ and MSKEY = M.mskey) as ValidTo,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_DISABLED’ and MSKEY = M.mskey) as Disabled_status
from idmv_vallink_basic M with (nolock) where M.mcAttrName=’MX_ENTRYTYPE’ and M.mcValue=’MX_PERSON’
Above query will result, all users (MX_ENTRYTYPE = MX_PERSON) with their Mskey and value of attributes MSKEYVALUE, DISPLAYNAME, MX_MAIL_PRIMARY, MX_ADDRESS_COUNTRY, MX_PHONE_PRIMARY, MX_MOBILE_PRIMARY, MX_VALIDFROM, MX_VALIDTO and MX_DISABLED.
If a user will not have any of the above attributes then query will result NULL for that attribute.
The result will look like
Based on requirement, Attributes can be removed/added in the above query.
The above query can be modified to generate even more customized report.
Example – To generate of users (MX_ENTRYTYPE = MX_PERSON) with their Mskey and value of attributes MSKEYVALUE, DISPLAYNAME, MX_MAIL_PRIMARY, MX_ADDRESS_COUNTRY, MX_PHONE_PRIMARY, MX_MOBILE_PRIMARY, MX_VALIDFROM, MX_VALIDTO and MX_DISABLED whose MSKEYVALUE starts from TEST.
Query can be written as
Select distinct
(select mskey from idmv_vallink_basic with (nolock) where mcAttrName=’MSKEYVALUE’ and MSKEY = M.mskey) as MSKEY,
(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=’DISPLAYNAME’ and MSKEY = M.mskey) as DISPLAYNAME,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_MAIL_PRIMARY’ and MSKEY = M.mskey) as MAIL,
(select mcValue from idmv_vallink_basic with (nolock) where mcAttrName =’MX_ADDRESS_COUNTRY’ and MSKEY = M.mskey) as COUNTRY,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_PHONE_PRIMARY’ and MSKEY = M.mskey) as Phone,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_MOBILE_PRIMARY’ and MSKEY = M.mskey) as Mobile,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_VALIDFROM’ and MSKEY = M.mskey) as ValidFrom,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_VALIDTO’ and MSKEY = M.mskey) as ValidTo,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName=’MX_DISABLED’ and MSKEY = M.mskey) as Disabled_status
from idmv_vallink_basic M with (nolock), idmv_vallink_basic N with (nolock) where M.mcAttrName=’MX_ENTRYTYPE’ and
M.mcValue=’MX_PERSON’ and N.mcAttrName=’MSKEYVALUE’ and N.mcvalue like ‘TEST%’ and M.mskey=N.mskey
The result will look like
P.S. – Please note that above query will work perfectly with single value attributes while for multivalue attribute it will return only one value which will be picked randomly therefore use this query wisely for multi-value attribute. with (nolock) is specific to MS SQL so if you want to run this query on Oracle/DB2 don’t forget to remove with (nolock).
Hope It will help to prepare reports 🙂
C Kumar
Interesting query.... Easier than using PIVOT functionality.
I did find that I needed to add a 'distinct' to the query as I was getting multiple entries.
Select distinct
(select mskey from idmv_vallink_basic where mcAttrName='MSKEYVALUE' and MSKEY = M.mskey) as MSKEY,
(select mcvalue from idmv_vallink_basic where mcAttrName='MSKEYVALUE' and MSKEY = M.mskey) as MSKEYVALUE,
Matt
Easier and also faster, Matt. 🙂
If you base the query on the view idmv_entry simple and use only one view in the from part you only get one row though. In this example it wouldn't be needed then.
We use a function for pivoting for many things (UI, reading out data quickly, some other views used for ... thingys) which does exactly the same thing like above. For non-reference attributes the idmv_value_basic is better as there are less entries in the table (hm, I gotta check tomorrow if the function actually uses the basic view). For reference attributes I use the link_ext which is quite fast, too.
We can pivot our 35000 identities with almost 40 attributes in 6-7 seconds (I guess Oracle would be faster, we only have MS). We also have a isnull(avalue, '') in usage for some attributes as we don't want null's in our UI's. Yet, this costs some time.
Don't forget to add with (nolock) for MS-SQL, this saves some time, too. And it avoids deadlocks.
I found out that the value_ext is somewhat slower than the value_basic when running a full select on them. We have some 6.5 million entries in the views and the basic view was finished in half the time.
Personally my favorite query for gathering data is:
select mskey, attrname, avalue from idmv_value_ext_active where mskey in (select mskey from idmv_value_ext_active where attrname = 'MX_ENTRYTYPE' and searchvalue = 'MX_PERSON')
But I know it doesn't pivot. It does do well for ASCII stuff as Tero suggests. I know it's not the fastest view, but it almost always has everything I am looking for... 🙂
Hi.
What about joins?
Is there any reason for using one method over the other?
I would use query as below to get the data from Kumar's post:
SELECT
entry.mcmskey AS MSKEY,
entry.mcMSKEYVALUE AS MSKEYVALUE,
entry.mcDisplayName AS MCDISPLAYNAME,
mail.mcSearchValue AS MAIL,
country.mcSearchValue AS COUNTRY,
phone.mcSearchValue AS PHONE,
mobile.mcsearchvalue AS MOBILE,
validfrom.mcSearchValue AS VALIDFROM,
validto.mcSearchValue AS VALIDTO,
disabled.mcSearchValue AS DISABLED
FROM idmv_entry_simple entry WITH (nolock)
LEFT OUTER JOIN idmv_vallink_basic mail WITH (nolock) ON mail.mskey = entry.mcMSKEY and mail.mcAttrname = 'MX_MAIL_PRIMARY'
LEFT OUTER JOIN idmv_vallink_basic country WITH (nolock) ON country.mskey = entry.mcMSKEY and country.mcAttrname = 'MX_ADDRESS_COUNTRY'
LEFT OUTER JOIN idmv_vallink_basic phone WITH (nolock) ON phone.mskey = entry.mcMSKEY and phone.mcAttrname = 'MX_PHONE_PRIMARY'
LEFT OUTER JOIN idmv_vallink_basic mobile WITH (nolock) ON mobile.mskey = entry.mcMSKEY and mobile.mcAttrname = 'MX_MOBILE_PRIMARY'
LEFT OUTER JOIN idmv_vallink_basic validfrom WITH (nolock) ON validfrom.mskey = entry.mcMSKEY and validfrom.mcAttrname = 'MX_VALIDFROM'
LEFT OUTER JOIN idmv_vallink_basic validto WITH (nolock) ON validto.mskey = entry.mcMSKEY and validto.mcAttrname = 'MX_VALIDTO'
LEFT OUTER JOIN idmv_vallink_basic disabled WITH (nolock) ON disabled.mskey = entry.mcMSKEY and disabled.mcAttrname = 'MX_DISABLED'
WHERE entry.mcEntryType = 'MX_PERSON' AND entry.mcMSKEYVALUE like 'IDMTEST%'
In case of multivalue attributes it will simply return one additional row per each value which I don't think, is a bad thing.
Regards,
Darek.
Hello Darek,
Your query is working perfectly, but you can observe the excution time difference.
I have just run your query and found that execution time is around 2 seconds to display the output while the query shared by me has execution time even less than 30 milliseconds for the same sample data for the same result 🙂
Regards,
C Kumar
Good to know. Didn't expect that table joins are THAT MUCH slower than nested selects. I will consider this while building larger queries.
Regards,
Darek.
Thanks Dominik for reminding me about with (nolock), I will add this in the query.
Thanks Matt for your comment!
Could you please share for which attribute you was getting multiple entries so that I can modify the query accordingly. It would be good if you can paste the query result here.
Regards,
C Kumar
Looks like mskey...
Matt, I didn't find any such cases while running this query in my test system.
Would be great if you can share the output result so that I can analyze it and make the corresponding modification,
I really didn't change anything. Just wondering, did you do this on Oracle or MS SQL? I did mine on MS SQL.
I also did this on MS SQL.
interesting. I will post the query I used later.
IMO, reports like the are where the IdM's toAscii pass comes in handy.. Just have a select that retrieves the MSKEYs you want and then you can output the entries to file with whatever columns you're interested.
regards, Tero
Hello Tero,
I agree we can always write ToAscii pass to generate such reports however I found this sql-query quite fast and easy so thought to share here.
I am really glad today to see comments/suggestions from SAP IDM experts (Matt, Dominik, Tero...) on my first IDM blog 🙂
Regards,
C Kumar
Seeing this query, I can't help but wonder about performances. Running it on a test ID Store, I can see this will generate a table lookup for each attribute requested. Here, the number of attributes is quite small, but I had to do a report on all attributes available to a user, which mean more than a hundred table lookup, left join
I prefer to use the following version:
SELECT MAX(CASE WHEN dn.AttrName = 'MSKEYVALUE' THEN dn.aValue END) AS MSKEYVALUE,
MAX(CASE WHEN dn.AttrName = 'DISPLAYNAME' THEN dn.aValue END) AS DISPLAYNAME,
MAX(CASE WHEN dn.AttrName = 'MX_MAIL_PRIMARY' THEN dn.aValue END) AS MAIL,
MAX(CASE WHEN dn.AttrName = 'MX_ADDRESS_COUNTRY' THEN dn.aValue END) AS COUNTRY,
MAX(CASE WHEN dn.AttrName = 'MX_PHONE_PRIMARY' THEN dn.aValue END) AS Phone,
MAX(CASE WHEN dn.AttrName = 'MX_MOBILE_PRIMARY' THEN dn.aValue END) AS Mobile,
MAX(CASE WHEN dn.AttrName = 'MX_VALIDFROM' THEN dn.aValue END) AS ValidFrom,
MAX(CASE WHEN dn.AttrName = 'MX_VALIDTO' THEN dn.aValue END) AS ValidTo,
MAX(CASE WHEN dn.AttrName = 'MX_DISABLED' THEN dn.aValue END) AS Disabled_status,
entry.mcMSKEY
FROM idmv_entry_simple AS entry WITH (NOLOCK)
LEFT JOIN idmv_value_basic_active AS dn WITH (NOLOCK)
ON dn.MSKEY = entry.mcMSKEY
WHERE entry.mcEntryType = 'MX_PERSON'
GROUP BY entry.mcMSKEY
Here, we are only doing a single table lookup, and use aggregation to group the results by entry and pivot the columns. On my database, it takes around 10 sec to run while the provided one takes more than twice this time.
I believe much easiest solution would be exporting the report via the the job, using export to Ascii file pass by giving the mskey list of the MX_PERSON entry type and in the source tab and in the destination tab, list out all the MX_PERSON attributes. This job can be reused for many times for any kind of requirement - extract list of users based on location/companycode_id, assigned with role/privilege/manager etc., - Only the source query needs to be modified.
Regards,
DP