correlation data from same index different soucetype

[Symon]

I have the index=fortigate and there are two sourcetypes ("fgt_event" and "fgt_traffic").

index=fortigate sourcetype=fgt_event |stats count by user, assignip

user	assignip
john	192.168.1.1
paul	192.168.1.2

index=fortigate soucetype=fgt_traffic | stats count by src srcport dest destport

src	srcport	dest	destport
192.168.1.1	1234	10.0.0.1	22
192.168.1.2	4321	10.0.0.2	22

I want to correlate the result like_

user	src (or) assignip	srcport	dest	destport
john	192.168.1.1	1234	10.0.0.1	22
paul	192.168.1.2	4321	10.0.0.2	22

I have learned SPL query like join,  mvappend, coalesce, subsearch ,etc. I tried a lot by combining the SPL functions to output. It doesn't still working. Please help me. Thanks.

[gcusello]

Hi @Symon ,

don't use join because it's a very slow search, use stats in this way:

index=fortigate sourcetype IN (fgt_event, fgt_traffic )
| eval src=coalesce(src,assignip)
| stats 
   values(srcport) AS srcport 
   values(dest) AS dest
   values(destport) AS destport
   BY user src

If you want only the events present in both the sourcetypes, you have to add an additional condition:

index=fortigate sourcetype IN (fgt_event, fgt_traffic )
| eval src=coalesce(src,assignip)
| stats 
   values(srcport) AS srcport 
   values(dest) AS dest
   values(destport) AS destport
   dc(sourcetype) AS sourcetype_count
   BY user src
| where sourcetype_count=2
| fields - sourcetype_count

Ciao.

Giuseppe