Splunk Search

Dynamic lookup - how to remove entries?

lukasmecir
Path Finder

Hello,

I have following issue:

I have VPN GW used to remote connecting of users, this GW sends log to Splunk. I would like to have in Splunk list of currently logged in VPN users, which would be used for some purposes (I need this list to be as current as possible, lets say that username should be added/removed to/from this list no more than 5 - 10 minutes after logging in/logged out of user). My idea is create dynamic lookup based on LOGIN and LOGOUT messages from VPN GW. What I mean exactly:

When LOGIN message for particular user (lets say user "USERA") appears, username is extracted and added to dynamic lookup "vpn_active_users.csv".

When LOGOUT message for the same user ("USERA") appears, username is extracted and removed from dynamic lookup "vpn_active_users.csv".

I know how to create dynamic lookup and append usernames on it, but I did not find a way how to remove previously added username from it. Is there any way how to do it? Or my approach is completely wrong? Any hint would be highly appreciate. Many thanks.

Regards

Lukas

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

See if this helps.  It loads the current lookup file, incorporates new events, and writes the results back to the lookup.  You'll need to update the specifics of the query to match your environment.

| inputlookup vpn_active_users.csv
| eval _time=1, action="LOGIN"
| append [ index=foo (action="LOGIN" OR action="LOGOUT")
  | fields _time user action ]
| stats latest(action) by user
| where NOT action="LOGOUT"
| table user
| outputlookup vpn_active_users.csv

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

See if this helps.  It loads the current lookup file, incorporates new events, and writes the results back to the lookup.  You'll need to update the specifics of the query to match your environment.

| inputlookup vpn_active_users.csv
| eval _time=1, action="LOGIN"
| append [ index=foo (action="LOGIN" OR action="LOGOUT")
  | fields _time user action ]
| stats latest(action) by user
| where NOT action="LOGOUT"
| table user
| outputlookup vpn_active_users.csv

 

---
If this reply helps you, Karma would be appreciated.

lukasmecir
Path Finder

Hi,

thank you very much for your hint, it is really heplful. Finally, I made this search (use tstats in subsearch, because I need search in Data model):

| inputlookup vpn_active_users.csv
| eval _time=1, signature="SSLVPN:LOGIN"
| append [| tstats `summariesonly` values(Authentication.app) as "app", latest(_time) as "_time" from datamodel="Authentication"."Authentication" where nodename="Authentication.Successful_Authentication" by Authentication.user,Authentication.signature
| rename Authentication.* AS *
| where app=="netscaler" AND (signature="SSLVPN:LOGIN" OR signature="SSLVPN:LOGOUT")
| fields _time user signature]
| stats latest(signature) as signature by user
| where NOT signature="SSLVPN:LOGOUT"
| table user
| outputlookup vpn_active_users.csv

Thank you very much for help.

Lukas 

0 Karma
Get Updates on the Splunk Community!

Splunk Security Content for Threat Detection & Response, Q1 Roundup

Join Principal Threat Researcher, Michael Haag, as he walks through:An introduction to the Splunk Threat ...

Splunk Life | Happy Pride Month!

Happy Pride Month, Splunk Community! 🌈 In the United States, as well as many countries around the ...

SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...