One of the ways I provide value to client engagements is with scripting. However sometimes I would like to have a scripting tool right within ServiceNow.

This is the "Alpha version" of the scripting tool I am working on, version 1.0. Even though it is a little rough, I wanted to post it because I often use some the ideas from the UI page in some of my other work. I am sure others might find it interesting if they are using UI Pages.

NOTE: The scripting tool is powerful. Use at your own risk. Always test in a development instance first.

SCREENSHOT

Step 1: Create Application and Menu

1. Click New Application

Name: Scripting Tool
User Role: security_admin
Menu: Scripting Tool

2. Create Module

Title: Scripting Tool
Application: Scripting Tool
Roles: security_admin
Link Type: URL from arguments
Arguments: sne_scripting_tool.do

Step 3: Create UI Page

Name: sne_scripting_tool

HTML

<g:ui_form>
   <input type="hidden" name="name" value="sne_scripting_tool" />
   <input type="hidden" id="cancelled" name="cancelled" value="false" />
   <table style="padding-left:2px" class="wide" cellspacing="0">
      <tr class="header">
         <td class="column_head" align="left">
            Scripting Tool
         </td>
      </tr>
   </table>
   <p style="padding:15px;"><strong><font style="color:white;font-size:110%;background-color:red;padding:15px;">Warning: Running the script tool can cause system disruption or loss of data.</font></strong></p>
   <p style="margin:15px"><strong>Scripting Options:</strong></p>
   <table style="margin:15px">
     <tr><td nowrap="true"><g:ui_radio checked_value="update_record" name="script_tool_type" value="update_record" text="Field Update: Mass update field values" /></td></tr>
      <tr><td nowrap="true"><g:ui_radio name="script_tool_type" value="find_duplicates"  text="Find Duplicates: Find duplicates of field values" /></td></tr>    
      <tr><td nowrap="true"><g:ui_radio name="script_tool_type" value="table_row_count" text="Table Row Count: Find table row count using query" /></td></tr>    
      <tr><td nowrap="true"><g:ui_radio name="script_tool_type" value="field_average" text="Field Average: Find average of a field" /></td></tr>
      <tr><td nowrap="true"><g:ui_radio name="script_tool_type" value="field_max" text="Field Max: Find max of a field" /></td></tr>
      <tr><td nowrap="true"><g:ui_radio name="script_tool_type" value="field_min" text="Field Min: Find min of a field" /></td></tr>
      <tr><td nowrap="true"><g:ui_radio name="script_tool_type" value="field_sum" text="Field Sum: Find sum of a field" /></td></tr>    
    </table>  
    <p style="margin:15px"><strong>Parameters:</strong></p>
    <table style="margin:15px">
      <tr>
         <td nowrap="true" id="my_label" class="label" type="string" choice="0"><span id="status." class="mandatory label_description" mandatory="true" oclass="mandatory">$[SP]</span><label for="script_table_name" onclick="" dir="">Table:</label></td>
         <td nowrap="true">
            <select name="script_tool_table_name" id="script_tool_table_name" onchange="setupReference()">
               <g:evaluate var="jvar_item" expression="
                  var gr = new GlideRecord('sys_db_object'); 
                  gr.orderBy('name');
                  gr.query();
                  "/>
               <j:while test="$">
                  <option value="$</option>
               </j:while>
            </select>
         </td>
      </tr>
      <tr>
         <td id="my_label" class="label" nowrap="true" type="string" choice="0"><label>Field Name:</label></td>
         <td nowrap="true">
            <g:textarea id="script_tool_field_name" name="script_tool_field_name" cols="80" rows="1" />
         </td>
      </tr>  
      <tr>
         <td id="my_label" class="label" nowrap="true" type="string" choice="0"><label>Field Value:</label></td>
         <td nowrap="true">
            <g:textarea id="script_tool_field_value" name="script_tool_field_value" cols="80" rows="1" />
         </td>
      </tr>
      <tr>
         <td id="my_label" class="label" nowrap="true" type="string" choice="0"><label>Group By:</label></td>
         <td nowrap="true">
            <g:textarea id="script_tool_group_by" name="script_tool_group_by" cols="80" rows="1" />
         </td>
      </tr>
      <tr>
         <td id="my_label" class="label" nowrap="true" type="string" choice="0" valign="top"><label>Query:</label></td>
         <td nowrap="true">
            <g:textarea id="script_tool_query" name="script_tool_query" cols="80" rows="5" />
         </td>
      </tr>  
      <tr>
         <td nowrap="true" id="my_label" class="label" type="string" choice="0"><label>Test Mode:</label></td>
         <td>
            <g:ui_checkbox name="script_tool_test_mode" value="true" />
         </td>
      </tr>
      <tr>
         <td nowrap="true" id="my_label" class="label" type="string" choice="0">
            <label>Update System Fields:</label>
         </td>
         <td>
            <g:ui_checkbox name="script_tool_update_sys_fields" value="false" />
         </td>
      </tr>
      <tr>
         <td nowrap="true" id="my_label" class="label" type="string" choice="0">
            <label>Set Workflow:</label>
         </td>
         <td>
            <g:ui_checkbox name="script_tool_set_workflow" value="false" />
         </td>
      </tr>
      <tr>
         <td align="right" colspan="2">
            <br />
            <g:dialog_buttons_ok_cancel ok="return validateForm();" cancel="return onCancel();" />
         </td>
      </tr>
   </table>
</g:ui_form>

Client Script

