Splunk Search

How to remove a row from lookup table and update it?

Path Finder

Hi,

I wonder whether someone may be able to help me please.
I have created in a separate search with a lookup table containing src_user, StartTime, and action (which its value is connected): It adds all the connected users to lookup table with the time:

source=........    VPNaction=connected |dedup src_user _time |eval 
StartTime=strftime(_time,"%m/%d/%Y %H:%M:%S")  |eval action=VPNaction|table src_user StartTime action
 |outputlookup ConnectedVpn.csv createinapp=true 

Now I want to look for the ended connection and compare the end time and start time:

source=..... VPNaction=ended |dedup src_user _time |eval 
EndTime=strftime(_time,"%m/%d/%Y %H:%M:%S")  |eval action=VPNaction|table src_user EndTime action | lookup ConnectedVpn.csv src_user OUTPUT StartTime |eval diff=EndTime-StartTime|table src_user StartTime action EndTime diff

How can I remove the row of the user whose connection is ended from ConnectedVpn.csv, otherwise it will cause problem for its next start.

Thank you

New Member

This should do the trick:

|inputlookup blah | where field!=itemtoremove | outputlookup create_empty=true blah
0 Karma

Splunk Employee
Splunk Employee

|inputlookup blah | search field!=itemtoremove |outputlookup blah

This will look at current csv remove the rows you don't want then overwrite the csv with only the data you want to keep

Run it without the ouptlookup first so you can see what you are going to replace with for safety

Path Finder

Thanks , Actually I want to find the users whose vpn connection is more that 24h, so in the second command as you see on the top I try to calculate the difference but I will also need to delete the records for the users who ended their connection, the only field that is the same with the lookup table is src_user

0 Karma

Splunk Employee
Splunk Employee

Outputlookup is also shc aware so it would replicate if you are using shc

0 Karma

Path Finder

I want to remove based on my search so when you say field!=removeitem how I can define it?

0 Karma

Splunk Employee
Splunk Employee

|inputlookup ConnectedVpn.csv |WHERE EndTime="" OR isnull(EndTime) |outputlookup ConnectedVpn.csv

Based on what I think your data is

0 Karma

SplunkTrust
SplunkTrust

CSV lookup files cannot be edited - they must be replaced completely or appended to.

KV Store lookups, however, can modified individual records.

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

Path Finder

Thanks, so in this case how can I run my scenario?

0 Karma

SplunkTrust
SplunkTrust

That's a good question. Ideally, you'd end your query with a REST command that updates the KV store. Unfortunately, REST is a generating command that must start a query so that idea won't work.

Perhaps a KV store expert will have another suggestion.

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