Splunk Search

## How to show empty bins on my chart?

Path Finder

Hello,

I have my data between -1 and 1 and I want to show a distribution graph. I would like that the X axis is always 20 bins of size 0.1.
I tried to used the bin command, but whenever all the data lies between 0.1 and 0.2 for example, one single bin is displayed on the graph. I would like that empty bins are shown so that at a glance you can see if you are more distributed above 0 than below 0. Is this possible?

Here is what I have tried:

``````... | bin FAVG_ERR_DOSE_PRIM start=-1.0 end=1.0 bins=20 span=0.1 | chart count(FIELD_ID) by FAVG_ERR_DOSE_PRIM
``````

See postimg.org/image/swm3z7ddd/

Thanks!

Tags (3)
1 Solution
Path Finder

Finally, the only way I could make this work as I wanted was to compute myself the total in each bin:

``````...
| stats
sum(eval(if(NERR_DOSE_PRIM<-1.2,1,0))) AS NERR_DOSE_PRIM_LT012
, sum(eval(if(NERR_DOSE_PRIM>=-1.2 AND NERR_DOSE_PRIM<-1.1,1,0))) AS NERR_DOSE_PRIM_012LT011
, sum(eval(if(NERR_DOSE_PRIM>=-1.1 AND NERR_DOSE_PRIM<-1.0,1,0))) AS NERR_DOSE_PRIM_011LT010
, sum(eval(if(NERR_DOSE_PRIM>=-1.0 AND NERR_DOSE_PRIM<-0.9,1,0))) AS NERR_DOSE_PRIM_010LT009
, sum(eval(if(NERR_DOSE_PRIM>=-0.9 AND NERR_DOSE_PRIM<-0.8,1,0))) AS NERR_DOSE_PRIM_009LT008
, sum(eval(if(NERR_DOSE_PRIM>=-0.8 AND NERR_DOSE_PRIM<-0.7,1,0))) AS NERR_DOSE_PRIM_008LT007
, sum(eval(if(NERR_DOSE_PRIM>=-0.7 AND NERR_DOSE_PRIM<-0.6,1,0))) AS NERR_DOSE_PRIM_007LT006
, sum(eval(if(NERR_DOSE_PRIM>=-0.6 AND NERR_DOSE_PRIM<-0.5,1,0))) AS NERR_DOSE_PRIM_006LT005
, sum(eval(if(NERR_DOSE_PRIM>=-0.5 AND NERR_DOSE_PRIM<-0.4,1,0))) AS NERR_DOSE_PRIM_005LT004
, sum(eval(if(NERR_DOSE_PRIM>=-0.4 AND NERR_DOSE_PRIM<-0.3,1,0))) AS NERR_DOSE_PRIM_004LT003
, sum(eval(if(NERR_DOSE_PRIM>=-0.3 AND NERR_DOSE_PRIM<-0.2,1,0))) AS NERR_DOSE_PRIM_003LT002
, sum(eval(if(NERR_DOSE_PRIM>=-0.2 AND NERR_DOSE_PRIM<-0.1,1,0))) AS NERR_DOSE_PRIM_002LT001
, sum(eval(if(NERR_DOSE_PRIM>=-0.1 AND NERR_DOSE_PRIM<0.00,1,0))) AS NERR_DOSE_PRIM_001LT000
, sum(eval(if(NERR_DOSE_PRIM>=0.0 AND NERR_DOSE_PRIM<0.1,1,0))) AS NERR_DOSE_PRIM_000LT001
, sum(eval(if(NERR_DOSE_PRIM>=0.1 AND NERR_DOSE_PRIM<0.2,1,0))) AS NERR_DOSE_PRIM_001LT002
, sum(eval(if(NERR_DOSE_PRIM>=0.2 AND NERR_DOSE_PRIM<0.3,1,0))) AS NERR_DOSE_PRIM_002LT003
, sum(eval(if(NERR_DOSE_PRIM>=0.3 AND NERR_DOSE_PRIM<0.4,1,0))) AS NERR_DOSE_PRIM_003LT004
, sum(eval(if(NERR_DOSE_PRIM>=0.4 AND NERR_DOSE_PRIM<0.5,1,0))) AS NERR_DOSE_PRIM_004LT005
, sum(eval(if(NERR_DOSE_PRIM>=0.5 AND NERR_DOSE_PRIM<0.6,1,0))) AS NERR_DOSE_PRIM_005LT006
, sum(eval(if(NERR_DOSE_PRIM>=0.6 AND NERR_DOSE_PRIM<0.7,1,0))) AS NERR_DOSE_PRIM_006LT007
, sum(eval(if(NERR_DOSE_PRIM>=0.7 AND NERR_DOSE_PRIM<0.8,1,0))) AS NERR_DOSE_PRIM_007LT008
, sum(eval(if(NERR_DOSE_PRIM>=0.8 AND NERR_DOSE_PRIM<0.9,1,0))) AS NERR_DOSE_PRIM_008LT009
, sum(eval(if(NERR_DOSE_PRIM>=0.9 AND NERR_DOSE_PRIM<1.0,1,0))) AS NERR_DOSE_PRIM_009LT010
, sum(eval(if(NERR_DOSE_PRIM>=1.0 AND NERR_DOSE_PRIM<1.1,1,0))) AS NERR_DOSE_PRIM_010LT011
, sum(eval(if(NERR_DOSE_PRIM>=1.1 AND NERR_DOSE_PRIM<1.2,1,0))) AS NERR_DOSE_PRIM_011LT012
, sum(eval(if(NERR_DOSE_PRIM>=1.2,1,0))) AS NERR_DOSE_PRIM_GT012
``````
Path Finder

