Aggregate API

The Aggregate API provides endpoints that allow you to compute aggregate statistics about existing table and column data.

This API uses GlideAggregate calls and only honors read-only queries if read access is defined for the table being called on based on role ownership.

Parent Topic:REST API reference

Aggregate - GET /now/stats/{tableName}

Retrieves records for the specified table and performs aggregate functions on the returned values.

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
max
min
sum

URL format

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

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

Note: Available versions are specified in the REST API Explorer. For scripted REST APIs there is additional version information on the Scripted REST Service form.

Supported request parameters

Name	Description
api\_version	Optional. Version of the endpoint to access. For example, `v1` or `v2`. Only specify this value to use an endpoint version other than the latest. Data type: String
tableName	Name of the table for which to retrieve records.Data type: String

Name	Description
name-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`. Data type: String
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. Data type: String
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. Data type: String
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).Data type: String
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`.Data type: String
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`.Data type: String
sysparm\_order\_by	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_order_by=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_order_by=state^DESC`. Data type: String
sysparm\_query	An encoded query.For example: `(sysparm_query=active=true)(sysparm_query=caller_id=javascript:gs.getUserID()^active=true)` Data type: String

Name	Description
None

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.

Header	Description
Accept	Data format of the response body. Supported types: application/json or application/xml. Default: application/json

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 API HTTP response codes.

Status code	Description
200	Successful. The request was successfully processed.
401	Unauthorized. The user credentials are incorrect or have not been passed.
403	Forbidden. The user doesn't have access rights to the specified record.
500	Internal server error. An unexpected error occurred while processing the request. The response contains additional information about the error.

Response body parameters (JSON or XML)

Name	Description
Depends on specified table and specified request parameters.

Sample cURL request

curl "https://instance.servicenow.com/api/now/stats/incident?sysparm_avg_fields=reassignment_count%2Cbusiness_stc&sysparm_group_by=assignment_group" \
--request GET \
--header "Accept:application/json" \
--user "username":"password"

{
  "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"
        }
      ]
    }
  ]
}