Splunk Search

merge two search results

Contributor

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 ?

Tags (2)
0 Karma

Path Finder

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.

Explorer

Try this:

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

0 Karma

Communicator

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

0 Karma

Engager

Use 'appendcols'

Contributor

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
0 Karma

Legend

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

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

Contributor

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

0 Karma

Contributor

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

0 Karma

Contributor

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

0 Karma

Ultra Champion

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!