[Satellite6] How to list Content view versions and how many hosts are using them

Environment

Red Hat Satellite 6

Issue

If you want to get information about how many clients are using which versions of which Content Views.

Resolution

Execute command below for specified version Satellite:

Satellite 6.3-6.13

# su - postgres -c "psql foreman -c \"SELECT cv.id as cv_id, cvv.id as cvv_id, cv.name as cv_name, cvv.major, cvv.minor, le.name as le_name, count(kcf.*) FROM katello_content_view_versions cvv FULL OUTER JOIN katello_content_views cv ON cv.id = cvv.content_view_id FULL OUTER JOIN katello_content_view_environments cvle ON cvle.content_view_id = cv.id AND cvle.content_view_version_id = cvv.id LEFT JOIN (SELECT content_view_id, lifecycle_environment_id from katello_content_facets) kcf ON kcf.content_view_id = cv.id AND kcf.lifecycle_environment_id = cvle.environment_id FULL OUTER JOIN katello_environments le ON le.id = cvle.environment_id WHERE cv.id IS NOT NULL GROUP BY cv.id, cvv.id, cv.name, cvv.major, cvv.minor, le.name ORDER BY cv.name, cvv.major, cvv.minor;\""

The SQL command formatted in more human-readable form:

SELECT cv.id as cv_id, cvv.id as cvv_id, cv.name as cv_name, cvv.major, cvv.minor, le.name as le_name, count(kcf.*)
  FROM katello_content_view_versions cvv
FULL OUTER JOIN katello_content_views cv
  ON cv.id = cvv.content_view_id
FULL OUTER JOIN katello_content_view_environments cvle
  ON cvle.content_view_id = cv.id AND cvle.content_view_version_id = cvv.id
LEFT JOIN (SELECT content_view_id, lifecycle_environment_id from katello_content_facets) kcf
  ON kcf.content_view_id = cv.id AND kcf.lifecycle_environment_id = cvle.environment_id
FULL OUTER JOIN katello_environments le
  ON le.id = cvle.environment_id
WHERE cv.id IS NOT NULL
GROUP BY cv.id, cvv.id, cv.name, cvv.major, cvv.minor, le.name
ORDER BY cv.name, cvv.major, cvv.minor;

Satellite 6.14-6.16

# echo 'select katello_content_views.name, katello_environments.name "Environment", count(hosts.name) "Host Count" from katello_content_facets
        inner join katello_content_view_environment_content_facets
                ON katello_content_view_environment_content_facets.content_facet_id = katello_content_facets.id
        inner join katello_content_view_environments
                ON katello_content_view_environments.id = katello_content_view_environment_content_facets.content_view_environment_id
        inner join katello_environments
                ON katello_environments.id = katello_content_view_environments.environment_id
        inner join katello_content_views
                ON katello_content_views.id = katello_content_view_environments.content_view_id
        inner join hosts
                ON hosts.id = katello_content_facets.host_id
        group by katello_content_views.name,katello_environments.name;' | su - postgres -c "psql foreman"

Diagnostic Steps

You should see output as below for Satellite 6.3-6.13:

 cv_id | cvv_id |          cv_name          | major | minor | le_name | count
-------+--------+---------------------------+-------+-------+---------+-------
    40 |     90 | my_cv_1                   |     1 |     0 |         |     0
    40 |     92 | my_cv_1                   |     1 |     1 |         |     0
    40 |     94 | my_cv_1                   |     1 |     2 | Library |     0
    32 |     79 | my_cv_2                   |     1 |     0 | Library |     0
    39 |     89 | my_content_view           |     1 |     0 |         |     0
    39 |     91 | my_content_view           |     1 |     1 |         |     0
    39 |     93 | my_content_view           |     1 |     2 |         |     0
    39 |     95 | my_content_view           |     1 |     3 | Dev     |     0
    39 |     96 | my_content_view           |     2 |     0 | Library |     0
     1 |      1 | Default Organization View |     1 |     0 | Library |     6
    42 |        | unpublished_cv            |       |       |         |     0
(11 rows)

cv_id = Content View ID
cvv_id = Content View version ID (can be used e.g. for hammer to manage the version)
cv_name = Content View (CV) name
major = Major version of Content View
minor = Minor version of Content View
le_name = Lifecycle Environment (LE) name where the CV version is published to
count = count of hosts which are using CV in major version x and minor version y

For example from output above you can see that my_cv_2 is published to Library in version 1.0, but it is not used by any host. If same CV name (esp. the Default Organization View) is listed multiple times with different cv_id, it refers to the default CV in different Organizations (see cv.organization_id attribute). A CV not published to any LE has some blank values in above output, like unpublished_cv has.

You will see output as below for Satellite 6.14-6.16:

           name            | Environment | Host Count 
---------------------------+-------------+------------
 Default Organization View | Library     |          4
 my_cv_1                   | ENG         |          1

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.