eQL for displaying containers with a "Warning" (Missing Mandatory data)

I apologize. I was about to answer this last week but I got sidetracked and then forgot.

The good news is yes you can do this. The bad news is that there isn't a clean, concise way to write this report.

First you have to know which attributes are mandatory. You can look at the attributes by object type in Director>system administration (F8) or you can write an attribute definition eQL query, as below, if you want a convenient list.

* * * * * *

START WITH AttributeDef 
SELECT 
   Id,
   Name,
   DISPLAY_NAME( DataType ) 
WHERE 
   IsMandatory = 'Y' 
   AND
   ObjectType = 3
ORDER BY 
   Name

* * * * * *

This will list all the attributes that are mandatory for documents (that's what ObjectType = 3 means). Other types, for example are 1 for physical items, 6 for change requests and 212 for tags.

If you want to look at attributes across object types, remove AND ObjectType = 3 from the WHERE clause and add ObjectType to the SELECT list. Unfortunately DISPLAY_NAME() does not apply to object types, so if you want to translate the numbers into human-readable names you'll have to do it manually with a CASE statement.

Anyway, once you know which attributes are mandatory, you can write the report(s). At this point, if you are concerned with more than one object type, you need to write a separate report for each type, For example:

* * * * * *

START WITH Document
SELECT
Id,
Code,
Revision,
Name
WHERE
IsTemplate = 'N'
AND

(

Attributes["Global", "[Your Mandatory Attribute 1]".Value IS NULL

OR 

Attributes["Global", "[Your Mandatory Attribute 2]".Value IS NULL

OR 

Attributes["Global", "[Your Mandatory Attribute 3]".Value IS NULL

[OR . . .]

)
ORDER BY
Code,
Revision

* * * * * *

The text in square brackets [] is informational, and not literally part of the query. Note that the IsTemplate = 'N' is important here, because mandatory attributes are not usually populated on templates and you don't want these cluttering up the output.

So if your configuration has a large number of mandatory attributes and you are concerned with them all, this query may get pretty long. This is not a problem for eQL, some queries have run into thousands of characters, it is just cumbersome for us humans.

I apologize. I was about to answer this last week but I got sidetracked and then forgot.

The good news is yes you can do this. The bad news is that there isn't a clean, concise way to write this report.

First you have to know which attributes are mandatory. You can look at the attributes by object type in Director>system administration (F8) or you can write an attribute definition eQL query, as below, if you want a convenient list.

* * * * * *

START WITH AttributeDef 
SELECT 
   Id,
   Name,
   DISPLAY_NAME( DataType ) 
WHERE 
   IsMandatory = 'Y' 
   AND
   ObjectType = 3
ORDER BY 
   Name

* * * * * *

This will list all the attributes that are mandatory for documents (that's what ObjectType = 3 means). Other types, for example are 1 for physical items, 6 for change requests and 212 for tags.

If you want to look at attributes across object types, remove AND ObjectType = 3 from the WHERE clause and add ObjectType to the SELECT list. Unfortunately DISPLAY_NAME() does not apply to object types, so if you want to translate the numbers into human-readable names you'll have to do it manually with a CASE statement.

Anyway, once you know which attributes are mandatory, you can write the report(s). At this point, if you are concerned with more than one object type, you need to write a separate report for each type, For example:

* * * * * *

START WITH Document
SELECT
Id,
Code,
Revision,
Name
WHERE
IsTemplate = 'N'
AND

(

Attributes["Global", "[Your Mandatory Attribute 1]".Value IS NULL

OR 

Attributes["Global", "[Your Mandatory Attribute 2]".Value IS NULL

OR 

Attributes["Global", "[Your Mandatory Attribute 3]".Value IS NULL

[OR . . .]

)
ORDER BY
Code,
Revision

* * * * * *

The text in square brackets [] is informational, and not literally part of the query. Note that the IsTemplate = 'N' is important here, because mandatory attributes are not usually populated on templates and you don't want these cluttering up the output.

So if your configuration has a large number of mandatory attributes and you are concerned with them all, this query may get pretty long. This is not a problem for eQL, some queries have run into thousands of characters, it is just cumbersome for us humans.

Thanks again for your help! :-)