
synopsis:

	System event history

description:

	Event history for each system

columns:

	server_id:i	System identifier
	hostname	hostname
	event_id:i	Event id
	time		Time of event
	status		Status of the event
	event		Type of the event
	event_data	Additional parameters / details for the event

multival_columns:

	event_id
	event_data : event_data

sql:

	select server_id, hostname, event_id, time, status, event, event_data
	from (
		select rhnserveraction.server_id,
			rhnserver.hostname,
			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
				when rhnactiontype.label = 'kickstart.initiate' then (
					select distinct rhnksdata.label
					from rhnkickstartsessionhistory, rhnkickstartsession, rhnksdata
					where rhnserveraction.action_id = rhnkickstartsessionhistory.action_id
						and rhnkickstartsessionhistory.kickstart_session_id = rhnkickstartsession.id
						and rhnkickstartsession.kickstart_id = rhnksdata.id
					)
				when rhnactiontype.label = 'scap.xccdf_eval' then (
					select distinct rhnxccdftestresult.identifier
					from rhnxccdftestresult, rhnactionscap
					where rhnaction.id = rhnactionscap.action_id
						and rhnxccdftestresult.action_scap_id = rhnactionscap.id
					)
				end as event_data
		from rhnserveraction left outer join rhnactionerrataupdate
			on rhnserveraction.action_id = rhnactionerrataupdate.action_id
			inner join rhnserver 
				on rhnserveraction.server_id = rhnserver.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, 
			rhnserver.hostname,
			rhnserverhistory.id,
			to_char(greatest(rhnserverhistory.created, rhnserverhistory.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 inner join rhnserver on rhnserverhistory.server_id = rhnserver.id
	) X
	-- where placeholder
	order by server_id, time, event_id

