How to show empty bins on my chart?

[pduflot]

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!

[pduflot · Accepted solution]

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

[DMohn]

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

[jeffland]

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

[pduflot]

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?

[DMohn]

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.

[pduflot]

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

[DMohn]

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.

[pduflot]

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

[jeffland]

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

[jeffland]

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: