topic New search using values from first search in Splunk Search

New search using values from first search

splunker1981 — Mon, 25 Jan 2016 17:05:07 GMT

Hi all,

I can't seem to figure out how to use the values from a search and use those values to kick off another new search with those values. I've tried append and using subsearches but don't seem to get the correct data back. I am not sure what the heck I am doing wrong.

What I'd like to accomplish is search by a specific value which I input then use the results returned by the search to kick off a whole new search against all the data using those value.

For example if I search for buyer1 I get back one or more values I would like to then run a whole new search by passing each of those values to the new search and only get back those results.

If I search for buyer1 I will back values 5556677 and 888999 for example

   sourcetype=buyer_data buyer="buyer1" | stats count by cust_id | fields - count

I would like to then use those values as part of my next search to get the final IDs associated with that customer.

  sourcetype=buyer_data (cust_id=5556677 OR cust_id=888999) | stats count by cust_id | dedup cust_id | fields - count

This should return everything all record associated with this customer which is what I am looking to do.

I tried running a subsearch and append

  sourcetype=buyer_data | stats count by id | append [|search sourcetype=buyer_data buyer="buyer1" | stats count by cust_id  | fields - count | eval id=buyer


   sourcetype=buyer_data * [ |search sourcetype=buyer_data buyer="buyer1" | stats count by cust_id | fields - count]


   sourcetype=buyer_data * stats count by id | append [|search sourcetype=buyer_data buyer="buyer1" | stats count by cust_id | eval id=cust_id

Any help would be much appreciated.

Re: New search using values from first search

jbjerke_splunk — Mon, 25 Jan 2016 17:15:20 GMT

Hi splunker1981

I believe the correct syntax in this example would be

sourcetype=buyer_data [sourcetype=buyer_data buyer="buyer1" | stats count by cust_id | fields - count]| stats count by cust_id | dedup cust_id | fields - count

If you are from an SQL background there is a good guide on how to think while crafting SPL queries:

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/SQLtoSplunk

Let me know how you get along.

Re: New search using values from first search

splunker1981 — Tue, 29 Sep 2020 08:33:02 GMT

Thanks for the quick reply, jbjerke

So I think that's the same result I was getting with all my previous attempts. I ran your query and I get back the result from the first search, which returns back a value ID for buyer1. In this case for example it returns back a value of 5556677

What I need to do is then run a new search against all the dataset for the specific sourcetype and pass the value I just got with the query above. Something like sourcetype=buyer_data 5556677 It should then be returning back three unique values, which is what I get if I were to take the value 5556677 manually and then do a new search like so.
index=cust_data sourcetype=buyer_data * 5556677 | dedup cust_id | table cust_id

Re: New search using values from first search

splunker1981 — Tue, 26 Jan 2016 11:47:05 GMT

I figured out what the issue is but still not quite sure how to go about fixing it. It appears that the result being passed is also specifying a field name. When it goes to perform the second search it is specifying a fieldname along with the value, which is NOT what I am trying to do. I just want to have it search on the value and not fieldname->value combination.

Instead of actually running the following query:

 sourcetype=buyer_data * 5556677

It is running this one which is why it's limiting my results.

 sourcetype=buyer_data * cust_id=5556677

How to I make the subsearch not pass a field name and only pass a value to the second search?

 [sourcetype=buyer_data buyer="buyer1" | stats count by cust_id | fields - count]

Re: New search using values from first search

splunker1981 — Tue, 29 Sep 2020 08:35:25 GMT

Figured out my own problem. I needed to remove the stats count by cust_id in the first search and instead select the field using fields + cust_id

This worked for those trying to do the same:

  sourcetype=buyer_data [sourcetype=buyer_data buyer="buyer1" | fields + cust_id | rename cust_id AS search ]| stats count by cust_id | dedup cust_id | fields - count

Re: New search using values from first search

jbjerke_splunk — Wed, 27 Jan 2016 18:39:48 GMT

Hi splunker1981

I believe you can achieve what you want by building a dynamic search filter with the commands mvcombine and nomv. The syntax would look something like this:

   sourcetype=buyer_data [search sourcetype=buyer_data buyer="buyer1" | fields cust_id | mvcombine delim=" OR " cust_id | nomv cust_id| eval search="(".cust_id.")" | fields - cust_id]

The search above would essentially evaluate to this

   sourcetype=buyer_data (5556677 OR 888999)

Is this what you are looking for?

Re: New search using values from first search

splunker1981 — Tue, 29 Sep 2020 08:39:19 GMT

Hi jbjerke - thanks for sticking with me here.

To answer your question; yes and no. Let me try to explain a little better to see if that helps clear things up.

Your search partially gets me what I need. What happens is that I am left with ALL the events (a good thing) which looks like what I pasted below as an example. All of these events are currently parsed into their own fields with key/val pairs.

2015-01-01 ID=9999 SE=se_value01 PR=898989 I_ID=0001
2015-01-01 ID=9999 RE=re_value01 start=01
2015-01-01 ID=9999 SB=sb_value01 end=09
2015-01-01 ID=9999 AT=at_value01 pause=03
2015-01-01 ID=555 SE=se_value02 PR=55988 I_ID=000488
2015-01-01 ID=555 RE=re_value02 end=33
2015-01-01 ID=555 SB=sb_value02 UID=99990
2015-01-01 ID=555 AT=at_value02 UID=99990 pause=03
2015-01-01 I_ID=001 MAT=mat_value001 STAT=received SET=off EN=on
2015-01-01 I_ID=000488 MAT=mat_value000488 STAT=closed SET=on EN=off

The problem I am running into is figuring out how to group the events, for reporting purposes by the two unique IDs? For example - each set of events has an ID and mixed in within those IDs there's always one event that has and I_ID field. What I would like to be able to say is - group events that have the same ID and I_ID (but keep in mind that I_ID only exists once per group of events) and then be able to pull fields values based on that grouping to display in one row.

 2015-01-01 9999  se_value01 sb_value01 at_value01 pause03 mat_value001 received
 2015-01-01 555   se_value02 sb_value02 at_value02 pause03 mat_value000488 closed

Re: New search using values from first search

jbjerke_splunk — Mon, 08 Feb 2016 10:59:16 GMT

Hi splunker1981

How about adding a stats grouping at the end of your search?

 THE INITIAL SEARCH | stats list(*) AS * by ID

I think this should work with the sample data you posted.