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.
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.
j
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
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?
j
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
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]
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.
j
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