Need to get backlog tickets(Incidents) older than 30 business days(excluding Holidays and weekends)

aish1234 · ‎07-09-2024

Aishwarya Ghool · ‎07-16-2024

I got the result by below script.

//Client callable script include

var BusinessDays = Class.create();

BusinessDays.prototype = Object.extendsObject(AbstractAjaxProcessor, {

Bdays: function() {

var temp = new ExcludeHolidays().holidays(); // getting last business day (considering 30 business days) refer script below.

var array = [];

var c = 0;

var gr = new GlideRecord('incident');

gr.addEncodedQuery('sys_created_on<' + temp);//assignment group Parent is AAFR_XX_CG_ComputaCenter

gr.query();

while (gr.next()) {

array.push(gr.number.toString());

}

return array;

},

type: 'BusinessDays'

});

============//getting last business day from today(30 days ago)

var ExcludeHolidays = Class.create();

ExcludeHolidays.prototype = Object.extendsObject(AbstractAjaxProcessor, {

holidays: function() {

var count = 0;

var tday = new GlideDateTime();

var temp = tday; //temporary variable

while (count < 30) {

if (temp.getDayOfWeekUTC() != 6 && temp.getDayOfWeekUTC() != 7) {

count = count + 1;

} //excluding weekends

var schedule = "US Holidays";

var sch = new GlideRecord("cmn_schedule");

sch.addQuery("name", schedule);

sch.query();

if (sch.isInSchedule(temp)) {

count = count - 1; //reducing count since last count was including holidays

}

temp.addDaysUTC(-1); // reducing day by 1

}

return temp; // returning business day 30 days ago

},

//in the filter you need to add

Number is one of javascript&colon; new BusinessDays().Bdays();

View solution in original post

Mahathi · ‎07-09-2024

Hi @aish1234 ,

Please find the attached Sample report.

Also refer to this article:

https://www.servicenow.com/community/knowledge-blog/excluding-weekends-from-reports-when-you-re-repo...

If my answer helped in any way, please mark it as ✅Correct & 👍Helpful

Thanks,
Mahathi

aish1234 · ‎07-09-2024

Thanks for your reply, but we need KPI which shows backlog incidents created 30 days ago. 30 days should be excluding holidays and weekends.

Mark Manders · ‎07-09-2024

Always nice, just a title and nothing explaining the why or showing what already has been tried and what not.

But you can get them through this: https://your_instance.service-now.com/task_sla_list.do?sysparm_query=business_duration%3Ejavascript%3Ags.getDurationDate('10%200%3A0%3A0')%5Etask.sys_class_name%3Dincident&sysparm_view=

Assuming your SLA's are running on all incidents, you can use the 'business elapsed time' field to check on the holidays and such (since that's what the business elapsed time is for). If you put that at your 30 days calculation (the URL will show you 240 hours (30 days times 8 hours), which is 10 days on the duration field.

Please mark any helpful or correct solutions as such. That helps others find their solutions.
Mark

aish1234 · ‎07-09-2024

Thanks for your reply.

But all incidents don't have SLA attached. So we need to do it with the script.