How to create a trend report with unique users?

[Loudigi]

I'm looking for a way to make a trend report that shows the count of users over month who have opened and incident, but the the user must be unique - meaning that this user must not have opened an incident in the last previous months (6), only new in the current month. Any suggestions would be helpful.

[Josh Cooper]

Neat challenge.

Assuming that you don't want a breakdown by unique user (don't do that), then here are some options.

The way that I would probably do it, if it were me, is to create what I call an Automatic Manual indicator.

- Create a new manual indicator, save it, and copy the sys_id.

- Create a new scheduled job to run nightly, at the end of the day (for today) or beginning of the day (if using yesterday) criteria.

  - High-level:

  - Declare a counter variable, like i=0;

  - Do a GlideAggregate query against incident to get all the incidents opened that day (or yesterday, depending on your schedule timing, count by caller_id)   GlideAggregate - ServiceNow Wiki .     GlideAggregate is super fast, when you don't need all the values of the record.

  - Next, in the while loop of the records you return (GlideAggregate will give you a unique record for each caller_id, instead of every record for each caller_id):

              - Do another GlideAggregate count query against incident, to get all incidents opened BEFORE today, AFTER 6 months ago (create a variable to get current date and time and subtract whatever epoch equivalent of six months is), caller_id is (value from while loop of glideAggregate.)

            - now in THIS GlideAggregate, if there are NO results (count == 0), then i = i + 1; (to count users)

At the close of the first (outside) while loop, then GlideRecord query the Performance Analytics scores table, for the sys_id of the manual indicator you created first, look for a score for today, and if there's not one, create one.   Set the value to i, which should now be the count of all the distinct users that (before today) didn't create any incidents.

NOTE:   Helsinki and earlier, the Performance Analytics Scores table is pa_scores.   Istanbul and later, they changed it to two different tables to better handle scaling.   So if you're building this in Helsinki or less, it'll probably break and need to be rebuilt for Istanbul and later.   Not a huge issue, but something that you need to be aware of.

Another Note:   The drawback to this is maintainability.   If you're running release +2, are you going to remember how this was setup if it breaks when you move to Istanbul next year?