Data crunching your ServiceNow - month by month

[SimonMorris]

I had an interesting task yesterday... Take a ServiceNow table and retrospectively create month end statistics.

We are defining some new Key Performance Indicators for a process that we run internally, and it helps to be able to see how we would have performed in previous months if we had been tracking that metric.

I love working with big data sets, but it took a bit of thinking to get my code iterating through all of the previous months data

Getting the oldest record

First of all I needed to know where to start in my dataset, which is my oldest record. This was fairly easy to do with GlideRecord.

(My code examples are from a Object Orientated class... if anyone would like the full script feel free to email)

get_oldest_prb: function() {

    var gr = new GlideRecord('problem');
    gr.orderBy('sys_created_on');
    gr.setLimit(1);
    gr.query()
    if (gr.next()) {
        gs.print(gr.number + ' ' + gr.sys_created_on);
        return gr.sys_created_on;
    }
    gs.print('get_oldest_prb: Fatal error getting oldest record');
    return null;
},

So, get_oldest_prb() returns a date time string in the format "2005-04-26 05:19:45"

Getting the first and last day of that (oldest) month

I want to iterate through each month from the date of the oldest record to the present day.

Next job is to work out the first and last day of the oldest month.

//Get the date of the oldest Problem record as a starting point
var oldest_prb_date_string = this.get_oldest_prb();

// What was the first and last day of that month (when the oldest ticket was)
var gdt = new Packages.com.glide.glideobject.GlideDateTime();
gdt.setValue(oldest_prb_date_string);
var oldest_month_start = gs.beginningOfMonth(gdt);
var oldest_month_end = gs.endOfMonth(gdt);

So now I have 2 new string variables oldest_month_start and oldest_month_end.

My oldest problem record (oldest_prb_date_string) was "2005-04-26 05:19:45" so the beginning and end of that month is

• oldest_month_start = 2005-03-31 23:00:00
• oldest_month_end = 2005-04-30 22:59:59

Note that the time is offset for my users timezone, but it was accurate enough for what I wanted to do.

How many months ago was that?

Lets work out how many months we've had since the creation of the first record. We can do this by comparing the oldest_prb_date_string to todays date

// How many months from the beginning of the oldest month until today.. how many months
// to iterate through to get to the present time
var diff_seconds = gs.dateDiff(oldest_month_start, gs.nowDateTime(), false);
var diff_days = diff_seconds.split(' ')[0];
var diff_months = parseInt(diff_days/30);
gs.print('diff_months=' + diff_months);

There is a bit of a hack to get diff_days. The output of gs.dateDiff is a string containing the number of days, hours and minutes between the 2 times. I split that string to only get the days which I divided into months. With my example there are 82 months between the creation of the first record and the present time.

Iterate through each month and generate some stats!

// Iterate through each month.
for (var x=diff_months; x>0; x--) {
    var b = gs.monthsAgoStart(x);
    var e = gs.monthsAgoEnd(x);
    var d = new GlideDateTime(e);

    this.create_ss_chart_1_for_month(b, e, d);
}

The for loop here counts down from 82 to 0 and then stops.

GlideSystem has 2 functions - gs.monthsAgoStart() and gs.monthsAgoEnd() - which give you the start and end of a given month.

When we iterate through our 82 months we call a function - in this case called this.create_ss_chart_1_for_month() - which takes the beginning and end of the month as arguments b and e

Get the records for that month

My task involved getting a couple of different statistics.

• How many records were opened that month
• How many records were closed that month
• How many records were open at the end of that month (our backlog)

Now that we have the beginning and end of a month we can start to pull data from the database and get some stats from it.

How many records were opened that month

opened_that_month: function(b, e, d) {
    // Calculate the number of Problems opened between b and e
    var gr = new GlideRecord('problem');
    gr.addQuery('opened_at', '>', b);
    gr.addQuery('opened_at', '<', e);
    gr.query();

    // Do something with gr
}

How many records were closed that month

closed_that_month: function(b, e, d) {
    // Calculate the number of Problems closed between b and e
    var gr = new GlideRecord('problem');
    gr.addQuery('closed_at', '>', b);
    gr.addQuery('closed_at', '<', e);
    gr.query();

    // Do something with gr
}

How many records were open at the end of that month (our backlog)

backlog_for_that_month: function(b, e, d) {
    // Calculate how many Problems were open at e
    var gr = new GlideRecord('problem');
    gr.addQuery('opened_at', '<', e);
    var orQuery = gr.addQuery('closed_at', '>', e);
    orQuery.addOrCondition('closed_at', '');
    gr.query();

    // Do something with gr
}

The backlog is slightly harder... you have to consider all of the Incidents opened before the end of that month and closed after that month (e) plus all of the records that may still be open.

Hopefully not a problem for 82 months ago, but as you get closer to the present day you need to count records that are still open.

If you've read to the bottom of this blog post... firstly well done! Now go and read about Summary Sets so that you can create cool graphs from your legacy data.

Comments welcome below!