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.

So looking at this again. In “Report Templates”. I see the “Host - All Installed Packages” template and thinking I can clone this one and modify the code to give me a csv list of host and package version matching a certain package name,

name: Host - All Installed Packages
snippet: false
- name: Hosts filter
  required: false
  input_type: user
  description: Limit the report to only hosts found by this search query. Keep empty
    to report on all available hosts.
  advanced: false
  value_type: search
  resource_type: Host
model: ReportTemplate
- plugin: katello
  version: 4.7.0
<%  load_hosts(search: input('Hosts filter')).each_record do |host| -%>
<%    host.installed_packages.each do |pkg| -%>
<%-   report_row(
        'Package Name':,
        'Package NVRA': pkg.nvra,
        'Package NVREA': pkg.nvrea
<%    end -%>
<%    host.installed_debs.each do |pkg| -%>
<%-   report_row(
        'Package Name':,
        'Package Version': pkg.version,
        'Package Architecture': pkg.architecture
<%    end -%>
<%  end -%>
<%= report_render -%>

Anyone with some experience with this “report syntax”?

Also if I get a report to show what I want, how would I send out a report like that once a week by email?

seen Look for create report template to list hosts with an installed package and borrowed the code from this one. It actually gave me a the needed list. Also works well to send the report to an email address. So to schedule such a report it seems hammer is my only option so managed to send a report using:

hammer report-template schedule --id 215 --inputs="packages=mariadb-server" --mail-to "" --gzip false

My only problem now is I would like to add another package to the packages argument but I am not able to figure out the syntax. Feels I have tried 100 different methods and all ending up with “Error: Option ‘–inputs’: Value must be defined as a comma-separated list of key=value or valid JSON…”
Can anyone help?

Should be something like (all below obviously fails):

 hammer report-template schedule --id 215 --inputs="packages=mariadb-server,mysql-server" --mail-to "" --gzip false
 hammer report-template schedule --id 215 --inputs="packages='mariadb-server,mysql-server'" --mail-to "" --gzip false
 hammer report-template schedule --id 215 --inputs='packages="mariadb-server,mysql-server"' --mail-to "" --gzip false

Also tried with json format:

hammer report-template schedule --id 215 --inputs='{"packages": ["package1", "package2"]}'

Hammer take the command with but fails with:

Error performing TemplateRenderJob (Job ID: 1278a246-a6a3-4c6f-aa49-6ee397713eda) from Dynflow(default) in 9086.81ms: Safemode::NoMethodError (undefined method '#split' for Array::Jail (Array)):

Managed to find the correct syntax:

hammer report-template schedule --id 215 --inputs='{"packages": "package1,package2"}' --mail-to "" --gzip false