SERVICENOW FUN

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Scheduled Scripts======
----

====Application Update Checker==== 
<code>
// JWJ03.14.24  This will check all applications installed to see if there is an update
//run in xplore
ClearUserLog();
var apps =
    new sn_appclient.AppsDataAPI()
    .appsData
    .getAllAppsWithVersions(false, true, false, false)['data']

apps.forEach(function(app) {
    var hasUpdate =
        app.version &&
        app.active &&
        app.latest_version != app.version &&
        app.can_install_or_upgrade &&
        app.isInstalledAndUpdateAvailable;

    if (hasUpdate) {
        gs.info(app);
		//UserLog(app);
    }
});
</code>
----

====Classify Aging Tickets 2-3-6 Months ==== 
<code>
// JWJ0215 2024
// view aging tickets 2, 3, and 6 months.
ClearUserLog();
u_updateIncidentAging();

function u_updateIncidentAging() {
	var elapsedTime = 0;
	var aging = '';
	var currentTimeNow = gs.nowDateTime();
	var twoMonthsAgoStart = gs.monthsAgoStart(2);
	var threeMonthsAgoStart = gs.monthsAgoStart(3);
	var sixMonthsAgoStart = gs.monthsAgoStart(6);
	
	var gr = new GlideRecord('incident');
	gr.addEncodedQuery('u_aging_category!=>6 Months^ORu_aging_category=');
	gr.setLimit(1000);  // remove set limit  *******************************************************
	gr.query();
	while(gr.next()) {
		elapsedTime = (gs.dateDiff(gr.opened_at, currentTimeNow, true))/60/60/24;
		
		// Calculate days ago and assign aging choice
		if (elapsedTime <= 14) {
			aging = '1-2 Weeks';
		} else if ((elapsedTime > 14) && (elapsedTime <= 28)) {
			aging = '3-4 Weeks';
		} else if ((elapsedTime > 28) && (gr.opened_at >= twoMonthsAgoStart)) {
			aging = '2 Months';
		} else if ((gr.opened_at < twoMonthsAgoStart) && (gr.opened_at >= threeMonthsAgoStart)) {
			aging = '3 Months';
		} else if ((gr.opened_at < threeMonthsAgoStart) && (gr.opened_at >= sixMonthsAgoStart)) {
			aging = '4-6 Months';
		} else if (gr.opened_at < sixMonthsAgoStart) {
			aging = '6 Months';
		}
			
		gr.setWorkflow(false); // Skip any Business Rules
		gr.autoSysFields(false); // Do not update system fields
		gr.u_aging_category = aging;
		//gr.update();   //uncomment to make the update
		UserLog('updated results = ' + gr.number);
	}
}
</code>
----

====Close Req if RITM is closed ==== 
<code>
// JWJ0215
// This will close any request that is open ONLY If all RITMs are closed.
// If RITMs are open, the Request will be left open.
closeREQ();

function closeREQ() {
	ClearUserLog();
    var req = new GlideRecord('sc_request');
    req.addQuery('active', true);
    req.query();

    while (req.next()) {
        var ritm = new GlideRecord('sc_req_item');
        ritm.addQuery('active', true);
        ritm.addQuery('request', req.sys_id);
        ritm.query();

        if (ritm.hasNext()) {
            //if not closed abort the action
            current.setAbortAction(true);
            gs.addErrorMessage("Please close the RITM before closing the request");

        } else {

            req.state = '3'; // closed
            //req.update(); Uncomment to run live
			UserLog("Open Requests " + request.sys_id);
        }
    }

}
</code>
----

====CMDB Knowledge Related Lists ==== 
<code>
// JWJ0215 4.2.24
// get all the tables in the cmdb class
ClearUserLog();
var table = new GlideRecord("sys_db_object");
table.addQuery("name", "STARTSWITH", "cmdb_");
table.query();
UserLog(table.name);

while(table.next()){
//Create a glide record for creating new list entry's
var list_entry = new GlideRecord("sys_ui_related_list_entry");
UserLog(list_entry.number);

// GlideRecord to find the list 
var list = new GlideRecord("sys_ui_related_list");

// Find the list
  list.addQuery("name", table.name);
  list.query();
// if there is a list add a new entry
  if(list.next()){
// Create the list entry
  list_entry.initialize();
  list_entry.related_list = "kb_knowledge.cmdb_ci";
  list_entry.list_id = list.sys_id;
  list_entry.position = 100;
  list_entry.insert();
  UserLog(list_entry.related_list + list_entry.list_id);
  }
}
</code>
----

====Find Record with Given SYS_ID ==== 
<code>
// JWJ0215 2022
//This script searches all tables for the record identified by a given sys_id.
//It returns the table name where the sys_id was found, and links to the List View and Form View where the record may be found.

