1 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.
