Splunk Search

How to join two searches?

jerrytao
New Member

First search:

index=A source="FunctionHandler@*" "ul-ctx-caller-span-id"=null

With this search, I can get several row data with different methods in the field ul-log-data.method, so the table will be:

ul-ctx-head-span-id | ul-log-data.method
------------A-----------|---------------1-------------
------------B-----------|---------------2-------------
------------C-----------|---------------3-------------
------------D-----------|---------------2-------------        

Second search:

index=A source="FunctionHandler@*" "ul-ctx-caller-span-id"!=null ul-ctx-head-span-id=1-5D0A0438-736C50A33B81102B75CBA44D

With the field "ul-ctx-head-span-id", second search will return 2 row data with different ul-log-data.function, ul-span-duration, so the table will be:

ul-ctx-head-span-id | ul-log-data.function|ul-span-duration
------------A-----------|---------------get------------- |--------xx-----------
------------A-----------|---------------post--------------|--------xx---------

Please note: the second search depends on the field "ul-ctx-head-span-id" in the result of first search.

Finally, I want get a table like below:

ul-ctx-head-span-id | ul-log-data.method | ul-log-data.function|ul-span-duration
------------A-----------|---------------1-------------|-----------get------------ |--------xx-----------
------------A-----------|---------------1-------------|-----------post---------- |--------xx----------
------------B-----------|---------------2-------------|-----------get------------ |--------xx-----------
------------B-----------|---------------2-------------|-----------post---------- |--------xx----------
------------C-----------|---------------3-------------|-----------get------------ |--------xx-----------
------------C-----------|---------------3-------------|-----------post---------- |--------xx----------
------------D-----------|---------------2-------------|-----------get------------ |--------xx-----------
------------D-----------|---------------2-------------|-----------post---------- |--------xx----------

It means if I get 4 row data in first search, then after join, I need show 8 row data

Forgive my poor English, can someone help on this?
Thanks in advance

Tags (3)
0 Karma
1 Solution

jnudell_2
Builder

Hi @jerrytao ,

The easiest way to do this would be to use a join command:

index=cosv2 ul-ctx-source=c4rupgrd source="FunctionHandler@*" "ul-ctx-caller-span-id"!=null "ul-log-data.function"="GetRemainingAsync" OR "ul-log-data.http_url"="https://string/validatetoken"
| join ul-ctx-head-span-id 
   [ search index=cosv2 ul-ctx-source=c4rupgrd source="FunctionHandler@*" "ul-ctx-caller-span-id"=null "ul-log-data.http_url"!="*/health" "ul-log-data.http_url" IN ("https://string/queries*", "https://string/jobs*") ]
| rename ul-log-data.* as *
| replace ... put your replace stuff here ...        
| eval function = tostring(function) . "___" . tostring(http_url)
| table _time ul-ctx-head-span-id http_url function ul-span-duration

Try that and see if you get the results you're looking for.

Edit: Another way to accomplish this:

(index=cosv2 ul-ctx-source=c4rupgrd source="FunctionHandler@*")  ( ("ul-ctx-caller-span-id"=null) OR ("ul-ctx-caller-span-id"!=null "ul-log-data.method"="*") )
| eval func_dur = 'ul-log-data.function' . "|" . 'ul-span-duration'
| stats values(ul-log-data.method) as ul-log-data.method values(func_dur) as func_dur by ul-ctx-head-span-id
| mvexpand func_dur
| eval ul-log-data.function = mvindex(split(func_dur, "|"), 0), ul-span-duration = mvindex(split(func_dur, "|"), 1)
| table ul-ctx-head-span-id ul-log-data.method ul-log-data.function ul-span-duration

View solution in original post

0 Karma

marco1987
Explorer

Hi @jerrytao,
consider your Search1 with table result -> *A | B* and your Search2 with table result -> A | C | D, try this below to join table result -> A| B |C |D

  Search1
     |append[Search2]
     |stats values(B) as B values(C) as C values(D) as D by A

Vijeta
Influencer

@jerrytao Try below

