Match 2 log lines using common value and compare

[mohanrajm]

Hi Guys,

I'm new to Splunk and trying to achieve the below requirements. Please help me.

If the system name is not started with "AUH" and if it's part of "corporate_VPN" then give the result in a table with the user name, computer name, and group name. user field has a common value between these 2 log lines but system name is from another log line.

2020-05-21 13:47:18 System4.Info 10.10.10.1 date=2020-05-21 time=13:47:45 devname="FW01-T1" devid="FG201" logid="39949" type="event" subtype="vpn" level="information" logdesc="SSL VPN statistics" action="tunnel-stats" tunneltype="ssl-tunnel" user="testuser" group="corporate_VPN" msg="SSL tunnel statistics"

2020-05-21 13:47:51 System4.Info 10.10.10.1 date=2020-05-21 time=13:47:51 devname="FW01-T1" devid="FG201" logid="45057" type="event" subtype="endpoint" level="information" logdesc="FortiClient connection added" action="add" status="success" connection_type="sslvpn" count=1 user="testuser" systemname="AUHWIN01" msg="Add a FortiClient Connection."

Regards,
Mohan

[richgalloway · Accepted solution]

This may help, but the results may be not great if there are multiple sets of events for the same user.

index=foo
| stats values(*) as * by user
| where match(group, "corporate_VPN") AND NOT match(systemname, "(?i)AUH.*")

[mohanrajm]

Thanks for your quick reply. I'm getting the unexpected results but due to case-sensitivity, I guess. how to match the system name even if it's case sensitive or not?

For Example:
AUH
auh
Auh

[richgalloway]

Use the case-insensitive flag (?i) in the regular expression. I've updated my answer to include it.

[mohanrajm]

Thank you so much for your support. It is working perfectly.