How to Create a Dynamic Monthly Scheduled Incident Report 21st Previous Month to 20th Current Month

[pr8172510]

Hi Community,

I am working on a requirement to create a dynamic monthly scheduled report for Incidents.

Requirement:

• The report should include incidents created from the 21st of the previous month till the 20th of the current month.
• Example:
  • Report generated on 21st May → should fetch incidents from 21st April to 20th May.
  • Report generated on 21st June → should fetch incidents from 21st May to 20th June.
• The report should run automatically every month and send the report through email.
  

  Current challenge:

  • I am unable to configure an exact dynamic filter for:
    • Previous month 21st
    • Current month 20th

[pr8172510 · Accepted solution]

Hi Community,

I was able to achieve this requirement using a combination of:

• Scheduled Script Execution
• Event Registry
• Email Notification

Since ServiceNow report filters cannot directly support a dynamic range like:

21st Previous Month → 20th Current Month

I used GlideDateTime in a Scheduled Script to calculate the dates dynamically every month.

Requirement Achieved:

Report generated on 21st May → Fetches incidents from 21st April to 20th May
Report generated on 21st June → Fetches incidents from 21st May to 20th June
Runs automatically every month
Sends the report through email as CSV attachment

Step 1: Create Scheduled Script Execution
Navigation:
System Definition → Scheduled Jobs → New

Configuration:

Run: Monthly
Day: 21
Time: As required
Active: True

Script:

var start = new GlideDateTime();
start.addMonthsUTC(-1);
start.setDayOfMonthUTC(21);
start.setDisplayValue(start.getDate() + " 00:00:00");

var end = new GlideDateTime();
end.setDayOfMonthUTC(20);
end.setDisplayValue(end.getDate() + " 23:59:59");

gs.info("Start Date: " + start);
gs.info("End Date: " + end);

var csv = "Number,Short Description,Priority,State,Created\n";

var gr = new GlideRecord('incident');
gr.addQuery('sys_created_on', '>=', start);
gr.addQuery('sys_created_on', '<=', end);
gr.query();

while(gr.next()) {

    csv +=
        gr.number + "," +
        gr.short_description + "," +
        gr.priority + "," +
        gr.state + "," +
        gr.sys_created_on + "\n";
}

var attachment = new GlideSysAttachment();

var fileName = "Monthly_Incident_Report.csv";

attachment.write(
    current,
    fileName,
    "text/csv",
    csv
);

gs.eventQueue(
    'monthly.incident.report',
    current,
    fileName,
    gs.getUserName()
);

Step 2: Create Event Registry
Navigation:
System Policy → Events → Registry

Create Event:

Event Name:
monthly.incident.report
Table:
Scheduled Script Execution [sysauto_script]

Step 3: Create Email Notification
Navigation:
System Notification → Email → Notifications

Configuration:

Send when: Event is fired
Event name:
monthly.incident.report
Include Attachments: True

 Email Content:
Subject:
Body:
Result:
The solution dynamically works for every month  and year changes without modifying filters manually.

Thanks.