Splunk Search

Difference between stats and chart

Katsche
Path Finder

Hi all,

I have been working with Splunk for quite a while now. Still I am wondering:

Whatis the difference between chart and stats? Most of the time I find myself getting the same results no matter which function I am using...

Kind regards,
Katsche

Tags (3)
1 Solution

sideview
SplunkTrust
SplunkTrust

Let's compare with two examples:

  1. * | stats sum(x) by user, host, status will output rows that look like:

        user       host      status    sum(x) 
        ---------------------------------------
        bob        host1     200       25
        bob        host1     404       12
        bob        host2     404        3
        alice      host1     200       17
        alice      host2     500        1
    

2) But * | chart sum(x) over user by status will output quite different rows that look like.

        user       200       404      500
        ---------------------------------------
        bob         25        15         
        alice       17                  1

Note that the first example incorporates data about the "host" field, whereas the second one does not. We'll come back to this.

In more formal terms, stats sum(x) by user, host, status will create one row for each combination of user, host and status that are present in the data. Then for each of those rows it will also compute whatever statistic(s) or function(s) you tell it (here it's just sum(x)).

On the other hand, the chart command, will create rows that are each of the values of the single "group by" field, and COLUMNS that are each of the values of the "split by" field. (btw the timechart command you can sort of think of chart that is locked into using _time as the "group-by" field, although the reality is a little more complex)

Some Interesting Upshots

  1. Note that you can specify any number of "group by" fields to the stats command, whereas the chart/timechart command can only have one "group by" (with timechart it is always _time) and one "split by". This is why our first example was able to incorporate the "host" field easily whereas the second example did not.

  2. This creates a concept of a "stats style" result set, versus a "chart style" result set. I say "style" because I mean it looks like the output of the given command, even if it didn't necessarily come from that command. ie |inputlookup foo might well emerge blinking into the light of your browser and be a "chart style" set. This has some implications that you get used to, like "filling in last known values" in a stats-style set is generally done with the streamstats command, whereas doing the thing with chart-style results is more often done with the filldown command.

  3. The stats command will throw away any events where one or more of the "group" by fields does not exist. If you want it to keep them, you have to use an explicit fillnull command. The chart/timechart commands will likewise throw away events where the single "group by" field doesn't exist, but it will actually roll up all the null values of the "split by" field into a big column called "NULL" which you can fiddle with and/or suppress with various arguments.

  4. You can always transform your results from a "stats style" result set to the "chart style" with the xyseries command. eg xyseries foo bar baz, or if you will xyseries groupByField splitByField computedStatistic.

  5. Going the other way, you can transform your results from a "chart style" result set to the "stats style" with the untable command. eg | untable foo bar baz, or labeling the fields, | untable groupByField splitByField computedStatistic.

  6. Following from this, | xyseries foo bar baz | untable foo bar baz negates itself and so is a fun way to do nothing at all. 😃

  7. As you might guess from the runaway bullet points here, this is a deep topic. Not uncommonly a single search might start out doing things in one style, because it needs to use eval in a certain way, and then switch it all over to the other style because it needs to do some other thing that needs "chart-style" rows.

Other things that are a little confusing.

-- You can also use chart command with no split-by field specified at all, and in such cases it behaves identically to the stats command. eg stats count by foo is exactly the same as chart count over foo. So some people think of "chart" as being an alias to "stats" when actually it's quite important and does things nothing else can.

-- The chart command also allows you to express it as chart count by foo, bar which looks a lot like the stats syntax. HOWEVER, chart recognizes the first field foo as the "group by" field, thus becoming the output rows, and the second field is recognized as the "split by" field, becoming the column names across the top. To avoid this confusion I recommend avoiding the chart count by foo bar syntax entirely, and instead try and do chart count over foo by bar. It's a bit more verbose but it will help new users avoid this confusion. (random trivia: it was actually me that lobbied for the "over" syntax as a result of which it got snuck into a 4.X release)

View solution in original post

sideview
SplunkTrust
SplunkTrust

Let's compare with two examples:

  1. * | stats sum(x) by user, host, status will output rows that look like:

        user       host      status    sum(x) 
        ---------------------------------------
        bob        host1     200       25
        bob        host1     404       12
        bob        host2     404        3
        alice      host1     200       17
        alice      host2     500        1
    

2) But * | chart sum(x) over user by status will output quite different rows that look like.

        user       200       404      500
        ---------------------------------------
        bob         25        15         
        alice       17                  1

Note that the first example incorporates data about the "host" field, whereas the second one does not. We'll come back to this.

