SELECT 
    DATE_FORMAT(STARTED_TIME, '%m/%d-%H:%i:%s') AS "Start",
    DATE_FORMAT(ENDED_TIME, '%H:%i:%s') AS "End",
	TIME_FORMAT(ROUND(MILLIS/1000,0), '%k:%i:%s' ) AS "Age",
--    SUBSTR(JOB_NAME, 1, 45) AS JOB_NAME,
    REGEXP_REPLACE(job_name, "^(.*)__(.*)__(.*)", '\\2') as job_name, /* needs mariadb v10+, should be ok for Oracle */
    TOTAL_COUNT AS "Tot",
    INSERT_COUNT AS "Ins",
    UPDATE_COUNT AS "Upd",
    DELETE_COUNT AS "Del",
    UNRESOLVABLE_SUBJECT_COUNT AS "UN",
    STATUS AS "Status",
--    JOB_TYPE AS "Job Type",
 	HOST as "Host",  /* important if you have >1 host running loader jobs - is container id in Docker */
    JOB_MESSAGE AS "Job Message"
FROM grouper.grouper_loader_log
WHERE
		started_time >= curdate() - interval 1 day and
		( JOB_TYPE = 'CHANGE_LOG'
	        AND ( STATUS != 'SUCCESS' AND
				(STATUS != 'ERROR' and ((job_name like '%consumer_psp_%' and total_count != 0) ))
	        	-- and (STATUS != 'STARTED' and total_count != 0) 
        	)
		OR (JOB_TYPE not in ('CHANGE_LOG', 'MAINTENANCE')
			AND (job_name not LIKE 'subjobFor%'
				OR (INSERT_COUNT != 0 OR UPDATE_COUNT != 0 OR DELETE_COUNT != 0 OR UNRESOLVABLE_SUBJECT_COUNT != 0)
				)
        	)
        OR (INSERT_COUNT != 0 OR UPDATE_COUNT != 0 OR DELETE_COUNT != 0 OR UNRESOLVABLE_SUBJECT_COUNT != 0)
        OR (job_name = 'CHANGE_LOG_changeLogTempToChangeLog' and STATUS != 'SUCCESS')
        )
ORDER BY  1 DESC, 2 DESC 
;
