merge two search results

[KarunK]

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 ?

[romanwaldecker]

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.

[srujan9292]

Try this:

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

[Laya123]

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

[ramab]

Use 'appendcols'

[mehulraisinghan]

Thanks this worked for me

[KarunK]

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

[Ayn]

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

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

[KarunK]

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

[KarunK]

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

[KarunK]

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 ?

search 1 | stats .... by object
| join type=outer object [ search2 | stats ..... by object
| join type=outer object [ search3 | stats ...... by object

[kristian_kolb]

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?

/k