IDM Queries in SQL Studio Manager.

[former_member96398]

I'm currently just beginning to work on an 7.1 system that has been partially configured. I need to pull some information to clean up the system but I'm fairly new to SQL.

Could someone help with a query that would give me all roles/users with their attributes?

Mine errors out and is for a single MSKEY:

SELECT * FROM MXUV_ENTRIES WHERE ( attrname in ('MX_FIRSTNAME') or attrname in ('MX_LASTNAME') or attrname in ('MX_ADMIN_UNIT')  or attrname in ('MX_MAIL_PRIMARY')  or attrname in ('MX_USERTYPE')  or attrname in ('DISPLAYNAME') or attrname in ('MX_MANAGER') or attrname in ('CUS_BOTTLER_ID' ) or attrname in ('mskeyvalue') )  and mskey = 446775

The other query that would be helpful would return userid or mskey of all users that have a null value for their password self-service authentication questions.

So we can get their managers on them to fill them out. Select all users with a null value for  MX_AUTHQ_001

And lastly if anyone could tell me what the  %ADMINMSKEY% is?

Regards,

Curtis

[Former Member · Accepted solution]

Hi Curtis,

For 7.1 you can try this, if on Oracle you need to remove the "with (nolock)":

select mskey,avalue from mxiv_pentries with (nolock)

where  attrName in ('MX_FIRSTNAME','MX_LASTNAME','DISPLAYNAME','MSKEYVALUE')

Expand the list of attributes as required. There's several views in 7.1, the simplest is mxiv_values which goes directly to mxi_values, mxiv_pentries (p for performance) joins in mxi_attributes, mxiv_sentries adds even further information, and mxiv_entries adds auditinformation and more. Try to avoid using more complex views than you need. There should be a document somewhere on SDN explaining the views.

%ADMINMSKEY% is replaced the internal userid of the logged in user in the WebUI.

To list entries that dont have password recovery questions defined you can try:

select mskey,searchvalue from mxiv_pentries P with (nolock) where AttrName='MSKEYVALUE'

and not exists

(select 1 from MXIV_PENTRIES Q with (nolock) where Q.AttrName = 'MX_AUTHQ_001' and Q.MSKEY = P.MSKEY)

and exists

(select 1 from MXIV_PENTRIES E with (nolock) where E.AttrName = 'MX_ENTRYTYPE' and E.searchvalue = 'MX_PERSON' and E.MSKEY = P.MSKEY)

or add this to Peters suggested query to avoid returning roles, privileges or other non-person entrytypes that dont have the password recovery questionsdefined:

   and mskey in (select mskey from mxiv_pentries where attrname = 'MX_ENTRYTYPE' and searchvalue='MX_PERSON')

Keep in mind: The AVALUE column is NOT indexed, SEARCHVALUE is, but it is also uppercased. So filter on searchvalue, return avalue (where correct casing of values is required)

Br,

Chris

[former_member96398 · Accepted solution]

Thanks to both for the help.

[Former Member]

Hi Curtis

I haven't checked these and I can't remember what views are in 7.1 so I'm going directly to the tables.  There'll be more efficient methods.

Selecting users who don't have their password self service attributes set:

SELECT mskey FROM mxi_values WHERE mskey NOT IN

(SELECT mskey FROM mxi_values WHERE attr_id = (SELECT attr_id FROM mxi_attributes WHERE attrname = 'Password self service attribute'))

I'm not sure what you're trying to achieve with the first one.

If you just want to retrieve the user:

select * from idmv_entry_simple where mcmskey = xxxx;

The view is 7.2 so you might need to find the 7.1 view.  You can reference any attribute and ignore the ones you don't in the job.

Peter

[former_member96398]

Thanks, just had to change some of the table names. Appreciate the help.

Curtis