topic Filtering data for stats purpose in Splunk Search

Filtering data for stats purpose

ripson — Wed, 06 Dec 2023 12:09:23 GMT

I am using Splunk 9.0.4 and I need to make a query where I extract data from a main search.
So I am interested in results from the main search:

stage=it sourcetype=some_type NOT trid="<null>" reqest="POST /as/*/auth *"

But then I need filter out results from the main search, using a subsearch that operates on a different data set, using a value from a field from the main search, let's call it trid, and trid is a string that might be part of a value called message in a subsearch. There might be more results in the subsearch, but if there is at least one result in a subsearch then the result from the main search stays in the main search, if not it should not be included in the main search.

So I am interested only in the results from the main search, and the subsearch is only used to filter out some of them that does not match.

stage=it sourcetype=some_type NOT trid="<null>" reqest="POST /as/*/auth *" | fields trid [ search stage=it sourcetype=another_type | eval matches_found=if(match(message, "ID=PASSLOG_" + trid), 1, 0) | stats max(matches_found) as matches_found ] | where matches_found>0

After a few hours I cannot figure out how to make it. What is wrong with it? Please advise.

Re: Filtering data for stats purpose

ITWhisperer — Wed, 06 Dec 2023 14:28:24 GMT

Subsearches execute before main searches (although there are exceptions), therefore trid from the main search is not available in the subsearch. However, you could try something like this

stage=it sourcetype=some_type NOT trid="<null>" reqest="POST /as/*/auth *" [ search stage=it sourcetype=another_type | rex field=message "ID=PASSLOG_(?<trid>\d+)" | stats count by trid | fields trid ]

Here I have assumed trid is numeric - if not, you should define a pattern that will allow rex to extract the trid from the message field

Re: Filtering data for stats purpose

ripson — Wed, 06 Dec 2023 15:06:18 GMT

Thank you so much! This is UUID actually but I have added a pattern and it works perfectly!