Scheduled job to create an Incident if Expiry Date is 30 days from now

[SN Rookie]

I've a database view from where I'm fetching the expiry date. My below query filters those records where the expiry date is on or before 30 days from now and then create an incident. I've the below script which works and creates an INC but my scheduled job will run everyday hence resulting in duplicate incidents so I only need the script to check for expiry every 30 days, not before and not after. Can someone help me in checking that pls?

var gr = new GlideRecord('u_pki_certificate_view');
gr.addEncodedQuery('pki_u_expiry_dateRELATIVELE@dayofweek@ahead@30^pki_state=Active');
gr.query();
//gs.log("Total Count of Expired Certiticates is" + ' ' + gr.getRowCount());
while (gr.next()) {
    var inc = new GlideRecord('incident');
    inc.addQuery('cmdb_ci', gr.app_sys_id);
    inc.query();
    if (inc.next()) {
        inc.initialize();
        inc.caller_id.setDisplayValue('ServiceNow API');
        inc.contact_type = 'Internal Support Group';
        inc.category = 'applications';
        inc.subcategory = 'app configuration issue';
        inc.cmdb_ci = gr.app_sys_id;
        inc.assignment_group = gr.app_assignment_group;
        inc.short_description = gr.app_asset_tag + ' has a certificate expiry in next 30 days, please fix it. The certificate name is' + ' ' + gr.pki_short_description + ' ' + '. Kindly note that if no action is taken within 15 days, the Impact/Urgency will raise to 3M for this Incident.';
        
inc.insert();
}
}

[johnfeist · Accepted solution]

Hi SN,

Try something like this as your query qualifiers:

var endAt = new GlideDateTime();
endAt.addDays(-30);
var startAt = new GlideDateTime(endAt);
startAt.add(-43200000);
endAt.add(43200000);

var gr = new GlideRecord("u_pki_certificate_view');
gr.addQuery("pki_u_expiry_date", ">=", startAt);
gr.addQuery("pki_u_expiry_date"<, "<=", endAt);
gr.Query();

Assuming that you run your job at the same time every day, this will cover a time span twelve hours before and twelve hours after your job starts (43,200,000 is the number of milliseconds in twelve hours). That will limit each record to only being picked up once unless the expiry date gets changed.

The messy alternatives are to add a field to your pki record where you record the incident number and then add a query condition that the incident field is empty.

Hope that helps.

:{)

Helpful and Correct tags are appreciated and help others to find information faster

[SN Rookie]

Thanks Mark, I used your logic and was able to build the script as below 

var endAt = new GlideDateTime();
endAt.addDays(30);
var startAt = new GlideDateTime(endAt);
startAt.add(-43200000);
endAt.add(43200000);
//gs.log('Start is ' + startAt);
//gs.log('End is ' + endAt);

var gr = new GlideRecord('u_certificate_view');
//gr.addEncodedQuery('pki_u_expiry_dateRELATIVELE@dayofweek@ahead@30^pki_state=Active');
gr.addQuery('cert_u_expiry_date', '>=', startAt);
gr.addQuery('cert_u_expiry_date', '<=', endAt);
gr.addEncodedQuery('cert_state=Active');
gr.query();
//gs.log("Total Count of Expired Certificates is" + ' ' + gr.getRowCount());
while (gr.next()) {
    
        var inc = new GlideRecord('incident');
        inc.addQuery('cmdb_ci', gr.app_sys_id);
        inc.query();
	
        if (inc.next()) {
            inc.initialize();
            inc.caller_id.setDisplayValue('ServiceNow API');
            inc.contact_type = 'Internal Support Group';
            inc.category = 'applications';
            inc.subcategory = 'app configuration issue';
            inc.cmdb_ci = gr.app_sys_id;
            inc.assignment_group = gr.app_assignment_group;
            //gs.log("Assignment Group is " + gr.app_assignment_group);
            inc.short_description = gr.app_asset_tag + ' has a certificate expiry in next 30 days, please fix it. The certificate name is' + ' ' + gr.cert_short_description + ' ' + '. Kindly note that if no action is taken within 15 days, the Impact/Urgency will raise to 3M for this Incident.';
            inc.insert();
        }
  }