//Specify the sys_id you are searching for.
var sysIdToFind = 'b08ad363537411106883ddeeff7b12db';

//Invoke the search function.
FindRecGivenSysId(sysIdToFind);

function FindRecGivenSysId(sys_id) {
    //Exclude any tables which are causing a problem for our search.
    var tablesToExcludeFromSearch = [
        "itom_licensing_exclusion_metadata",
        "sn_employee_app_access",
        "sn_employee_profile",
        "sn_m2m_note_template_for_table",
        "sys_ux_sitemap_definition",
    ];
    var baseTablesToSearch = new GlideRecord('sys_db_object');
    var instanceURI = gs.getProperty('glide.servlet.uri');
    var recordFound = false;
    var current;
    var dict;

    //OutputToAll('Searching base tables in sys_db_object for ' + sys_id);

    //Grab base tables, excluding text-indexing, virtual, and sysx_ tables.
    //Important to know: Records from Child tables will still be found in a search of the base table!  There is no need to search child tables directly.
    //The function getRecordClassName() can then be used to tell you the specific (child) table the record was found in.
    baseTablesToSearch.addNullQuery("super_class");
    baseTablesToSearch.addQuery("name", "NOT LIKE", "ts_c_");
    baseTablesToSearch.addQuery("name", "NOT LIKE", "v_");
    baseTablesToSearch.addQuery("name", "NOT IN", tablesToExcludeFromSearch.toString());
    baseTablesToSearch.query();

    while (baseTablesToSearch.next()) {
        //OutputToAll('Searching: ' + baseTablesToSearch.name);

        current = new GlideRecord(baseTablesToSearch.name);

        //Find out if there is even a "sys_id" column in the table.  If there is not, just skip it.
        dict = new GlideRecord('sys_dictionary');
        dict.addQuery('name', baseTablesToSearch.name);
        dict.addQuery('element', 'sys_id');
        dict.queryNoDomain();
        if (!dict.next()) continue;

        //Now search for the actual sys_id in the current table.
        current.addQuery('sys_id', sys_id);
        //Prevent Query Rules from running, if allowed, as these may limit our results.
        current.setWorkflow(false);
        current.queryNoDomain();

        if (current._next()) {
            //We found the actual record by its sys_id value!
            recordFound = true;
            //OutputToAll('Record found in base table: ' + baseTablesToSearch.name);
            //OutputToAll('Record found in child table: ' + current.getClassDisplayValue() + " " + current.getRecordClassName());

            var listViewURL = instanceURI + "/nav_to.do?uri=/[theTable]_list.do?sysparm_query=sys_id=[theSysId]";
            listViewURL = listViewURL.replace("[theTable]", current.getRecordClassName());
            listViewURL = listViewURL.replace("[theSysId]", sys_id);
            var listViewHTML = '<a href="' + listViewURL + '"target=_blank>' + "List View" + '</a>';

            var formViewURL = instanceURI + "/nav_to.do?uri=/[theTable].do?sys_id=[theSysId]";
            formViewURL = formViewURL.replace("[theTable]", current.getRecordClassName());
            formViewURL = formViewURL.replace("[theSysId]", sys_id);
            var directLinkHTML = '<a href="' + formViewURL + '"target=_blank>' + "Form View" + '</a>';

            OutputToAll("TABLE: " + current.getClassDisplayValue() + " ( " + current.getRecordClassName() + " )");
            OutputToAll("SYS_ID: " + sys_id);
            OutputToForm(listViewHTML);
            OutputToForm(directLinkHTML);
            OutputToLog("List View: " + listViewURL);
            OutputToLog("Form View: " + formViewURL);

            //We found what we came for.  No need to keep searching.
            break;
        }
    }

    if (!recordFound) {
        OutputToAll("sys_id " + sys_id + " could not be found in any of the tables searched.");
    }

    //OutputToAll('FindRecGivenSysId completed successfully');
}

function OutputToAll(outputString) {
    OutputToForm(outputString);
    OutputToLog(outputString);
}

function OutputToForm(outputString) {
    gs.addInfoMessage(outputString);
}

function OutputToLog(outputString) {
    //Log Prefix makes it much easier to find the statements we care about in the system log (table syslog).
    var logPrefix = "FindRecGivenSysId: ";
    gs.print(logPrefix + outputString);
    gs.log(logPrefix + outputString);
}
</code>
----

====Inactive Accounts ==== 
<code>
// JWJ This will disable inactive accounts and send an email to admins of the group is-applications-servicenow
// line 8 sets the time of inactivity. Also, this only finds active users.
disable_users();

