Search with result that shows all installed versions of a package on all hosts

I can not seem to find a good place to write in a search query that gives me back a list of all installed versions of a package on all hosts.
Query “installed_package ~ openssh”
Result should be something like this:
host1 openssh-7.4p1-21.el7.x86_64
host2 openssh-7.4p1-22.el7_9.x86_64

I know I can do this with remove execution, with something like “rpm -qa | grep ^openssh” but since Foreman has all the information, I figured I should be able to get this result from the database.

Looking in hammer also to get this done but can not seem to figure out how to perform the search. In Content → Packages, a search only give me a list of all available packages, not the installed ones. Searching in the Hosts → All Hosts just give me a list of hosts that has package installed but does not return the version.

Comparing it with the “Advanced Search” in Spacewalk where you can search for installed packages and it returns a list of all the hosts + package name and version that match the string.

You can get the list of installed packages and versions for a particular host on the host details page (new UI) or content host details page.

However, currently I don’t know of a way to get exactly what you’re looking for, short of an SQL join query.

Since recently moving from Spacewalk to Foreman, I’ve also been racking my brain trying to figure out how to do this, and failing. In one way it’s a relief to know it’s not just me, but in another sense it’s disappointing since it’s (for me) such a common question I get asked.

Is there a place for feature requests?

You may be interested in this new report template coming in Katello 4.7 (Foreman 4.5) - Feature #29590: new report template to list all the installed packages - Foreman

demo here:

Not that it is what I was looking for but perhaps it is possible to create another template with a custom filter on installed packages + hosts would do it calling it “Installed package on hosts”. Have not really looked much on report templates yet but feels like that could be the way to get this done.