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]

Please provide the code that you are using

[Charles Tiu]

I'm using the code you provided above.

[ezAdmin]

Try GlideDateTime("date") & addMonths(#) by creating two variables and every +1 to count as month which may help you choose operational status by month.

Example:

var getdate = new GlideDateTime("2024-01-01 08:00:00");

var jan = gdt.addMonths(1)

Let me know if you this works.

[Charles Tiu]

it return record which has changes to CI record... while I only want  changes in the operational status only.

Am I looking at the wrong table?

[Charles Tiu]

To simply it, I rewrite the query to the following, however, it display server which has changes to the operational status 1 year ago but has recent changes to other field...

var gr = new GlideRecord ('cmdb_ci_server');

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

gr.addQuery ('operational_status', '=', '4');

gr.addEncodedQuery('sys_updated_onBETWEEN' + startDate + '@' + endDate);

gr.query();

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