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 ?
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.
Try this:
search 1 | append [ search2 ] | append [search 3]
| Stats values(*) AS * by OBJECT
| table OBJECT COUNT Requests Uniuqe
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
Use 'appendcols'
Thanks this worked for me
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
Couldn't you just move the stats command to the end of your query?
search 1 | append [ search2 ] | append [search 3] | stats ..... by object
Nope its not working I am getting less no: of results than when I search separately and add them together.
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?
/k