function validateForm() {
if (gel('script_tool_type_update_record').checked && (gel('script_tool_field_name').value == '' || gel('script_tool_field_value').value == '')) {
alert("${JS:gs.getMessage('Please input Field Name and Field Value')}");
return false;
}
if (gel('script_tool_type_find_duplicates').checked && gel('script_tool_field_name').value == '') {
alert("${JS:gs.getMessage('Please input Field Name')}");
return false;
}
if (gel('script_tool_type_field_average').checked && (gel('script_tool_field_name').value == '' || gel('script_tool_group_by').value == '')) {
alert("${JS:gs.getMessage('Please input Field Name and Group By')}");
return false;
}
if (gel('script_tool_type_field_max').checked && (gel('script_tool_field_name').value == '' || gel('script_tool_group_by').value == '')) {
alert("${JS:gs.getMessage('Please input Field Name and Group By')}");
return false;
}
if (gel('script_tool_type_field_min').checked && (gel('script_tool_field_name').value == '' || gel('script_tool_group_by').value == '')) {
alert("${JS:gs.getMessage('Please input Field Name and Group By')}");
return false;
}
if (gel('script_tool_type_field_sum').checked && gel('script_tool_field_name').value == '') {
alert("${JS:gs.getMessage('Please input Field Name')}");
return false;
}
if (gel('script_tool_type_update_record').checked && gel('script_tool_test_mode').value == 'false') {
var answer = confirm("${JS:gs.getMessage('WARNING: THIS IS A MASS UPDATE, DO YOU WANT TO PROCEED?')}");
if (answer == false) {
return false;
}
}
}

function onCancel() {
GlideDialogWindow.get().destroy();
return false;
}

Processing Script

scriptToolRun();

function scriptToolRun() {
var scriptToolType = script_tool_type.toString();
if (scriptToolType == 'update_record') {
var updateCount = 0;
var grTableUpdate = new GlideRecord(script_tool_table_name);
grTableUpdate.addQuery(script_tool_query);
grTableUpdate.query();
while (grTableUpdate.next()) {
grTableUpdate[script_tool_field_name] = script_tool_field_value;
if (script_tool_test_mode == 'false') {
if (script_tool_update_sys_fields == 'true') 


if (script_tool_set_workflow == 'true') 


grTableUpdate.update();
}
updateCount++;
}
if (script_tool_test_mode == 'true') {
gs.addInfoMessage('Test Mode: '+script_tool_table_name.toString()+' Updated: ' + updateCount);
}
else {
gs.addInfoMessage(script_tool_table_name.toString()+' Updated: ' + updateCount);
}
response.sendRedirect(script_tool_table_name.toString()+'_list.do?sysparm_query='+script_tool_query);
}
else if (scriptToolType == 'find_duplicates') {
var dupRecords = [];
var gaDupCheck1 = new GlideAggregate(script_tool_table_name);
gaDupCheck1.addQuery(script_tool_query);
gaDupCheck1.addAggregate('COUNT', script_tool_field_name);
gaDupCheck1.addNotNullQuery(script_tool_field_name);
gaDupCheck1.groupBy(script_tool_field_name);
gaDupCheck1.addHaving('COUNT', '>', 1);
gaDupCheck1.query();
while (gaDupCheck1.next()) {
var dup1 = gaDupCheck1.getElement(script_tool_field_name);
dupRecords.push(dup1.toString());
}
gs.addInfoMessage('Duplicate records found: '+dupRecords);
response.sendRedirect(script_tool_table_name.toString()+'_list.do?sysparm_query='+script_tool_query);
}
else if (scriptToolType == 'table_row_count') {
//gs.addInfoMessage('Table Row Count started');
var count1 = new GlideAggregate(script_tool_table_name);
count1.addQuery(script_tool_query);
count1.addAggregate('COUNT');
count1.query();
if (count1.next()) {
gs.addInfoMessage('Table Row Count: '+script_tool_table_name+ ' contains '+ count1.getAggregate('COUNT') + ' rows.');
}
response.sendRedirect(script_tool_table_name.toString()+'_list.do?sysparm_query='+script_tool_query);
}
else if (scriptToolType == 'field_average' || scriptToolType == 'field_max' || scriptToolType == 'field_min') {
var count2 = new GlideAggregate(script_tool_table_name);
count2.addQuery(script_tool_query);
count2.addAggregate('MIN', script_tool_field_name);
count2.addAggregate('MAX', script_tool_field_name);
count2.addAggregate('AVG', script_tool_field_name);
count2.groupBy(script_tool_group_by);
count2.query();
while (count2.next()) {
var min = count2.getAggregate('MIN', script_tool_field_name);
var max = count2.getAggregate('MAX', script_tool_field_name);
var avg = count2.getAggregate('AVG', script_tool_field_name);
var category = count2.getElement(script_tool_group_by);
gs.addInfoMessage(category + " Aggregates: MIN = " + min + " MAX = " + max + " AVG = " + avg);
}
response.sendRedirect(script_tool_table_name.toString()+'_list.do?sysparm_query='+script_tool_query);
}
else if (scriptToolType == 'field_sum') {
var fieldSum = 0;
var grFieldSum = new GlideRecord(script_tool_table_name);
grFieldSum.addQuery(script_tool_query);
grFieldSum.query();
while (grFieldSum.next()) {
fieldSum += parseFloat(grFieldSum.getElement(script_tool_field_name));
}
gs.addInfoMessage('Field Sum: '+script_tool_field_name+ ' contains '+ fieldSum);
response.sendRedirect(script_tool_table_name.toString()+'_list.do?sysparm_query='+script_tool_query);
}
}