Deleting data that hasn't updated in the last 7 days?

InfluxDB 1
,
1

Hi, first post so apologies before I start.

So ideally I’m trying to delete the host and its associated data where it hasn’t updated any of its metrics in the last 7 days. (null values basically or no value is used)

This comes about when a server fails and is then re-built automatically in the AWS ASG

I can probably do this manually by using the delete command on the server name after interrogating the grafana graphs and seeing the server hasn’t updated any of it’s metrics over the last 7-8 days eg:

DELETE FROM "systems" WHERE "host" = 'server_name'

However this “server_name” is spread across a number of measurements of which “system” is but one, cpu, disk, diskio and processes are others!
To do this manually I would need to enter all these measurement_name values where the host value is present…I can find these by doing this command:

show tag values with key = “host”
Then I can use each name in my measurement_name below:

DROP SERIES FROM <measurement_name[,measurement_name]> WHERE <tag_key>='<tag_value>'

I have since realised that the field I should use to determine the trigger point would be time.
For example instead of using the trigger as a null value, I should be using the trigger point as a calculation between the last timestamp and the current time. If that is greater than 7 days then I should delete it!
However I don’t know the syntax to do a comparison to determine the current system time versus the last timestamp entry of the data as a “where” operator.

So in conclusion then, how do I:

  1. Find and delete all the data linked to that non-updating server as well as the server itself from the database
  2. Specify a “condition” reflecting that it hasn’t updated the time metric for more than 7 days
  3. Potentially write an automated task to do this for me.

Any assistance or even a solution is and will be gratefully appreciated. Thank you all so far much in advance.

2

Hello @sfrench,
I believe you would have to use Kapacitor to do this in 1.x…which is tricky. Have you considered upgrading to 2.x? This would be very achievable with Flux and Tasks.

You could use the stateDuration function in conjunction with conditional logic

You could automatically expire all of your data every 7 days, but if the state did change, then you could write that data to a new bucket to keep it for longer with the to() function.

Does that sound appealing? Do you want help upgrading your instance?

3

Hi Anaisdg,
Thanks for the reply…however not quite what I was looking for.

Basically using the DELETE or DROP commands as above, I was hoping to write the command / script to calculate and identify that if the last timestamp for an entry is greater than 7 days old, then I could delete / drop that entry predicated by the tag “host” ie: server name. Possibly using the operator “LAST()”

I am currently using influx shell version 1.8.3

4

Hello @sfrench,
Yah unfortunately I don’t think that’s possible in 1.x without kapacitor, but I’ll share your question with experts. You can achieve that in 2.x though.

1 Like
5

Is there a way of doing this using a Continuous Query?