Help in refining query to have a threshold

[jaydiare]

Hi Splunk Community,  I need some help with the following query:

(index=* OR index=*) (sourcetype=A OR sourcetype=C OR sourcetype=D) (a_location=* OR b_location=* OR c_location=* OR d_location=*) (a_location!=*S1* OR b_location!=*S1* OR c_location!=*S1* OR d_location!=*S1*) User!=basketball UserGroup!=baseball | eval Interface_card=mvappend(a_location,b_location,c_location,d_location) | mvexpand Interface_card | bin span=1d _time | stats sum(TCDuration) as TCDuration by _time Interface_card | eval TCDuration=TCDuration/1000 | eval Utilization=round(((TCDuration/86400)*100),1) | eval Utilization=if(Utilization >100, 100, Utilization) | fields - TCDuration | timechart eval(round(avg(Utilization),1)) by Interface_card limit=0

1. How can I optimized it

2. how can I filter only Utilization between 0-40 and/or 70-99 or any other limit I want to filter...

Appreciate any help

thank you

[PickleRick · Accepted solution]

1. "index=* OR index=*"? You were generating it somehow? Limit your search to appropriate indexes.

2. You have many "left wildcard" conditions - conditions in which fields start with an asterisk. It is highly inefficient since splunk needs to read whole events.

3. You have a condition matching "a_location=* OR b_location=* OR c_location=* OR d_location=*" which, depending on your data, will most probably match all your events (or at least a very significant subset of them) so it's not very effectiv. Furthermore, next composite condition is (a_location!=*S1* OR b_location!=*S1* OR c_location!=*S1* OR d_location!=*S1*) which will again most probably match most of your events.

4. eval Interface_card=mvappend(a_location,b_location,c_location,d_location) | mvexpand Interface_card - interesting approach but firstly drop the fields you won't be needing later so you lower the memory footprint

5. If you want to filter your results, you can simply add

| search Utilization<A Utilization>B

 or

| where Utilization>C AND Utilization<D

(In this case - if you want to limit by constant values - they will both work. In general case - these are differently working commands and you cannot simply substitute one for another)

[ITWhisperer]

There are a couple of things

(a_location!=*S1* OR b_location!=*S1* OR c_location!=*S1* OR d_location!=*S1*) might not be doing what you think it is - when using inequalities you might want to change the ORs to ANDs

the mvexpand interface_card is unnecessary because the by clause on the stats command effectively does that