TNT: Useful Script - Find records created/updated within a time window

[Jim Coyne]

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.