Splunk Search

merge two search results

KarunK
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

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

srujan9292
Explorer

Try this:

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

Laya123
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

ramab
Engager

Use 'appendcols'

mehulraisinghan
Engager

Thanks this worked for me

0 Karma

KarunK
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

Ayn
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

KarunK
Contributor

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

0 Karma

KarunK
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

KarunK
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

kristian_kolb
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
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...