Thestats,chart, andtimechartcommands are great commands to know (especiallystats). When I first started learning about the Splunk search commands, I found it challenging to understand the benefits of each command, especially how the BY clause impacts the output of a search. It wasn't until I did a comparison of the output (with some trial and a whole lotta error) that I was able to understand the differences between the commands.
These three commands are transforming commands. A transforming command takes your event data and converts it into an organized results table. You can use these three commands to calculate statistics, such as count, sum, and average.
Note:The BY keyword is shown in these examples and in the Splunk documentation in uppercase for readability. You can use uppercase or lowercase in your searches when you specify the BY keyword.
The Stats Command Results Table
Let's start with thestatscommand. We are going to count the number of events for each HTTP status code.
... | stats count BY status
The count of the events for each unique status code is listed in separate rows in a table on the Statistics tab:
status | count |
---|---|
200 | 34282 |
400 | 701 |
403 | 228 |
404 | 690 |
Basically the field values (200, 400, 403, 404) become row labels in the results table.
For thestatscommand, fields that you specify in the BY clause group the results based on those fields. For example, we receive events from three different hosts: www1, www2, and www3. If we add thehostfield to our BY clause, the results are broken out into more distinct groups.
... | stats count BY status, host
Each unique combination of status and host is listed on aseparaterowin the results table.
status | host | count |
---|---|---|
200 | www1 | 11835 |
200 | www2 | 11186 |
200 | www3 | 11261 |
400 | www1 | 233 |
400 | www2 | 257 |
400 | www3 | 211 |
403 | www2 | 228 |
404 | www1 | 244 |
404 | www2 | 209 |
404 | www3 | 237 |
Each field you specify in the BY clause becomes a separate column in the results table. You're splitting the rows first on status, then on host. The fields that you specify in the BY clause of thestatscommand are referred to as <row-split> fields.
In this example, there are fiveactionsthat customers can take on our website: addtocart, changequantity, purchase, remove, and view.
Let's add action to the search.
... | stats count BY status, host, action
You are splitting the rows first on status, then on host, and then on action. Below is a partial list of the results table that is produced when we add theactionfield to the BYclause:
status | host | action | count |
---|---|---|---|
200 | www1 | addtocart | 1837 |
200 | www1 | changequantity | 428 |
200 | www1 | purchase | 1860 |
200 | www1 | remove | 432 |
200 | www1 | view | 1523 |
200 | www2 | addtocart | 1743 |
200 | www2 | changequantity | 365 |
200 | www2 | purchase | 1742 |
One big advantage of using thestatscommand is that you can specify more than two fields in the BY clause and create results tables that show very granular statistical calculations.
Chart Command Results Table
Using the same basic search, let's compare the results produced by thechartcommand with the results produced by thestatscommand.
If you specify only one BY field, the results from thestatsandchartcommands are identical. Using thechartcommand in the search with two BY fields is where you really see differences.
Remember the results returned when we used the stats command with two BY fields are:
status | host | count |
---|---|---|
200 | www1 | 11835 |
200 | www2 | 11186 |
200 | www3 | 11261 |
400 | www1 | 233 |
400 | www2 | 257 |
400 | www3 | 211 |
403 | www2 | 228 |
404 | www1 | 244 |
404 | www2 | 209 |
404 | www3 | 237 |
Now let's substitute thechartcommand for thestatscommand in the search.
... | chart count BY status, host
The search returns the following results:
status | www1 | www2 | www3 |
---|---|---|---|
200 | 11835 | 11186 | 11261 |
400 | 233 | 257 | 211 |
403 | 0 | 288 | 0 |
404 | 244 | 209 | 237 |
Thechartcommand uses the first BY field,status, to group the results. For each unique value in thestatusfield, the results appear on a separate row. This first BY field is referred to as the <row-split>field. Thechartcommand uses the second BY field,host, to split the results into separate columns. This second BY field is referred to as the <column-split> field. The values for thehostfield become the column labels.
Notice the results for the 403 status code in both results tables. With thestatscommand, there are no results forthe 403 status code andthe www1 and www3 hosts. With thechartcommand, when there are no events for the <column-split> field that contain the value for the <row-split> field, a 0 is returned.
One important difference between thestatsandchartcommands is how many fields you can specify in the BY clause.
With thestatscommand, you can specify a list of fields in the BY clause, all of which are <row-split> fields. The syntax for thestatscommand BY clause is:
BY <field-list>
For thechartcommand, you can specify at most two fields. One <row-split> field and one <column-split> field.
Thechartcommand provides two alternative ways to specify these fields in the BY clause. For example:
... | chart count BY status, host
... | chart count OVER status BY host
The syntax for thechartcommand BY clause is:
[ BY <row-split> <column-split> ] | [ OVER <row-split> ] [BY <column-split>] ]
The advantage of using thechartcommand is that it creates a consolidated results table that is better for creating charts. Let me show you what I mean.
Stats and Chart Command Visualizations
When you run the stats and chart commands, the event data is transformed into results tables that appear on the Statistics tab. Click theVisualizationtab to generate a graph from the results. Here is the visualization for thestatscommand results table:
Thestatusfield forms the X-axis, and thehostandcountfields form the data series. The range of count values form the Y-axis.
There are several problems with this chart:
- There are multiple values for the same status code on the X-axis.
- Thehostvalues (www1, www2, and www3) are string values and cannot be measured in the chart. Thehostshows up in the legend, but there are no blue columns in the chart.
Because of these issues, the chart is confusing and does not convey the information that is in the results table.
While youcancreate a usable visualization from thestatscommand results table, the visualization is useful only when you specify one BY clause field.
It's better to use thechartcommand when you want to create a visualization using two BY clause fields:
Thestatusfield forms the X-axis and thehostvalues form the data series. The range of count values form the Y-axis.
What About the Timechart Command?
When you use thetimechartcommand, the results table is always grouped by the event timestamp (the_timefield). The time value is the <row-split> for the results table. So in the BY clause, you specify only one field, the <column-split> field. For example, this search generates a count and specifies the status field as the<column-split> field:
... | timechart count BY status
This search produces this results table:
_time | 200 | 400 | 403 | 404 |
---|---|---|---|---|
2018-07-05 | 1038 | 27 | 7 | 19 |
2018-07-06 | 4981 | 111 | 35 | 98 |
2018-07-07 | 5123 | 99 | 45 | 105 |
2018-07-08 | 5016 | 112 | 22 | 105 |
2018-07-09 | 4732 | 86 | 34 | 84 |
2018-07-10 | 4791 | 102 | 23 | 107 |
2018-07-11 | 4783 | 85 | 39 | 98 |
2018-07-12 | 3818 | 79 | 23 | 74 |
If you search by thehostfield instead, this results table is produced:
_time | www1 | www2 | www3 |
---|---|---|---|
2018-07-05 | 372 | 429 | 419 |
2018-07-06 | 2111 | 1837 | 1836 |
2018-07-07 | 1887 | 2046 | 1935 |
2018-07-08 | 1927 | 1869 | 2005 |
2018-07-09 | 1937 | 1654 | 1792 |
2018-07-10 | 1980 | 1832 | 1733 |
2018-07-11 | 1855 | 1847 | 1836 |
2018-07-12 | 1559 | 1398 | 1436 |
The time increments that you see in the_timecolumn are based on the search time range or the arguments that you specify with thetimechartcommand. In the previous examples the time range was set toAll timeand there are only a few weeks of data. Because we didn't specify aspan, a default time span is used. In this situation, the default span is 1 day.
If you specify a time range likeLast 24 hours, the default time span is 30 minutes. TheUsagesection in the timechart documentation specifies the default time spans for the most common time ranges. This results table shows the default time span of 30 minutes:
_time | www1 | www2 | www3 |
---|---|---|---|
2018-07-12 15:00:00 | 44 | 22 | 73 |
2018-07-12 15:30:00 | 34 | 53 | 31 |
2018-07-12 16:00:00 | 14 | 33 | 36 |
2018-07-12 16:30:00 | 46 | 21 | 54 |
2018-07-12 17:00:00 | 75 | 26 | 38 |
2018-07-12 17:30:00 | 38 | 51 | 14 |
2018-07-12 18:00:00 | 62 | 24 | 15 |
Thetimechartcommand includes several options that are not available with the stats and chart commands. For example, you can specify a time span like we have in this search:
... | timechart span=12h count BY host
_time | www1 | www2 | www3 |
---|---|---|---|
2018-07-04 17:00 | 801 | 783 | 819 |
2018-07-05 05:00 | 795 | 847 | 723 |
2018-07-05 17:00 | 1926 | 1661 | 1642 |
2018-07-06 05:00 | 1501 | 1774 | 1542 |
2018-07-06 17:00 | 2033 | 1909 | 1857 |
2018-07-07 05:00 | 1482 | 1671 | 1594 |
2018-07-07 17:00 | 2027 | 1818 | 2036 |
In this example, the 12-hour increments in the results table are based on when you run the search (local time) and how that aligns that with UNIX time (sometimes referred to as epoch time).
Note: There are other options you can specify with thetimechartcommand, which we'll explore in a separate blog.
So how do these results appear in a chart? On theVisualizationtab, you see that_timeforms the X-axis. The axis marks the Midnight and Noon values for each date. However, the columns that represent the data start at 1700 each day and end at 0500 the next day.
The field specified in the BY clause forms the data series. The range of count values forms the Y-axis.
In Summary
The stats, chart, and timechart commands have some similarities, but you’ve got to pay attention to the BY clauses that you use with them.
- Usethe stats command when you want to create results tables that show granular statistical calculations.
- Use the stats command when you want to specify 3 or more fields in the BY clause.
- Use the chart command when you want to create results tables that show consolidated and summarized calculations.
- Use the chart command to create visualizations from the results table data.
- Use the timechart command to create results tables and charts that are based on time.
References
Other blogs:
- Search Command> stats, eventstats and streamstats
Splunk documentation:
- Stats command:https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Stats
- Chart command:https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Chart
- Timechart command:https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Timechart
Splunk
The world’s leading organizations trustSplunkto help keep their digital systems secure and reliable. Our software solutions and services help to prevent major issues, absorb shocks and accelerate transformation. Learnwhat Splunk doesandwhy customers choose Splunk.