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

DatabaseOutput1.JPG

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

DatabaseOutput2.JPG

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

To report this post you need to login first.

17 Comments

You must be Logged on to comment or reply to a post.

  1. Matt Pollicove

    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

    (0) 
    1. Dominik Trui

      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.

      (0) 
      1. Matt Pollicove

        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… 🙂

        (0) 
        1. Dariusz Trzoch

          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.

          (0) 
          1. C Kumar Post author

            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

            (0) 
            1. Dariusz Trzoch

              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.

              (0) 
    2. C Kumar Post author

      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

      (0) 
        1. C Kumar Post author

          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,

          (0) 
  2. Tero Virta

    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

    (0) 
    1. C Kumar Post author

      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

      (0) 
  3. Julien Garagnon

    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.

    (0) 
  4. D P

    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

    (0) 

Leave a Reply