Dynamic lookup - how to remove entries?

[lukasmecir]

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

[richgalloway · Accepted solution]

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

[lukasmecir]

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