Please use docs.servicenow.com for the latest documentation.

This site is for reference purposes only and may not be accurate for the latest ServiceNow version

Talk:GlideAggregate

From Wiki Archive
Jump to: navigation, search

Question about encoded query in month to month example -- Jeremy.gardner (talk) 13:47, 19 June 2013 (PDT)

In the example that shows how to compare activity from one month to the next, there is a line that adds the encoded first query to the second. What does this do? At first glance it seems like it would filter for both 2 months ago and 3 months ago. Wouldn't the first agg variable already contain 2 months ago?

[...]

while (agg.hasNext()) {

 [...]
 var query = agg.getQuery();  
 [...]
 agg2.addEncodedQuery(query);


Thanks, Jeremy

Re: Question about encoded query in month to month example -- George.rawlins (talk) 17:34, 18 December 2013 (PST)

Thanks, Jeremy. I'll look into this and get back to you.

Re: Question about encoded query in month to month example -- George.rawlins (talk) 14:12, 9 June 2014 (PDT)

Jeremy, these examples have been revised. Let us know if the revisions haven't answered your question.

Using addTrend() -- - w.h. 11:58, 15 August 2014 (PDT) -- - w.h. 11:58, 15 August 2014 (PDT)

The addTrend() feature of GlideAggregate is not documented usefully in this wiki, and what documentation does exist (section 3.4 of this article) is erroneous in an least one respect ("Day of the Week" is not a valid time interval; the correct interval for that is "Weekday"). By searching the Community I was able to find one key piece of information in a post (jamesrgrintner's reply on Mar 30, 2011 to [1]) about this feature: the syntax for extracting the time value from the trend field is rec.getValue("timeref") where rec is your GlideAggregate record.

I have just completed several hours of painstaking research from that starting point, a labor which I would like to spare anyone else who may have an interest in doing Trend queries from a GlideAggregate; here is what I found:

Formats for useful timeref values:

  • Year: "2014/2014"
  • Month: "1/2014" through "12/2014"
  • Week: "1/2006" though "53/2006" and "0/2007" through "52/2007" (note: 2006 started and ended on Sunday; see Gotchas below for more information)
  • Day: "1/2014" through "31/2014" (for months with 31 days)
  • Date: "2014-01-01 00:00:00/2014" through "2014-12-31 00:00:00/2014"
  • Weekday: "0/2014" through "6/2014" (Sunday through Saturday)
  • Hour: "0/2014" through "23/2014"
Example: Report the count of SLA breaches per month for the period from 6 to 9 months ago, grouped by the SLA:
var a = new GlideAggregate('task_sla');
a.addEncodedQuery('planned_end_time<javascript:gs.monthsAgoStart(6)^planned_end_time>javascript:gs.monthsAgoStart(9)^has_breached=true');
a.groupBy('sla.name');
a.addTrend('planned_end_time','Month');
a.addAggregate('COUNT');
a.query();
while (a.next())
{ gs.addInfoMessage(a.getValue('timeref') + ' ' + a.getValue('sla.name') + ':: ' + a.getAggregate('COUNT')); }
Output when run as a freeform script on Aug 15, 2014:
Background message, type:info, message: 11/2013 Priority 1 resolution:: 2
Background message, type:info, message: 11/2013 Priority 1 response:: 2
Background message, type:info, message: 11/2013 Priority 2 resolution:: 14
Background message, type:info, message: 11/2013 Priority 2 response:: 3
Background message, type:info, message: 11/2013 Priority 3 resolution:: 30
Background message, type:info, message: 11/2013 Priority 4 resolution:: 33
Background message, type:info, message: 11/2013 Priority 4 response:: 6
Background message, type:info, message: 11/2013 Priority 5 response:: 1
Background message, type:info, message: 12/2013 Priority 1 resolution:: 3
Background message, type:info, message: 12/2013 Priority 1 response:: 3
Background message, type:info, message: 12/2013 Priority 2 resolution:: 16
Background message, type:info, message: 12/2013 Priority 2 response:: 1
Background message, type:info, message: 12/2013 Priority 3 resolution:: 38
Background message, type:info, message: 12/2013 Priority 3 response:: 1
Background message, type:info, message: 12/2013 Priority 4 resolution:: 26
Background message, type:info, message: 12/2013 Priority 4 response:: 2
Background message, type:info, message: 12/2013 Priority 5 response:: 1
Background message, type:info, message: 1/2014 Priority 1 resolution:: 5
Background message, type:info, message: 1/2014 Priority 1 response:: 4
Background message, type:info, message: 1/2014 Priority 2 resolution:: 11
Background message, type:info, message: 1/2014 Priority 2 response:: 1
Background message, type:info, message: 1/2014 Priority 3 resolution:: 30
Background message, type:info, message: 1/2014 Priority 3 response:: 2
Background message, type:info, message: 1/2014 Priority 4 resolution:: 39
Background message, type:info, message: 1/2014 Priority 4 response:: 2
Example: the first 6 lines of output from the same query using "Week" as the interval:
Background message, type:info, message: 43/2013 Priority 3 resolution:: 4
Background message, type:info, message: 43/2013 Priority 4 resolution:: 6
Background message, type:info, message: 43/2013 Priority 4 response:: 1
Background message, type:info, message: 44/2013 Priority 1 resolution:: 1
Background message, type:info, message: 44/2013 Priority 1 response:: 1
Background message, type:info, message: 44/2013 Priority 2 resolution:: 4
Example: the first 6 lines of output from the same query using "Weekday" as the interval:
Background message, type:info, message: 0/2013 Priority 1 resolution:: 1
Background message, type:info, message: 0/2013 Priority 2 resolution:: 2
Background message, type:info, message: 0/2013 Priority 2 response:: 1
Background message, type:info, message: 0/2013 Priority 3 resolution:: 13
Background message, type:info, message: 0/2013 Priority 4 resolution:: 8
Background message, type:info, message: 0/2013 Priority 5 response:: 1
Example: the first 6 lines of output from the same query using "Hour" as the interval:
Background message, type:info, message: 6/2013 Priority 1 resolution:: 1
Background message, type:info, message: 6/2013 Priority 1 response:: 1
Background message, type:info, message: 8/2013 Priority 4 response:: 1
Background message, type:info, message: 9/2013 Priority 2 resolution:: 5
Background message, type:info, message: 9/2013 Priority 3 resolution:: 7
Background message, type:info, message: 9/2013 Priority 4 resolution:: 10
Example: the first 6 lines of output from the same query using "Date" as the interval:
Background message, type:info, message: 2013-11-01 00:00:00/2013 Priority 3 resolution:: 4
Background message, type:info, message: 2013-11-01 00:00:00/2013 Priority 4 resolution:: 6
Background message, type:info, message: 2013-11-01 00:00:00/2013 Priority 4 response:: 1
Background message, type:info, message: 2013-11-03 00:00:00/2013 Priority 1 resolution:: 1
Background message, type:info, message: 2013-11-04 00:00:00/2013 Priority 2 resolution:: 1
Background message, type:info, message: 2013-11-04 00:00:00/2013 Priority 4 resolution:: 2


Gotchas: (as of Calgary patch 4):

  1. addTrend() is only useful one time; if you attempt to add both Weekday and Hour for trend, for example, the effect will be as if you only added the second of the two.
  2. The timeref value is always formatted as timevalue/year, and the result set will contain a row (assuming there is any matching data) for each one of these timeref values; for example, if you have an Hour trend and are querying records from 12/15/2013 through 1/15/2014, you will get a row for "13/2013" and also a row for "13/2014" -- not a combined row for "13".
  3. The "'Week'" trend suffers particularly seriously from the issue described above. The timeref value for Tue 12/31/2013 will be "52/2013", while the timeref value for Wed 1/1/2014 - which is in the same week - will be "0/2014"; fortunately, "0" is used only for the first partial week in a year. Week 0 of any given year is therefore always synonymous with the last week of the previous year (i.e. either 52 or 53); years which start on a Sunday (e.g. 2012) skip over week "0" and start with week "1".
  4. The timeref value for the Hour trend is in local time but it does not correctly reflect Daylight Savings Time for the date in question. Instead of determining if DST applies to that date, the system applies today's DST offset (if any) to determine the Hour for this value.
    • If the time period you need to query does not cross the start or end of Daylight Savings Time, you can adjust for this by looking up the offset for the target date range and for today and then applying +/- 1 (modulo 24) to the supplied hour.
    • If it does cross that boundary you will get inaccurate results from the aggregation of (for example) data from the 08:00 hour on days prior to the start of DST with data from the 09:00 hour on days after the start of DST; the only workaround for that issue is to:
      1. Split your date range on the DST boundary
      2. Query separately for data from before the time change and data from after the time change
      3. Apply the +/- 1 (modulo 24) calculation to the hour for whichever period doesn't match today's offset, and finally
      4. Combine the 2 result sets.

Re: Using addTrend() -- - w.h. 11:58, 15 August 2014 (PDT) -- Amy.bowman (talk) 12:28, 15 August 2014 (PDT)

Thank you for sharing this information with us. I will work with the writer to incorporate your findings into the wiki page. Thanks again!