Adam Stout
ServiceNow Employee
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
01-04-2018
02:35 PM
The discussion below is intended for advanced Performance Analytics users who have 6 or months experience with Performance Analytics and the same or more experience with the ServiceNow Platform. The advanced topics covered assume that you understand the basics of Performance Analytics and have a working knowledge of JavaScript.
There comes a time in every Analytics Administrator's life when they are faced with making some big changes to your configuration. The UI works great for configuring indicators or reports one at a time, but what do you do if you have to make hundreds or thousands of changes in a short amount of time? Let's look at some ways to save time when administering Performance Analytics.
One huge advantage of Performance Analytics is being part of the NOW Platform. That lets us use the same scripting we use elsewhere in the instance to make bulk changes to your analytics configuration. This can save you time as well as reducing the chances of missing a piece of the configuration.
Where to Run a Script
Before we begin, let's discuss where the best place to run a script is. There are a few options each with different pros and cons. Be sure to discuss your plans with your System Administrator to ensure you use an option that conforms to your organization's system controls.
Run a Background Script
If you have admin access to your instance, you can run a script directly on your instance. This is very quick to complete, but one typo can cause you lots of headaches. [Here is some more information on the Script - Background module]
Run a Fix Script
A safe option for background scripts is a fix script. This can run the same scripts that you can run as a background script, but it is saved in an update set and is easily rerun. This also has the benefit of allowing you to write your script in a sub-prod environment in an update set then moving the update set (with the fix script included) to a different instance to test and then finally moving it to production. [Here is some additional documentation on Fix Scripts]
Run a Background Script with an Update Set
Since most analytics objects are captured in update sets when you make changes to them, you can run a script to make all your changes in a sub-prod and capture the changes in an update set. The update set only contains the analytic object changes and does not contain the script that created/modified them. This is the safest option but may miss things depending on how old the clone is.
Which is Best?
While there are use cases for each of these options, I generally prefer to use a Fix Script as this goes through all the appropriate change controls but still ensure we configure all objects in the instance and can't fall victim to a stale clone.
What Can I Script?
In ServiceNow, you can script just about anything, but let's walk through a few examples of things that you might want to do in bulk in the Analytics Space. These are just quick examples and should always be tested in your environment prior to executing them in a production environment. No guarantee is made that these will work in your environment.
Before executing any script you should thoroughly test it in your environment. Scripts are very powerful and you must ensure that you understand what you are running before you run it. The scripts below are intended for discussion purposes only.
Add a Group to a Large Number of Reports
In this example, we were splitting one Group ("Group A") into two. As part of the migration, we needed to add the new group ("Group B") to all the reports that "Group A" had access to so everyone retained access to all the same reports they did today.
This script takes advantage of all the permissions being stored in the table "sys_report_users_groups". While we normally configure permissions with the Report UI, we can make changes using GlideRecord as well.
// MODIFY THESE TWO LINES - Set the group name you are matching and the one you are adding
var origGroup = 'Group A';
var newGroup = 'Group B';
// get the sys_id of the group from the name
var getGroupID = function (name)
{
// if the name is a 32 character string, assume it is a sys_id
if(/^[0-9a-z]{32}$/ig.test(name))
{
return name;
}
var gr = new GlideRecord('sys_user_group');
gr.addQuery('name', '=', name);
gr.query();
var groupCount = gr.getRowCount();
// if we find one and only one, use it
if(groupCount == 1)
{
gr.next();
return gr.getValue('sys_id');
} if(gr.getRowCount() == 0) {
_log('No group found with the name: ' + name);
return false;
} else if(gr.getRowCount() > 1) {
_log("AHHHHH!!!!! There is more than one - " + name + " specify the sys_id instead of the name") ;
return false;
}
};
// add the new group everywhere the original group is named
var copyReportPermissions = function (origGroup, newGroup)
{
var gr = new GlideRecord('sys_report_users_groups');
gr.addQuery('group_id', '=', origGroup);
gr.query();
var groupsFound = parseInt(gr.getRowCount());
// loop through the report permission records for this dashboard/group
while(gr.next())
{
// change the group on the record
gr.setValue('group_id', newGroup);
// insert the record (which makes a copy and does not update the existing record)
gr.insert();
}
// return the number of records added
return gr.groupsFound();
};
// logging function so it is easy to change the output
var _log = function (str)
{
gs.log(str, 'PARPermCopy');
//gs.addInfoMessage(str);
}
// translate the names to sys_ids
var origGroupID = getGroupID(origGroup);
var newGroupID = getGroupID(newGroup);
if(origGroupID && newGroupID)
{
_log("Report Permissions Copied: " + copyReportPermissions(origGroupID, newGroupID));
} else {
_log("One or both groups were not found");
}
_log('Done');
Dashboard permissions are stored in a very similar (but not identical) table called "pa_dashboards_permissions". If you needed to make bulk permissions changes to Dashboards, a modified version of the script above may come in handy.
Add an Additional Breakdown to Indicators
In this example, we add a new breakdown (Assigned Location) to every indicator that has an existing breakdown (Assigned To). To change the breakdowns being checked, you just need to modify line 2 & 3 to set the Base Indicator (the one we are copying) and the New Indicator (that one we are adding everywhere the Base Indicator is added).
// MODIFY THESE TWO LINES - Set the breakdowns you are matching and the one you are adding
var baseBreakdown = 'Assigned To'; // the existing indicator
var newBreakdown = 'Assigned Location'; // the new indicator (be sure you have already created it)
// get the indicators that have the base indicator but do NOT have the new one
var getIndicators = function (existingBreakdown, newBreakdown)
{
// get the ones that already have the new one
var gr2 = new GlideRecord('pa_indicator_breakdowns');
gr2.addQuery('breakdown', '=' , newBreakdown);
//gr.setLimit(5);
gr2.query();
var existingNew = [];
while(gr2.next())
{
existingNew.push(gr2.indicator.toString());
}
// get the ones that need the new breakdown
var gr = new GlideRecord('pa_indicator_breakdowns');
gr.addQuery('breakdown', '=' , existingBreakdown);
gr.addQuery('indicator', 'NOT IN', existingNew.join(','));
gr.query();
_log("New Already Exists: " + gr2.getRowCount() + " - Need to Add: " + gr.getRowCount() + " - Query:" + gr.getEncodedQuery());
return gr;
};
// insert the new breakdown if it doesn't already exist
var upsertIndicatorBreakdown = function (indicator, breakdown)
{
// check if the new breakdown is already associated to the indicator
var gr = new GlideRecord('pa_indicator_breakdowns');
gr.addQuery('indicator', '=', indicator);
gr.addQuery('breakdown', '=', breakdown);
gr.query();
if(gr.hasNext())
{
// if it is already associated, do nothing
_log('already exists: ' + indicator);
return;
} else {
// if not there, add it
_log('need to create for indictor breakdown: ' + indicator);
var gr = new GlideRecord('pa_indicator_breakdowns');
gr.setValue('indicator', indicator);
gr.setValue('breakdown', breakdown);
gr.insert();
return;
}
};
// get the sys_id given the name
var getBreakdown = function (name)
{
// if the name is a 32 character string, assume it is a sys_id
if(/^[0-9a-z]{32}$/ig.test(name))
{
return name;
}
var gr = new GlideRecord('pa_breakdowns');
gr.addQuery('name', '=' , name);
gr.query();
// if you have a duplicate name, we don't know which one so abort (modify the script to set the sys_id manually)
if(gr.getRowCount() > 1)
{
_log("AHHHHH!!!!! There is more than one - " + name + " specify the sys_id instead of the name") ;
}
gr.next();
// return the sys_id of the breakdown
return gr.getValue('sys_id');
};
// logging function so it is easy to change the output
var _log = function (str)
{
gs.log(str, 'PAAddBreakdown');
//gs.addInfoMessage(str);
}Note: This script assumes that there is only one breakdown with the name you set (Assigned To and Assigned Location in the example). If this is not the case, you need to manually select the sys_id instead of the name on lines 2 & 3. If the value in the array is a 32 character alphanumeric string, we assume it is a sys_id.
// MODIFY THESE TWO LINES - Set the breakdowns you are matching and the one you are adding
var baseBreakdown = 'Assigned To'; // the existing indicator
var newBreakdown = 'Assigned Location'; // the new indicator (be sure you have already created it)
// get the indicators that have the base indicator but do NOT have the new one
var getIndicators = function (existingBreakdown, newBreakdown)
{
// get the ones that already have the new one
var gr2 = new GlideRecord('pa_indicator_breakdowns');
gr2.addQuery('breakdown', '=' , newBreakdown);
//gr.setLimit(5);
gr2.query();
var existingNew = [];
while(gr2.next())
{
existingNew.push(gr2.indicator.toString());
}
// get the ones that need the new breakdown
var gr = new GlideRecord('pa_indicator_breakdowns');
gr.addQuery('breakdown', '=' , existingBreakdown);
gr.addQuery('indicator', 'NOT IN', existingNew.join(','));
gr.query();
_log("New Already Exists: " + gr2.getRowCount() + " - Need to Add: " + gr.getRowCount() + " - Query:" + gr.getEncodedQuery());
return gr;
};
// insert the new breakdown if it doesn't already exist
var upsertIndicatorBreakdown = function (indicator, breakdown)
{
// check if the new breakdown is already associated to the indicator
var gr = new GlideRecord('pa_indicator_breakdowns');
gr.addQuery('indicator', '=', indicator);
gr.addQuery('breakdown', '=', breakdown);
gr.query();
if(gr.hasNext())
{
// if it is already associated, do nothing
_log('already exists: ' + indicator);
return;
} else {
// if not there, add it
_log('need to create for indictor breakdown: ' + indicator);
var gr = new GlideRecord('pa_indicator_breakdowns');
gr.setValue('indicator', indicator);
gr.setValue('breakdown', breakdown);
gr.insert();
return;
}
};
// get the sys_id given the name
var getBreakdown = function (name)
{
// if the name is a 32 character string, assume it is a sys_id
if(/^[0-9a-z]{32}$/ig.test(name))
{
return name;
}
var gr = new GlideRecord('pa_breakdowns');
gr.addQuery('name', '=' , name);
gr.query();
// if you have a duplicate name, we don't know which one so abort (modify the script to set the sys_id manually)
if(gr.getRowCount() > 1)
{
_log("AHHHHH!!!!! There is more than one - " + name + " specify the sys_id instead of the name") ;
}
gr.next();
// return the sys_id of the breakdown
return gr.getValue('sys_id');
};
// logging function so it is easy to change the output
var _log = function (str)
{
gs.log(str, 'PAAddBreakdown');
//gs.addInfoMessage(str);
}
// get the indicators that have the base indicator but do NOT have the new one
var neededIndicators = getIndicators(getBreakdown(baseBreakdown), getBreakdown(newBreakdown));
_log(neededIndicators.getRowCount() + " indicators that need to have " + newBreakdown + " added")
while(neededIndicators.next())
{
upsertIndicatorBreakdown(neededIndicators.getValue('indicator'), newIndicator);
}
_log("Done");Add Breakdown Exclusion Everywhere Breakdowns Exist
In this example, we are adding a breakdown exclusion for "Assigned To" and "Assigned Location" to every indicator that has both of these breakdowns. To change the breakdowns being checked, you just need to modify line 2 (the values in the "levels" array).
Note: This script assumes that there is only one breakdown with the name you set (Assigned To and Assigned Location in the example). If this is not the case, you need to manually select the sys_id instead of the name on line 2. If the value in the array is a 32 character alphanumeric string, we assume it is a sys_id.
// MODIFY THIS - Which breakdowns are we checking for needed exclusions?
var levels = ['Assigned To', 'Assigned Location'];
// Get all indicators with both breakdowns
var getIndicators = function (level1, level2)
{
var gr = new GlideRecord('pa_indicator_breakdowns');
gr.addQuery('breakdown', '=' , level1);
gr.query();
var has1 = [];
// create an array with all the indicators with the 1st breakdown
while(gr.next())
{
has1.push(gr.indicator.toString());
}
// query for indicators that have both the 1st (in list) breakdown and the 2nd breakdown
var gr2 = new GlideRecord('pa_indicator_breakdowns');
gr2.addQuery('indicator', 'IN', has1)
gr2.addQuery('breakdown', '=' , level2);
gr2.query();
// create an array of the indicators with both breakdowns
var hasBoth = [];
_log('has level 1: ' + gr.getRowCount() + ' has level 2: ' + gr2.getRowCount());
{
hasBoth.push(gr2.indicator.toString());
}
// return the array with indicators with both breakdowns
return hasBoth;
};
// check if the breakdown exclusion is already there, if not add it
var upsertExclusion = function (indicator, breakdown1, breakdown2)
{
// query for the needed exclusion
var gr = new GlideRecord('pa_indicator_breakdown_excl');
gr.addQuery('indicator', '=', indicator);
gr.addQuery('breakdown', '=', breakdown1);
gr.addQuery('breakdown_level2', '=', breakdown2);
gr.query();
if(gr.hasNext())
{
// it already exists, do nothing
_log('already exists: ' + indicator);
return;
} else {
// it doesn't exist, add it
_log('need to create for indictor: ' + indicator);
var gr = new GlideRecord('pa_indicator_breakdown_excl');
gr.setValue('indicator', indicator);
gr.setValue('breakdown', breakdown1);
gr.setValue('breakdown_level2', breakdown2);
gr.insert();
return;
}
};
// get the sys_id given the name
var getBreakdown = function (name)
{
// if the name is a 32 character string, assume it is a sys_id
if(/^[0-9a-z]{32}$/ig.test(name))
{
return name;
}
var gr = new GlideRecord('pa_breakdowns');
gr.addQuery('name', '=' , name);
gr.query();
// if you have a duplicate name, we don't know which one so abort (modify the script to set the sys_id manually)
if(gr.getRowCount() > 1)
{
_log("AHHHHH!!!!! There is more than one - " + name + " specify the sys_id instead of the name") ;
}
gr.next();
// return the sys_id of the breakdown
return gr.getValue('sys_id');
};
// logging function so it is easy to change the output
var _log = function (str)
{
gs.log(str, 'PAAddBreakdownExcl');
//gs.addInfoMessage(str);
}
// loop through all the options
for(var l = 0; l < levels.length; l++)
{
// loop through the list again to get all the permutations
for(var l2 = 0; l2 < levels.length; l2++)
{
// since order does not matter, make sure we only insert the records once
if(l >= l2)
{
continue;
}
_log(levels[l] + ': ' + getBreakdown(levels[l]) + ' - ' + levels[l2] + ': ' + getBreakdown(levels[l2]));
// get the indicators that need to have the exclusion
var indicators = getIndicators(getBreakdown(levels[l]), getBreakdown(levels[l2]));
for(var i = 0; i < indicators.length; i++)
{
// Insert the exclusion if it is missing
upsertExclusion(indicators[i], getBreakdown(levels[l]), getBreakdown(levels[l2]));
}
}
}
_log('Done');This may be useful to run on combinations that are never looked at. For instance, Assigned to/Priority. Do we trend the priority by the person working on it? If we do, leave it, but if not, use this script to quickly exclude it.
Conclusion
Hopefully, these examples give you enough to get started with Scripting for Analytics. There is no reason to spend hours clicking on things when you have the power of the Now Platform at your fingertips.
- 2,281 Views
4 Comments
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