function disable_users() {
	ClearUserLog();
    var gr = new GlideRecord("sys_user");
    gr.addQuery('u_last_refreshed', '<', gs.daysAgoStart(1));
    gr.addQuery('active', true);
    gr.addQuery('source', '!=', "");
    gr.query();
    var DeactiveCount = gr.getRowCount();
    gs.log("johntest" +  DeactiveCount);   // uncomment for test
	UserLog("johntest " + DeactiveCount);
    //limit the deactivation to 500 users per day
    if (DeactiveCount <1600) {
        while (gr.next()) {
          gr.active = false;
          gs.log("Disabled inactive user: " + gr.user_name + " – last LDAP update: " + gr.u_last_refreshed);  // comment b4
          //gr.update();    // comment b4 jwj0215
        }
        gs.log("Completed disabling inactive accounts");
    } else {
        var ga = new GlideRecord('sys_user_group');
        ga.addQuery('name', 'IS-Applications-ServiceNow');
        ga.query();
        while (ga.next()) {
            var gadminuser = new GlideRecord("sys_user");
            gadminuser.addQuery("user_name", "admin");
            gadminuser.query();
            while (gadminuser.next()) {
                var cmdbci = new GlideRecord("cmdb_ci");
                cmdbci.addQuery("name", "ServiceNow-PROD");
                cmdbci.query();
                while (cmdbci.next()) {
                    var gi = new GlideRecord("incident");
                    gi.initialize();
                    gi.caller_id = gadminuser.sys_id;
                    gi.short_description = "Error in Scheduled Job: 'Disable Inactive Accounts'";
                    gi.description = "There are more than 500 users discovered to be marked inactive. Please investigate and confirm the LDAP sync is working properly. If there are 500 records either increase the deactivation limit or manually run the script without the limit.";
                    gi.impact = '2';
                    gi.urgency = '2';
                    gi.category = "IS Processes";
                    gi.subcategory = "Operations Center";
                    gi.assignment_group = ga.sys_id;
                    gi.cmdb_ci = cmdbci.sys_id;
                    gi.insert();
                }
            }
        }
    }
}
</code>
----

====Knowledge in Draft Mode ==== 
<code>
// JWJ0215 2022
ClearUserLog();
var gr = new GlideRecord('kb_knowledge');
gr.addQuery('workflow_state','draft'); // see if there are articles in draft mode
gr.query();
while(gr.next()) {
         gr.getValue('workflow_state','draft');
         //gr.update();
		UserLog(gr.number);
}
</code>
----

====MHCure Close REQ-RITM ==== 
<code>
// JWJ0215 06/07/22
// var SOURCE_REQUESTS = ['REQ0049983', 'REQ0049970','REQ0050149'];
// This will close the request, requested item, and the task of MH Cure Requests.
// put all req number in lone 5
ClearUserLog();
var SOURCE_REQUESTS = ['REQ0050149'];

var requestGR = new GlideRecord('sc_request');
requestGR.addQuery('number' , 'IN' , SOURCE_REQUESTS);
requestGR.setValue('state' , 4);
requestGR.setValue('request_state' , 'closed_incomplete');
requestGR.setValue('stage' , 'closed_incomplete');
requestGR.setValue('description' , 'Bulk closure of old tickets per SER0058621');
requestGR.setWorkflow(false);
requestGR.updateMultiple();

var requestItemGR = new GlideRecord('sc_req_item');
requestItemGR.addQuery('request.number' , 'IN' , SOURCE_REQUESTS);
requestItemGR.addActiveQuery();
requestItemGR.setValue('state' , 4);
requestItemGR.setValue('stage' , 'Request Cancelled');
requestItemGR.setWorkflow(false);
requestItemGR.updateMultiple();

var taskGR = new GlideRecord('sc_task');
taskGR.addQuery('request.number' , 'IN' , SOURCE_REQUESTS);
taskGR.addActiveQuery();
taskGR.setValue('state' , 4);
taskGR.setWorkflow(false);
taskGR.updateMultiple();
</code>
----

====Scheduled Data Collector Killer ==== 
<code>
//Cancel all Scheduled Data Collectors -Set to On Demand
// JWJ0215 07.02.24
ClearUserLog();
gs.setSession.setStrictQuery(true);
var dcollector = new GlideRecord ('sysauto_pa');
dcollector.addEncodedQuery(active=true);
dcollector.addEncodedQuery(run='On Demand');
dcollector.query();
UserLog("found " + dcollector.getRowCount() + " data collectors to update.");
while (dcollector.next()){
	UserLog("changing " + dcollector.sys_name + " records to active=false.");
	dcollector.active = 'false';
	dcollector.run = 'On Demand';
	dcollector.update();
	UserLog('Data Colectors set to Active=false ' + dcollector.sys_name);
}
</code>
----

