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!

#### Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

#### Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

#### We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...