Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field
Hi there,
When talking about Instance Scan, mostly mentioned is the ability to perform checks on code, certain settings on Business Rules, Client Scripts, Script Includes, etcetera. In my opinion: Instance Scan can be utilized for way more! I've shared in the past several thoughts on this, like Sanity checks, Core Configuration checks, and... Data checks!
Especially the Data checks I would like to give some attention again. With this article, I will share a nice example that will bring certain Data issues on your ServiceNow instance to the surface. Data checks that a System Administration should perform regularly, though which can be difficult to perform manually or which can be too time-consuming to perform. Data issues that might be minor, though which could also have serious consequences for related data / reporting / automation / etcetera.
Let's have a look at:
"Attachment target table sys_id does not exist"
Attachment target table sys_id does not exist
So why would you care about such? Recently I dived into the Database Footprint for three customers. When doing so, quickly the Attachment table stood out as this was for all three customers the number one table on the list. This could be correct. Though looking more closely at the attachments: several records without a "Table name", several records without a "Table sys ID", several records for deleted tables. My next thought was checking if the Table sys IDs were actually also valid, or that perhaps there are also several records for invalid Table sys IDs.
Checking for invalid Table sys IDs...
For one customer 30% of all records queries listed as invalid, which is a huge amount. Especially when the Database Footprint report shows several hundreds of GB, and 30% might be not useful. More interesting, where is this coming from? Is this works as expected, because new records are abandoned though attachments were already added? Is this because of deletion of records, which left attachments? Is this because incorrect development? Etcetera.
What better way to check such on a regular basis or at multiple customers with an Instance Scan check 🙂
Script Only Check
My thought for validating all attachments is basically querying and validating all records in the Attachment table. If not valid, create a Scan Finding. Generally with Instance Scan you could achieve such with a Table Check combined with the Script field. For the Attachment table though, we need to create a Script Only Check. Reason being:
While a Table Check is not an option, a Script Only Check would be possible. The first query to query the sys_attachment table could look something like:
// Define variables
var table_name = 'sys_attachment',
encoded_query = 'table_nameNOT LIKEinvisible.^table_name!=NULL^table_sys_id!=NULL';
var getRecord = new GlideRecord(table_name);
getRecord.addEncodedQuery(encoded_query);
getRecord._query();
// Query record
while(getRecord._next()) {
...
}
The encoded query basically to filter some unwanted situations, like the earlier mentioned empty Table name and empty Table sys ID.
When performing the while, a validation if the Table name is valid would prevent errors while running the script. You could argue if when a Table name is not valid, a Scan Finding should also be created. Though for this article, I'm just ignoring the invalid Table names. We do need to be aware that for a good portion of the records in the Attachment table, the Table name starts with "ZZ_YY". This is something to consider when validating the Table names.
if(gs.tableExists(getRecord.table_name.replace('ZZ_YY', ''))) {
...
}
Now it's just a matter of validating every individual attachment:
var validateRecord = new GlideRecord(getRecord.table_name.replace('ZZ_YY', ''));
validateRecord.addQuery('sys_id', getRecord.table_sys_id);
validateRecord.setLimit(1);
validateRecord._query();
// Create scan finding
if(!validateRecord.hasNext()) {
}
If there's no record to query, we can generate our Scan Finding:
engine.finding.setCurrentSource(getRecord);
engine.finding.increment();While this check is not about finding the reason why behind the Scan Findings which will be generated, it can be useful bringing potential issues to the surface. Based on this check, you could ofcourse look at more checks on the Attachment table. Like validating that the Table name is not empty, validating that the Table sys ID is not empty, validating that the Table name is valid, etcetera.
Note: As always test thoroughly on a sub-Production instance before performing this on a Production instance (or perform this on a recently cloned Production instance). Since the Attachment table can be fairly large, expect this Scan Check to run for quite some time!
---
And that's it! I hope this Instance Scan Check benefits you.
If you are interested in performing Instance Scan on your instance, feel free to reach out to me. For a small fee, I can add 700 Instance Scan Checks to your instance and generate a scorecard for you.
| C |
If this content helped you, I would appreciate it if you hit bookmark or mark it as helpful.
Interested in more Articles, Blogs, Videos, Podcasts, Share projects I shared/participated in? |
Kind regards,
Mark Roethof
Independent ServiceNow Consultant
4x ServiceNow Developer MVP
4x ServiceNow Community MVP
---
