How to join two searches?

[jerrytao]

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

[marco1987]

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]

@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

[jerrytao]

@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.

[jnudell_2 · Accepted solution]

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

[jerrytao]

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.

[jnudell_2]

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

[jerrytao]

@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-----

[jnudell_2]

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

[jerrytao]

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

[woodcock]

Are you using the join one or the other one?

[jnudell_2]

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.