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