Page tree
Skip to end of metadata
Go to start of metadata

This search command creates an aggregate representation of data in the form of a table of statistics and a corresponding chart based on the field or fields specified. The command also supports the optional group by parameter, to apply aggregate functions on the distinct value of a specified field. For example, you can plot the aggregate count of data occurring from a particular host depending on the data-collector names. Similarly, you can run other aggregate functions such as sum, avg, min, and max to find the sum, average, minimum (smallest), and maximum (highest) values of a field.

Additionally, you can perform the following functions:

  • Limit the number of results: You can limit the results displayed by using the limit parameter.
  • Sort the results: You can sort the results in an ascending or descending way based on one of the following options:
    • One of the aggregate functions that you specify.
    • The group by parameter that you specify.

This command is similar to the timechart command, except that it does not represent data on the basis of a specified time span; instead, it provides an aggregate representation.

This topic contains the following information:

For a list of all search commands, see Search commands.

Syntax

stats (<aggregate-function>)+
stats (<aggregate-function> [as <alias>])+ 
stats (<aggregate-function>)+ [by <field>]
stats (<aggregate-function> [as <alias>])+ [by <field>]
stats (<aggregate-function> [as <alias>])+ [by <field> [limit=<int>]]
stats (<aggregate-function> [as <alias>])+ [by <field> [sortby <option>]]
stats (<aggregate-function> [as <alias>])+ [by <field> [sortby <option>] 
[limit=<int>]]

In the preceding syntax, the following definitions apply:

  • + indicates one or more similar expressions separated by a comma (,) or space.
  • <aggregate-function> refers to the aggregate function used for creating the chart; for example, count, sum, or average.

    The following table describes the aggregate functions that can be used in the command syntax:

    FunctionSyntaxDescription
    Count
    count (<field>)
    Uses field name as an argument and returns the number of occurrences of that field in the search results.
    Sum
    sum (<field>)
    Uses field name as an argument and returns the sum of values of that field in the search results.
    Average
    avg (<field>)
    Uses field name as an argument and returns the average of values of that field in the search results.
    Minimum
    min (<field>)
    Uses field name as an argument and returns the smallest value of that field in the search results.
    Maximum
    max (<field>)
    Uses field name as an argument and returns the highest value of that field in the search results.
    Standard deviationstddev (<field>)Uses field name as an argument and returns the standard deviation of the values of that field in the search results.
    where, <field> can be a field name or a tag name
  • [as <alias>] refers to the as parameter used for optionally renaming a field with an alternate field name (an alias).
  • [by <field>] refers to a group by parameter that is optionally used in conjunction with the aggregate functions, to group the result set by the specified field.
  • [limit=<int>] refers to the limit parameter that can be optionally used to limit the number of results by the integer value specified. <int> refers to the integer value that represents the maximum number of results to return. This parameter must be used in conjunction with the aggregate functions and the by parameter. If you specify both the limit parameter and the sortby parameter, then you must specify the limit parameter only after the sortby parameter.
  • [sortby=<option>] refers to the sortby parameter that can be optionally used to sort the results displayed by using one of the following options:

    • One of the aggregate functions that you specify.
    • The group by parameter that you specify.

    This parameter must be used in conjunction with the aggregate functions and the by parameter. By default, this parameter sorts results in the ascending order based on the by parameter field.

    If you want to sort results in the ascending way, it is optional to specify ASC after specifying the sortby parameter. However, if you want to sort results in the descending way, you must specify DESC after specifying the sortby parameter.

    The following table provides the syntax and examples for sorting results:

    Sorting orderSyntaxExample
    (Default) Ascendingsortby=<option> ASC
    sortby count(HOST) ASC
    Descendingsortby=<option> DESC
    sortby count(HOST) DESC 

Short examples

Example 1: Return the number of occurrences of the RequestType field in the search results.

... | stats count(RequestType)

Example 2: Return the number of occurrences of the RequestType field grouped by the ClientIp field.

... | stats count(RequestType) by ClientIp
Example 3: Return the number of occurrences of the events field and the sum of the values of the bytes-read field in the search results.
... | stats count(events) as Count, sum(bytes-read) as Total 

Example 4: Return the number of occurrences of the events field and the sum of the values of the bytes-read field, grouped by the HOST field.

... | stats count(events) as Count, sum(bytes-read) as Total by HOST

Example 5: Return the sum of values and the average of values of the ResponseSize field, grouped by the ClientIp field, and sorted by the aggregate function for average.
... | stats sum(ResponseSize), avg(ResponseSize) by ClientIp sortby avg(ResponseSize)

Example 6: Return the minimum (smallest), maximum (highest), average, and standard deviation of the ResponseSize field values, grouped by the ClientIp field.
... | stats min(ResponseSize), max(ResponseSize), avg(ResponseSize) 
stddev(ResponseSize) by ClientIp

Example 7: Return the average of values of the ResponseSize field, grouped by the ClientIp field, and limit the number of results to 1.
... | stats avg(ResponseSize) by ClientIp limit=1

Long examples

 The following sample data and sample indexed data (displayed on the Search tab) will help you understand the examples of using the stats command.

Sample data

