reporting/reports/data/system-history | 82 ++++++++++++++++++++++++++++++++++
1 file changed, 82 insertions(+)
New commits:
commit 1c6d11e57b19358dc7f6242972b0391882d6dbce
Author: Jan Pazdziora <jpazdziora(a)redhat.com>
Date: Mon Feb 21 16:13:34 2011 +0100
Reporting: system-history proof of concept report.
diff --git a/reporting/reports/data/system-history
b/reporting/reports/data/system-history
new file mode 100755
index 0000000..e9af005
--- /dev/null
+++ b/reporting/reports/data/system-history
@@ -0,0 +1,82 @@
+
+synopsis:
+
+ System event history
+
+description:
+
+ Event history for each system. Proof of concept.
+
+columns:
+
+ server_id System identifier
+ event_id Event id
+ time Time of event
+ status Status of the event
+ event Type of the event
+ event_data Additional parameters for the event
+
+multival_columns:
+
+ event_id
+ event_data : event_data
+
+sql:
+
+ select server_id, event_id, time, status, event, event_data
+ from (
+ select rhnserveraction.server_id,
+ rhnserveraction.action_id as event_id,
+ to_char(rhnserveraction.completion_time, 'YYYY-MM-DD HH24:MI:SS') as time,
+ rhnactionstatus.name as status,
+ case when rhnactiontype.name = 'Package Install' and
rhnactionpackage.parameter = 'upgrade' then 'Package Upgrade'
+ else rhnactiontype.name end as event,
+ case when rhnactiontype.name = 'Errata Update' then rhnerrata.advisory
+ when rhnactiontype.name in ( 'Package Install', 'Package Removal',
'Verify deployed packages' ) then rhnpackagename.name
+ || case when rhnpackageevr.id is not null then '-' ||
evr_t_as_vre_simple(rhnpackageevr.evr) end
+ || case when rhnpackagearch.id is not null then '.' || rhnpackagearch.label
end
+ when rhnactiontype.label like 'configfiles.%' then rhnconfigfilename.path
+ end as event_data
+ from rhnserveraction left outer join rhnactionerrataupdate
+ on rhnserveraction.action_id = rhnactionerrataupdate.action_id
+ left outer join rhnactionpackage
+ on rhnserveraction.action_id = rhnactionpackage.action_id
+ left outer join rhnpackagename
+ on rhnactionpackage.name_id = rhnpackagename.id
+ left outer join rhnpackageevr
+ on rhnactionpackage.evr_id = rhnpackageevr.id
+ left outer join rhnpackagearch
+ on rhnactionpackage.package_arch_id = rhnpackagearch.id
+ left outer join rhnerrata
+ on rhnactionerrataupdate.errata_id = rhnerrata.id
+ left outer join rhnactionconfigrevision
+ on rhnserveraction.action_id = rhnactionconfigrevision.action_id
+ and rhnserveraction.server_id = rhnactionconfigrevision.server_id
+ left outer join rhnconfigrevision
+ on rhnactionconfigrevision.config_revision_id = rhnconfigrevision.id
+ left outer join rhnconfigfile
+ on rhnconfigrevision.config_file_id = rhnconfigfile.id
+ left outer join rhnconfigfilename
+ on rhnconfigfile.config_file_name_id = rhnconfigfilename.id
+ , rhnaction, rhnactiontype, rhnactionstatus
+ where rhnserveraction.action_id = rhnaction.id
+ and rhnaction.action_type = rhnactiontype.id
+ and rhnserveraction.status = rhnactionstatus.id
+ union all
+ select rhnserverhistory.server_id,
+ rhnserverhistory.id,
+ to_char(greatest(created, modified), 'YYYY-MM-DD HH24:MI:SS'),
+ 'Done',
+ case when rhnserverhistory.summary like 'subscribed to channel %' then
'Subscribed to channel'
+ when rhnserverhistory.summary like 'unsubscribed from channel %' then
'Unsubscribed from channel'
+ when rhnserverhistory.summary like 'Updated system release %' then
'Updated system release'
+ else trim(upper(substr(rhnserverhistory.summary, 1, 1)) ||
substr(rhnserverhistory.summary, 2)) end,
+ case when summary in ( 'added system entitlement ', 'removed system
entitlement ') then details
+ when summary like 'subscribed to channel %' then details
+ when summary like 'unsubscribed from channel %' then details
+ when summary like 'Updated system release %' then substr(summary, 24)
+ end
+ from rhnserverhistory
+ ) X
+ order by server_id, time, event_id
+
Show replies by date