topic How to efficiently delete rows from KV Store based lookup. in Splunk Search

How to efficiently delete rows from KV Store based lookup.

iet_ashish — Tue, 01 Sep 2020 09:41:04 GMT

I have a lookup which is based on KV store. The lookup contains thousands of rows. We want to delete rows from this lookup which are older than 7 days.

To give an idea this is how the lookup looks like except that it contains thousands of rows.

my_date	name	age
Sep-1-2020	Rupert	20
Aug-31-2020	Sam	30
Aug-30-2020	Tony	25
Aug-29-2020	Prince	27
Aug-28-2020	Tom	24
Aug-27-2020	Sean	28
Aug-26-2020	Roger	21

We keep on appending new data to this lookup on a daily basis but we don't really care about data which are 7 days old and hence want to remove those rows.

How do I do remove the rows efficiently from this KV store based lookup. I know the classic way to do this would be to search for rows that are newer than 7 days and output the results to the same lookup. Something like,

inputlookup my_lookup | where date > now()-7 days | outputlookup my_lookup

But given that the lookup contains tons of rows, I would like to do this efficiently and remove just the old rows instead of writing the entire result set again.

This should be possible with KV store but I am unable to figure out how.

Re: How to efficiently delete rows from KV Store based lookup.

rnowitzki — Tue, 01 Sep 2020 10:12:01 GMT

Hi @iet_ashish ,

Besides the input-/outputlookup I am only aware of using REST. Check this thread for a solution.
You could run it as a cron maybe.

In the example they use LastUpdateTime with an exact epoch timestamp. I am not sure if you can use something like -7d.

If you have to give a unix timestamp, you could calculate it on the CLI and call the command in the crontable with a variable for the timestamp.

BR
Ralph

Re: How to efficiently delete rows from KV Store based lookup.

iet_ashish — Wed, 02 Sep 2020 06:34:26 GMT

Thanks @rnowitzki for replying.

Is there anything apart from REST API to delete rows from KV store. I was looking for an approach where SPL could be leveraged.

Also, you are right about the -7d thing. I used it just to get my point across. In practice, I would have to use epoch time.

Re: How to efficiently delete rows from KV Store based lookup.

rnowitzki — Wed, 02 Sep 2020 07:47:34 GMT

Hi @iet_ashish,

You might want to try the rest command. I never used it, but if you can call the uri that deletes the rows with the command, you could stay in the SPL world.

https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/Rest

Let us know if it works.

BR
Ralph