Aggregate API - GET /now/stats/{tableName}

This method retrieves records for the specified table and performs aggregate functions on the returned values.

URL format

Versioned URL: /api/now/v1/stats/{tableName}

Default URL: /api/now/stats/{tableName}

Supported request parameters

Table 1. Parameters
Parameter Description
sysparm_query An encoded query.

For example: (sysparm_query=active=true)(sysparm_query=caller_id=javascript:gs.getUserID()^active=true)

If part of the query is invalid, such as by specifying an invalid field name, the instance ignores the invalid part. It then returns rows using only the valid portion of the query. You can control this behavior using the property glide.invalid_query.returns_no_rows. Set this property to true to return no rows on an invalid query.
Note: This property controls the behavior of all queries across the instance, such as in lists, scripts (GlideRecord.query()), and web service APIs.
sysparm_group_by Fields by which to group the returned data. You can specify multiple fields by separating each field with a comma, such as sysparm_group_by=priority,state.
sysparm_having Additional query that enables you to filter the data based on an aggregate operation. The value for this parameter must follow the syntax aggregate^field^operator^value, such as count^priority^>^3 to obtain the number of records within the query results with a priority greater than 3. You can specify multiple queries by separating each with a comma, such ascount^state^=^1,avg^priority^>^3.
sysparm_<aggregate>_fields List of fields on which to perform each aggregate operation. You can specify multiple fields by separating each with a comma. For example, to get the average values from the duration and priority fields, use sysparm_avg_fields=duration,priority.
Note: Specify this parameter, the sysparm_count parameter, or both for your query to return meaningful results. If neither parameter is passed, no aggregate operation is performed.
sysparm_count Flag that determines whether to return the number of records returned by the query.
Note: Specify this parameter, the sysparm_<aggregate>_fields parameter, or both for your query to return meaningful results. If neither parameter is passed, no aggregate operation is performed.
sysparm_display_value Data retrieval operation when grouping by reference or choice fields. Based on this value, the query returns either the display value, the actual value in the database, or both.
  • true returns display values for all fields.
  • false returns actual values from the database. If a value is not specified, this parameter defaults to false.
  • all returns both actual and display values.
There is no preferred method for setting this parameter. However, specifying the display value may cause performance issues as they are not read from the database and may reference other fields and records. For more information on display values and actual values, see Table API FAQs (KB0534905).
sysparm_orderby List of values by which to order grouped results. You can specify an order using a field or an aggregate. For example, if you specify sysparm_orderby=AVG^state, groups of results with lower average state values are returned first. You can also order by COUNT to arrange groups of records by the number of records in each group.

When you specify an order, groups are ordered in ascending order by default. Use ^DESC to sort in descending order, such as sysparm_orderby=state^DESC.

Key-Value Pairs An alternative to using the sysparm_query parameter. You can filter a query using key-value pairs where the key is the name of a field.

For example, instead of using the parameter &sysparm_query=active=true, you can use &active=true. You can use the display value when the field is a choice or reference type field, such as &state=closed instead of &state=7. To specify multiple key-value pairs, separate each with an ampersand, such as &active=true&assigned_to=john.smith.

Headers

The following request and response headers apply to this HTTP action only, or apply to this action in a distinct way. For a list of general headers used in the REST API, see Supported REST API headers.

Table 2. Request headers
Header Description
None
Table 3. Response headers
Header Description
None

Status codes

The following status codes apply to this HTTP action. For a list of possible status codes used in the REST API, see REST response codes .

Table 4. Status codes
Status code Description
200 Indicates that the request completed successfully.

Available aggregate functions

You can specify which aggregate functions to perform by using either the sysparm_<aggregate>_fields parameter or sysparm_having=<aggregate>^field^operator^value parameter, substituting <aggregate> for one of these aggregate functions:

  • avg
  • sum
  • min
  • max

Sample cURL request

