Report of groups which do not have On-Call Schedules filled out for the next 10 days.

[VijayKummamuru]

Hi All,

Can you please hep me with Report of groups which do not have On-Call Schedules filled out for the next 10 days.

I tried using on call schedule table  but no luck 

@itil #Reporting

[pavani_paluri · Accepted solution]

Hi @VijayKummamuru ,

This can’t be done directly via simple reporting on the On-Call Schedule tables, because you’re asking for something that does not exist (i.e., no schedules), which typical filters can't catch.

Instead, you’ll need to do this with a scripted report, scripted REST API, or background script to query:

All groups (sys_user_group)

All On-Call schedules (cmn_schedule_span, on_call_rotation, on_call_roster)

For each group, check if there’s any on-call coverage in the next 10 days

List the groups that have no coverage

var daysAhead = 10;
var endDate = new GlideDateTime();
endDate.addDaysLocalTime(daysAhead);

You can convert this to a script include and call in report with sys_id is one of and then give script include name
var groupGR = new GlideRecord('sys_user_group');
groupGR.query();

gs.print('Groups with NO on-call coverage for the next ' + daysAhead + ' days:\n');

while (groupGR.next()) {
var hasCoverage = false;

// Check for any on-call schedules tied to this group
var onCallGR = new GlideRecord('on_call_schedule');
onCallGR.addQuery('group', groupGR.sys_id);
onCallGR.query();

while (onCallGR.next()) {
// For each schedule, check if any span (roster) covers the next 10 days
var spanGR = new GlideRecord('cmn_schedule_span');
spanGR.addQuery('schedule', onCallGR.schedule.sys_id);
spanGR.addQuery('ends', '>=', gs.nowDateTime()); // Only future spans
spanGR.addQuery('starts', '<=', endDate); // Spans within next X days
spanGR.query();

if (spanGR.hasNext()) {
hasCoverage = true;
break;
}
}

if (!hasCoverage) {
gs.print('- ' + groupGR.name);
}
}

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for

Kind Regards,

Pavani P

[VijayKummamuru]

Thanks @pavani_paluri  , will try this 

[nityabans27]

Hi @VijayKummamuru,

I made some adjustments in the code provided by @pavani_paluri .

var daysAhead = 10;
var endDate = new GlideDateTime();
endDate.addDaysLocalTime(daysAhead);

gs.print('Groups with NO on-call coverage for the next ' + daysAhead + ' days:\n');

var groupGR = new GlideRecord('sys_user_group');
groupGR.query();

while (groupGR.next()) {
var hasCoverage = false;

// Find on-call schedules linked to this group
var onCallGR = new GlideRecord('on_call_schedule');
onCallGR.addQuery('group', groupGR.sys_id);
onCallGR.query();

while (onCallGR.next()) {
// Check if there are any spans covering the next 10 days
var spanGR = new GlideRecord('cmn_schedule_span');
spanGR.addQuery('schedule', onCallGR.schedule);
spanGR.addQuery('ends', '>=', gs.nowDateTime()); // Must not be expired
spanGR.addQuery('starts', '<=', endDate); // Must fall in the 10-day window
spanGR.query();

if (spanGR.hasNext()) {
hasCoverage = true;
break; // Found coverage, no need to check more spans
}
}

if (!hasCoverage) {
gs.print('- ' + groupGR.name);
}
}

Try this and let me know if it works for you or we can brainstorm more ways. 

Please accept it as solution and mark it helpful.