====Time and Date Update ==== 
<code>
// JWJ0215 12.5.24
// Set the time and date properties
ClearUserLog();
gs.setProperty("glide.sys.time_format","hh:mm a");
gs.setProperty("glide.sys.date_format","MM/dd/yyyy");
UserLog("Time has been set to hh:mm a.");
UserLog("Date has been set to MM/dd/yyyy.");
</code>
----

====Update Knowledge to Published ==== 
<code>
// JWJ This will find knowledge articles in the internal knowledgebase that are in draft mode
// and set them to published.

ClearUserLog();
var updateWorkflow = new GlideRecord('kb_knowledge');
updateWorkflow.addQuery('kb_knowledge_base', '55cd30f81b77b410c4fec913604bcb03'); // sys id of internal knowledge
updateWorkflow.addEncodedQuery('workflow_state=draft');
updateWorkflow.query();
while (updateWorkflow.next()) {
    updateWorkflow.workflow_state = 'published';  //draft, review, published, pending retirement, retired
    UserLog(updateWorkflow.number);
	//updateWorkflow.update();    // uncomment to make the change
}
</code>
----


=====Project Insight Update=====
<code>
/*MDP0722 10/23/2024
Take project insight response and update ServiceNow
*/

try {
ClearUserLog();
    var r = new sn_ws.RESTMessageV2('Project Insight Get Updates', 'Default Get');
    //response.setStringParametersNoEscape('hoursBack', '24');

    var response = r.execute();
    var responseBody = response.getBody();
    var httpStatus = response.getStatusCode();
    var responseObj = JSON.parse(responseBody);

    for (var i = 0, len = responseObj.length; i < len; ++i) {
        var projectName = responseObj[i].Name;
        var description = responseObj[i].description;
        var sysId = responseObj[i].sys_id;
        var number = responseObj[i].number;
        var prioritizationGroup = responseObj[i].u_prioritization_group;
        var priority = responseObj[i].priority;
        var expense = responseObj[i].u_expense;
        var status = responseObj[i].u_status;
        var shortDescription = responseObj[i].short_description;
        var estimatedHours = responseObj[i].u_estimated_hours;
        var sponsorMgrUp = responseObj[i].sponsor_manager_and_up_only_for_this_field;
        var contactName = responseObj[i]["variables.contact_name"];
        var costCenter = responseObj[i]["variables.cost_center"];
        var createdOn = responseObj[i].request_created_on;
        var contactPhone = responseObj[i]["variables.contact_s_phone_number"];
        var dueDate = responseObj[i].due_date;
        var piId = responseObj[i].u_project_insight_records_sysid;
        var piNum = responseObj[i].u_project_insight_records_number;
        var projectType = responseObj[i].u_project_type;
        var complete = responseObj[i].Complete;
        var name = sponsorMgrUp.split('@')[0];
        var lname = name.split('.')[1];

         //Query the u_project_insight table by sys_id and update fields for the matching sys_id
        var gr = new GlideRecord("u_project_insight_records");
        gr.addQuery("sys_id", piId);
        gr.setLimit(100);
        gr.query();

        if (gr.next()) {
            if (status.length > 0) {
                gr.u_status = status;
            }

            gr.u_estimated_hours = estimatedHours;
            gr.u_complete = complete;
            gr.update();
        }

        var gr1 = new GlideRecord("sc_req_item");
        gr1.addQuery("number", number);
        gr1.setLimit(100);
        gr1.query();

        while (gr1.next()) {
            if (dueDate.length > 0) {
                gr1.due_date = dueDate;
            }
            gr1.u_complete = complete;
            gr1.u_status = status;
            gr1.u_hours = estimatedHours;

            var gr2 = new GlideRecord("sys_user");
            gr2.addEncodedQuery("email=" + sponsorMgrUp + "^last_name=" + lname);
            gs.info("email=" + sponsorMgrUp + "^last_name = " + lname);
            gr2.setLimit(100);
            gr2.query();

            if (gr2.next()) {
                gs.info("Sponsor Query =  " + gr2.last_name + " Sys ID = " + gr2.sys_id)
                var sponsorSysId = gr2.sys_id;
                gr1.variables.sponsor_manager_and_up_only_for_this_field = sponsorSysId;
            }
            gr1.update();

            UserLog("SNOW PULL from Project Insight Completed");
        }
        //    
    }

} catch (ex) {
    var message = ex.message;
}

// LINK
// https://<instance>.service-now.com/now/nav/ui/classic/params/target/sysauto_script.do%3Fsys_id%3D083258c887655610fdd876a6cebb3540%26sysparm_view%3D%26sysparm_domain%3Dnull%26sysparm_domain_scope%3Dnull%26sysparm_record_row%3D2%26sysparm_record_rows%3D668%26sysparm_record_list%3DORDERBYDESCsys_updated_on
</code>

----

=====Next Title=====

----