How to compare 4 fields against multiple ranges and sum by number of sources?

[mshumate]

I have four utilization fields (with 30 days worth of averages). Fields are inbound_avg_util, inbound_max_util, outbound_avg_util, and outbound_max_util. Averaging these four fields against a range of 0%-19%, 20%-39%, 40%-59%, 60%-79%, 80+%, into four separate charts by count of source. Someone has presented me an excel worksheet as an example asking me to reproduce in Splunk, and for my love of Splunk, I said sure I can do that. Well a week later I'm still trying to figure out the best solution. I've tried using eval to create the ranges, tried rangemap will little luck on one field only. I'm stepping away from it for a bit and collect my thoughts and rethink it, but in the meantime, I thought I would try the forum for the first time.

[mshumate · Accepted solution]

ah! Thank you Thank you!

Last night I had came up with something similar to the above but without stats and didn't produce the results as yours did. I took what I had and combined it with yours and now have the following:

sourcetype=xxx_xxxx | eval inbound_avg_util = round(inbound_avg_util,2) | eval inbound_max_util = round(inbound_max_util,2)
| eval outbound_avg_util = round(outbound_avg_util,2)| eval outbound_max_util = round(outbound_max_util,2) |
stats avg(inbound_avg_util) AS A avg(inbound_max_util) AS B avg(outbound_avg_util) AS C avg(outbound_max_util) AS D by source
| eval range1=case(A >= 0 AND A <= 19, "0%-19%", A > 20 AND A <= 39,"20%-39%", A > 39 AND A <= 59, "40%-59%", A > 60 AND A <= 79, "60%-79%", A > 80, "80+%" )
| eval range2=case(B >= 0 AND B <= 19, "0%-19%", B > 20 AND B <= 39,"20%-39%", B > 39 AND B <= 59, "40%-59%", B > 60 AND B <= 79, "60%-79%", B > 80, "80+%" )
| eval range3=case(C >= 0 AND C <= 19, "0%-19%", C > 20 AND C <= 39,"20%-39%", C > 39 AND C <= 59, "40%-59%", C > 60 AND C <= 79, "60%-79%", C > 80, "80+%" )
| eval range4=case(D >= 0 AND D <= 19, "0%-19%", D > 20 AND D <= 39,"20%-39%", D > 39 AND D <= 59, "40%-59%", D > 60 AND D <= 79, "60%-79%", D > 80, "80+%" )

It's now producing the charts (stacked) by the source. Alot of sources (35).

Now I'm trying to figure out how to narrow down the charts (stacked) only on the ranges with four stacked charts. I thought there was a way to group field names into a new field using eval and/or rex and figured I could use the newly created field to chart on. Again I head down that path to eventually become confused. So time to step a way again and rethink and read some more. I'm thinking now this may require subsearches?? am I wrong or is there a better solution?

Basically the end results i'm shooting for would be four charts (stacked) inbound_avg_util, inbound_max_util, outbound_avg_util, outbound_max_util with the range results stacked. I know there's a way, just finding the right solution is the journey with Splunk.

Thanks again

[mshumate]

Still working for resolution (@woodcock) - How would I be able to chart / count source by each range and display in stacked charts separated by each range. Following the search above if I append "| stats count(source) by range1 " gives me half of what I need (results aren't stacked). I've tried append appendcols and join, with no luck. The end results need to be stacked charts of source count for each range, separated by range1 range2 range3 range4.

[woodcock]

I think this should do it but if not, you really should ask a new question:

sourcetype=xxx_xxxx | eval inbound_avg_util = round(inbound_avg_util,2) | eval inbound_max_util = round(inbound_max_util,2) | eval outbound_avg_util = round(outbound_avg_util,2)| eval outbound_max_util = round(outbound_max_util,2) | stats avg(inbound_avg_util) AS A avg(inbound_max_util) AS B avg(outbound_avg_util) AS C avg(outbound_max_util) AS D by source | eval range1=case(A >= 0 AND A <= 19, "0%-19%", A > 20 AND A <= 39,"20%-39%", A > 39 AND A <= 59, "40%-59%", A > 60 AND A <= 79, "60%-79%", A > 80, "80+%" )  | eval range2=case(B >= 0 AND B <= 19, "0%-19%", B > 20 AND B <= 39,"20%-39%", B > 39 AND B <= 59, "40%-59%", B > 60 AND B <= 79, "60%-79%", B > 80, "80+%" ) | eval range3=case(C >= 0 AND C <= 19, "0%-19%", C > 20 AND C <= 39,"20%-39%", C > 39 AND C <= 59, "40%-59%", C > 60 AND C <= 79, "60%-79%", C > 80, "80+%" ) | eval range4=case(D >= 0 AND D <= 19, "0%-19%", D > 20 AND D <= 39,"20%-39%", D > 39 AND D <= 59, "40%-59%", D > 60 AND D <= 79, "60%-79%", D > 80, "80+%" ) | chart dc(source) by range

[woodcock]

I am not sure I understand what you are trying to do. Go ahead and "Accept" this answer and start over with a new question. If you include @woodcock somewhere in it, I will see it and respond.

[woodcock]

Something like this will work:

... | stats avg(inbound_avg_util) AS A avg(inbound_max_util) AS B avg(outbound_avg_util) AS C avg(outbound_max_util) AS D by source 
| eval Arange=case(A<20, "0%-19%", A< 40, "20%-39%", A<60, "40%=59%", A<80, "60%-79%", "80+%" )
| eval Brange=case(B<20, "0%-19%", B< 40, "20%-39%", B<60, "40%=59%", B<80, "60%-79%", "80+%" )
| eval Crange=case(C<20, "0%-19%", C< 40, "20%-39%", C<60, "40%=59%", C<80, "60%-79%", "80+%" )
| eval Drange=case(D<20, "0%-19%", D< 40, "20%-39%", D<60, "40%=59%", D<80, "60%-79%", "80+%" )