Splunk Search

correlation data from same index different soucetype

Symon
Explorer

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

userassignip
john192.168.1.1
paul192.168.1.2

 

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

srcsrcportdestdestport
192.168.1.1123410.0.0.122
192.168.1.2432110.0.0.222


I want to correlate the result like_

usersrc (or) assignipsrcportdestdestport
john192.168.1.1123410.0.0.122
paul192.168.1.2432110.0.0.222


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.

Labels (3)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...