Bug 1642428

I *think* the dashboard widget invokes these queries (for organization id 1):

SELECT COUNT(*) FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."organization_id" = 1;

SELECT COUNT(DISTINCT "hosts"."id") FROM "hosts" LEFT OUTER JOIN "host_status" ON "host_status"."host_id" = "hosts"."id" AND "host_status"."type" IN ('Katello::SubscriptionStatus') WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."organization_id" = 1 AND ((("host_status"."status" = 1)));

SELECT COUNT(DISTINCT "hosts"."id") FROM "hosts" LEFT OUTER JOIN "host_status" ON "host_status"."host_id" = "hosts"."id" AND "host_status"."type" IN ('Katello::SubscriptionStatus') WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."organization_id" = 1 AND ((("host_status"."status" = 0)));

SELECT COUNT(DISTINCT "hosts"."id") FROM "hosts" LEFT OUTER JOIN "host_status" ON "host_status"."host_id" = "hosts"."id" AND "host_status"."type" IN ('Katello::SubscriptionStatus') WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."organization_id" = 1 AND ((("host_status"."status" = 2)));

SELECT COUNT(DISTINCT "hosts"."id") FROM "hosts" LEFT OUTER JOIN "host_status" ON "host_status"."host_id" = "hosts"."id" AND "host_status"."type" IN ('Katello::SubscriptionStatus') WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."organization_id" = 1 AND ((("host_status"."status" = 3)));


while particular content host filters run query:

"hosts"."id" AS t0_r0, "hosts"."name" AS t0_r1, "hosts"."last_compile" AS t0_r2, "hosts"."last_report" AS t0_r3, "hosts"."updated_at" AS t0_r4, "hosts"."created_at" AS t0_r5, "hosts"."root_pass" AS t0_r6, "hosts"."architecture_id" AS t0_r7, "hosts"."operatingsystem_id" AS t0_r8, "hosts"."environment_id" AS t0_r9, "hosts"."ptable_id" AS t0_r10, "hosts"."medium_id" AS t0_r11, "hosts"."build" AS t0_r12, "hosts"."comment" AS t0_r13, "hosts"."disk" AS t0_r14, "hosts"."installed_at" AS t0_r15, "hosts"."model_id" AS t0_r16, "hosts"."hostgroup_id" AS t0_r17, "hosts"."owner_id" AS t0_r18, "hosts"."owner_type" AS t0_r19, "hosts"."enabled" AS t0_r20, "hosts"."puppet_ca_proxy_id" AS t0_r21, "hosts"."managed" AS t0_r22, "hosts"."use_image" AS t0_r23, "hosts"."image_file" AS t0_r24, "hosts"."uuid" AS t0_r25, "hosts"."compute_resource_id" AS t0_r26, "hosts"."puppet_proxy_id" AS t0_r27, "hosts"."certname" AS t0_r28, "hosts"."image_id" AS t0_r29, "hosts"."organization_id" AS t0_r30, "hosts"."location_id" AS t0_r31, "hosts"."type" AS t0_r32, "hosts"."otp" AS t0_r33, "hosts"."realm_id" AS t0_r34, "hosts"."compute_profile_id" AS t0_r35, "hosts"."provision_method" AS t0_r36, "hosts"."grub_pass" AS t0_r37, "hosts"."discovery_rule_id" AS t0_r38, "hosts"."global_status" AS t0_r39, "hosts"."lookup_value_matcher" AS t0_r40, "hosts"."pxe_loader" AS t0_r41, "hosts"."openscap_proxy_id" AS t0_r42, "host_status"."id" AS t1_r0, "host_status"."type" AS t1_r1, "host_status"."status" AS t1_r2, "host_status"."host_id" AS t1_r3, "host_status"."reported_at" AS t1_r4 FROM "hosts" LEFT OUTER JOIN "host_status" ON "host_status"."host_id" = "hosts"."id" AND "host_status"."type" IN ('Katello::SubscriptionStatus') WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."id" IN (  "hosts"."id" FROM "hosts" INNER JOIN "taxonomies" ON "taxonomies"."id" = "hosts"."organization_id" AND "taxonomies"."type" IN ('Organization') WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "taxonomies"."type" IN ('Organization') AND ("taxonomies"."id" = 1 OR "taxonomies"."title" = '1')) AND ((("host_status"."status" = 0))) ORDER BY "hosts"."name" ASC