10.1.1.140 - - [11/Jul/2013:15:01:52 -0700] "GET /themes/ComBeta/images/bullet.png 
HTTP/1.1" 404 100
10.1.1.140 - - [11/Jul/2013:15:02:52 -0700] "GET /themes/ComBeta/images/bullet.png 
HTTP/1.1" 201 150
10.1.1.141 - - [11/Jul/2013:15:03:52 -0700] "PUT /themes/ComBeta/images/bullet.png 
HTTP/1.1" 201 200
10.1.1.141 - - [11/Jul/2013:15:04:52 -0700] "POST /themes/ComBeta/images/bullet.png 
HTTP/1.1" 200 100

Back to examples ↑

Sample indexed data

10.1.1.141 - - [11/Jul/2013:15:04:52 -0700] "POST /themes/ComBeta/images/bullet.png 
HTTP/1.1" 200 100
HOST=local.bmc.com |ResponseSize=100|COLLECTOR_NAME=u4 |ClientIp=10.1.1.141 |ResponseCode=200 |RequestType=POST|RequestURL=/themes/ComBeta/images/bullet.png
10.1.1.141 - - [11/Jul/2013:15:03:52 -0700] "PUT /themes/ComBeta/images/bullet.png 
HTTP/1.1" 201 200
HOST=local.bmc.com |ResponseSize=200|COLLECTOR_NAME=u4 |ClientIp=10.1.1.141 |ResponseCode=201 |RequestType=PUT|RequestURL=/themes/ComBeta/images/bullet.png
10.1.1.140 - - [11/Jul/2013:15:02:52 -0700] "GET /themes/ComBeta/images/bullet.png 
HTTP/1.1" 201 150
HOST=local.bmc.com |ResponseSize=150|COLLECTOR_NAME=u4 |ClientIp=10.1.1.140 |ResponseCode=201 |RequestType=GET|RequestURL=/themes/ComBeta/images/bullet.png
10.1.1.140 - - [11/Jul/2013:15:01:52 -0700] "GET /themes/ComBeta/images/bullet.png 
HTTP/1.1" 404 100
HOST=local.bmc.com |ResponseSize=100|COLLECTOR_NAME=u4 |ClientIp=10.1.1.140 |ResponseCode=404 |RequestType=GET|RequestURL=/themes/ComBeta/images/bullet.png

Back to examples ↑

count

In this example, you use the command to display the number of occurrences of the RequestType field.

Command

... | stats count(RequestType)

Output

count(RequestType)
4

Back to examples ↑

count with by parameter

In this example, you use the command to display the number of occurrences of the RequestType field, grouped by the ClientIp field.

Command

... | stats count(RequestType) by ClientIp

Output

ClientIpcount(RequestType)
10.1.1.1412
10.1.1.1402

Back to examples ↑

sum and avg with by parameter

In this example, you use the command to display the sum of values and the average of values of the ResponseSize field, grouped by the ClientIp field.

Command

... | stats sum(ResponseSize), avg(ResponseSize) by ClientIp

Output

ClientIpsum(ResponseSize)avg(ResponseSize)
10.1.1.141300150
10.1.1.140250125

Back to examples ↑

min

In this example, you use the command to display the minimum value (smallest value) of the ResponseSize field.

Command

... | stats min(ResponseSize)

Output

min(ResponseSize)
100

Back to examples ↑

max

In this example, you use the command to display the maximum value (highest value) of the ResponseSize field.

Command

... | stats max(ResponseSize)

Output

max(ResponseSize)
200

Back to examples ↑

min with by parameter

In this example, you use the command to display the minimum value (smallest value) of the ResponseSize field, grouped by the ClientIp field. By running this command, you can understand the hosts from where minimum values are derived.

Command

... | stats min(ResponseSize)by ClientIp

Output

ClientIpmin(ResponseSize)
10.1.1.141100
10.1.1.140100

Back to examples ↑

min, max, and avg

In this example, you use the command to display the minimum (smallest), maximum (highest), and average of the ResponseSize field values.

Command

... | stats min(ResponseSize) max(ResponseSize), avg(ResponseSize)

Output

min(ResponseSize)max(ResponseSize)avg(ResponseSize)
100200137.500

Back to examples ↑

stddev, min, max, and avg

In this example, you use the command to display the minimum (smallest), maximum (highest), average, and standard deviation of the ResponseSize field values, grouped by the ClientIp field.

Command

... | stats min(ResponseSize), max(ResponseSize), avg(ResponseSize), 
stddev(ResponseSize) by ClientIp

Output


ClientIpmin(ResponseSize)max(ResponseSize)avg(ResponseSize)stddev(ResponseSize)
10.1.1.14010015012525
10.1.1.14110020015050

Notes

  • If this command is preceded by a search command, the fields specified in the aggregate functions, sum and avg, must be of the numeric type. You can specify the numeric type when you create a data pattern. 
  • If the group by field has multiple values, the first value is considered for grouping.
  • If you specify both the limit parameter and the sortby parameter, then you must specify the limit parameter only after the sortby parameter.
  • Fields with duplicate names can have conflicting field types when one field type is set to STRING, while the other is set to any of the numeric field types such as LONG or INTEGER. In this scenario, you can run the stats command, however you might not be able to see accurate results unless you apply the workaround described in following example.

    Example

    If: You were running the following search string where ignored field is saved with the field type STRING and INTEGER:

    _index=metrics DATA_PATTERN=”ITDA Metrics” | stats avg(ignored)

    Then: In your search string, include the filter search command command with isNumeric function before the stats command, as follows:

    _index=metrics DATA_PATTERN=”ITDA Metrics” | filter isNumeric (ignored) | stats avg(ignored)