index=cosv2 ul-ctx-source=c4rupgrd source="FunctionHandler@*" "ul-ctx-caller-span-id"!=null ul-ctx-head-span-id=1-5D0A0438-736C50A33B81102B75CBA44D | join ul-ctx-head-span-id [search index=cosv2 ul-ctx-source=c4rupgrd source="FunctionHandler@*" "ul-ctx-caller-span-id"=null| fields ul-ctx-caller-span-id , ul-log-data.method] | table  ul-ctx-head-span-id ul-log-data.method ul-log-data.function ul-span-duration
0 Karma

jerrytao
New Member

@Vijeta , I need join the result of second search for every ul-ctx-head-span-id, not only that single one. So I should not see "ul-ctx-head-span-id=1-5D0A0438-736C50A33B81102B75CBA44D". You can refer to newly added screenshot "full.PNG". Thanks for reply.

0 Karma

jnudell_2
Builder

Hi @jerrytao ,

The easiest way to do this would be to use a join command:

index=cosv2 ul-ctx-source=c4rupgrd source="FunctionHandler@*" "ul-ctx-caller-span-id"!=null "ul-log-data.function"="GetRemainingAsync" OR "ul-log-data.http_url"="https://string/validatetoken"
| join ul-ctx-head-span-id 
   [ search index=cosv2 ul-ctx-source=c4rupgrd source="FunctionHandler@*" "ul-ctx-caller-span-id"=null "ul-log-data.http_url"!="*/health" "ul-log-data.http_url" IN ("https://string/queries*", "https://string/jobs*") ]
| rename ul-log-data.* as *
| replace ... put your replace stuff here ...        
| eval function = tostring(function) . "___" . tostring(http_url)
| table _time ul-ctx-head-span-id http_url function ul-span-duration

Try that and see if you get the results you're looking for.

Edit: Another way to accomplish this:

(index=cosv2 ul-ctx-source=c4rupgrd source="FunctionHandler@*")  ( ("ul-ctx-caller-span-id"=null) OR ("ul-ctx-caller-span-id"!=null "ul-log-data.method"="*") )
| eval func_dur = 'ul-log-data.function' . "|" . 'ul-span-duration'
| stats values(ul-log-data.method) as ul-log-data.method values(func_dur) as func_dur by ul-ctx-head-span-id
| mvexpand func_dur
| eval ul-log-data.function = mvindex(split(func_dur, "|"), 0), ul-span-duration = mvindex(split(func_dur, "|"), 1)
| table ul-ctx-head-span-id ul-log-data.method ul-log-data.function ul-span-duration
0 Karma

jerrytao
New Member

Hi @jnudell_2 , thanks for help, unfortunately, it doesn't work as my expectation.
In my example, I need show 8 rows if first search returns 4 rows, but with your command, it only returns 4 rows whose ul-log-data.function is "get".
I update the request to make it much clearer, could you help to take a look? Thanks.

0 Karma

jnudell_2
Builder

Sorry, the join was wrong also... I have updated it above.

0 Karma

jerrytao
New Member

@jnudell_2 , thanks for your quick response!
Actually, there are other filter rules in ul-log-data, so I simplified the description in the post. But I don't know how to process your command with other filters.
So I attached new screenshot with 2 single search results, hopes it can help to make the problem clearer. And I hided some private information, sorry for this.

The final table I want is as below:
_time | ul-ctx-head-span-id | http_url | duration | function | ul-span-duration

And for the second search, do you have better way to combine the function? as the original result is
id | function1 | function2
--A-- GetRemainingAsync---(null)--
--A--------(null)----|------outgoing-----

0 Karma

jnudell_2
Builder

I've updated the answer to reflect your additional data.

0 Karma

jerrytao
New Member

@jnudell_2 , thank you so much! It works after reverse this 2 searches.

0 Karma

woodcock
Esteemed Legend

Are you using the join one or the other one?

0 Karma

jnudell_2
Builder

Can you provide some sample data in non-formatted view (raw json)? If I had some sample data, I could put something together for you.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...