Splunk Search

How to show empty bins on my chart?

pduflot
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!

1 Solution

pduflot
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

View solution in original post

0 Karma

pduflot
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
0 Karma

DMohn
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

jeffland
SplunkTrust
SplunkTrust

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

0 Karma

pduflot
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?

0 Karma

DMohn
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.

0 Karma

pduflot
Path Finder

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

0 Karma

DMohn
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.

0 Karma

pduflot
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.

0 Karma

jeffland
SplunkTrust
SplunkTrust

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

0 Karma

jeffland
SplunkTrust
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:
alt text

Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...