If the stats command is used without a BY clause, only one row is returned, which is the aggregation over the entire incoming result set.
Click Search on the left panel and enter the query in the search field.
Syntax: ..| stats <fieldname>
Example: *|stats resptime
Output: This generates all the possible statistics of a field, that is, count, max, min, avg, and sum.
Syntax:
..|stats <aggregate_function>(<fieldname>)
Example: *|stats max(resptime)
Output: It calculates the maximum value of response time. The output field is highlighted.
Note: A user can use any other such aggregation function in query same as ‘Max’.
Syntax:
..|stats <aggregate_function> (<fieldname>) AS <aliasname>
Example: *|stats max(resptime) AS rtime
Output: This generates the statistics of a field based on the aggregation function, such as, count, max, min, avg, or sum and the resultant field is based on <aliasname>.
Stats with BY Clause
If the Stats command is used with BY clause, one row is returned for each distinct value specified in the BY clause.
Syntax: BY <field-list>
Description: The name of one or more fields to group by. The user cannot use a wildcard character to specify multiple fields with similar names. Specify each field separately. The BY clause returns one row for each distinct value in the BY clause fields. If no BY clause is specified, the stats command returns only one row, which is the aggregation over the entire incoming result set.
Syntax:
..|stats <aggregate_function> (<fieldname1>) BY <fielname2>
Example 1: *|stats max(resptime) BY server
Output: This generates the statistics of a field based on the aggregation function, such as, count, max, min, avg, or sum, and is grouped on the field2 basis. The resultant field is based on the aggregation function and the field name, such as <aggregate_function>_< fieldname>.
Note: A user can use more than one comma-separated field names in group by clause.
Syntax:
..|stats <aggregate_function> (<fieldname1>) AS <aliasname> BY <fieldname2>,<fieldname3>
Example 2: *|stats max(resptime) by user,query
Output: This generates the statistics of a field based on the aggregation function, grouped by multiple field names.
Syntax:
..|stats <aggregate_function> (<fieldname1>) AS <aliasname> BY <fielname2>
Example 3: *|stats max(resptime) AS rtime BY server
Output: This generates the statistics of a field based on the aggregation function, such as count, max, min, avg, or sum, and is grouped on the field2 basis. The resultant field is based on <aliasname>. Here, <fieldname> is the field name for which a user wants to aggregate data. <aggregate_function> is the aggregate function to be applied. Currently, count, max, min, avg, and sum functions are applicable.
Stats Query with Span Clause
This command aggregates and make buckets based on a field and the span mentioned. A user can group the result based on interval, such as year, quarter, month, week, day, hour, minute, or second.
Syntax:
..|stats <aggregationFunction>(<fieldName>) span=<timeInterval>
Example: * | stats max(offset) AS maximum SPAN=1y
Output: This generates the log results grouped based on the time intervals. The different time intervals that can be used are milliseconds, seconds, minutes, hours, days, and so on.
Stats Query with Span and BY Clause
This displays the result based on the provided span and field mentioned in BY clause.
Different Time-Intervals
- milliseconds (ms): Fixed length interval; supports multiples.
- Seconds (s): 1000 milliseconds; fixed length interval (except for the last second of a minute that contains a leap-second, which is 2000ms long); supports multiples.
- Minutes(m) — One minute (1m) is the interval between 00 seconds of the first minute and 00 seconds of the following minute in the specified time zone.
- Multiple minutes (nm) are intervals of exactly 60×1000=60,000 milliseconds each.
- Hours(h): One hour (1h) is the interval between 00:00 minutes of the first hour and 00:00 minutes of the following hour in the specified time zone.
- Multiple hours (nh) are intervals of exactly 60x60x1000=3,600,000 milliseconds each.
- Days(d): One day (1d) is the interval between the start of the day and the start of the following day in the specified time zone.
- Multiple days (nd) are intervals of exactly 24x60x60x1000=86,400,000 milliseconds each.
- Weeks(w): Multiple weeks (nw) are not supported.
- Months(M): Multiple months (nM) are not supported.
- Quarters (q): Multiple quarters (nq) are not supported.
- Years (y): Multiple years (ny) are not supported.
Syntax: ..|stats <aggregation>(fieldName) span=<DatetimeInterval>
Example: * | stats count(offset) max(offset) SPAN=1y BY server, type
Output: This generates the log result based on the given aggregated fields for a provided span of time grouped by multiple fields’ name.
Stats latest along with BY Clause
This provides the result with the first occurrence of the field specified in BY clause.
Syntax: ..|stats latest(<filedName1>) as <aliasName> by <fieldName2>,<fieldName3>..
Example: * | stats latest(@timestamp) BY server,type
Output: This result in latest occurrence of @timestamp field grouped by multiple fields provided.
Stats earliest along with BY Clause
This provides the result with the latest occurrence of the field specified in BY clause.
Syntax: …|stats earliest(<filedName1>) as <aliasName> by <fieldName2>,<fieldName3>..
Example: * | stats earliest(@timestamp) BY server, type
Output: This result in earliest occurrence of @timestamp field grouped by multiple fields provided.
Docstats Query
This query finds the statistics based on the aggregation function and the grouping of data will be based on another field. Based on the group by field, it appends the generated field (key: value) in the document source.
Syntax: ..|docstats <aggregation>(<fieldname>) AS <aliasname> by <fieldname>
Example: *|docstats max(resptime) AS rtime BY server
Output: This generates the statistics of a field based on the aggregation function, such as count, max, min, avg, or sum and is grouped on field2 basis. The resultant field is based on <aliasname>. The resultant field value (key: value pair) is appended in the source based on the <fielname2>. Here, <fieldname> is the field name for which a user wants to aggregate data. <aggregate_function> is the aggregate function to be applied. Currently count, max, min, avg, and sum functions are applicable.
In this case, first server wise max response time is calculated then the resultant value (key: value) is added in the source based on the matching server.
Example: *|eval a= exec (“return $tier$”)|rex b = “^([a-z]+)$” server|docstats max(resptime) AS rtime BY server
Description: Docstats query can be used along with Rex and Eval.
Output
Streamstats Query
This query finds the cumulative sum of the field based on the timestamp. Currently, the default timestamp interval is 5 seconds.
Syntax: ..|streamstats <numeric fieldname>
Here, <numeric fieldname> is the numeric field name.
Example 1: *|streamstats resptime
Output: This generates the cumulative sum of the resptime that is calculated based on the timestamp.
Multistats Query
This query is used to find the statistics for more than one field, such as sum, count, avg, min, and max, at the same time. It can be grouped by another field.
Syntax:
..|stats <aggregation>(<fieldname>) <aggregation>(<fieldname>) <aggregation>(<fieldname>)
Example: *|stats max(httpstatuscode) min(httpstatuscode) max(httpstatuscode)
Output: This finds the maximum value of the field log_message. status as max_log_message. status, minimum of as httpstatuscode min_ httpstatuscode, maximum of httpstatuscode as max_ httpstatuscode.
Syntax:
..|stats <aggregation>(<fieldname>) AS <aliasname> <aggregation>(<fieldname>) AS <aliasname>
Example: *|stats max(httpstatuscode) AS higher min(httpstatuscode) AS lower
Output: This find the maximum value of filed log_message. status as higher and minimum of httpstatuscode as lower.
Syntax:
..|stats <aggregation>(<fieldname>) AS <aliasname> <aggregation>(<fieldname>) AS <aliasname> BY <fieldname>
Example: *|stats max(httpstatuscode) AS higher min(httpstatuscode) AS lower BY httpstatuscode
Output: This find the maximum value of filed httpstatuscode as higher and minimum as lower group by httpstatuscode
Lookup
Lookup enriches the document data by adding field-value combinations from lookup tables. NetForest uses lookup to match field-value combinations in document data with field value combinations in external resources. If it finds those field-value combinations in the resources, it appends the corresponding field-value combinations from the resources to document data in the search result. A user can use it along with REX and EVAL queries.
Syntax:|lookup <lookup-filename> <field-name> output <destfield-name>
where,
<lookup-filename> refers to the lookup file name.
<field-name> refers to a field in the index to match against the events.
<destfield-name> refers to a field in the lookup file to be copied into the events.
Example: * | lookup file cp output sname
Output: This search the json format lookup file in $nfdb/config/lookup directory. If file1 contains field cp it matches the field values and added the field in current searchable indexed data.
Where
Where command is used to filter search results by comparing two different fields and values and use logical expression (AND / OR / NOT) for multi-fields operation.
Syntax: ..|where <expression>
where,
<expression> can be any combination of field name, comparison operator, and logical operation.
Example: *|where resptime<1000
Output: This generates the results where the response size is less than 1000.
Append query appends the results of a sub-search to the current results. The append command runs only over historical data.
Note: It does not produce correct results if used in a real-time search.
Syntax: ..|append [<subsearch>]
Here, search and subsearch refers to <string> or <query> or <queryWithPipe>
Example: * | append[type:accesslog]
Output: This generates the results by adding documents where type: accesslog to the output of ‘*’ .
Example: Append query with multi rex and multi eval.
index=*query=*ANDS NOT tier: Tomcat|append[*|rex a = “^([A-Z]+)”tier | eval b = exec(“returns $a$”) rex c = “^([A-Z]+)”type|eval d=exec(“return $c$”)]
Output: This generates the result by adding the rex/eval evaluated query to the output of ‘*’.