Finally, the only way I could make this work as I wanted was to compute myself the total in each bin:

``````...
| stats
sum(eval(if(NERR_DOSE_PRIM<-1.2,1,0))) AS NERR_DOSE_PRIM_LT012
, sum(eval(if(NERR_DOSE_PRIM>=-1.2 AND NERR_DOSE_PRIM<-1.1,1,0))) AS NERR_DOSE_PRIM_012LT011
, sum(eval(if(NERR_DOSE_PRIM>=-1.1 AND NERR_DOSE_PRIM<-1.0,1,0))) AS NERR_DOSE_PRIM_011LT010
, sum(eval(if(NERR_DOSE_PRIM>=-1.0 AND NERR_DOSE_PRIM<-0.9,1,0))) AS NERR_DOSE_PRIM_010LT009
, sum(eval(if(NERR_DOSE_PRIM>=-0.9 AND NERR_DOSE_PRIM<-0.8,1,0))) AS NERR_DOSE_PRIM_009LT008
, sum(eval(if(NERR_DOSE_PRIM>=-0.8 AND NERR_DOSE_PRIM<-0.7,1,0))) AS NERR_DOSE_PRIM_008LT007
, sum(eval(if(NERR_DOSE_PRIM>=-0.7 AND NERR_DOSE_PRIM<-0.6,1,0))) AS NERR_DOSE_PRIM_007LT006
, sum(eval(if(NERR_DOSE_PRIM>=-0.6 AND NERR_DOSE_PRIM<-0.5,1,0))) AS NERR_DOSE_PRIM_006LT005
, sum(eval(if(NERR_DOSE_PRIM>=-0.5 AND NERR_DOSE_PRIM<-0.4,1,0))) AS NERR_DOSE_PRIM_005LT004
, sum(eval(if(NERR_DOSE_PRIM>=-0.4 AND NERR_DOSE_PRIM<-0.3,1,0))) AS NERR_DOSE_PRIM_004LT003
, sum(eval(if(NERR_DOSE_PRIM>=-0.3 AND NERR_DOSE_PRIM<-0.2,1,0))) AS NERR_DOSE_PRIM_003LT002
, sum(eval(if(NERR_DOSE_PRIM>=-0.2 AND NERR_DOSE_PRIM<-0.1,1,0))) AS NERR_DOSE_PRIM_002LT001
, sum(eval(if(NERR_DOSE_PRIM>=-0.1 AND NERR_DOSE_PRIM<0.00,1,0))) AS NERR_DOSE_PRIM_001LT000
, sum(eval(if(NERR_DOSE_PRIM>=0.0 AND NERR_DOSE_PRIM<0.1,1,0))) AS NERR_DOSE_PRIM_000LT001
, sum(eval(if(NERR_DOSE_PRIM>=0.1 AND NERR_DOSE_PRIM<0.2,1,0))) AS NERR_DOSE_PRIM_001LT002
, sum(eval(if(NERR_DOSE_PRIM>=0.2 AND NERR_DOSE_PRIM<0.3,1,0))) AS NERR_DOSE_PRIM_002LT003
, sum(eval(if(NERR_DOSE_PRIM>=0.3 AND NERR_DOSE_PRIM<0.4,1,0))) AS NERR_DOSE_PRIM_003LT004
, sum(eval(if(NERR_DOSE_PRIM>=0.4 AND NERR_DOSE_PRIM<0.5,1,0))) AS NERR_DOSE_PRIM_004LT005
, sum(eval(if(NERR_DOSE_PRIM>=0.5 AND NERR_DOSE_PRIM<0.6,1,0))) AS NERR_DOSE_PRIM_005LT006
, sum(eval(if(NERR_DOSE_PRIM>=0.6 AND NERR_DOSE_PRIM<0.7,1,0))) AS NERR_DOSE_PRIM_006LT007
, sum(eval(if(NERR_DOSE_PRIM>=0.7 AND NERR_DOSE_PRIM<0.8,1,0))) AS NERR_DOSE_PRIM_007LT008
, sum(eval(if(NERR_DOSE_PRIM>=0.8 AND NERR_DOSE_PRIM<0.9,1,0))) AS NERR_DOSE_PRIM_008LT009
, sum(eval(if(NERR_DOSE_PRIM>=0.9 AND NERR_DOSE_PRIM<1.0,1,0))) AS NERR_DOSE_PRIM_009LT010
, sum(eval(if(NERR_DOSE_PRIM>=1.0 AND NERR_DOSE_PRIM<1.1,1,0))) AS NERR_DOSE_PRIM_010LT011
, sum(eval(if(NERR_DOSE_PRIM>=1.1 AND NERR_DOSE_PRIM<1.2,1,0))) AS NERR_DOSE_PRIM_011LT012
, sum(eval(if(NERR_DOSE_PRIM>=1.2,1,0))) AS NERR_DOSE_PRIM_GT012
``````
Motivator