In more formal terms, stats sum(x) by user, host, status will create one row for each combination of user, host and status that are present in the data. Then for each of those rows it will also compute whatever statistic(s) or function(s) you tell it (here it's just sum(x)).

On the other hand, the chart command, will create rows that are each of the values of the single "group by" field, and COLUMNS that are each of the values of the "split by" field. (btw the timechart command you can sort of think of chart that is locked into using _time as the "group-by" field, although the reality is a little more complex)

Some Interesting Upshots

  1. Note that you can specify any number of "group by" fields to the stats command, whereas the chart/timechart command can only have one "group by" (with timechart it is always _time) and one "split by". This is why our first example was able to incorporate the "host" field easily whereas the second example did not.

  2. This creates a concept of a "stats style" result set, versus a "chart style" result set. I say "style" because I mean it looks like the output of the given command, even if it didn't necessarily come from that command. ie |inputlookup foo might well emerge blinking into the light of your browser and be a "chart style" set. This has some implications that you get used to, like "filling in last known values" in a stats-style set is generally done with the streamstats command, whereas doing the thing with chart-style results is more often done with the filldown command.

  3. The stats command will throw away any events where one or more of the "group" by fields does not exist. If you want it to keep them, you have to use an explicit fillnull command. The chart/timechart commands will likewise throw away events where the single "group by" field doesn't exist, but it will actually roll up all the null values of the "split by" field into a big column called "NULL" which you can fiddle with and/or suppress with various arguments.

  4. You can always transform your results from a "stats style" result set to the "chart style" with the xyseries command. eg xyseries foo bar baz, or if you will xyseries groupByField splitByField computedStatistic.

  5. Going the other way, you can transform your results from a "chart style" result set to the "stats style" with the untable command. eg | untable foo bar baz, or labeling the fields, | untable groupByField splitByField computedStatistic.

  6. Following from this, | xyseries foo bar baz | untable foo bar baz negates itself and so is a fun way to do nothing at all. 😃

  7. As you might guess from the runaway bullet points here, this is a deep topic. Not uncommonly a single search might start out doing things in one style, because it needs to use eval in a certain way, and then switch it all over to the other style because it needs to do some other thing that needs "chart-style" rows.

Other things that are a little confusing.

-- You can also use chart command with no split-by field specified at all, and in such cases it behaves identically to the stats command. eg stats count by foo is exactly the same as chart count over foo. So some people think of "chart" as being an alias to "stats" when actually it's quite important and does things nothing else can.

-- The chart command also allows you to express it as chart count by foo, bar which looks a lot like the stats syntax. HOWEVER, chart recognizes the first field foo as the "group by" field, thus becoming the output rows, and the second field is recognized as the "split by" field, becoming the column names across the top. To avoid this confusion I recommend avoiding the chart count by foo bar syntax entirely, and instead try and do chart count over foo by bar. It's a bit more verbose but it will help new users avoid this confusion. (random trivia: it was actually me that lobbied for the "over" syntax as a result of which it got snuck into a 4.X release)

sundarrajan
Path Finder

Is there any performance related issues that may arise while using chart function over stats function? I mean which one takes a longer time to execute in a complex search query (with multiple appends, yet searched at index level).

0 Karma

bhawkins1
Communicator

This should be the accepted answer, and is worth reading.

0 Karma

meenal901
Communicator

When using split-by clause in chart command, the output would be a table with distinct values of the split-by field. Whereas in stats command, all of the split-by field would be included (even duplicate ones). For e.g.

index=_internal | stats count by date_hour,sourcetype

index=_internal | chart count over sourcetype by date_hour

Will give you different output because of "by" field. Not because of over 🙂

Ayn
Legend

I don't claim to know the full truth here either, but you can see how they commands differ when generating statistics split by two fields. stats will stack the values of field2 after each other whereas chart will generate a matrix with one column for each value of field2.

Have a look at this search for example:

index=_internal | chart count by host,sourcetype

vs

index=_internal | stats count by host,sourcetype

kristian_kolb
Ultra Champion

I think that the chart ... by ... rearranges to sort the results by the by-field(s) so-to-speak. /k

0 Karma

rakesh_498115
Motivator

can i sort the count field with the chart command..tried like this but didnt worked

index=_internal | stats count by host,sourcetype | sort -count | chart count by host,sourcetyp

0 Karma

Lowell
Super Champion

Good question 😉 Normally, I use chart if I'm looking for a visual display, and the stats search command for anything else. But they do seem fairly interchangeably (certainly all the stats-related command share common functions, which is nice.) Perhaps there's a legacy reason for having both. I'm looking forward to an informed answer.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...