What is the correct method to query Schedule Date/time fields in cmn_schedule_span?

sid31 · ‎12-18-2016

Hi all,

I'm trying to write a query that I thought should be relatively simple but is proving challenging. I would like to write a query that filters entries in the cmn_schedule_span based on the start_date_time field (e.g. show all entries where start_date_time >= 7 days from now). However because start_date_time is of type "Schedule Date/time" I don't get the regular date filter options:

What is the correct way query this field type? Ultimately I'm trying to build an encoded query to use in a reference qualifier where users can select a particular schedule entry.

Thanks

Sid

Aditya Mallik · ‎12-18-2016

This should work, but I am concerned about the performance. How many records would you go through, all the records in cmn_schedule_span? If you restrict the span records only for your specific schedule, then you might be fine.

You should explore the methods available for GlideSchedule API.

You can try to use one of these APIs to build your logic:

var schedID = "30b99b3d93a0220050bef157b67ffb2e";

var sched = new GlideSchedule(schedID);

var spans = sched.getSpans(startDate, endDate); //start date and end date are the range of dates that you are interested in

Jeffrey Siegel · ‎03-22-2023

I am looking to do something similar, i need to get a list of anything on a perticular cmn_schedule_span where schedule = "some_sys_id" and that are occuring sometime over the next 4 weeks, (it can start during the 4 weeks, end during the 4 weeks, or for those crazy long schedules, occur during those 4 weeks.

the problem i am having is finding those records that repeat over time. i cant get this to list properly and i need to include this as text in an email message. can you help?

RobinR1 · ‎04-02-2019

Does ServiceNow offer any kind of reporting / visual to multiple schedules at once for representation to users? My users are not going to know how to use the suggestions above, which forces us to copy all the blackout and maintenance schedules into the Release module for reporting. This is causing issues in Release Management.

Zach Wehrli · ‎01-29-2024

For those that come across this as I did, this was my solution I used in a script include.

Side Note: I also replied on this post about how I create span records with a start/end time:

How to create entry in "cmn_schedule_span" table through script.

In the script, the dates from the cmn_schedule_span table are retrieved using the getDisplayValue() method to obtain a human-readable string representation of the date, which is originally stored in the yyyyMMddT000000Z format.

Date Retrieval from `cmn_schedule_span`:

The script queries the cmn_schedule_span table to fetch schedule span records related to a specific schedule.
When retrieving date fields from these records, it uses the getDisplayValue() method. Without this method it returns a string that represents the date in a format almost like ISO, such as '20240124T000000Z'.

Conversion to GlideDateTime Object:

The obtained date string from getDisplayValue() is then converted into a GlideDateTime object. You could probably stop converting here and compare it with other GlideDateTime variables.

spanstartdt = new GlideDateTime(spanGR.start_date_time.getDisplayValue());

The same process is repeated for both start and end dates of the schedule spans.

Numeric Conversion for Simplified Comparison:

To simplify the comparison process, the script then converts the GlideDateTime objects into numeric values using getNumericValue(). This numeric representation allows for straightforward numerical comparisons of dates.

spanstartdtnum = spanstartdt.getNumericValue();

Numeric values for both start and end dates are obtained and used in subsequent date comparisons.

My scenario:

We maintain blackout schedules for different service/environment combinations, even though not all are actively used. There are two main types of blackout schedules: one that applies environment-wide (e.g., prod, stage, qa) and another that covers all services and environments. Some services are excluded from the broader blackout schedules.

The client script, embedded in our 'create a change request' catalog item, examines the proposed change's start date against blackout schedules before submission. It notifies the user if there's a blackout that may lead to denial or suggests choosing a different time.

Here's how the script works:

It takes the service name, environment, and start date of a proposed change.
Checks for blackout or exclusion spans for that service/environment.
If an exclusion is found, indicating a higher-level blackout, it informs the user that there's no blackout for that service but suggests a separate change request if other services are involved.
If it finds a blackout for the service, it returns true for result.isInSchedule and notifies the user.
If no service-level blackout is found, it checks the 'fallback' schedule for the target environment.
If no environment-level blackout is found, it checks the final 'default' blackout schedule that covers all services/environments.

In essence, the script ensures proposed changes align with blackout schedules, informing users about any potential conflicts or exclusions.

The script below could probably be simplified more, as apparent by the many different date conversions left in that I had tested, but I was just happy I finally got it working. 😂 It also contains the gs.log() outputs I used to debug it and should be useful if there is any issue in the future.

The script include:

var CheckBlackoutScheduleByService = Class.create();
CheckBlackoutScheduleByService.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    initialize: function(request, responseXML, gc) {
        global.AbstractAjaxProcessor.prototype.initialize.apply(this, arguments);

        this.result = {
            isInSchedule: false,
            scheduleName: '',
            scheduleSpanName: '',
            scheduleSpanStartDate: '',
            scheduleSpanEndDate: '',
            scheduleExclusion: false
        },
        this.inspan = false;
    },

    isWithinSchedule: function() {

        dateTime = this.getParameter('sysparm_dateTime'),
            servicename = this.getParameter('sysparm_servicename'),
            environment = this.getParameter('sysparm_environment'),
            fallbackSchedules = {
                'production': 'sys_id_here',
                'integration': 'sys_id_here',
                'development': 'sys_id_here',
                'stage': 'sys_id_here',
                'qa': 'sys_id_here',
                'ux': 'sys_id_here'
            },
            defaultFallbackSchedID = 'sys_id_here',
            startGDT = new GlideDateTime(dateTime),
            startDate = startGDT.getDate(),
            startDateTimenum = startGDT.getNumericValue(),
            startDatenum = startDate.getNumericValue(),
            sched = '';

        gs.log('CheckBlackoutScheduleByService Script Include Log 1 - \nChecking if there is a blackout for ' + servicename + ' ' + environment + ' during the selected start date:' + startGDT);

		gr = new GlideRecord('cmn_schedule_blackout');
        gr.addQuery('name', 'CONTAINS', servicename);
        gr.addQuery('name', 'CONTAINS', environment);
        gr.setLimit(1);
        gr.query();


        if (gr.next()) {
            // Check if the date is within the service-specific schedule
            sched = new GlideSchedule(gr.sys_id);
            this.result.isInSchedule = sched.isInSchedule(startGDT);
            if (this.result.isInSchedule) {
                // Check if there is an exclude entry at the given datetime for the service-specific schedule
                this.getScheduleSpan(gr.sys_id, startDatenum);
                return JSON.stringify(this.result);
            }
        }

        // If not in the service-specific schedule, check fallback schedule
        sched = new GlideSchedule(fallbackSchedules[environment]);
        this.result.isInSchedule = sched.isInSchedule(startGDT);
		if (this.result.isInSchedule) {
			// Check if there is an exclude entry at the given datetime for the service-specific schedule
			this.getScheduleSpan(gr.sys_id, startDatenum);
			return JSON.stringify(this.result);
		}

        // If not in the fallback schedule, check the defaultFallbackSchedID
            sched = new GlideSchedule(defaultFallbackSchedID);
        this.result.isInSchedule = sched.isInSchedule(startGDT);
		if (this.result.isInSchedule) {
			// Check if there is an exclude entry at the given datetime for the service-specific schedule
			this.getScheduleSpan(gr.sys_id, startDatenum);
			return JSON.stringify(this.result);
		}
		return JSON.stringify(this.result);
    },

    getScheduleSpan: function(schedulesysid, dateTime) {

        spanGR = new GlideRecord('cmn_schedule_span');
        spanGR.addQuery('schedule', schedulesysid);
        spanGR.query();

        while (spanGR.next() && !this.inspan) {
            spanstartdt = new GlideDateTime(spanGR.start_date_time.getDisplayValue());
            spanstartdtnum = spanstartdt.getNumericValue();
            spanstartdate = spanstartdt.getDate();
            spanstartdatenum = spanstartdate.getNumericValue();
            spanenddt = new GlideDateTime(spanGR.end_date_time.getDisplayValue());
            spanenddtnum = spanenddt.getNumericValue();
            spanenddate = spanenddt.getDate();
            spanenddatenum = spanenddate.getNumericValue();
            schedname = spanGR.schedule.name.getDisplayValue();
            spanname = spanGR.name.getDisplayValue();
            spantype = spanGR.type;
            if ((spanstartdatenum <= dateTime) && (spanenddatenum >= dateTime)) {
                this.inspan = true;
                this.result.scheduleName = schedname;
                this.result.scheduleSpanName = spanname;
                this.result.scheduleSpanStartDate = spanGR.start_date_time.getDisplayValue();
                this.result.scheduleSpanEndDate = spanGR.end_date_time.getDisplayValue();
            }
            if (this.inspan && spantype == 'exclude') {
                this.result.isInSchedule = false;
                this.result.scheduleExclusion = true;
				gs.log('CheckBlackoutScheduleByService Script Include Log 3 - \n' + 
					'FOUND A MATCH' + 
					'\nAn exclusion from blackout was found for:' + dateTime + 
					'\nSpan Name: ' + spanname + 
					'\nSpan start date: ' + spanstartdt + 
					'\nSpan end date: ' + spanenddt + 
					'\n\ncurrent result: ' + JSON.stringify(this.result) + '\n');
            } else if (this.inspan) {
				gs.log('CheckBlackoutScheduleByService Script Include Log 4 - \n' + 
					'FOUND A MATCH' + 
					'No exclusion, There is a blackout for:' + dateTime + 
					'\nSpan Name: ' + spanname + 
					'\nSpan start date: ' + spanstartdt + 
					'\nSpan end date: ' + spanenddt + 
					'\n\ncurrent result: ' + JSON.stringify(this.result) + '\n');
			}
        }
		if (!this.inspan) {
			gs.log('CheckBlackoutScheduleByService Script Include Log 5 - \n' + 
				'No applicable spans found in schedule: ' + sched.name);
		}
		
    },

    type: 'CheckBlackoutScheduleByService'
});