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.