How to edit my search to add extra columns to my chart showing the average and max indexed volume per host?

[hcheang]

Hello,

I've been using the query provided at http://wiki.splunk.com/Community:TroubleshootingIndexedDataVolume to get the indexed volume by host and I modified it a little to see the pattern - such as log stoppage.

My query is

index=_internal group="per_host_thruput" earliest=-3d@d latest=@d| chart eval(round(sum(kb), 2)/1024) over series by date_mday 

which works fine but I would like to add another field (column to the chart) using eval or eventstats command such as finding the average (avg) or peak volume (max). If I was gathering this information by stats command then it would be easy but the problem is that I need to have at least 3 days of time range to see the pattern. Yes, I can use timechart which works but I have over 200 devices which cannot fit in a row (and this must be shown in a dashboard panel).

Currently, my query looks like this

series         26         27         28
10.0.0.1       50         48         24
10.0.0.2       4          8          2
10.0.0.3                  1          1
...

which I would like to have this

series         26         27         28          Description(avg)      Description(max)      Description
10.0.0.1       50         48         24          xx                    xx
10.0.0.2       4          8          2           x                     x
10.0.0.3                  1          1           x                     x                     Alert
...

Can this be done? or can it be done using stats command? The closest I got with stats command is this

index=_internal group="per_host_thruput" earliest=-3d@d latest=@d| eval D1 = max(date_mday) | eval D2 = D1-1 | eval D3 = D2-1 |stats sum(kb) by series D1 D2 D3

but it's not giving sum(kb) by date.

[jayannah]

Here you go..

                          index=_internal group="per_host_thruput" earliest=-3d@d latest=@d| streamstats max(date_mday) as D1 | eval D2 = D1-1 | eval D3 = D2-1 |stats sum(kb) by series D1 D2 D3

                         Output is
                         series D1  D2  D3  sum(kb)
                         cc-index01 28  27  26  78107.664978
                         cc-index02 28  27  26  78186.546889
                         cc-index03 28  27  26  78157.350569
                         cc-index04 28  27  26  78496.797853
                         cc-licmgr  28  27  26  52702.763749
                         cc-sh1a    28  27  26  45635.153433
                         cc-sh2a    28  27  26  44189.015641
                         cc-sh3a    28  27  26  44197.826188

you can use streamstats or eventstats commands to take the max value of given parameter.

if you want total, avg, max & min of volume injection by each host on daily basis, then use the below query

                   index=_internal group="per_host_thruput" |  bucket _time span=1d |eval MB=kb/1024 |eventstats sum(MB) as daily_volMB by series | timechart span=1d sum(daily_volMB) as daily_volMB , max(daily_volMB) as max_daily_volMB avg(daily_volMB) as avg_daily_volMB, min(daily_volMB) as min_daily_volMB  by series

As you have 200 hosts and you seems to be looking for each hostname to be in row

               index=_internal group="per_host_thruput" |  bucket _time span=1d |eval MB=kb/1024 |eventstats sum(MB) as daily_volMB by series | stats  sum(daily_volMB) as daily_volMB , max(daily_volMB) as max_daily_volMB avg(daily_volMB) as avg_daily_volMB, min(daily_volMB) as min_daily_volMB  by series _time

               series  _time  daily_volMB   max_daily_volMB  avg_daily_volMB  min_daily_volMB
               myhost   2015-01-05 00:00:00 808.219299412   28.864974979    28.864975   28.864974979
               myhost   2015-01-11 00:00:00 16712.820512841 28.864974979    28.864975   28.864974979
               myhost   2015-01-29 00:00:00 1703.033523761  28.864974979    28.864975   28.864974979

[hcheang]

Thanks but it is the same as the query I put at the last. I need the sum for each day as shown above as well as extra fields (Description 1, 2 etc.)

[jayannah]

You are using " ...| eval D1 = max(date_mday)" to calculate max value which wont work.

Updated the query in the answer to calculate daily sum, avg, max, min of volume injection by each host.

[hcheang]

Thanks again but the whole point of not using timechart is because I have more than 200 hosts (in this case, series). As a result, when the provided query is ran, only the results (avg, max, etc.) of few hosts are shown and the rest seems to be aggregated as OTHERS. Unless there is a way to switch column and row, I need to find better way. The original question is to have another field (column) to already generated chart which is possible for the table generated by stats command. What I meant by adding another field is not limited to avg or max but the field generated by eval command as well which enable us to do all kind of stuff.

[jayannah]

1. useother=null will not club the hosts into OTHERS
2. Basically you need 3 values in the table, i,e _time, count, and stats for each host. So, you want all hosts name as row and not as column. Update the query in the main answer..

Regarding your original question, you can add columns using eval, eventstats, streamstats..etc