Today I removed devel/freebsd-git-devtools from a buildlist I have in #poudriere - it has been renamed.
I removed it because it wasn't installed on any of my hosts.
I conclude that by this query:
samdrucker=# select host from hostswithpackageshowversion('freebsd-git-devtools');
host
------
(0 rows)
Then I got to thinking: where was it installed and when?
The comment in the buildlist said:
-# for working on FreeBSD
-
-devel/freebsd-git-devtools
I went back to the SamDrucker database and struggled parsing the JSON for a while. Eventually I came up with this inefficient but effective query:
samdrucker=# select date_added, client_ip from incoming_packages where data::text like '%freebsd-git-devtools%' order by date_added desc;
date_added | client_ip
----------------------------+---------------
2024-01-17 03:05:26.208061 | 10.55.0.29/32
2024-01-16 03:24:16.145212 | 10.55.0.29/32
2024-01-15 03:39:19.388747 | 10.55.0.29/32
2024-01-14 03:58:35.967538 | 10.55.0.29/32
...
2023-04-01 03:35:54.836752 | 10.55.0.29/32
2023-03-31 03:59:51.397951 | 10.55.0.29/32
2023-03-30 03:52:11.768713 | 10.55.0.29/32
That tells me it was installed on one host for about 10 months. That host is pkg01, my package build server.
This type of search was one of the orignal goals for SamDrucker, but having historical data has helped me here.
SamDrucker was written for FreeBSD, but it wouldn't take much to port. Both client and server are lightweight and easily understood.
The PostgreSQL function might take a bit of work if you want to migrate away from that choice. The reset is all shell/lua.
https://github.com/dlangille/SamDrucker