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