Splunk Search
Highlighted

How to filter as long as one of fields meets the filtering condition?

Communicator

Hi all, I'm trying to sort few rows out of the .csv file as long as one of the fields OverallAvgNetworkMOS, Stream_1_PacketLossRate, Stream_2_PacketLossRate, Stream_1_RoundTrip, Stream_2_RoundTrip, Stream_1_JitterInterArrival, Stream_2_JitterInterArrival meets the filtering condition and then display these rows in the table. Now my searching string is like this:

...| sort (OverallAvgNetworkMOS < 3.5 AND isnotnull(OverallAvgNetworkMOS)) OR (Stream_1_PacketLossRate> 0.1 AND isnotnull(Stream_1_PacketLossRate)) OR  (Stream_2_PacketLossRate>0.1 AND isnotnull(Stream_2_PacketLossRate)) OR (Stream_1_RoundTrip>500 AND isnotnull(Stream_1_RoundTrip)) OR (Stream_2_RoundTrip>500 AND isnotnull(Stream_2_RoundTrip)) OR (Stream_1_JitterInterArrival>30 AND isnotnull(Stream_1_JitterInterArrival)) OR (Stream_2_JitterInterArrival>30 AND isnotnull(Stream_2_JitterInterArrival)  

*****Display in table
    table StartTime, EndTime, MediaTypesDescription, FromUri, ToUri, FromIPAddr, ToIPAddr, Stream_1_PacketLossRate, Stream_1_RoundTrip, Stream_1_JitterInterArrival, Stream_2_PacketLossRate, Stream_2_RoundTrip, Stream_2_JitterInterArrival, OverallAvgNetworkMOS

But now it will display all the rows instead of the ones I'm trying to filter. Does anyone know what's wrong with this search string? Thanks!

0 Karma
Highlighted

Re: How to filter as long as one of fields meets the filtering condition?

SplunkTrust
SplunkTrust

Hi @dannili ,

Your command starts with sort . Hope its a typo instead of search 🙂

0 Karma
Highlighted

Re: How to filter as long as one of fields meets the filtering condition?

Influencer

You misunderstand the sort command. Sort will not filter rows out, it orders rows.

If your events contain all these fields just include this in your initial search

(OverallAvgNetworkMOS<3.5 Stream_1_PacketLossRate>0.1 Stream_2_PacketLossRate>0.1 Stream_1_RoundTrip>500 Stream_2_RoundTrip>500 Stream_1_JitterInterArrival>30 Stream_2_JitterInterArrival>30 )

If each event does not contain each field its a little trickier. Can you provide some sample events?

View solution in original post

0 Karma
Highlighted

Re: How to filter as long as one of fields meets the filtering condition?

Splunk Employee
Splunk Employee

This is where you will want to use the 'where' command:
http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/Where

Inequalities (such as '<' and '>') cannot be used with the 'search' command, but the can be used with 'where'

<inital_search> | where (OverallAvgNetworkMOS < 3.5 AND isnotnull(OverallAvgNetworkMOS)) 
OR (Stream_1_PacketLossRate> 0.1 AND isnotnull(Stream_1_PacketLossRate)) 
OR  (Stream_2_PacketLossRate>0.1 AND isnotnull(Stream_2_PacketLossRate)) 
OR (Stream_1_RoundTrip>500 AND isnotnull(Stream_1_RoundTrip)) 
OR (Stream_2_RoundTrip>500 AND isnotnull(Stream_2_RoundTrip)) 
OR (Stream_1_JitterInterArrival>30 AND isnotnull(Stream_1_JitterInterArrival)) 
OR (Stream_2_JitterInterArrival>30 AND isnotnull(Stream_2_JitterInterArrival)  
| table tartTime, EndTime, MediaTypesDescription, FromUri, ToUri, FromIPAddr, ToIPAddr, Stream_1_PacketLossRate, Stream_1_RoundTrip, Stream_1_JitterInterArrival, Stream_2_PacketLossRate, Stream_2_RoundTrip, Stream_2_JitterInterArrival, OverallAvgNetworkMOS

One thing to point out, that is an awful lot of 'OR' statements. Be sure the statement that is most likely to evaluate as true first. Also, I don't believe you need the isnotnull statements. For example, if Stream2JitterInterArrival is greater than 30, how can the value be null? Test it out and see what you think.

0 Karma