topic correlation data from same index different soucetype in Splunk Search

correlation data from same index different soucetype

Symon — Thu, 07 Mar 2024 07:38:37 GMT

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.

Re: correlation data from same index different soucetype

gcusello — Thu, 07 Mar 2024 10:23:34 GMT

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