List Servers with changes in "Operational Status"

[Charles Tiu]

Hi,

I need to generate the list of server with changes in the field "operational status" for a specific month.

Any idea try to do it?

I have try the filtering from the UI.. but it will list any change to the CI record within the specify time frame.

I have tried the script below... it list all record changes within that time frame as well.

// Define the table to query
var tableName = 'cmdb_ci_server';

// Create a GlideRecord object for the specified table
var gr = new GlideRecord(tableName);

// Define the specific time range
var startDate = '2023-06-01 00:00:00'; // Replace with your desired start date and time
var endDate = '2023-06-30 23:59:59'; // Replace with your desired end date and time

// Define the statuses you are interested in (replace with actual status values)
var status1 = '4'; // Example: '4' could represent 'Operational' status
var status2 = '1'; // Example: '1' could represent 'In Maintenance' status

// Add queries to find records where 'Operational Status' has changed to specific statuses within the specified time range
var query = 'operational_statusCHANGES^operational_status=' + status1 + '^ORoperational_status=' + status2 + '^sys_updated_onBETWEEN' + startDate + '@' + endDate;
gr.addEncodedQuery(query);

// Query the table
gr.query();

// Iterate over the results and print the details
while (gr.next()) {
gs.print('Server Name: ' + gr.getValue('name'));
gs.print('Operational Status: ' + gr.getValue('operational_status'));
gs.print('Changed By: ' + gr.getValue('sys_updated_by'));
gs.print('Changed On: ' + gr.getValue('sys_updated_on'));
gs.print('---------------------------------------------');
}

[Narsing1]

Looks like you want to take each server and see what changes made to Operational status field over a period of time. If so, you may think of using the HistoryWalker API. 

Thanks,

Narsing

[Charles Tiu]

Hi, what I want is to find out all servers which has changes in the Operational Status in a period..

Example. List of servers with operational status changes in June 2024

[Narsing1]

Here you go.  I have changed the operational status for couple of servers like this

Now, I am going to use the Historywalker api to see how many servers has changed its operational status in July 2024 by using the below code.

var grserver = new GlideRecord("cmdb_ci_server");
grserver.setLimit(5);
grserver.orderByDesc("sys_updated_on");
grserver.query();
while (grserver.next()) {
    try {
        var hw = new sn_hw.HistoryWalker(grserver.getTableName(), grserver.getUniqueValue());
        hw.walkTo(0);
        do {
            var walkedGr = hw.getWalkedRecord();
            var fields = GlideScriptRecordUtil.get(walkedGr).getChangedFieldNames();
            if (fields.indexOf("operational_status") > -1) {
                var dt = new GlideDate();
                dt.setValue(walkedGr.sys_updated_on);
                if (dt.getMonthNoTZ() == 7 && dt.getYearNoTZ() == 2024) { //You may change the month and year as per your requirement
				//You may think of creating a seperate table to store these entries
                    gs.print("Operational Status changed for " + grserver.name);
                }
            }
        } while (hw.walkForward());
    } catch (e) {

    }
}

Here is the result

Note: If you have CMDB 360 plugin, you can view these changes in an OOTB table I believe.  If not, its better to create a custom table to store these values regularly for reporting purpose

Thanks,

Narsing

[Charles Tiu]

I encounter the following error. Not familiar with ServiceNow scripting. Are you able to advise?

getGlideElement called for unknown field 'hyper_threading' in table 'cmdb_ci_server'
setValue called for unknown field 'hyper_threading' in table 'cmdb_ci_server'
getGlideElement called for unknown field 'u_device_ru_position' in table 'cmdb_ci_server'
setValue called for unknown field 'u_device_ru_position' in table 'cmdb_ci_server'
getGlideElement called for unknown field 'Rack contains' in table 'cmdb_ci_server'
setValue called for unknown field 'Rack contains' in table 'cmdb_ci_server'