Fetch Data in report

[Twinkle S]

Hi SN Community Members,

I have stumbled upon a problem and need your help.

I want to fetch data for Incidents older than 40 days  for every month till 1 year . Lets say in the Month of June total created Incidents were 20.Now I want incidents created 40days ago from 30th June. This needs to be shown in the reports.

How to automate this process?

Thank you

Twinkle

[Yashsvi]

Hi @Twinkle S,

1. Create a Custom Table (Optional):

Create a custom table (e.g., u_monthly_incident_summary) to store the results.

1. Navigate to System Definition > Tables.
2. Click on New and create a table with fields such as month, year, incident_count, etc.

2. Create a Scheduled Script Execution:

1. Navigate to System Definition > Scheduled Script Executions.
2. Click on New to create a new Scheduled Script Execution.
3. Configure the schedule to run monthly.

Script that fetches the incidents older than 40 days for the previous month and stores the results in the custom table:

(function() {
    var currentDate = new GlideDateTime();
    var fortyDaysAgo = new GlideDateTime();
    fortyDaysAgo.addDaysUTC(-40);
    
    // Calculate the start and end of the previous month
    var lastMonthEnd = new GlideDateTime();
    lastMonthEnd.setValue(currentDate.getYearUTC() + '-' + currentDate.getMonthUTC() + '-01 00:00:00');
    lastMonthEnd.addDaysUTC(-1);
    
    var lastMonthStart = new GlideDateTime();
    lastMonthStart.setValue(lastMonthEnd.getYearUTC() + '-' + lastMonthEnd.getMonthUTC() + '-01 00:00:00');
    
    // Query for incidents created 40 days ago from the end of the last month
    var gr = new GlideRecord('incident');
    gr.addEncodedQuery('sys_created_on>=javascript&colon;gs.dateGenerate(\'' + lastMonthStart.toString().substring(0, 10) + '\', \'00:00:00\')^sys_created_on<=javascript&colon;gs.dateGenerate(\'' + lastMonthEnd.toString().substring(0, 10) + '\', \'23:59:59\')');
    gr.addEncodedQuery('sys_created_on<=javascript&colon;gs.dateGenerate(\'' + fortyDaysAgo.toString().substring(0, 10) + '\', \'23:59:59\')');
    gr.query();
    
    var count = 0;
    while (gr.next()) {
        count++;
    }
    
    // Store the results in the custom table
    var customTable = new GlideRecord('u_monthly_incident_summary');
    customTable.initialize();
    customTable.u_month = lastMonthEnd.getMonthUTC();
    customTable.u_year = lastMonthEnd.getYearUTC();
    customTable.u_incident_count = count;
    customTable.insert();
})();

3.Create a Report:

1. Navigate to Reports > Create New.
2. Choose the custom table u_monthly_incident_summary as the data source.
3. Configure the report as needed (e.g., bar chart, pie chart, etc.).
4. Save the report.

4. Schedule the Report:

1. Navigate to Reports > View / Run.
2. Find your report and open it.
3. Click on Schedule.
4. Configure the schedule (e.g., monthly) and the recipients.

Thank you, please make helpful if you accept the solution. 

[Hayo Lubbers]

Hi @Twinkle S ,

Do you have a license for Performance Analytics? If so, have a look at the bucket groups. You can group data the way you want, based upon your own definition.

Some information regarding bucketgroups: https://developer.servicenow.com/dev.do#!/learn/courses/washingtondc/app_store_learnv2_reportanalyti...

Regards,

Hayo