Hi @matthew_hughes ,
I have handled a very similar pattern before, and I would not try to solve this only inside the Database View.
A ServiceNow Database View is mainly for joining tables and exposing the joined result as a pseudo-table. It is not a good place to do SQL-style DISTINCT, GROUP BY, or conditional aggregation such as:
If Parent is empty, count the software model itself.
If Parent is populated, count the parent software model instead.
That logic needs a normalized “key model” value first.
The cleanest approach I would recommend is:
1. Create a field on the Software Model table, for example:
u_key_software_model
Type: Reference
Reference table: Software Model table
2. Populate it with this logic:
If Parent is empty:
u_key_software_model = current Software Model
If Parent is not empty:
u_key_software_model = Parent Software Model
3. Use that field in your Database View / Report.
4. In the report, use Count Distinct on u_key_software_model.
That way your example becomes:
- Unicom Systems solidDB 7.0 has no parent, so the key model is itself.
- Red Hat 9.2 / 9.3 / 9.4 all have the same parent, so the key model is the parent.
- The report can then count distinct key models correctly.
You can populate the field with a before Business Rule on insert/update of the Software Model record, or with a scheduled job if the parent relationship is maintained externally.
Example logic:
(function executeRule(current, previous) {
if (current.parent) {
current.u_key_software_model = current.parent;
} else {
current.u_key_software_model = current.sys_id;
}
})(current, previous);
Adjust the field name if your parent field is custom, for example u_parent instead of parent.
One thing to watch out for: if you only use a Database View over server/software/application relationships, you will naturally get duplicate rows because one software model can be discovered on multiple servers and related to multiple applications. That is expected behavior. The Database View can show the joined records, but the unique business count should be handled by reporting on a stable key field.
If you only need the count, you may not need a Database View at all. A report with Count Distinct on the normalized key model field may be enough.
If you need one row per key software model for operational review, then I would create either:
- a report grouped by u_key_software_model, or
- a small custom summary table populated by a scheduled job, if the logic is complex and used by multiple dashboards.
In short, I would not rely on the Database View to manipulate the rows into unique key models. Create a normalized Key Software Model field first, then use Count Distinct or grouping on that field.
References:
- ServiceNow Database Views are intended to join tables and expose the joined result for lists/reports:
https://www.servicenow.com/docs/r/bAHg1_u_N~ocpdu34t_qJA/fREynLm1Y3_~wcYdmhzKcg
- ServiceNow reporting supports Count Distinct as an aggregate option:
https://www.servicenow.com/docs/r/now-intelligence/reporting/r_AdministerReports.html
Thank you,
Vikram Karety,
ServiceNow Architect,
Octigo Solutions INC
