Find mismatched records using shared field

Question

I'm a Splunk newb and am wrestling with building a specific query. Any help would be appreciated.

This is the base search.

index=index (Food="*meat*") AND (Food="*veggie*" OR Food="*fruit*")

From here, I rex the result and sort.

| rex field=_raw "location \((?<Farm>\w+)\).*operator \((?<Farmer>\w+)\).*crops \((?<CropType>\w+)\).*"
| stats list(Farm), list(CropType), count by Farmer
| sort -count

This is a good start, but it returns way more than needed, and I'm getting way out in the weeds with this. Here's the details:

CropType contains two possible values, veggie or fruit.
I only want to see records where one veggie and one fruit share the same Farm. (No fruit/fruit, veggie/veggie.)

The end goal here is to present a list of farms and crops per farmer.

Thanks in advance for any assistance/insight.

what does your raw data look like? – warren Sep 24 '20 at 12:44 — warren, Sep 24 '20 at 12:44

score 0 · Answer 1 · answered Sep 24 '20 at 04:35

Does something like this work?

index=index (Food="*meat*") AND (Food="*veggie*" OR Food="*fruit*")
| rex field=_raw "location \((?<Farm>\w+)\).*operator \((?<Farmer>\w+)\).*crops \((?<CropType>\w+)\).*"
| eval fruitFarm=if(CropType=="fruit",1,0)
| eval vegFarm=if(CropType=="veggie",1,0)
| stats sum(fruitFarm) as fruitFarm, sum(vegFarm) as vegFarm by Farm, Farmer
| where fruitFarm>0 and vegFarm>0

Find mismatched records using shared field

1 Answers1