topic Re: How do you search a value from one query in another second query? in Splunk Search

How do you search a value from one query in another second query?

dsha — Tue, 02 Oct 2018 23:07:30 GMT

We have two different search queries with no unique fields and we would like to get the below info:

we would like to search the value which we get in first query in the second query.
if the value in first query is present in second query then we would like to print both the events .

For example, we tried the below, but we were unable to get both the events.

index=abcde_yudhbc  "Sending message to queue"  | dedup field1 | stats count by field1| map search="search index=ajhdahbd_hdhc  $field1$  Successfully pushed to system "

Whatever list of values we get for field1, we would like to pass each field1 value to the second query using the map command. And, if the field1 value is present, then we would like to display both the field1 value and the event which is matched in the second search.

Re: How do you search a value from one query in another second query?

HiroshiSatoh — Wed, 03 Oct 2018 00:08:41 GMT

Try this!

index=abcde_yudhbc "Sending message to queue" 
| dedup field1 
| stats count by field1
| map search="search index=ajhdahbd_hdhc $field1$ Successfully pushed to system |eval field1=\"$field1$\",count=$count$"

Re: How do you search a value from one query in another second query?

dsha — Wed, 03 Oct 2018 00:27:02 GMT

we are not able to see the expected output .we would like to see the result in three columns
1.filed1
2.event of query1 which is related to field1 value
3.event of query2 if it has filed1 value

from the above query we have 300 field1 values but we see output for only 10 values . which means that every value of field1 is not passed to the second query.

Re: How do you search a value from one query in another second query?

vupham — Wed, 03 Oct 2018 03:34:11 GMT

According to the map command documentation the behavior you see is normal since the default maxsearches via map is 10. You can increase that manually, but I believe you will still not get the desired output, because the map command is not designed to append events to an existing set of results.
One way to get the results you want is to change your base search to search both indexes for events you want then combining them with stats.

ex. index=abcde_yudhbc "Sending message to queue" OR index=ajhdahbd_hdhc "Successfully pushed to system " | stats count(eval(index=abcde-yudhbc)) as count by field
Modify the stats command with whatever fields you need.

Re: How do you search a value from one query in another second query?

dsha — Wed, 03 Oct 2018 05:18:31 GMT

Thanks for the info vupham. we are trying to achieve . with or with out using map we would like to achieve the below results.

We have two different search queries with no unique fields and we would like to get the below info:

1.we would like to search the value which we get in first query in the second query.

2.if the value in first query is present in second query then we would like to print both the events

we would like to see the result in three columns
1.filed1
2.event of query1 which is related to field1 value
3.event of query2 if it has filed1 value of first search query

note : field1 value we get only from first query . and in the secound query we need to search with the eah field1 value and if it matches then we need to print result in 3 columns which we have mentioned above.

Re: How do you search a value from one query in another second query?

HiroshiSatoh — Tue, 29 Sep 2020 21:25:22 GMT

When using map function please change maxsearches.

In order to change the search sentence, the difficulty level changes with how to hold filed1.
Does Query2(jhdahbd_hdhc) submit filed1? Can you search by keyword search only?

search index=ajhdahbd_hdhc $field1$

↓
search index=ajhdahbd_hdhc (field1="XX" OR field1="YY" ･･･)

Re: How do you search a value from one query in another second query?

dsha — Wed, 03 Oct 2018 16:13:23 GMT

field1 value is only present in search 1. we dont have same feildname in search 2. we would like the search the value of field1 in search query2 and get the events

Re: How do you search a value from one query in another second query?

HiroshiSatoh — Thu, 04 Oct 2018 01:24:29 GMT

It is a query with poor efficiency because I do not know the condition.

index=ajhdahbd_hdhc "Successfully pushed to system" 
     [search index=abcde_yudhbc  "Sending message to queue" | dedup field1 |fields field1]
| rename _raw as rawText
| eval match_field=[search index=abcde_yudhbc  "Sending message to queue" | dedup field1 |stats values(field1) as query | eval query=mvjoin(query,",") | fields query ]
| eval match_field=split(match_field,",") 
| mvexpand match_field
| where like(rawText,"%"+match_field+"%")
| join type=left match_field [search index=abcde_yudhbc  "Sending message to queue" | dedup field1|rename field1 as match_field]

※Because I am doing DEDUP, only one event is tied.

Re: How do you search a value from one query in another second query?

DalJeanis — Thu, 04 Oct 2018 20:28:12 GMT

Do not use map when it is not needed.

Is the position of the $field1$ value always the word directly before the phrase "Successfully pushed to system"?

To verify, try this... make sure the capitalization and wordign in the rex is exactly the same as the record...

(index=foo2 "Successfully pushed to system")
| head 10 
| rex "\b(?\w+)\s+Successfully pushed to system"

If the above properly extracts the field1 value, then run this...

(index=foo1 "Sending message to queue") OR (index=foo2 "Successfully pushed to system")
| dedup field1 keepempty=true 
| rex "\b(?\w+)\s+Successfully pushed to system"
| eval unit=case(index=foo2,1)
| stats sum(unit) as count by field1

... otherwise modify the rex until it is properly extracting the field1 value from the message, then proceed with the stats command

The lesson here is, if a human can tell what the field1 value should be by looking at the layout of the event, then there is a way to build the field1 field, so you do that then use stats.