"hosts"."id" AS t0_r0, "hosts"."name" AS t0_r1, "hosts"."last_compile" AS t0_r2, "hosts"."last_report" AS t0_r3, "hosts"."updated_at" AS t0_r4, "hosts"."created_at" AS t0_r5, "hosts"."root_pass" AS t0_r6, "hosts"."architecture_id" AS t0_r7, "hosts"."operatingsystem_id" AS t0_r8, "hosts"."environment_id" AS t0_r9, "hosts"."ptable_id" AS t0_r10, "hosts"."medium_id" AS t0_r11, "hosts"."build" AS t0_r12, "hosts"."comment" AS t0_r13, "hosts"."disk" AS t0_r14, "hosts"."installed_at" AS t0_r15, "hosts"."model_id" AS t0_r16, "hosts"."hostgroup_id" AS t0_r17, "hosts"."owner_id" AS t0_r18, "hosts"."owner_type" AS t0_r19, "hosts"."enabled" AS t0_r20, "hosts"."puppet_ca_proxy_id" AS t0_r21, "hosts"."managed" AS t0_r22, "hosts"."use_image" AS t0_r23, "hosts"."image_file" AS t0_r24, "hosts"."uuid" AS t0_r25, "hosts"."compute_resource_id" AS t0_r26, "hosts"."puppet_proxy_id" AS t0_r27, "hosts"."certname" AS t0_r28, "hosts"."image_id" AS t0_r29, "hosts"."organization_id" AS t0_r30, "hosts"."location_id" AS t0_r31, "hosts"."type" AS t0_r32, "hosts"."otp" AS t0_r33, "hosts"."realm_id" AS t0_r34, "hosts"."compute_profile_id" AS t0_r35, "hosts"."provision_method" AS t0_r36, "hosts"."grub_pass" AS t0_r37, "hosts"."discovery_rule_id" AS t0_r38, "hosts"."global_status" AS t0_r39, "hosts"."lookup_value_matcher" AS t0_r40, "hosts"."pxe_loader" AS t0_r41, "hosts"."openscap_proxy_id" AS t0_r42, "host_status"."id" AS t1_r0, "host_status"."type" AS t1_r1, "host_status"."status" AS t1_r2, "host_status"."host_id" AS t1_r3, "host_status"."reported_at" AS t1_r4 FROM "hosts" LEFT OUTER JOIN "host_status" ON "host_status"."host_id" = "hosts"."id" AND "host_status"."type" IN ('Katello::SubscriptionStatus') WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."id" IN (  "hosts"."id" FROM "hosts" INNER JOIN "taxonomies" ON "taxonomies"."id" = "hosts"."organization_id" AND "taxonomies"."type" IN ('Organization') WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "taxonomies"."type" IN ('Organization') AND ("taxonomies"."id" = 1 OR "taxonomies"."title" = '1')) AND ((("host_status"."status" = 3))) ORDER BY "hosts"."name" ASC

etc.

These two types of queries must differ, sometimes (and I can see it in some Satellites as well..).

No idea how to trigger the diff, though.

OK, there are 2 bugs:

1) candlepin query is wrong - that is covered by https://bugzilla.redhat.com/show_bug.cgi?id=1643432

2) "Unregistered": this number remains too high even after applying patch from bz1643432. Here the Content Hosts search filter provided is

null? subscription_uuid

but we IMHO forget for "subscription_status = unsubscribed_hypervisor". So the URL link should be e.g. (be aware of spaces in the URL):

https://satellite.example.com/content_hosts?search=null%3F subscription_uuid or subscription_status %3D%20 unsubscribed_hypervisor

?

.. so the change should be in

https://github.com/Katello/katello/blob/master/app/views/dashboard/_subscription_widget.html.erb#L72


Justin, does it make sense? I.e. shall there be a patch like:

--- a/app/views/dashboard/_subscription_widget.html.erb
+++ b/app/views/dashboard/_subscription_widget.html.erb
@@ -64,12 +64,12 @@
       </tr>
       <tr>
         <td>
-          <%= link_to("#{subscription_status_url}" + 'null? subscription_uuid') do %>
+          <%= link_to("#{subscription_status_url}" + ERB::Util.url_encode('null? subscription_uuid or subscription_status = unsubscribed_hypervisor')) do %>
               <i class="label label-danger" style="margin-right: 6px">&nbsp;</i><%= _("Unregistered") %>
           <% end %>
         </td>
         <td style="text-align:right">
-          <%= link_to( "#{unregistered_consumer_count}", "#{subscription_status_url}" + 'null? subscription_uuid')%>
+          <%= link_to( "#{unregistered_consumer_count}", "#{subscription_status_url}" + ERB::Util.url_encode('null? subscription_uuid or subscription_status = unsubscribed_hypervisor'))%>
         </td>
       </tr>
       <tr>

?

I believe this was resolved as part of https://bugzilla.redhat.com/show_bug.cgi?id=1557002

The problem was that the count included some 'hypervisor hosts', but these were not called out and were not included in the linked results.   That bz will be released in Satellite 6.6

*** This bug has been marked as a duplicate of bug 1557002 ***