A Penny for Your Query?

Over the years, I have begun to build up a small library of useful queries and query techniques, some of which I have shared in past posts.

Probably my most frequently used query is actually a two parter, the first part is pretty straightforward:

select distinct mskey from mxiv_sentries where searchvalue = ‘mapo’

This is a pretty basic query that simply grabs the MSKEY of a given Identity Store object. This is really interesting when you combine these results with this query:

select attrname, searchvalue, aValue from mxiv_sentries where mskey = 123456 order by attrname

Basically, it grabs the relevant attributes that match up with the MSKEY that we just found.  Most of the time, I'm using this to find user information, but I've been known to use it on roles and privileges as well. For extra visibility, I'll also throw an "order by" clause on the end to get better access to custom attributes depending on which side of the alphabet they are in.  Possibly the only good thing about all of those "MX_" attributes is that they all sit in the middle of the list.

What queries do you use a lot?

UPDATE:

Just as an idea of what you can do to expand on this basic technique, here's a query I developed that gets the user's Manager name assuming that the user's PerNr and PerNr of the user's manager is in IDM (this usually happens from the HCM feed)

select searchvalue from mxiv_sentries where attrname='DISPLAYNAME' and mskey in(select mskey from mxiv_sentries where attrname = 'MX_FS_PERSONNEL_NUMBER' and searchvalue in (select searchvalue from mxiv_sentries where attrname = 'MX_FS_PERSONNEL_NUMBER_OF_MANAGER' and mskey in (select distinct mskey from mxiv_sentries where searchvalue = 'MAPO')));

In a script the searchvalue ("MAPO" in this case) would most likely be the users MSKEYVALUE.

I also too the opportunity to update the first query to read "select distinct..." per my comment to the original post.