curl "https://instance.service-now.com/api/now/stats/incident?sysparm_avg_fields=reassignment_count%2Cbusiness_stc&sysparm_group_by=assignment_group" \
--request GET \
--header "Accept:application/json" \
--user 'admin':'admin'
{
  "result": [
    {
      "stats": {
        "avg": {
          "business_stc": "804162.7143",
          "reassignment_count": "1.0000"
        }
      },
      "groupby_fields": [
        {
          "value": "",
          "field": "assignment_group"
        }
      ]
    },
    {
      "stats": {
        "avg": {
          "business_stc": "2037371.0000",
          "reassignment_count": "1.5000"
        }
      },
      "groupby_fields": [
        {
          "value": "287ee6fea9fe198100ada7950d0b1b73",
          "field": "assignment_group"
        }
      ]
    },
    {
      "stats": {
        "avg": {
          "business_stc": "1821488.2857",
          "reassignment_count": "1.1111"
        }
      },
      "groupby_fields": [
        {
          "value": "8a5055c9c61122780043563ef53438e3",
          "field": "assignment_group"
        }
      ]
    },
    {
      "stats": {
        "avg": {
          "business_stc": "1730322.0000",
          "reassignment_count": "1.2500"
        }
      },
      "groupby_fields": [
        {
          "value": "287ebd7da9fe198100f92cc8d1d2154e",
          "field": "assignment_group"
        }
      ]
    },
    {
      "stats": {
        "avg": {
          "business_stc": "1564478.6250",
          "reassignment_count": "1.2500"
        }
      },
      "groupby_fields": [
        {
          "value": "d625dccec0a8016700a222a0f7900d06",
          "field": "assignment_group"
        }
      ]
    },
    {
      "stats": {
        "avg": {
          "business_stc": "1512202.2500",
          "reassignment_count": "1.1111"
        }
      },
      "groupby_fields": [
        {
          "value": "8a4dde73c6112278017a6a4baf547aa7",
          "field": "assignment_group"
        }
      ]
    }
  ]
}

Sample Python request

#Need to install requests package for python
#easy_install requests
import requests

# Set the request parameters
url = 'https://instance.service-now.com/api/now/stats/incident?sysparm_avg_fields=reassignment_count%2Cbusiness_stc&sysparm_group_by=assignment_group'

# Eg. User name="admin", Password="admin" for this code sample.
user = 'admin'
pwd = 'admin'

# Set proper headers
headers = {"Content-Type":"application/json","Accept":"application/json"}

# Do the HTTP request
response = requests.get(url, auth=(user, pwd), headers=headers  )

# Check for HTTP codes other than 200
if response.status_code != 200: 
    print('Status:', response.status_code, 'Headers:', response.headers, 'Error Response:',response.json())
    exit()

# Decode the JSON response into a dictionary and use the data
data = response.json()
print(data)
<?xml version="1.0" encoding="UTF-8"?>
<response>
   <result>
      <stats>
         <avg>
            <business_stc>804162.7143</business_stc>
            <reassignment_count>1.0000</reassignment_count>
         </avg>
      </stats>
      <groupby_fields>
         <field>assignment_group</field>
         <value />
      </groupby_fields>
   </result>
   <result>
      <stats>
         <avg>
            <business_stc>2037371.0000</business_stc>
            <reassignment_count>1.5000</reassignment_count>
         </avg>
      </stats>
      <groupby_fields>
         <field>assignment_group</field>
         <value>287ee6fea9fe198100ada7950d0b1b73</value>
      </groupby_fields>
   </result>
   <result>
      <stats>
         <avg>
            <business_stc>1821488.2857</business_stc>
            <reassignment_count>1.1111</reassignment_count>
         </avg>
      </stats>
      <groupby_fields>
         <field>assignment_group</field>
         <value>8a5055c9c61122780043563ef53438e3</value>
      </groupby_fields>
   </result>
   <result>
      <stats>
         <avg>
            <business_stc>1730322.0000</business_stc>
            <reassignment_count>1.2500</reassignment_count>
         </avg>
      </stats>
      <groupby_fields>
         <field>assignment_group</field>
         <value>287ebd7da9fe198100f92cc8d1d2154e</value>
      </groupby_fields>
   </result>
   <result>
      <stats>
         <avg>
            <business_stc>1564478.6250</business_stc>
            <reassignment_count>1.2500</reassignment_count>
         </avg>
      </stats>
      <groupby_fields>
         <field>assignment_group</field>
         <value>d625dccec0a8016700a222a0f7900d06</value>
      </groupby_fields>
   </result>
   <result>
      <stats>
         <avg>
            <business_stc>1512202.2500</business_stc>
            <reassignment_count>1.1111</reassignment_count>
         </avg>
      </stats>
      <groupby_fields>
         <field>assignment_group</field>
         <value>8a4dde73c6112278017a6a4baf547aa7</value>
      </groupby_fields>
   </result>
</response>