Other Usage

Help in refining query to have a threshold

jaydiare
Explorer

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

 

Labels (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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)

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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

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

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...