Splunk Search

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

nazanin2016
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

gertman
New Member

This should do the trick:

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

jwelch_splunk
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

nazanin2016
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

jwelch_splunk
Splunk Employee
Splunk Employee

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

0 Karma

nazanin2016
Path Finder

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

0 Karma

jwelch_splunk
Splunk Employee
Splunk Employee

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

Based on what I think your data is

richgalloway
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, Karma would be appreciated.
0 Karma

nazanin2016
Path Finder

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

0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...