The command `makecontinuous` should be all you need ...

http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Makecontinuous

``````      ... | chart count(FIELD_ID) by FAVG_ERR_DOSE_PRIM | makecontinuous FAVG_ERR_DOSE_PRIM start=-1.0 end=1.0 bins=20
``````
SplunkTrust

Oh, didn't realize you can specify start and end for that. This is even better.

Path Finder

Thanks. Actually this works on example data:

``````index=_internal | HEAD 1 | eval FAVG_ERR_DOSE_PRIM=0.1 | fields FAVG_ERR_DOSE_PRIM | bin FAVG_ERR_DOSE_PRIM span=0.1 start=-1.1 end=1.1 bins=22 | chart count(FIELD_ID) by FAVG_ERR_DOSE_PRIM | makecontinuous FAVG_ERR_DOSE_PRIM span=0.1 start=-1.1 end=1.09
``````

But as soon as the data comes from a stats function, it is buggy. The bins are initially correct but as data flows out of the pipeline the bins are adjusted and the final result is not as expected:

``````index="sca_rs_index2" sourcetype=recordspecif | stats avg(ERR_DOSE_PRIM) AS FAVG_ERR_DOSE_PRIM by FIELD_ID | bin FAVG_ERR_DOSE_PRIM span=0.1 start=-1.1 end=1.1 bins=22 | chart count(FIELD_ID) by FAVG_ERR_DOSE_PRIM | makecontinuous FAVG_ERR_DOSE_PRIM span=0.1 start=-1.1 end=1.09
``````

This leads to two bins with the -0.2:-0.1 label but none on the -0.1:0.0 label.

Is there a way to avoid this?

Motivator

Have you tried issuing the search command without the `bin ....` part? This should not be needed at all, as the bin amount and size will be determined by the `makecontinuous` command. Maybe this causes the error.

Path Finder

This is even worse without the bin command as new bins are created for each value:
See http://postimg.org/image/ilo0d7wi3/

Motivator

Strage, I was not aware of this behavior until now.

Anyway, I have experimented a bit myself and found a solution that could be suitable. You can try adding the ranges to the `chart` command instead:

`````` [...] | stats avg(ERR_DOSE_PRIM) AS FAVG_ERR_DOSE_PRIM by FIELD_ID | chart count(FIELD_ID) over FAVG_ERR_DOSE_PRIM span=0.1 start=-1 end=1
``````

This worked for me - even with "live" data.

Path Finder

Well, it still does not wok in my case, same result as abose. I am on version 6.1.3 if it can matter.

SplunkTrust

This is weird. I don't know why this would happen.

SplunkTrust

This is not exactly a pretty solution, but it works (at least in my preliminary testing).

Change your search to contain this

``````... | append [| stats count | fields - count | eval FAVG_ERR_DOSE_PRIM="-1,1" | makemv delim="," FAVG_ERR_DOSE_PRIM | mvexpand FAVG_ERR_DOSE_PRIM] | bin FAVG_ERR_DOSE_PRIM start=-1.0 end=1.0 bins=20 span=0.1 | chart count(FIELD_ID) by FAVG_ERR_DOSE_PRIM | makecontinuous FAVG_ERR_DOSE_PRIM
``````

This is your search, but with an appended mininum and maximum value (-1 and 1). This in combination with the `makecontinuous` at the end should create a chart to your needs:

Get Updates on the Splunk Community!