Find, and purge, series that have no updates in X amount of days

I know this has been asked before, but I haven’t seen a solution. How can I find series that have had no updates in X amount of days, and then purge them? I don’t want to create a retention policy that just purges data after X days, only for those hosts that have not (and will not) send any more data.

Use case: I use AWS/EC2 and have autoscaling groups – once an instance is “scaled down” (terminated) it will no longer be sending data (via telegraf) to influxdb. Over time, this creates a lot of “dead” series that then clutter the database, and therefore my Grafana dashboards.

I’ve tried this query:

select host,last(uptime) from system where time > now() - 30d group by host;

But it doesn’t seem to work - I see hosts returned that I know are still sending system metrics. I know how to drop the series, I just need to get the list of hosts to purge, and I can’t figure out how to properly get that.

Hi @Dayton_Jones ,welcome !

I only know a way to find the last point

select host from system group by host order by desc limit 1 ; // select at least 1 field as well

hope this helps a bit …