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!

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