Splunk Search

Count/List results from subquery that exist in main query

yashodhan01
New Member
index="main" "recommended product" [ search index="main" "purchased product" | fields itemid | rename itemid as search | format ] 

So Subquery returns a list of itemid like so... ("abc" OR "xyz" OR "mno")
Main query will return 5 events, consisting of "abc"(found in 2 events) and "xyz"(found in 3 events). "mno" does not exist in main query data set.
Is there a way I can get a list of subquery keywords that were found in main query?

EDIT: Clarifying the ask with an example -

My main search gives me a list of "recommended products"
My subsearch gives me a list of "purchased products"
I would like to find out how many products were purchased because they were recommended.

0 Karma

to4kawa
Ultra Champion
 index="main" AND ("recommended product" OR "purchased product")
| fields _raw itemid
| mvexpand itemid
| eventstats count(eval(searchmatch("recommended product"))) as itemid_count by itemid
| eventstats values(itemid) as itemid_list

Hi, @yashodhan01
Since there is no sample log, I can't write an expression to aggregate.

0 Karma

woodcock
Esteemed Legend

You are overcomplicating it; try simply this:

index="main" AND ("recommended product" OR "purchased product")
| stats count(eval(searchmatch("recommended product"))) AS recommended count(eval(searchmatch("purchased product"))) AS purchased count BY itemid and possibly other fields like userid here
0 Karma

yashodhan01
New Member

This seems like a simple task but I am working with constraints here. The ItemID field is logged twice in the "purchased products" events and the value of first occurrence of ItemID is "unknown" in this system. Splunk stop trying to match after this first occurrence of ItemID in the event. I can't go back and change the logs or configure Splunk to use multivalued fields. Due to this, I cannot count BY ItemID in the way you proposed.

0 Karma

to4kawa
Ultra Champion
index="main" "subsearch keywords" OR "mainsearch keywords"
| stats count by myField

Hi, @yashodhan01
you don't need sub search.
what do you expect your output?
please provide example.

0 Karma

yashodhan01
New Member

This won't work for me because I need to extract some results from the subsearch and feed them to the main search.

0 Karma

mydog8it
Builder

Here is a place to start (I've gotta run for the day!)

index="main" "main keywords" | join type=inner left=L right=R where L.YourFieldontheLeft=R.YourFieldontheRight [ search index="main" "subsearch keywords" | fields myField | rename myField as search | format ] 
0 Karma

woodcock
Esteemed Legend

NO JOINS EVER

0 Karma
Get Updates on the Splunk Community!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...