Unexpected results when running report with Model ID is not empty

[Robert Campbell]

When creating a report to show recently discovered cis that have a Model ID, I still get many results that have no model ID.  Model ID actually shows as empty.  Is there a workaround for this?

I'm not sure if this is the proper location.  I was thinking it would fall under reporting which I would think would fall under Now Platform but I don't see it.  Since it is cmdb data that I'm referring to, I used this forum.

[CMDB Whisperer]

It is most likely the case that you are showing "(empty)" for the Model ID field because it has an invalid reference value in the Model ID.  I have seen this dozens of times.  What often happens is that someone creates a transform map to import CIs, and they put the name of the model into the Model column, and then they map that column to the Model ID field, which is of course a Reference field and thus requires a Sys_ID value.  Unfortunately ServiceNow has no built-in logic to validate that the value you provided is actually a Sys_ID value, and it just blindly puts the name of the Model into the Model ID field.  When you go to filter your CIs where Model ID is not null, it will display those CIs where the Model ID field contains something other than a Sys_ID, because it meets the criteria of "not empty", but when ServiceNow displays the Model ID field value, either in a list or in a form, it will display its Display Value, which it tries to get from the display name of the model record.  But since that model record doesn't exist, it displays the string "(empty)".  Another way this can happen is that a valid record exists but was deleted without dereferencing it first.  This can happen for a variety of different reasons, such as skipping the workflow in a business rule, or modifying the reference cascade rules in the dictionary entry.

Technically this can and does happen in any area of the platform, but I find the Model ID = Model Name scenario to be a very common mistake that gives this result. 

To properly query for Model ID not empty, you actually need to dot-walk to an attribute of the model.  For example, if I have a CI with an invalid reference value in the Model ID field, you will see the following:

• (model_id != null) is true
• (model_id.sys_id != null) is false

Because the model ID field contains an invalid value, it is still not null.  But if you try to reference it and get its sys_id value, it will attempt to retrieve the record and that record does not exist.  So in order to really check for null state on any reference field, the most thorough way to do this is to say "does this reference field have a non-null value, and is the Sys ID of that record also non-null".

[Robert Campbell]

That all made sense.  I did change the report to Model ID.Sys ID is not empty but that didn't fix the issue of it having a once valid sys ID. Records do show that seem to have a sys_id (because I group by model_id) but that sys_id doesn't actually exist because the model_id for that record is still empty (which makes sense from what you said).  I tried:

• Model ID.Sys ID is not empty
• and Model ID is not empty
• and Model ID does not contain 'Unknown' (which won't catch anything but that is how it's translated')

Do I need to do this in an script include and if so, would it then be where ci.sys_id is one of array of sys_ids?

[CMDB Whisperer]

Can you post a screen shot of your query and/or right-click on the query and select Copy Query command from the popup menu?  I want to make sure we are precise here.  Also another possibility is that you have some Product Model records that don't have a display name set.  Usually this should be set automatically but it is possible that some rogue import process created Product Models that don't have a Display Name set.  In that case you would have a Model ID that is not empty and a Model ID.Sys ID that is not empty, but the field still shows as empty when displayed because there is no display value to display.

[Robert Campbell]

This seems to be a clear case of bad data that needs to be cleaned up but I'd still like to know why this data is so bad and to quickly identify that bad data as well as exclude the bad data (which I'm trying to do here).  I need to take a closer look to see what they are doing with the imports and discovery that has these bad records.

last_discovered>javascript:gs.endOfLastMonth()^model_id.sys_idISNOTEMPTY^ORmodel_idISNOTEMPTY^nameISNOTEMPTY^manufacturerISNOTEMPTY