Splunk Search

Splunk Newbie: Using buckets/bins

rfernandez2010
New Member

Hi Splunkers,

I can't seem to find a efficient way to bucket my results where anything greater than 174 days gets tossed to one collective bucket.
The table below is the result I want, but what I'm getting are buckets spanning to 10,000 days with each individual count.

index=[index here] source=[source] 
......
|eval days=round((now()-strptime(OPEN_DATE, "%m%d%Y"))/86400)
|bin days span=29
|stats count  by days

days , count

0-29 , 450

29-58 , 411

58-87 , 471

87-116 , 1389

116-145, 5828

145-174 , 806

174+ 30,,000

0 Karma
1 Solution

lguinn2
Legend

I think you may need to use more of the options for the bin command, such as setting the number of bins.
You could also calculate this instead of trying to force bin - bin doesn't like unequal bin sizes... but I recommend the case function, which gives you complete control:

...
| eval days=round((now()-strptime(OPEN_DATE, "%m%d%Y"))/86400)
| eval day_category=case(days < 30, "0-29",
                  days < 59,"30-58",
                  days < 88,"59-87",
                  days < 117,"88-116",
                  days < 146,"117-145",
                  days < 175,"146-174",
                  true(),"174+")
| stats count by date_category

Note a couple of things about the case function: it takes the value corresponding to the first match. So if days is 118, then the day_category will be assigned to "117-145". The true() in the last part of the case function defines a default value, which will be used if no other test matches.

The formatting is just to make it easy to read; you can put the whole case function on a single line; Splunk doesn't care.

Finally, the categories that you defined in your desired output are overlapping. I corrected that in my case statement.

HTH!

View solution in original post

mayurr98
Super Champion

hey you can try something like this

index=<your_index> source=<your_source>
    ...... 
| eval days=round((now()-strptime(OPEN_DATE, "%m%d%Y"))/86400) 
| eval t=days 
| bin days span=29 
| eval days=case(t>=0 AND t<29,"0-29",t>=29 AND t<58,"29-58",t>=58 AND t<87,"58-87",t>=87 AND t<116,"87-116",t>=116 AND t<145,"116-145",t>=145 AND t<174,"145-174",t>=174,"174+") 
| stats count by days 
| sort days

let me know if this helps!

0 Karma

lguinn2
Legend

I think you may need to use more of the options for the bin command, such as setting the number of bins.
You could also calculate this instead of trying to force bin - bin doesn't like unequal bin sizes... but I recommend the case function, which gives you complete control:

...
| eval days=round((now()-strptime(OPEN_DATE, "%m%d%Y"))/86400)
| eval day_category=case(days < 30, "0-29",
                  days < 59,"30-58",
                  days < 88,"59-87",
                  days < 117,"88-116",
                  days < 146,"117-145",
                  days < 175,"146-174",
                  true(),"174+")
| stats count by date_category

Note a couple of things about the case function: it takes the value corresponding to the first match. So if days is 118, then the day_category will be assigned to "117-145". The true() in the last part of the case function defines a default value, which will be used if no other test matches.

The formatting is just to make it easy to read; you can put the whole case function on a single line; Splunk doesn't care.

Finally, the categories that you defined in your desired output are overlapping. I corrected that in my case statement.

HTH!

rfernandez2010
New Member

Awesome, worked like a charm.

Thank you for your help.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...