How to check if an attribute IS NOT present

[former_member2987]

Hello everyone,

So I have a fun one... As part of my current project, I need to write a query to check if a particular attribute is not assigned.  Not NULL, but not a part of the IDM entry (as in an attribute that was once populated and was removed via {D})

So in essence, I want to see all entries (MX_PERSON to be exact) that do not have a given attribute(s). In my case it's MX_FIRSTNAME, MX_LASTNAME, and DISPLAYNAME.  We have a process that's blanking those out, causing some issues so we need to go find and fix them.

If someone has a better way of considering this problem, I'm open to that too!

I'm running this project on Oracle, but if you come up with something in MS SQL, hopefully I can work with it.

Thanks for your help!

Cheers,

Matt

[Steffi_Warnecke · Accepted solution]

Hello Matt,

in reports or statements to find entries to fix, I use stuff like

select * from idmv_entry_simple wheremcmskey is NOT in (select mskey from idmv_value_basic where attrname = 'MX_LASTNAME')

a lot.

I had a similar task as you now (finding identities with windows accounts, where those 3 attributes were not filled) and so I just created a SQL statement that resulted in all identities, where one of those attributes was not filled (using the statement above with OR to also check the other two).

Regards,

Steffi.

PS: I'm on Oracle, as you know. ^^

[ole_k_rosberg · Accepted solution]

Hi Matt,

Seems like you have already got what you want, but anyway, this is my way:

select mcMSKEYVALUE from idmv_entry_simple with (nolock) where mcEntryType = 'MX_PERSON'

and mcMSKEY not in (select mskey from idmv_value_basic with (nolock) where attrname = 'DISPLAYNAME')

union

select mcMSKEYVALUE from idmv_entry_simple with (nolock) where mcEntryType = 'MX_PERSON'

and mcMSKEY not in (select mskey from idmv_value_basic with (nolock) where attrname = 'MX_FIRSTNAME')

union

select mcMSKEYVALUE from idmv_entry_simple with (nolock) where mcEntryType = 'MX_PERSON'

and mcMSKEY not in (select mskey from idmv_value_basic with (nolock) where attrname = 'MX_LASTNAME')

This will give a distinct list of all persons missing one or more of the attributes.

Regards,

Ole K.

[former_member2987 · Accepted solution]

Steffi, I'm getting an error with yours.... ORA 00908 at column 53

Any ideas, also going to try OleK's idea

(and yes, I remember that you are on Oracle )

[former_member2987 · Accepted solution]

Hi Ole, this query is pretty amazing.  However, can I also get mskey?

[former_member2987 · Accepted solution]

fixed it.  needed to remove the IS keyword.  I'll have to play with this more tomorrow.

Matt

[Steffi_Warnecke · Accepted solution]

Meh, sorry. I started with the written statement and then switched to actual SQL and forgot to take that out. 😕

For mskey just change "mcMSKEYVALUE" to "mcmskey as mskey". I was taught to always go "mcmskey as mskey", because jobs tend to not work when the result is not "mskey".

[ole_k_rosberg · Accepted solution]

Hi Matt,

Sure you can get the MSKEY. It would be select mcMSKEY, mcMSKEYVALUE from idmv_entry_simple ....

Also if you have several IdStores and want this check to be valid for only one of them, you have to add the IdStor to the queries.

select mcMSKEY, mcMSKEYVALUE from idmv_entry_simple with (nolock) where mcEntryType = 'MX_PERSON' and mcIdStore = 1

and mcMSKEY not in (select mskey from idmv_VALUE_BASIC where attrname = 'DISPLAYNAME' and IS_ID = 1)

union

select mcMSKEY, mcMSKEYVALUE from idmv_entry_simple with (nolock) where mcEntryType = 'MX_PERSON' and mcIdStore = 1

and mcMSKEY not in (select mskey from idmv_VALUE_BASIC where attrname = 'MX_FIRSTNAME' and IS_ID = 1)

union

select mcMSKEY, mcMSKEYVALUE from idmv_entry_simple with (nolock) where mcEntryType = 'MX_PERSON' and mcIdStore = 1

and mcMSKEY not in (select mskey from idmv_VALUE_BASIC where attrname = 'MX_LASTNAME' and IS_ID = 1)

Ole K.

[former_member2987 · Accepted solution]

Steffi,

Yes, MSKEY works better for joins, and I know I can rename, but I always like to work with views that use MSKEY.  I should probably break myself of that habit.

Matt

[Steffi_Warnecke · Accepted solution]

But... mcmskey is mskey. Just the same with another name. At least that's what I was told. ^^

[ole_k_rosberg · Accepted solution]

Hi,

Yes, mcMSKEY and MSKEY is the same.

mcMSKEY is used in mxi_entry and in the views of this table, other tables and views use MSKEY.

Some views even has both 🙂

Ole K.

[former_member201064 · Accepted solution]

The link and entry views are really neat though. If they wouldn't exist, I'd build them myself.

Actually we added a function for transposition of the most relevant attributes and a lot of views / procedures for our own request forms and our daily work.

The function got a bit slow when we went 35000+ identities, but in the redesign I got that covered.

[former_member201064]

Hi Matt,

depends where you need it. I'll use the value view and DISPLAYNAME in my examples.

In a conditinal task:

Select count(*) from idmv_value_ext where mskey = %mskey% and attrname = 'DISPLAYNAME'.

And placing whatever you need in the false part. But I guess that's not really what you want.

In a Source tab of a batch job, where you want to find all entries which don't have that attribute and using the Identity Store option:

select mcmskey mskey from idmv_entry_simple where mcEntryType = 'MX_PERSON' where not mcmskey in (select mskey from idmv_value_ext where attrname = 'DISPLAYNAME')

In a Source tab where you don't want to use the Identity Store option:

select * from (

select whatever you want, adding the displayname as column with a sub-select* in the select part

) allrows where displayname is null

No problems with performance on that outer select / correlated subquery

If you want to double check on the history view if the value was once present each query has to be joined with the mxiv_oentries (idmv_ovalue_basic_all) adding a "where o.attrname = 'DISPLAYNAME' with the history view named o and the value named v or something.

The queries should work for MSSQL (with (nolock) should be added though on all views) and Oracle. Yet, I am not entirly sure about the outer select / correlated subquery thingy.

Best regards

Dominik

*Example of what I mean with sub-select as I always keep on forgetting what the name for this is. The example has nothing to do with your question, I just copied that query out of my saved queries:

select

(select taskname from mxp_tasks with (nolock) where taskid = s.TaskId) taskName, s.*

from mxp_audit s with (nolock) where mskey = 12345