CLI query to display unregistered hosts

Support
1

Problem:
In the “Content Hosts” section of the web interface there is a “Registered” column that displays the date that a host was registered, or is blank if the host is not registered. I’ve been unable to find a method either via the command line or REST API to display this same information. How can this be done?

Expected outcome:
We have large numbers of systems that are not registered (not sure how this came to be) that need to be deleted, but using the web interface to do so is cumbersome. I need a simple list of unregistered systems (which is different than systems that have not reported via “last check in”) that can be used to perform a batch delete operation.

Foreman and Proxy versions:
foreman-3.12.0.7-1.el8sat.noarch
foreman-proxy-3.12.0-1.el8sat.noarch

Foreman and Proxy plugin versions:
?

Distribution and version:
Red Hat Enterprise Linux 8.10 (Ootpa)
Red Hat Satellite (build: 6.16.4)

Other relevant data:

2

I may have found the answer to my own question. Can anyone verify whether the following query is barking up the right tree and produces accurate results?:

echo "select h.name from hosts h left join katello_subscription_facets s on s.host_id = h.id where s.host_id is null order by h.name" | su - postgres -c "psql -t -d foreman"
3

In our code, our test for “is this host registered?” is the presence of host > subscription_facet > uuid. But unregistered hosts probably won’t have a subscription facet at all.