topic Re: Using a subsearch to match a string and a field. in Splunk Search

Using a subsearch to match a string and a field.

howyagoin — Mon, 28 Sep 2020 18:09:12 GMT

Hi,

In one of my indexes I've got a series of pipe separated fields which has one value expressed as so:

31.22:88.91

In other cases it's merely:

88.91

In order to match both occurrences of 88.91, I'm currently doing:

... | rex field=foo ":(?<locx>\d+\.\d+)$" | rex field=_raw "|(?<locx>\d+\.\d+)|" ...

This correctly extracts into "locx" cases where the raw data may appear as:

John|Yellow|31.22:88.91|Thursday|Pass
Eve|Red|73.22:88.91|Monday|Pass
Mary|Green|88.91|Friday|Fail

But it will not put into locx the following version:

Mark|Red|88.91:36.03|Monday|Pass

The problem is what comes next - say the final field is "test_result" and I want to match all of the values of locx where the test_result is pass, but then I want to find the events where the locx from the test_result=pass is set, but only when locx is the second element in the colon separated version of the field, or when it's the only value (but never in the case of Mark where it's the FIRST value).

What's the best way to do this?

My thinking was that I'd do a subsearch for the pass conditions, get the value of locx for the Pass conditions, and the go back over the data and search for the fails; but that matches locx in the case of Mark where 88.91 is the first of the two fields.

I'm doing a horrific job of explaining this, but what I was looking for was the right way to say:

"Match this condition in the data and then look for this field. For each value of that field, go back through the data and find other matches for a different condition."

From my pseudo data above, I'm trying to find people who failed an exam that took course 88.91 on its own, or, after taking some other course first. But I don't want to find people who took course 88.91 and failed an exam if they took another course after 88.91.

I need a drink.

Re: Using a subsearch to match a string and a field.

MuS — Wed, 12 Nov 2014 13:31:43 GMT

Hi howyagoin,

I would use two fields for this, using your provided data I would extract one field as baseCourse (this would be 88.91 in your example) and otherCourse for the others. Try something like this:

.... | rex "[\:\|](?P<myCourse>\d+\.\d+)\|" | rex "\|(?P<otherCourse>\d+\.\d+)\:" | ....

this way you can compare the two field without any problem.

hope this helps ...

cheers, Mus

Re: Using a subsearch to match a string and a field.

howyagoin — Wed, 12 Nov 2014 19:55:51 GMT

Great approach MuS, thanks for that suggestion. Do you have any recommendations on what the best comparison option would be, something using eval, perhaps?

I'm trying to ensure that after extracting myCourse from the rex in a subsearch that when I go back to the main search I'm only matching those cases where myCourse is not the same as otherCourse.

I think I'm just stuck at how to properly do the comparison check to make certain that the students who have a "failed" status for a given exam only have it if the only or most recent course they took was myCourse and not if the only or most recent course they took was any otherCourse.

Re: Using a subsearch to match a string and a field.

MuS — Thu, 13 Nov 2014 08:34:24 GMT

No need for a subsearch here. Setup the regex as automatic field extraction so you have the two fields available in the matching events http://docs.splunk.com/Documentation/Splunk/6.2.0/Knowledge/Managesearch-timefieldextractions
Check out this answer to get some ideas about how to compare fields http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html

cheers, MuS