The following list contains the SPL2 functions that you can use to change the order of the events based on time.
For an overview about the stats and charting functions, seeOverview of SPL2 stats functions.
earliest(<value>)
Returns the chronologically earliest seen occurrence of a value in a field.
Usage
You can use this function with the stats
and timechart
commands.
This function processes field values as strings.
Basic example
You run the following search to locate invalid user login attempts against a specific sshd (Secure Shell Daemon). You use the fields
command to see the values in the _time
, source
, and _raw
fields.
| FROM main WHERE `sourcetype=secure "invalid user" "sshd[5258]"`| fields _time, source, _raw
The results look something like this:
_time | source | _raw |
---|---|---|
29 Apr 2020 00:15:05 | .../mailsv/secure.log | Tue Apr 28 2020 00:15:05 mailsv1 sshd[5258]: Failed password for invalid user tomcat from 67.170.226.218 port 1490 ssh2 |
01 May 2020 00:15:04 | .../www2/secure.log | Fri May 01 2020 00:15:04 www2 sshd[5258]: Failed password for invalid user brian from 130.253.37.97 port 4284 ssh2 |
30 Apr 2020 00:15:02 | .../www3/secure.log | Thu Apr 30 2020 00:15:02 www3 sshd[5258]: Failed password for invalid user operator from 222.169.224.226 port 1711 ssh2 |
28 Apr 2020 00:15:01 | .../www1/secure.log | Tue Apr 28 2020 00:15:01 www1 sshd[5258]: Failed password for invalid user rightscale from 87.194.216.51 port 3361 ssh2 |
01 May 2020 00:15:05 | .../mailsv/secure.log | Fri May 01 2020 00:15:05 mailsv1 sshd[5258]: Failed password for invalid user testuser from 194.8.74.23 port 3626 ssh2 |
27 Apr 2020 00:15:01 | .../www1/secure.log | Mon Apr 27 2020 00:15:01 www1 sshd[5258]: Failed password for invalid user redmine from 91.208.184.24 port 3587 ssh2 |
You extend the search using the earliest
function.
| FROM main WHERE `sourcetype=secure "invalid user" "sshd[5258]"`| fields _time, source, _raw | stats earliest(_raw)
The search returns the event with the _time
value 2020-04-27 00:15:01
, which is the event with the oldest timestamp.
_time | source | _raw |
---|---|---|
2020-04-27 00:15:01 | .../www1/secure.log | Mon Apr 27 2020 00:15:01 www1 sshd[5258]: Failed password for invalid user redmine from 91.208.184.24 port 3587 ssh2 |
earliest_time(<value>)
Returns the UNIX time of the chronologically earliest-seen occurrence of a given field value.
Usage
You can use this function with the stats
and timechart
commands.
This function processes field values as strings.
If you have metrics data, you can use the earliest_time
function in conjunction with earliest
, latest
, and latest_time
functions to calculate the rate of increase for a counter. Alternatively you can use the rate
function counter to do the same thing.
Basic example
The following search runs against metric data. It returns the earliest UNIX time values, for every minute, for each metric_name
that begins with deploy
.
| FROM _metrics WHERE earliest_time(_value) metric_name=deploy* span(metric_name, 1m)
The results look something like this:
_time | metric_name | earliest_time(_value) |
---|---|---|
2018-11-11 18:14:00 | deploy-connections.nCurrent | 1541988860.000000 |
2018-11-11 18:14:00 | deploy-connections.nStarted | 1541988860.000000 |
2018-11-11 18:14:00 | deploy-server.volumeCompletedKB | 1541988860.000000 |
2018-11-11 18:15:00 | deploy-connections.nCurrent | 1541988922.000000 |
2018-11-11 18:15:00 | deploy-connections.nStarted | 1541988922.000000 |
2018-11-11 18:15:00 | deploy-server.volumeCompletedKB | 1541988922.000000 |
latest(<value>)
Returns the chronologically latest seen occurrence of a value in a field.
Usage
You can use this function with the stats
and timechart
commands.
This function processes field values as strings.
Basic example
You run the following search to locate invalid user login attempts against a specific sshd (Secure Shell Daemon). You use the fields
command to see the values in the _time
, source
, and _raw
fields.
| FROM main WHERE `sourcetype=secure "invalid user" "sshd[5258]"`| fields _time, source, _raw
The results look something like this:
_time | source | _raw |
---|---|---|
28 Apr 2020 00:15:05 | .../mailsv/secure.log | Tue Apr 28 2020 00:15:05 mailsv1 sshd[5258]: Failed password for invalid user tomcat from 67.170.226.218 port 1490 ssh2 |
01 May 2020 00:15:04 | .../www2/secure.log | Fri May 01 2020 00:15:04 www2 sshd[5258]: Failed password for invalid user brian from 130.253.37.97 port 4284 ssh2 |
30 Apr 2020 00:15:02 | .../www3/secure.log | Thu Apr 30 2020 00:15:02 www3 sshd[5258]: Failed password for invalid user operator from 222.169.224.226 port 1711 ssh2 |
28 Apr 2020 00:15:01 | .../www1/secure.log | Tue Apr 28 2020 00:15:01 www1 sshd[5258]: Failed password for invalid user rightscale from 87.194.216.51 port 3361 ssh2 |
01 May 2020 00:15:05 | .../mailsv/secure.log | Fri May 01 2020 00:15:05 mailsv1 sshd[5258]: Failed password for invalid user testuser from 194.8.74.23 port 3626 ssh2 |
27 Apr 2020 00:15:01 | .../www1/secure.log | Mon Apr 27 2020 00:15:01 www1 sshd[5258]: Failed password for invalid user redmine from 91.208.184.24 port 3587 ssh2 |
You extend the search using the latest
function.
| FROM main WHERE `sourcetype=secure "invalid user" "sshd[5258]"`| fields _time, source, _raw | stats latest(_raw)
The search returns the event with the _time
value 2020-05-01 00:15:05
, which is the event with the most recent timestamp.
_time | source | _raw |
---|---|---|
01 May 2020 00:15:05 | .../mailsv/secure.log | Fri May 01 2020 00:15:05 mailsv1 sshd[5258]: Failed password for invalid user testuser from 194.8.74.23 port 3626 ssh2 |
latest_time(<value>)
Returns the UNIX time of the chronologically latest-seen occurrence of a given field value.
Usage
You can use this function with the stats
and timechart
commands.
This function processes field values as strings.
If you have metrics data, you can use the latest_time
funciton in conjunction with earliest
, latest
, and earliest_time
functions to calculate the rate of increase for a counter. Alternatively, you can use the rate
function counter to do the same thing.
Basic example
The following search runs against metric data. It is designed to return the earliest UNIX time values in the past 60 minutes for metrics with names that begin with queue.
.
select latest(_value), metric_name, _time from metrics where metric_name like "queue.*" group by metric_name, span(_time, 1m)
The results look something like this:
_time | metric_name | earliest_time(_value) |
---|---|---|
2018-11-13 14:43:00 | queue.current_size | 1542149039.000000 |
2018-11-13 14:43:00 | queue.current_size_kb | 1542149039.000000 |
2018-11-13 14:43:00 | queue.largest_size | 1542149039.000000 |
2018-11-13 14:43:00 | queue.max_size_kb | 1542149039.000000 |
2018-11-13 14:43:00 | queue.smallest_size | 1542149039.000000 |
2018-11-13 14:44:00 | queue.current_size | 1542149070.000000 |
2018-11-13 14:44:00 | queue.current_size_kb | 1542149070.000000 |
2018-11-13 14:44:00 | queue.largest_size | 1542149070.000000 |
2018-11-13 14:44:00 | queue.max_size_kb | 1542149070.000000 |
2018-11-13 14:44:00 | queue.smallest_size | 1542149070.000000 |
per_day(<value>)
Returns the values in a field or eval expression for each day.
Usage
You can use this function with the timechart
command.
Basic examples
The following example returns the values for the total
field for each day.
... | timechart per_day(total)
The following example returns the results of the eval expression eval(method="GET"))
and labels the field for the evaluated results "Views".
... | timechart per_day(eval(method="GET")) AS Views
Extended example
This search uses the per_day
function and eval expressions to determine how many times the web pages were viewed and how many times items were purchased. This example should work with any format of Apache Web access log file.
| FROM main WHERE sourcetype=access_* | timechart per_day(eval(method="GET")) AS Views_day, per_day(eval(action="purchase")) AS Purchases
To determine the number of Views and Purchases for each hour, minute, or second you can add the other time functions to the search. For example:
| FROM main WHERE sourcetype=access_* | timechart per_day(eval(method="GET")) AS Views_day, per_hour(eval(method="GET")) AS Views_hour, per_minute(eval(method="GET")) AS Views_minute, per_day(eval(action="purchase")) AS Purchases
per_hour(<value>)
Returns the values in a field or eval expression for each hour.
Usage
You can use this function with the timechart
command.
Basic examples
The following example returns the values for the field total
for each hour.
... | timechart per_hour(total)
The following example returns the results of the eval expression eval(method="POST"))
and labels the field for the evaluated results Views
.
... | timechart per_hour(eval(method="POST")) AS Views
per_minute(<value>)
Returns the values in a field or eval expression for each minute.
Usage
You can use this function with the timechart
command.
Basic examples
The following example returns the values for the field total
for each minute.
... | timechart per_minute(total)
The following example returns the results of the eval expression eval(method="GET"))
and labels the fields for the evaluated results "Views".
... | timechart per_minute(eval(method="GET")) AS Views
per_second(<value>)
Returns the values in a field or eval expression for each second.
Usage
You can use this function with the timechart
command.
Basic examples
The following example returns the values for the field kb
for each second.
... | timechart per_second(kb)
rate(<value>)
Returns the per-second rate change of the value in a field. The rate
function represents the following formula:
(latest(<value>) - earliest(<value>) / latest_time(<value>) - earliest_time(<value>))
The rate
function also handles the largest value reset if there is at least one reset.
Usage
You can use this function with the stats
command.
- Provides the per-second rate change for accumulating counter metrics. Accumulating counters report the total counter value since the last counter reset.
- Requires the
earliest
andlatest
values of the field to be numerical, and theearliest_time
andlatest_time
values to be different. - Requires at least two metrics data points in the search time range.
- Should be used to provide rate information about single, rather than multiple, counters.
Basic example
The following search runs against metric data. It provides the hourly hit rate for a metric that provides measurements of incoming web traffic. It uses the processor
filter to ensure that it is not reporting on multiple metric series (name
and processor
combinations).
| FROM _metrics WHERE name=indexerpipe processor=index_thruput | stats rate(traffic.incoming) AS rate_hits span=1h
span(<time>,<span-length>)
This function groups search results by the timespan you specify. This function is used only as part of a group by clause.
Usage
You can use this function in the BY clause of the stats command and in the GROUPBY clause of the from command.
For the <time> parameter, you can specify any field that contains values in UNIX time.
- With the
stats
function, the <time> parameter is specified as part of the BY clause, before thespan
function. - With the GROUPBY clause in the
from
command, the <time> parameter is specified with the <span-length> in thespan
function.
The <span-length> consists of two parts, an integer and a time scale. For example, to specify 30 seconds you can use 30s
. To specify 2 hours you can use 2h
. If not specified, a <span-length> is chosen based on the time range of the search.
The following table lists the valid time scale units:
Time scale | Syntax |
---|---|
seconds | s | sec | secs | second | seconds |
minutes | m | min | mins | minute | minutes |
hours | h | hr | hrs | hour | hours |
days | d | day | days |
months | mon | month | months |
subseconds:
| us | ms | cs | ds |
Examples
1. Specify the span in the stats command
The following example counts the values in the action
field and organizes the results into 5 minute time spans.
...| stats count(action) AS count BY _time span=5min
2. Specify the span in the GROUP BY clause of the from command
The following example returns the count of the values in the error
field in 30 second intervals. In this example the <time> parameter is specified, the _time
field is used.
FROM <dataset> GROUP BY span(_time, 30sec) SELECT count(error), _time
3. Specify a time field other than _time
The following example returns the count of the values in the bytes
field in 1 hour intervals based on the values in the starttime
field:
FROM <dataset> GROUP BY span(starttime, 1hr) SELECT sum(bytes), starttime
sparkline(<aggregation>, <span-length>)
This function generates time-based trendline charts in the search results.
Usage
You can use this function with the stats or streamstats commands.
To use the sparkline function, you must specify an aggregate function, like count
or sum
. Include the name of a field with numeric values or a numeric literal with the aggregate function. With the count
aggregate function, you can omit the field name or literal to count the events.
The supported aggregate functions are:
- avg
- count
- dc
- max
- mean
- min
- range
- stdev
- stdevp
- sum
- sumsq
- var
- varp
The <span-length> parameter determines the set of events that fall into each particular time range when calculating the aggregate values in the chart. The <span-length> consists of two parts, an integer and a time scale. For example, to specify 30 seconds you can use 30s
. If not specified, a <span-length> is chosen based on the time range of the search.
Examples
1. Sparkline charts that summarize data
The following search generates sparkline charts that display a sum of the bytes
field values in 1 day increments. The sparkline charts are organized by the values in the host
field.
...| stats sparkline(sum(bytes), 1d) by host
2. Sparkline charts that count events
The following search generates sparkline charts that display a count of the events for each status
field value in 30 minute increments. The sparkline charts are organized by the values in the status
field, which holds HTTP status values like 200 and 404.
... | stats sparkline(count(), 30m) by status
See also
- Function information
- Overview of SPL2 stats and chart functions
- Quick Reference for SPL2 Stats and Charting Functions
- Naming function arguments in the SPL2 Search Manual