Script to both 'Count Distinct' & calculate 'Average Age'

[dhenslee]

Hey everyone,

Let me start by saying Im not a developer but that Im just dangerous enough to get by and understand basic coding concepts.

I ran into a situation where I need to count distinct records while also calculating those records average age (Current Date/Time - Opened Date/Time).   This is needed because I'm coming from a referenced Alert table that has a many to many relationship with INC records.

Basically I need a combined version of the below 2 screenshots.

What I would use to count distinct records

&

The below is what I would use to calculate average age of an INC

The script is pretty basic :

var diff=function(x,y){return y.dateNumericValue() - x.dateNumericValue();};

var days=function(x,y){return diff(x,y)/(24*60*60*1000);};

days(current.incident.opened_at, score_end);

Can someone at least help point me in the right direction ? My gut tells me this is an array type situation.

[Adam Stout]

Your description is leaving some important background.   There are often multiple ways of doing things but you have already started down a path that may not be the best.   Can you add more description of what you are trying to achieve with these indicators?

Can you use an indicator on the incident table and breakdown by what you need (what you are doing the counts by).   Then this should be straightforward as two indicators and will most likely produce more relevant records in the records tab (if you are keeping snapshots for it).

If you have to do it this way, I think you can do this by creating a script for the base table (not shown in your example where you are COUNTing DISTINCT).   In that script use a glide aggregate with a group by on incident and incident.opened_at.   Then you can loop through these and get the value you need.   Since you are distincting, be sure to use GlideAggreagate and not GlideRecord as the performance will often be very noticeable.

[dhenslee]

Hey Adam,

I'm trying to find an average age for INCs that are generated via Alerts.   I am starting from the the em_Alert table and using the reference field called 'Incident' to refer to the INCs.   The way we have alerts setup; there can be more than one Alert pointed to the same INC thus making it important to count distinct INCs as per my screenshot above.   Our Incident table is getting really large and so the ServiceNow development team at my company is reluctant to add a field that references from the INC table directly back to the em_Alert table.  

Okay I will try to use some type of Glide Aggregate funtion.

Thanks!

Dustin

[Adam Stout]

How about an indicator source on the incident table with a related list condition to only pick incidents that came from an alert?

[dhenslee · Accepted solution]

Good thinking but the problem is that there is information/fields on the em_alert table that we need to have from the Alert too.   The way we ended up solving this is by referencing a script include in the indicator source.   Then the subset of records were alerts that referred to distinct INCs.

Return array of sys_ids (which can be used as a value match for sys_id in record lists, or reports)

for alerts (em_alert) by distinct incident.   This will produce a single alert per incident, filtering

out any duplicate alerts for the incident.

This can be used from the em_alert table by querying on:

      Field: sys_id

      Operator: IS ONE OF

      Value: javascript: get_alert_per_distinct_incident()

function get_alert_per_distinct_incident() {

      var arrAlertsDistinctByIncident = [];

      var objAlertsDistinctByIncident = {};

      // Start by getting alerts with incidents

      var grAlerts = new GlideRecord("em_alert");

      grAlerts.addQuery("incident", "!=", "NULL");

      grAlerts.query();

      var strAlertSysID = "", strIncidentSysID = "";

      while (grAlerts.next()) {

              strAlertSysID = grAlerts.sys_id.toString();

strIncidentSysID = grAlerts.incident.toString();

              // If we haven't already seen this incident, store it as a

              // distinct alert for that incident

              if (!objAlertsDistinctByIncident[strIncidentSysID]) {

                      objAlertsDistinctByIncident[strIncidentSysID] = strAlertSysID;

arrAlertsDistinctByIncident.push(strAlertSysID);

              }

      }

      return arrAlertsDistinctByIncident;

}