Splunk Search

How do I edit my search to create a table of all currently logged on VPN users?

reswob4
Builder

So after reviewing a number of Q&As on this site, I created the following search to track currently logged on VPN users:

source="vpn.log" EventType="BROKER_USERLOGGEDIN" | eval n=strftime(_time, "%d-%m-%Y %H:%M:%S") | join UserDisplayName [ search source="vpn.log" EventType="BROKER_MACHINE_ALLOCATED" ] | search NOT [search source="vpn.log" EventType="BROKER_USERLOGGEDOUT" | table UserDisplayName] | iplocation ClientIpAddress | table n,UserDisplayName, MachineName, ClientIpAddress, Country

This gives me a very nice table of users currently logged on, when they logged on, machine name, remote IP and the country of the IP.

BUT there is a flaw. If, during the time parameters of the search, a user logs on, logs off, then logs back on, that user is not shown in the final table even though they are currently logged on.

How do I correct that problem? Ideally, I could use some sort of session ID, but our VPN doesn't provide that in a log.

Thanks

0 Karma

woodcock
Esteemed Legend

Whenever I have a problem like this, I manufacture a SessionID and then use that like this:

source="vpn.log" | reverse | streamstats count(eval(EventType="BROKER_USERLOGGEDIN")) AS SessionID by UserDisplayName
| eventstats earliest(_time) AS LoginTime latest(_time) AS lastTime latest(EventType) AS lastEventType by SessionID UserDisplayName

This is your bas search.
Now if you do this:

| stats values(*) AS * BY SessionID UserDisplayName

You have statistics on all Sessions.
If you do this:

| reverse | dedup UserDisplayName

You will have the information for the last (current) session.

somesoni2
SplunkTrust
SplunkTrust

Try something like this

source="vpn.log" EventType="BROKER_USERLOGGEDIN" OR EventType="BROKER_USERLOGGEDOUT" OR EventType="BROKER_MACHINE_ALLOCATED" | eval n=if(source="BROKER_USERLOGGEDIN",strftime(_time, "%d-%m-%Y %H:%M:%S"),null())
| stats values(n) Values(MachineName) as MachineName values(ClientIpAddress) as ClientIpAddress values(EventType) as EventType values(Country) Country by UserDisplayName | where mvcount(EventType)>1 AND isnotnull(mvfind(EventType,"BROKER_MACHINE_ALLOCATED")) AND isnull(mvfind(EventType,"BROKER_USERLOGGEDOUT")) | table n,UserDisplayName, MachineName, ClientIpAddress, Country
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...