-- Only copy is supported in the current format which would be sufficient to export to the local file system
-- https://www.postgresqltutorial.com/export-postgresql-table-to-csv-file/
--
-- Suggested Usage:
-- place in a file on the MC file system, e.g. /opt/waratek/db/test.sql and run as root using:
-- $ su postgres -c "psql -d management_console -f /opt/waratek/db/test.sql"
--
-- Explore the options described below until you find a suitable output
--
copy (
-- events can be grouped by:
	-- instance: separate row for each instance even if the events are the same as others in the same application (default)
	-- application: the same events for different instances are grouped by application (comment out the 2 '#instance' sections)
select
	-- #instance start
	e.instance_id,
	i.name as instance, 
	-- #instance end
	i.application_id, 
	a.name as application,
	e.message, count(*),
	min(e.triggered_date) as start,
	max(e.triggered_date) as end
from event e
left join instance i on i.instance_id = e.instance_id
left join application a on a.application_id = i.application_id
where 1 = 1
	-- To restrict by event triggered date uncomment/edit the appropriate line below
	--and e.triggered_date >= '2020-09-21'
	--and e.triggered_date between '2020-09-21' and '2020-10-21'
	-- To restrict by application ids uncomment/edit the line below with comma separated list of ids
    --and i.application_id in (1,2)
	-- To restrict by full application names uncomment/edit the line below with comma separated list of names
	-- and a.name in ('app1','app2')
	-- To restrict by partial application names uncomment/edit the line below, '%' serves as a wildcard
	--and a.name like 'app%'
	-- The 2 application name restrictions above can be applied to instance names, just replace 'a.name' with 'i.name'
	-- To filter out unassigned instances uncomment the line below
	-- and i.application_id is not null
	-- To filter out deleted instances uncomment the line below
    --and i.status_code <> 'DELETED'
	-- To restrict by instance ids uncomment/edit the line below with comma separated list of ids
    --and i.instance_id in (1,2)
    -- To restrict by rules in Allow mode only uncomment the line below
    --and e.message like 'ALLOW%'
	-- To filter out lifecycle events uncomment the line below
    --and e.message not like 'CEF%'
group by 
	-- #instance start
	e.instance_id,
	i.name, 
	-- #instance end
	i.application_id,
	a.name,
	e.message
-- can be sorted by any column in the select, just replace 'count'
order by count desc
) to '/tmp/events.csv' DELIMITER ',' CSV HEADER