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('---------------------------------------------');
}

[ezAdmin]

Audit history tracks changes to attribute values. Try checking/ enabling the Audit to the core table.

Else a new attribute needs to be created to update the date & time when Operational Status value gets updated.

Let me know which one works for you.