On 04/16/2012 11:25 AM, Tomas Lestach wrote:
On Friday 13 of April 2012 17:28:05 Duncan Mac-Vicar P. wrote:
On 04/13/2012 02:30 PM, Tomas Lestach wrote:
A separate query for a single system would definitelly be more efficient.
Looking at what you pointed out in IRC: what happens if the errata is
applied on the client side. So I modified them to look at the package
install time instead:
For the system list:
SELECT DISTINCT S.id, S.NAME,
(SELECT 1
FROM rhnServerFeaturesView SFV
WHERE SFV.server_id = S.id
AND SFV.label = 'ftr_system_grouping') AS selectable
FROM rhnServer S,
rhnErrata E,
rhnServerInfo SI,
rhnServerPackage SP,
rhnPackage P,
rhnActionErrataUpdate EA,
rhnServerAction SA,
rhnErrataPackage EP,
rhnPackageName PN
WHERE S.org_id = :org_id
AND EXISTS (SELECT 1 FROM rhnUserServerPerms USP WHERE
USP.user_id=:user_id AND USP.server_id = S.id)
AND SI.server_id = S.id
AND SP.server_id = S.id
AND P.evr_id = SP.evr_id
AND P.name_id = SP.name_id
AND EA.errata_id = E.id
AND SA.action_id = EA.action_id AND SA.server_id = S.id
AND EP.errata_id = E.id AND EP.package_id = P.id
AND (to_timestamp(S.last_boot) SP.installtime)
AND E.id IN (SELECT EK.errata_id FROM rhnErrataKeyword EK WHERE
EK.keyword = :keyword)
AND PN.id = P.name_id
Duncan,
if an erratum package is installed on the client side using yum, you
definitelly won't have any entry in the
rhnServerAction/rhnAction/rhnActionErrataUpdate tables. I would omit them from
the select.
Basicly you need only (user) servers having any errata packages installed
after the latest reboot, where the appropriate erratum has the need_reboot
flag. (no 'Actions' in the sentence)
I'm not sure, why you join also the rhnPackageName, if you do not really use
it.
Sorry for wasting your time. I pasted directly from the pgadmin3 console.
Attached patch, rebased against master. It contains all suggestions and
fixed some formatting issues not relevant to the patch. Additionally
changed to_timestamp() to something that works in both databases.
Duncan
From 34b8bea2ad8e64a4b9c5359d21ad6d0d480f054f Mon Sep 17 00:00:00 2001
From: Duncan Mac-Vicar P dmacvi...@suse.de
Date: Tue, 17 Apr 2012 15:31:06 +0200
Subject: [PATCH] Show systems that need reboot because of an errata.
---
.../common/db/datasource/xml/System_queries.xml| 55
.../action/systems/RequiringRebootSetupAction.java | 37 +
.../action/systems/sdc/SystemOverviewAction.java |5 ++
.../frontend/strings/jsp/StringResource_en_US.xml | 14 +
.../frontend/strings/nav/StringResource_en_US.xml |6 ++
.../redhat/rhn/manager/system/SystemManager.java | 40 ++
.../webapp/WEB-INF/nav/sitenav-authenticated.xml |1 +
.../WEB-INF/pages/systems/requiringrebootlist.jsp | 25 +
.../webapp/WEB-INF/pages/systems/sdc/overview.jsp |5 ++-
java/code/webapp/WEB-INF/struts-config.xml | 10
10 files changed, 197 insertions(+), 1 deletions(-)
create mode 100644 java/code/src/com/redhat/rhn/frontend/action/systems/RequiringRebootSetupAction.java
create mode 100644 java/code/webapp/WEB-INF/pages/systems/requiringrebootlist.jsp
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
index cdb0cc5..64560e0 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
@@ -757,6 +757,61 @@ ORDER BY UPPER(COALESCE(S.NAME, '(none)')), S.ID
elaborator name=is_virtual_host /
/mode
+query name=having_errata_with_keyword_applied_since_last_reboot_list params=org_id, user_id, keyword
+SELECT DISTINCT S.id, S.NAME,
+ (SELECT 1
+ FROM rhnServerFeaturesView SFV
+ WHERE SFV.server_id = S.id
+ AND SFV.label = 'ftr_system_grouping') AS selectable
+ FROM rhnServer S,
+ rhnErrata E,
+ rhnServerInfo SI,
+ rhnServerPackage SP,
+ rhnPackage P,
+ rhnErrataPackage EP
+ WHERE S.org_id = :org_id
+ AND EXISTS (SELECT 1 FROM rhnUserServerPerms USP WHERE USP.user_id=:user_id AND USP.server_id = S.id)
+ AND SI.server_id = S.id
+ AND SP.server_id = S.id
+ AND P.evr_id = SP.evr_id
+ AND P.name_id = SP.name_id
+ AND EP.errata_id = E.id AND EP.package_id = P.id
+ AND (to_date('1970-01-01', '-MM-DD') + numtodsinterval(S.last_boot, 'second')) lt; SP.installtime
+ AND E.id IN (SELECT EK.errata_id FROM rhnErrataKeyword EK WHERE EK.keyword = :keyword)
+/query
+
+mode name=having_errata_with_keyword_applied_since_last_reboot class=com.redhat.rhn.frontend.dto.SystemOverview
+ query name=having_errata_with_keyword_applied_since_last_reboot_list/
+ elaborator name=system_overview /
+
