How to Schedule a job to delete 30 days older records from KV Store?

[rishiaggarwal]

Hi All,

I have created a KV store which receives 100,000 records daily. I need only 30 days of historical data to produce a report. KV Store has a unique key (_key) for each record. I knew in the past we can't use saved search you to delete selected records from KV Store. Can anyone suggest me the way of deleting selected records (older than 30 days), without manual intervention? Can REST call be scheduled to delete them?

Thanks in advance.

[dmarling · Accepted solution]

Do you have any timestamps on the events in the KV or a way to calculate what events were written when? If so it would be easy to setup a scheduled search that runs once a day that filters out events that are > 30 days ago. Conceptually it would be this if you have it attached to a lookup definition:

| inputlookup KVStoreLookupDefinition
| where epochtimefield>=relative_time(now(), "-30d@d")
| outputlookup KVStoreLookupDefinition

[dinesh_cemad]

hi @dmarling

Does this retain the _key field?

[rishiaggarwal]

Can you please post that as an answer so that I can accept the answer and share some award points. Thanks.

[dmarling]

Done. Thank you for reminding me to do this.

[rishiaggarwal]

Simple and Effective solution. Thanks for your assistance.

[dinesh_cemad]

hi @dmarling

Does this keep _key field values?

[manjunathmeti]

If | inputlookup KVStoreLookupDefinition contains _key field then it will keep otherwise this query overwrites _key field. If you are using any field from your data as _key then you can use key_field in outputlookup.

| inputlookup KVStoreLookupDefinition
 | where epochtimefield>=relative_time(now(), "-30d@d")
 | outputlookup key_field=<field> KVStoreLookupDefinition

[dmarling]

This is a better response. Thanks @manjunathmeti 🙂

[manjunathmeti]

You are welcome @dmarling 🙂