topic Re: Filter records based on whether any records of a group match a given criteria in Getting Data In

Filter records based on whether any records of a group match a given criteria

landen99 — Mon, 17 Mar 2014 20:19:26 GMT

In general, I am trying to filter records based on whether any records of a group match a given criteria.

Specifically, I want to discard all DNS records from any IP where a DNS record is seen with an R in it (to the left of the Q):
But records from any IP where that look like the following to be discarded:
3/17/2014 2:00:18 PM 06E4 PACKET 000000000F458210 UDP Rcv xx.yyy.zz.48 1234 R Q [0001 D NOERROR] A .server005.mycorp.com.

I used the field extractor tool to put the R and Q values into the field, dns_type.

How do I do a search which filters based on whether all records of a group do not contain specified field values? Will we have to use streamstats or is there an easier way?

Re: Filter records based on whether any records of a group match a given criteria

lguinn2 — Tue, 18 Mar 2014 16:13:41 GMT

What do you mean by "a group"?

Re: Filter records based on whether any records of a group match a given criteria

landen99 — Tue, 18 Mar 2014 16:20:48 GMT

In the dns example, the group is the source ip. So if a source ip generates dns reply traffic then all dns traffic from that ip should be discarded. Records of ip addresses without any dns reply traffic would be kept.

It would probably be more clear to use the word "field" instead of group, but I am grouping these records by source ip in my mind so I am thinking in terms of these source ip groups.

Re: Filter records based on whether any records of a group match a given criteria

lguinn2 — Tue, 18 Mar 2014 16:25:25 GMT

Without more information about how to define "a group", this is sort of a shot in the dark but -

Also, I assume that dns_type could have "R Q" as a value, and that there is a field named ip containing the ip address.

To simply eliminate events with of the dns_type, you could do this

yoursearchhere dns_type!="R Q"

But let's assume that you want to eliminate events from any ip that was associated with an event with a dns_type of "R Q":

yoursearchhere NOT [ search dns_type="R Q" | dedup ip | fields ip ]

This should work as long as the number of ip's returned from the subsearch is relatively small. It will not be a particularly fast search, though, as both the subsearch and the use of NOT will slow things down.

Re: Filter records based on whether any records of a group match a given criteria

landen99 — Mon, 28 Sep 2020 16:10:05 GMT

What do you think about create a new field for each source ip called Query_only? The search would filter out any Query_only=False, and then set it to False if the record contains more than a single "Q" and then filters it out.

| while dns_type_ip!=False | if(dns_type!="Q", eval dns_type_ip="False" by src_ip) | while dns_type_ip!=False

..or maybe streamstats.. I am not completely clear on how it is used and have only seen it used to evaluate the current record in light of evaluations on a previous record.

Re: Filter records based on whether any records of a group match a given criteria

lguinn2 — Wed, 19 Mar 2014 00:04:09 GMT

I would avoid streamstats - I don't think it will be particularly helpful here, and it will probably not be faster than a subsearch.

Re: Filter records based on whether any records of a group match a given criteria

lguinn2 — Wed, 19 Mar 2014 00:06:30 GMT

What happens if you do this?

yoursearchhere NOT [ search dns_type="R Q" | dedup src_ip | fields src_ip ]

Re: Filter records based on whether any records of a group match a given criteria

landen99 — Wed, 19 Mar 2014 13:20:26 GMT

I have the field extracted so that dns_type is either R or Q even when there is "R Q" in the text. The search you suggested works:

sourcetype=dns | mysearch | search NOT [ search dns_type="R" | dedup src_ip | fields src_ip ]

I never knew you could bracket a search string under a not. Thank you. Why do you have "fields src_ip"?

Re: Filter records based on whether any records of a group match a given criteria

lguinn2 — Wed, 19 Mar 2014 22:59:39 GMT

The subsearch (which starts with [ and ends with ]) returns a set of results. The only thing I want from those results is a list of the src_ip values. The fields src_ip causes the subsearch to return a list of the src_ip values.

You can actually see the sub-search results if you check out the search job inspector after the search is finished.

Also, you can probably make your search go even faster if you don't put the pipes in it - they just add extra steps:

sourcetype=dns mysearch NOT [search .... ]

should work fine