Scheduled job to trigger exactly 30 days in date field

[riaz_mansuri]

Hi,

So with a little help from the community I am almost in a good position.

What I need is for the schedule job to run daily. It needs to search the u_infra_certificates table and query the u_expiry_date.

If this date is EXACTLY 30 days from today it will trigger an Incident.

The incident part works fine but what I cannot get it to do is only run it if it is exactly 30 days.

So when it runs daily it skips it if the date is 29 days or 31 days.

My script is below.

var rec = new GlideRecord('u_infra_certificates');

rec.query();

if(rec.next()) {

  var now = new GlideDateTime();

  var gdt = new GlideDateTime(''+rec.u_cert_expiry_date);

var diffSeconds = gs.dateDiff(gdt, now, true);

          if (diffSeconds < 2592000) {

  //creates incident  

    var gr = new GlideRecord('incident');

gr.intialize();

gr.short_description = 'Certificate renewal required';

gr.assignment_group = '5e8550e90f6e3900f6e783fc22050ef3';

gr.description = rec.u_subject_name;

//gr.cmdb_ci = 'Certificate Services (Corporate Internal)';

gr.setDisplayValue('cmdb_ci','Certificate Services (Corporate Internal)');

//gr.u_inf_certificate = current.number;

gr.u_inf_certificate = rec.sys_id;

gr.insert();

  }

}

[ccajohnson]

I realized I used rec.field_name instead of field_name in the query. Is this field a date field, or a date/time field? If it is a date/time field, we may have to rewrite the query and use an advanced query instead. Here is the corrected code without rec.field_name:

       var aDt = new GlideDateTime();
       aDt.addDays(-30);
       var cDate = aDt.toString().split(' ')[0]; 
       var rec = new GlideRecord('u_infra_certificates');
       rec.addQuery('u_cert_expiry_date', cDate); 
       rec.query();

       while(rec.next()) {
       //creates incident
               var incObj = new GlideRecord('incident');
               incObj.intialize();
               incObj.short_description = 'Certificate renewal required';
               incObj.assignment_group = '5e8550e90f6e3900f6e783fc22050ef3';
               incObj.description = rec.u_subject_name;
               incObj.setDisplayValue('cmdb_ci','Certificate Services (Corporate Internal)');
               incObj.u_inf_certificate = rec.sys_id;
               incObj.insert();
       }

You can also run the portion of your query string (the stuff before the while statement) in Scripts Background. That way you can determine if the query is finding the proper number of records:

       var aDt = new GlideDateTime();
       aDt.addDays(-30);
       var cDate = aDt.toString().split(' ')[0]; 
       var rec = new GlideRecord('u_infra_certificates');
       rec.addQuery('u_cert_expiry_date', cDate); 
       rec.query();
       gs.print(rec.getRowCount());

[riaz_mansuri]

Hi Christopher,

This is a Date Field only.

U just did run the top part of the script in Script Backround and the result was 0.

I changed the 30 number to 1 and 2 for testing as I know I have test records which would match that.

very very confused as to why it is not picking up any records

You can see I have test records in place

[ccajohnson · Accepted solution]

Your dates are in the future. I was under the impression that you wanted to create an incident if it had already expired. Try using positive numbers instead of negative numbers. I also discovered that there is a GlideDate method that was used since I created the original script, so there is no need to strip off the time:

       var chkDate = new GlideDate();
       chkDate.addDays(30);
       var rec = new GlideRecord('u_infra_certificates');
       rec.addQuery('u_cert_expiry_date', chkDate); 
       rec.query();
       gs.print(rec.getRowCount());

[riaz_mansuri]

Hi Christoper,

Perfect thank you very much that seems to have worked! Excellent much appreciated.

On a side note any idea why the Number is not transferring to the ticket?

This Part: incObj.u_inf_certificate = rec.sys_id;

It just needs to take the number of the record (the unique ID auro generated) and apply it to the relevent in the Incident form.

Thanks so much

riaz

[ccajohnson]

I am hoping that the u_inf_certificate is a reference field. If it is not, then you need to use a different field from the found record object. If it is and you are not seeing the certificate number, check to see what your table uses as the display value. This is what is shown in a reference field.