Skip to Content

How to check if an attribute IS NOT present

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!



Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Jul 20, 2016 at 07:47 PM

    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).



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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • Posted on Jul 20, 2016 at 06:38 PM

    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


    *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 taskname from mxp_tasks with (nolock) where taskid = s.TaskId) taskName, s.*

    from mxp_audit s with (nolock) where mskey = 12345

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.