topic Re: merge two search results in Splunk Search

merge two search results

KarunK — Tue, 07 Feb 2012 04:26:09 GMT

Hi,

I have three search results giving me three different set of results, in which three is one common filed called object and the number of results in each results may vary. I have used append to merge these results but i am not happy with the results. I need merge all these result into a single table.

The structure of the search I have used is given below. (its only a sample)

serach 1 | stats .... by object
append [ search2 | stats ..... by object
append [ search3 | stats ...... by object

Results after append

OBJECT  COUNT   Requests    Uniuqe
------------------------------------
http    100
rtsp    250

http            25
rtsp            21
rtmp            10

http                        10
rtsp                        11

What i need is as below.

OBJECT  COUNT   Requests    Uniuqe
------------------------------------
http    100     25          10
rtsp    250     21          11
rtmp            10

How can I do this. Can i use join instead of append ?

Re: merge two search results

kristian_kolb — Tue, 07 Feb 2012 06:59:23 GMT

It's a bit hard to tell, since you don't give an example of the actual logs. Join can be a very expensive operation, and should probably be avoided if possible.

Are there three different sourcetypes involved?

Re: merge two search results

KarunK — Tue, 07 Feb 2012 22:33:56 GMT

Yes there are different sourcetype involved.

I have figured out a way to do it with join. But not sure whether this is the best way.

Any Comments ?

Re: merge two search results

KarunK — Fri, 10 Feb 2012 04:13:54 GMT

The above soln seems to be not working. It only looks for the field - object in the first search and try to join the respective results from search 2 and search 3.

What I was looking for was to complete merger of the three results that means I would like to see the results from search 2 and search 3 in the final results even though corresponding object is missing in search 1.

Any ideas .....

Please help ???

Thanks in Advance

Re: merge two search results

Ayn — Fri, 10 Feb 2012 09:28:20 GMT

Couldn't you just move the stats command to the end of your query?

search 1 | append [ search2 ] | append [search 3] | stats ..... by object

Re: merge two search results

KarunK — Mon, 13 Feb 2012 01:26:29 GMT

Nope its not working I am getting less no: of results than when I search separately and add them together.

Re: merge two search results

KarunK — Mon, 13 Feb 2012 01:30:53 GMT

The actual search string is shown below.

(sourcetype="mms_export" c_status=200) OR (sourcetype="we_accesslog" " NOT *.isml) 
|stats sum(sc_bytes) as sum_m sum(Bytes_Xferred) as sum_http by client_ip
|join type=outer client_ip [search (sourcetype="we_accesslog"  *.isml) | stats sum(Bytes_Xferred) as sum_smooth by client_ip ] 
|join type=outer client_ip [search (sourcetype="fms_access" ) | chart sum(sc_bytes) as sum by client_ip, x_event | eval diff_flash=disconnect-connect ]
| fillnull sum_m sum_http sum_smooth diff_flash
| eval WMT(GB)= round(sum_m/(1024*1024*1024),4) 
| eval WEB_HTTP(GB)= round(sum_http/(1024*1024*1024),4) 
| eval WEB_SMOOTH(GB) = round(sum_smooth/(1024*1024*1024),4) 
| eval flash(GB)=round(diff_flash/(1024*1024*1024),4)
| fields client_ip WMT(GB) WEB_HTTP(GB) WEB_SMOOTH(GB) flash(GB) | addtotals

Re: merge two search results

ramab — Thu, 08 Mar 2012 12:04:02 GMT

Use 'appendcols'

Re: merge two search results

Laya123 — Tue, 19 Jul 2016 19:39:28 GMT

Hi KarunK,

Did you get the answer for your question, I am also looking for solution for the same problem. If you know the answer can you please help me.

Thanks in advance

Re: merge two search results

srujan9292 — Wed, 05 Jul 2017 18:23:10 GMT

Try this:

search 1 | append [ search2 ] | append [search 3]
| Stats values(*) AS * by OBJECT
| table OBJECT COUNT Requests Uniuqe

Re: merge two search results

romanwaldecker — Tue, 08 May 2018 20:31:44 GMT

What about adding...

| stats first(*) as * by OBJECT

...at the end of your search pipe?

Limitation: This lightweight approach only works if every other column contains max. one unique value per OBJECT. Otherwise stick with the values() variant suggested by @srujan9292.

Re: merge two search results

mehulraisinghan — Fri, 17 Sep 2021 17:35:57 GMT

Thanks this worked for me