Scheduled Job Utility Framework

1var ScheduledJobUtils = Class.create();
2ScheduledJobUtils.prototype = {
3
4  /**
5   * Clean up old records from specified table
6   * @param {string} table - Table name to clean
7   * @param {number} daysOld - Age threshold in days
8   * @param {object} options - {stateField, closedStates, batchSize, dryRun}
9   * @returns {object} {deleted: number, errors: array}
10   */
11  cleanupOldRecords: function(table, daysOld, options) {
12    options = options || {};
13    var stateField = options.stateField || 'state';
14    var closedStates = options.closedStates || ['6', '7', '8'];  // Resolved, Closed, Cancelled
15    var batchSize = options.batchSize || 100;
16    var dryRun = options.dryRun || false;
17
18    var result = {
19      deleted: 0,
20      errors: []
21    };
22
23    try {
24      // Calculate cutoff date
25      var cutoffDate = new GlideDateTime();
26      cutoffDate.addDaysLocalTime(-daysOld);
27
28      var gr = new GlideRecord(table);
29      gr.addQuery('sys_created_on', '<', cutoffDate);
30
31      // Only delete records in closed states
32      if (stateField && closedStates.length > 0) {
33        gr.addQuery(stateField, 'IN', closedStates.join(','));
34      }
35
36      gr.setLimit(batchSize);
37      gr.query();
38
39      while (gr.next()) {
40        try {
41          if (!dryRun) {
42            gr.setWorkflow(false);  // Skip business rules for performance
43            gr.deleteRecord();
44          }
45          result.deleted++;
46        } catch (e) {
47          result.errors.push('Error deleting ' + gr.number + ': ' + e.message);
48        }
49      }
50
51      gs.info('ScheduledJobUtils.cleanupOldRecords: ' + 
52              (dryRun ? 'Would delete' : 'Deleted') + ' ' + result.deleted + 
53              ' records from ' + table + ' older than ' + daysOld + ' days');
54
55    } catch (e) {
56      result.errors.push('Cleanup error: ' + e.message);
57      gs.error('ScheduledJobUtils.cleanupOldRecords error: ' + e.message);
58    }
59
60    return result;
61  },
62
63  /**
64   * Send digest notifications for overdue items
65   * @param {object} config - Notification configuration
66   * @returns {object} {sent: number, errors: array}
67   */
68  sendOverdueDigest: function(config) {
69    var result = {
70      sent: 0,
71      errors: []
72    };
73
74    try {
75      // Default configuration
76      var table = config.table || 'incident';
77      var dueDateField = config.dueDateField || 'due_date';
78      var stateField = config.stateField || 'state';
79      var openStates = config.openStates || ['1', '2', '3'];  // New, In Progress, etc.
80      var assigneeField = config.assigneeField || 'assigned_to';
81      var subjectTemplate = config.subjectTemplate || 'Overdue Items - ';
82
83      // Get overdue records grouped by assignee
84      var assigneeGroups = {};
85      var gr = new GlideRecord(table);
86      gr.addQuery(dueDateField, '<', new GlideDateTime());
87      gr.addQuery(stateField, 'IN', openStates.join(','));
88      gr.addQuery(assigneeField, '!=', '');
89      gr.query();
90
91      while (gr.next()) {
92        var assignee = gr.getValue(assigneeField);
93
94        if (!assigneeGroups[assignee]) {
95          assigneeGroups[assignee] = [];
96        }
97
98        assigneeGroups[assignee].push({
99          number: gr.getValue('number'),
100          short_description: gr.getValue('short_description'),
101          due_date: gr.getValue(dueDateField)
102        });
103      }
104
105      // Send digest to each assignee
106      for (var assignee in assigneeGroups) {
107        var items = assigneeGroups[assignee];
108
109        if (items.length === 0) continue;
110
111        try {
112          var grUser = new GlideRecord('sys_user');
113          if (!grUser.get(assignee) || !grUser.email) {
114            result.errors.push('No valid email for user: ' + assignee);
115            continue;
116          }
117
118          // Build email content
119          var subject = subjectTemplate + items.length + ' item(s)';
120          var body = 'Hello ' + grUser.first_name + ',\n\n';
121          body += 'You have ' + items.length + ' overdue ' + table + '(s):\n\n';
122
123          for (var i = 0; i < items.length; i++) {
124            var item = items[i];
125            body += '• ' + item.number + ' - ' + item.short_description + '\n';
126            body += '  Due: ' + new GlideDateTime(item.due_date).getDisplayValue() + '\n\n';
127          }
128
129          body += 'Please review and update these items.\n\n';
130          body += 'This is an automated notification.';
131
132          // Send email
133          var mail = new GlideEmailOutbound();
134          mail.setSubject(subject);
135          mail.setBody(body);
136          mail.addAddress(grUser.email.toString());
137          mail.send();
138
139          result.sent++;
140          gs.info('Sent overdue digest to ' + grUser.name + ' (' + items.length + ' items)');
141
142        } catch (e) {
143          result.errors.push('Error sending to ' + assignee + ': ' + e.message);
144        }
145      }
146
147    } catch (e) {
148      result.errors.push('Digest error: ' + e.message);
149      gs.error('ScheduledJobUtils.sendOverdueDigest error: ' + e.message);
150    }
151
152    return result;
153  },
154
155  /**
156   * Perform system health checks
157   * @returns {object} Health check results
158   */
159  performHealthCheck: function() {
160    var health = {
161      timestamp: new GlideDateTime().getValue(),
162      status: 'healthy',
163      checks: []
164    };
165
166    try {
167      // Check 1: Database connectivity
168      var dbCheck = {
169        name: 'Database Connectivity',
170        status: 'pass',
171        message: 'Database connection successful'
172      };
173
174      try {
175        var gr = new GlideRecord('sys_user');
176        gr.setLimit(1);
177        gr.query();
178        if (!gr.hasNext()) {
179          dbCheck.status = 'fail';
180          dbCheck.message = 'No users found in database';
181        }
182      } catch (e) {
183        dbCheck.status = 'fail';
184        dbCheck.message = 'Database query failed: ' + e.message;
185      }
186
187      health.checks.push(dbCheck);
188
189      // Check 2: System queue depth
190      var queueCheck = {
191        name: 'System Queue Depth',
192        status: 'pass',
193        message: 'Queue depth normal'
194      };
195
196      var queueDepth = this._getQueueDepth();
197      if (queueDepth > 1000) {
198        queueCheck.status = 'warn';
199        queueCheck.message = 'High queue depth: ' + queueDepth;
200        if (queueDepth > 5000) {
201          queueCheck.status = 'fail';
202        }
203      } else {
204        queueCheck.message += ' (' + queueDepth + ' items)';
205      }
206
207      health.checks.push(queueCheck);
208
209      // Check 3: Failed scheduled jobs
210      var jobCheck = {
211        name: 'Failed Scheduled Jobs',
212        status: 'pass',
213        message: 'No failed jobs in last 24 hours'
214      };
215
216      var failedJobs = this._getFailedJobs();
217      if (failedJobs > 0) {
218        jobCheck.status = 'warn';
219        jobCheck.message = failedJobs + ' failed jobs in last 24 hours';
220        if (failedJobs > 10) {
221          jobCheck.status = 'fail';
222        }
223      }
224
225      health.checks.push(jobCheck);
226
227      // Check 4: Disk space (via system properties)
228      var diskCheck = {
229        name: 'System Resources',
230        status: 'pass',
231        message: 'System resources normal'
232      };
233
234      // This is a simplified check - in real implementation,
235      // you'd integrate with monitoring APIs
236      var tempRecords = this._countTempRecords();
237      if (tempRecords > 100000) {
238        diskCheck.status = 'warn';
239        diskCheck.message = 'High temporary record count: ' + tempRecords;
240      }
241
242      health.checks.push(diskCheck);
243
244      // Determine overall status
245      var hasFailures = health.checks.some(function(check) { return check.status === 'fail'; });
246      var hasWarnings = health.checks.some(function(check) { return check.status === 'warn'; });
247
248      if (hasFailures) {
249        health.status = 'unhealthy';
250      } else if (hasWarnings) {
251        health.status = 'degraded';
252      }
253
254    } catch (e) {
255      health.status = 'error';
256      health.checks.push({
257        name: 'Health Check Execution',
258        status: 'fail',
259        message: 'Error running health checks: ' + e.message
260      });
261    }
262
263    gs.info('System health check completed: ' + health.status);
264    return health;
265  },
266
267  /**
268   * Update SLA breach status for overdue records
269   * @param {string} table - Table to check
270   * @param {string} dueDateField - Field containing due date
271   * @returns {number} Number of records updated
272   */
273  updateSLABreaches: function(table, dueDateField) {
274    table = table || 'incident';
275    dueDateField = dueDateField || 'due_date';
276    var updated = 0;
277
278    try {
279      var gr = new GlideRecord(table);
280      gr.addQuery(dueDateField, '<', new GlideDateTime());
281      gr.addQuery('sla_breached', '!=', 'true');
282      gr.addQuery('active', 'true');
283      gr.query();
284
285      while (gr.next()) {
286        gr.sla_breached = 'true';
287        gr.breach_time = new GlideDateTime();
288        gr.work_notes = 'SLA breach detected by scheduled job';
289        gr.setWorkflow(false);
290        gr.update();
291        updated++;
292      }
293
294      gs.info('ScheduledJobUtils.updateSLABreaches: Updated ' + updated + ' records');
295
296    } catch (e) {
297      gs.error('SLA breach update error: ' + e.message);
298    }
299
300    return updated;
301  },
302
303  /**
304   * Archive old attachment files (mark for deletion)
305   * @param {number} daysOld - Age threshold
306   * @param {number} maxSize - Maximum size in MB to process
307   * @returns {object} Archive results
308   */
309  archiveOldAttachments: function(daysOld, maxSize) {
310    var result = {
311      processed: 0,
312      sizeFreed: 0,
313      errors: []
314    };
315
316    try {
317      daysOld = daysOld || 365;  // Default 1 year
318      maxSize = maxSize || 100;  // Default 100MB limit
319
320      var cutoffDate = new GlideDateTime();
321      cutoffDate.addDaysLocalTime(-daysOld);
322
323      var totalSizeProcessed = 0;
324      var grAttachment = new GlideRecord('sys_attachment');
325      grAttachment.addQuery('sys_created_on', '<', cutoffDate);
326      grAttachment.addQuery('state', '!=', 'archived');  // Don't re-archive
327      grAttachment.orderBy('size_bytes');  // Process smallest first
328      grAttachment.query();
329
330      while (grAttachment.next()) {
331        var sizeMB = parseInt(grAttachment.size_bytes) / (1024 * 1024);
332        totalSizeProcessed += sizeMB;
333
334        // Stop if we hit the size limit
335        if (totalSizeProcessed > maxSize) {
336          break;
337        }
338
339        try {
340          // Mark as archived (don't actually delete)
341          grAttachment.state = 'archived';
342          grAttachment.u_archived_date = new GlideDateTime();
343          grAttachment.setWorkflow(false);
344          grAttachment.update();
345
346          result.processed++;
347          result.sizeFreed += sizeMB;
348
349        } catch (e) {
350          result.errors.push('Error archiving ' + grAttachment.file_name + ': ' + e.message);
351        }
352      }
353
354      gs.info('ScheduledJobUtils.archiveOldAttachments: Processed ' + result.processed + 
355              ' attachments, freed ' + result.sizeFreed.toFixed(2) + ' MB');
356
357    } catch (e) {
358      result.errors.push('Archive error: ' + e.message);
359      gs.error('ScheduledJobUtils.archiveOldAttachments error: ' + e.message);
360    }
361
362    return result;
363  },
364
365  /**
366   * Generate performance metrics report
367   * @returns {object} Performance metrics
368   */
369  generateMetricsReport: function() {
370    var metrics = {
371      timestamp: new GlideDateTime().getValue(),
372      tables: {},
373      overall: {}
374    };
375
376    try {
377      // Key tables to monitor
378      var tablesToCheck = ['incident', 'sc_request', 'change_request', 'problem'];
379
380      tablesToCheck.forEach(function(table) {
381        metrics.tables[table] = {
382          total_records: 0,
383          open_records: 0,
384          created_today: 0,
385          resolved_today: 0,
386          avg_resolution_time: 0
387        };
388
389        try {
390          // Total records
391          var gaTotal = new GlideAggregate(table);
392          gaTotal.addAggregate('COUNT');
393          gaTotal.query();
394          if (gaTotal.next()) {
395            metrics.tables[table].total_records = parseInt(gaTotal.getAggregate('COUNT'));
396          }
397
398          // Open records
399          var gaOpen = new GlideAggregate(table);
400          gaOpen.addQuery('active', 'true');
401          gaOpen.addAggregate('COUNT');
402          gaOpen.query();
403          if (gaOpen.next()) {
404            metrics.tables[table].open_records = parseInt(gaOpen.getAggregate('COUNT'));
405          }
406
407          // Created today
408          var today = gs.daysAgoStart(0);
409          var gaCreated = new GlideAggregate(table);
410          gaCreated.addQuery('sys_created_on', '>=', today);
411          gaCreated.addAggregate('COUNT');
412          gaCreated.query();
413          if (gaCreated.next()) {
414            metrics.tables[table].created_today = parseInt(gaCreated.getAggregate('COUNT'));
415          }
416
417          // Resolved today (if applicable)
418          if (table !== 'sc_request') {  // Requests use different state model
419            var gaResolved = new GlideAggregate(table);
420            gaResolved.addQuery('resolved_at', '>=', today);
421            gaResolved.addAggregate('COUNT');
422            gaResolved.query();
423            if (gaResolved.next()) {
424              metrics.tables[table].resolved_today = parseInt(gaResolved.getAggregate('COUNT'));
425            }
426          }
427
428        } catch (e) {
429          gs.warn('Error getting metrics for ' + table + ': ' + e.message);
430        }
431      });
432
433      // Calculate overall metrics
434      metrics.overall.total_incidents = metrics.tables.incident.total_records;
435      metrics.overall.total_open = Object.keys(metrics.tables).reduce(function(sum, table) {
436        return sum + metrics.tables[table].open_records;
437      }, 0);
438
439      gs.info('ScheduledJobUtils.generateMetricsReport: Generated metrics for ' + 
440              Object.keys(metrics.tables).length + ' tables');
441
442    } catch (e) {
443      gs.error('Metrics report error: ' + e.message);
444      metrics.error = e.message;
445    }
446
447    return metrics;
448  },
449
450  // Private helper methods
451  _getQueueDepth: function() {
452    try {
453      var ga = new GlideAggregate('ecc_queue');
454      ga.addQuery('state', 'ready');
455      ga.addAggregate('COUNT');
456      ga.query();
457
458      if (ga.next()) {
459        return parseInt(ga.getAggregate('COUNT'));
460      }
461    } catch (e) {
462      gs.warn('Error getting queue depth: ' + e.message);
463    }
464    return 0;
465  },
466
467  _getFailedJobs: function() {
468    try {
469      var yesterday = gs.daysAgoStart(1);
470      var ga = new GlideAggregate('syslog');
471      ga.addQuery('sys_created_on', '>=', yesterday);
472      ga.addQuery('level', 'error');
473      ga.addQuery('source', 'LIKE', 'scheduled');
474      ga.addAggregate('COUNT');
475      ga.query();
476
477      if (ga.next()) {
478        return parseInt(ga.getAggregate('COUNT'));
479      }
480    } catch (e) {
481      gs.warn('Error getting failed jobs: ' + e.message);
482    }
483    return 0;
484  },
485
486  _countTempRecords: function() {
487    try {
488      var ga = new GlideAggregate('sys_import_set_row');
489      ga.addAggregate('COUNT');
490      ga.query();
491
492      if (ga.next()) {
493        return parseInt(ga.getAggregate('COUNT'));
494      }
495    } catch (e) {
496      gs.warn('Error counting temp records: ' + e.message);
497    }
498    return 0;
499  },
500
501  type: 'ScheduledJobUtils'
502};