Part of the Tips 'N Tricks" series.
Other Useful Scripts:
TNT: Useful Script - Find a record by its sys_id
Here's another script I use from time to time. It allows you to search for records created and or updated within a certain timeframe. Useful when you want to know what all happens when you create or update a particular record.
There's an option (addLink = true) to output message with a link to the record so you can just click on the link and jump to the record. You will have to copy/paste the link if running as a background script or just click on the link if you are using the Xplore: Developer Toolkit:
When "addLink = false", you'll just get the record details. The class display label, table name and the record's display value are shown.
// Search for records created and/or updated in the last "x" seconds
//
// Optionally add a hyperlink to the record that was found.
// Useful when running the script in the 'Xplore: Developer Toolkit' - https://share.servicenow.com/app.do#/detailV2/9a1be70e13800b000de935528144b04c/overview
// To add a link, set the "addLink" variable to "true"
(function(){
//options
var created = true; //look for records created within the specified window
var updated = true; //look for records updated within the specified window
var secondsAgo = 60; //how many seconds ago were the records created/updated (integer)
var window = 600; //how large a window, in seconds, should we look at (integer)
var addLink = true; //set to "true" to add a link to the record in the output (Xplore)
var restrictToUser = "user_id"; //user ID of the user you want to restrict the search to. Leave blank to look for any user
//initialize
var userQuery = "";
var tableName = "";
var tableLabel = "";
var tableWeWantToSearch = true;
var recordCount = 0;
var foundRecord = false;
var message = "";
try {
//make sure we are restricting by one or the other
if (!created && !updated){
gs.addInfoMessage("Need to restrict by either the Created or Updated dates (both are fine)");
return;
}
//verify the times
secondsAgo = Math.abs(parseInt(secondsAgo));
window = Math.abs(parseInt(window));
if (isNaN(secondsAgo) || isNaN(window)){
gs.addInfoMessage("Please specify a proper value for both 'secondsAgo' and 'window'");
return;
}
var startTime = new GlideDateTime(); //current date/time in UTC
startTime.subtract(secondsAgo * 1000); //get the starting time
var endTime = new GlideDateTime(startTime);
endTime.addSeconds(window); //and then the end time
gs.addInfoMessage("Timeframe is between " + startTime + " and " + endTime + " UTC");
//should we limit the search for records created/updated by a particular user or not?
if (restrictToUser != ""){
var restrictedMessage = "Limited to records ";
if (created){
userQuery = "sys_created_by=" + restrictToUser;
restrictedMessage += "created ";
}
if (updated){
if (userQuery !=""){
userQuery += "^ORsys_updated_by=" + restrictToUser;
restrictedMessage += "or updated ";
} else {
userQuery = "sys_updated_by=" + restrictToUser;
restrictedMessage += "updated ";
}
}
userQuery += "^"; //need to add an "AND" condition to the filter
gs.addInfoMessage(restrictedMessage + "by '" + restrictToUser + "'");
}
//loop through all the valid base-class tables (no need to look at any sub-classes because the records are contained in the base-class as well)
var table = new GlideRecord("sys_db_object");
table.addEncodedQuery("super_class=NULL^label!=^nameISNOTEMPTY");
table.orderBy("name");
table.query();
while (table.next()){
//get the table name and label
tableName = (table.getValue("name") + "").toLowerCase();
tableLabel = (table.getValue("label")+ "").toLowerCase();
tableWeWantToSearch = true; //assume it is a table we want to look into
//skip views and some other tables that return a lot of probably irrelevant records
//just comment out one of the following lines if you want to include the table(s) in the search
if (tableName.indexOf("v_") == 0) tableWeWantToSearch = false; //views
else if (tableName == "ts_c_attachment") tableWeWantToSearch = false; //text search indices
else if (tableName == "ts_chain") tableWeWantToSearch = false; //..
else if (tableName == "ts_document") tableWeWantToSearch = false; //..
else if (tableName == "ts_phrase") tableWeWantToSearch = false; //..
else if (tableName == "ts_word") tableWeWantToSearch = false; //..
else if (tableName == "ts_word_roots") tableWeWantToSearch = false; //..
else if (tableLabel.indexOf("text index ") == 0) tableWeWantToSearch = false; //..
else if (tableLabel.indexOf("ts index stats") == 0) tableWeWantToSearch = false; //..
else if (tableLabel.indexOf("recorded incremental change") == 0) tableWeWantToSearch = false;
else if (tableName.indexOf("sh$") == 0) tableWeWantToSearch = false;
else if (tableLabel.indexOf("rollback sequence") == 0) tableWeWantToSearch = false;
else if (tableLabel.indexOf("score level") == 0) tableWeWantToSearch = false;
else if (tableLabel.indexOf("pa favorites") == 0) tableWeWantToSearch = false;
else if (tableName.indexOf("syslog") == 0) tableWeWantToSearch = false;
else if (tableName.indexOf("sys_cache_flush") == 0) tableWeWantToSearch = false;
else if (tableName.indexOf("sys_db_cache") == 0) tableWeWantToSearch = false;
else if (tableName.indexOf("sysevent") == 0) tableWeWantToSearch = false; //***** you may want to look at this one sometimes, depending on what you are looking for
if (tableWeWantToSearch){
var searchTable = new GlideRecord(table.getValue("name"));
//make sure it is a valid table first
if (searchTable.isValid()){
searchTable.addEncodedQuery(userQuery + "sys_created_onBETWEEN" + startTime + "@" + endTime + "^ORsys_updated_onBETWEEN" + startTime + "@" + endTime);
//message = searchTable.getEncodedQuery();
//gs.addInfoMessage(message);
searchTable.query();
while(searchTable.next()){
foundRecord = true;
recordCount ++;
_showFoundRecord();
}
} else {
message = "***** Trying to search an invalid table name called '" + table.getValue("name") + "' - the sys_id of that sys_db_object record is '" + table.getValue("sys_id") + "'";
gs.addInfoMessage(message);
}
}
}
if (foundRecord == true){
gs.addInfoMessage(recordCount + " record(s) found");
} else {
gs.addInfoMessage("No records were found");
}
} catch(err) {
gs.log("ERROR: " + err);
}
function _showFoundRecord(){
var details = searchTable.getDisplayValue();
if (addLink == true) {
details = "<a href='" + gs.getProperty('glide.servlet.uri') + "nav_to.do?uri=" + searchTable.getLink() +"' target='_blank'>" + searchTable.getDisplayValue() + "</a>";
}
message = "Found a record of type '" + searchTable.getClassDisplayValue() + "' (" + searchTable.getRecordClassName() + ") called '" + details + "'";
gs.addInfoMessage(message);
}
})();
Note: Remember, running scripts as a background script or the Xplore tool can modify data so be aware what you are running. Always a good idea to test it out on your personal development instance first.
