Return Value from Subsearch to Evaluate Against

[Abe_T]

I am building a search that will based on a table of products with different versions. I need to run an initial search that will return the version with most hosts ("Mainstream") and use that version to compare everything else against in order to determine if its less than/greater than (older/newer). I am currently using a foreach command to send each category of product to a subsearch which then grabs mainstream and return it so I can compare each event's version to this mainstream version. I am having extreme difficulty passing the field to the subsearch and filtering out the category by using something like a Where command without setting off confusing errors that don't really make any sense. ("Eval command malformed"). The logic of the query works when I am not using a '<<field>>' token but soon as I try pass a token with a where command within subsearch, it falls apart. I am a Splunk newbie so maybe I am missing something obvious, please advise:

| inputlookup Lookup_Table.csv
| eval Category = OSType. "-" .ProductName
| stats values(ProductVersion) AS Version values(LifeCycleStatus) AS Status by Category
| foreach  Category 
    [eval newLifecycleStatus=case(Version<
    [| inputlookup Lookup_Table.csv.csv
    | eval Category = OSType. "-" .ProductName
    | where Category =='<<FIELD>>'
    | sort -product_count
    | head 1
    | eval Version="\"".ProductVersion."\""
    | return $Version], "Declining")]

 I changed this code to something like this with no luck because I cant filter the results without a where statement:

| inputlookup Lookup_Table.csv
| stats values(ProductVersion) AS Version values(LifeCycleStatus) AS Status by ProductCode
| foreach ProductCode
    [eval newLifecycleStatus=case(Version==[| inputlookup Lookup_Table.csv
    | eval NewProductCode=tostring('<<FIELD>>')
    | sort -product_count
    | head 1
    | eval ProductVersion="\"".ProductVersion."\""
    | return $ProductVersion], "Emerging")]

[ITWhisperer · Accepted solution]

Use eventstats to workout the top product version and set status based on that - the first part of the below runanywhere example (before the blank lines) just sets up some dummy data).

| gentimes start=-1 increment=10m 
| streamstats count as event 
| eval OSType=mvindex(split("Windows,Linux",","),random()%2)
| eval ProductName=mvindex(split("A,B,C",","),random()%3)
| eval ProductVersion=random()%5
| table OSType ProductName ProductVersion



| eventstats count as product_count by OSType ProductName ProductVersion
| eventstats max(product_count) as top_product_count by OSType ProductName
| eval top_product_version=if(product_count=top_product_count,ProductVersion,null())
| eventstats max(top_product_version) as top_product_version by OSType ProductName
| eval status=if(ProductVersion<top_product_version,"Declining","Emerging")

[Abe_T]

Thank you! Worked like a charm! Much appreciated.