Enter Logic for 2 or More Tickets Created

[lewistommy]

I can't seem to find how to enter logic or build criteria on groupings. I would like to view all the tickets or cases entered by users, only if they have entered 2 or more tickets that month. From there I would like to create a report and dashboards with that data. Any clue as to how or where I can build that logic?

[Sujit Jadhav]

Hello @lewistommy 

you can use a scripted REST API along with GlideAggregate to query and filter the tickets entered by users who have entered two or more tickets in the current month. Below is a sample scripted REST API script that you can create:

(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
// Get the current month and year
var now = new GlideDateTime();
var currentMonth = now.getMonthLocalTime();
var currentYear = now.getYearLocalTime();

// Create a GlideAggregate to query the Incident table
var ga = new GlideAggregate('incident');
ga.addQuery('sys_created_on', '>=', currentYear + '-' + currentMonth + '-01 00:00:00');
ga.addQuery('sys_created_by', '!=', ''); // Exclude tickets created by the system
ga.addAggregate('COUNT');
ga.groupBy('sys_created_by');
ga.addHaving('COUNT', '>=', 2); // Filter users with 2 or more tickets

// Execute the query
ga.query();

var results = [];
while (ga.next()) {
var user = ga.sys_created_by.getDisplayValue();
var ticketCount = ga.getAggregate('COUNT');
results.push({
'User': user,
'Ticket Count': ticketCount
});
}

// Set the response body
response.setBody(results);

})(request, response);

Here's how the script works:

1. It retrieves the current month and year.
2. It uses GlideAggregate to query the incident table for tickets created in the current month.
3. It groups the results by the user who created the tickets.
4. It filters out users who have entered two or more tickets using the addHaving method.
5. It constructs a JSON response containing the user and the ticket count for each user.
   You can create a new scripted REST API in ServiceNow, paste the script into the script section, and configure the necessary authentication and access settings. Once the scripted REST API is created, you can call it to retrieve the list of users who have entered two or more tickets in the current month.

Please mark this as correct answer and helpful if it resolved, or mark this helpful if this help you to reach towards solution.