How to report field utilization on a particular table

[Bobby Campbell]

For a given table, how can I rank the fields by how often they are used currently? 

For example: cmdb_ci_ups

• sys_id (100% utilized)
• name (100% utilized)
• IP Address (94% utilized)
• Serial Number (89% utilized)
• Owned By (40% utilized)
• Support Group (37% utilized)
• etc., down to many which are 0% utilized.

I want this data to help CI owners design forms and views that better align with the data contained in the tables.

[Mark Manders · Accepted solution]

You can't within ServiceNow. 

It's also a bit 'the other way around'. You forms shouldn't be created based on the fields that are used, they should be designed based on the fields you need. 

You can create list views and do a 'group by' on the field to see what the result is, or create a pie chart and check on the 'empty' percentage so you know what the used percentage is. 

But you should go to your CI-Class owners and ask them what information is needed and make those fields mandatory and for the nice to haves, you put them on the form. Anything else is just data in the system that is not being used, except maybe by integrations filling the data.

[Mark Manders · Accepted solution]

You can't within ServiceNow. 

It's also a bit 'the other way around'. You forms shouldn't be created based on the fields that are used, they should be designed based on the fields you need. 

You can create list views and do a 'group by' on the field to see what the result is, or create a pie chart and check on the 'empty' percentage so you know what the used percentage is. 

But you should go to your CI-Class owners and ask them what information is needed and make those fields mandatory and for the nice to haves, you put them on the form. Anything else is just data in the system that is not being used, except maybe by integrations filling the data.

[Kieran Anson · Accepted solution]

If you'd like stats to help, there is a lesser known functionality that can provide this.

1. Create a record in sys_column_stats_registry with the name of the table
2. For field name, you can use an asterisk (*) to select all fields
3. Go back to the list view of sys_column_stats_registry and select the record you just created followed by the 'Collect statistics now' list action
4. Once done, go to sys_column_stats and your results will be available as
   1. Cardinality - number of distinct values
   2. Not null count - records with a value in that field
   3. Null count - records without a value