Back to Topics

// Count active incidents
var ga = new GlideAggregate('incident');
ga.addQuery('active', true);
ga.addAggregate('COUNT');
ga.query();

if (ga.next()) {
    var count = ga.getAggregate('COUNT');
    gs.info('Active incidents: ' + count);
}

// Count by priority
var ga2 = new GlideAggregate('incident');
ga2.addQuery('active', true);
ga2.groupBy('priority');
ga2.addAggregate('COUNT');
ga2.query();

while (ga2.next()) {
    var priority = ga2.priority.toString();
    var count = ga2.getAggregate('COUNT');
    gs.info('Priority ' + priority + ': ' + count + ' incidents');
}

// SUM - Total cost of all hardware
var ga = new GlideAggregate('alm_hardware');
ga.addAggregate('SUM', 'cost');
ga.query();

if (ga.next()) {
    var totalCost = ga.getAggregate('SUM', 'cost');
    gs.info('Total hardware cost: $' + totalCost);
}

// AVG - Average resolution time
var ga2 = new GlideAggregate('incident');
ga2.addQuery('state', '6'); // Resolved
ga2.addAggregate('AVG', 'calendar_duration');
ga2.query();

if (ga2.next()) {
    var avgDuration = ga2.getAggregate('AVG', 'calendar_duration');
    gs.info('Average resolution time: ' + avgDuration + ' seconds');
}

// MIN and MAX - Age range
var ga3 = new GlideAggregate('incident');
ga3.addAggregate('MIN', 'opened_at');
ga3.addAggregate('MAX', 'opened_at');
ga3.query();

if (ga3.next()) {
    var oldest = ga3.getAggregate('MIN', 'opened_at');
    var newest = ga3.getAggregate('MAX', 'opened_at');
    gs.info('Oldest: ' + oldest + ', Newest: ' + newest);
}

// Count incidents by assignment group
var ga = new GlideAggregate('incident');
ga.addQuery('active', true);
ga.groupBy('assignment_group');
ga.addAggregate('COUNT');
ga.orderByAggregate('COUNT'); // Order by count
ga.query();

gs.info('Incidents by Group:');
while (ga.next()) {
    var group = ga.assignment_group.getDisplayValue();
    var count = ga.getAggregate('COUNT');
    gs.info(group + ': ' + count);
}

// Multiple grouping - by state and priority
var ga2 = new GlideAggregate('incident');
ga2.groupBy('state');
ga2.groupBy('priority');
ga2.addAggregate('COUNT');
ga2.query();

while (ga2.next()) {
    var state = ga2.state.getDisplayValue();
    var priority = ga2.priority.toString();
    var count = ga2.getAggregate('COUNT');
    gs.info('State: ' + state + ', Priority: ' + priority + ', Count: ' + count);
}

// Groups with more than 10 incidents
var ga = new GlideAggregate('incident');
ga.addQuery('active', true);
ga.groupBy('assignment_group');
ga.addAggregate('COUNT');
ga.addHaving('COUNT', '>', 10);
ga.query();

gs.info('Groups with >10 incidents:');
while (ga.next()) {
    var group = ga.assignment_group.getDisplayValue();
    var count = ga.getAggregate('COUNT');
    gs.info(group + ': ' + count);
}

// Users with average resolution time > 2 days
var ga2 = new GlideAggregate('incident');
ga2.addQuery('state', '6');
ga2.groupBy('assigned_to');
ga2.addAggregate('AVG', 'calendar_duration');
ga2.addHaving('AVG', 'calendar_duration', '>', 172800); // 2 days in seconds
ga2.query();

while (ga2.next()) {
    var user = ga2.assigned_to.getDisplayValue();
    var avgTime = ga2.getAggregate('AVG', 'calendar_duration');
    gs.info(user + ': ' + avgTime + ' seconds average');
}

// Pattern 1: Get total count
function getTotalIncidents() {
    var ga = new GlideAggregate('incident');
    ga.addAggregate('COUNT');
    ga.query();
    return ga.next() ? parseInt(ga.getAggregate('COUNT')) : 0;
}

// Pattern 2: Count by field value
function countByPriority() {
    var results = {};
    var ga = new GlideAggregate('incident');
    ga.groupBy('priority');
    ga.addAggregate('COUNT');
    ga.query();
    
    while (ga.next()) {
        var priority = ga.priority.toString();
        results[priority] = parseInt(ga.getAggregate('COUNT'));
    }
    return results;
}

// Pattern 3: Top N records
function getTopAssignees(limit) {
    var ga = new GlideAggregate('incident');
    ga.groupBy('assigned_to');
    ga.addAggregate('COUNT');
    ga.orderByAggregate('COUNT');
    ga.setLimit(limit);
    ga.query();
    
    var results = [];
    while (ga.next()) {
        results.push({
            user: ga.assigned_to.getDisplayValue(),
            count: ga.getAggregate('COUNT')
        });
    }
    return results;
}