ETL for Reading ACL Analytics Exchange Server Job Logs (with AX Exception integration)

So you want have a machine way of reading the Job logs from ACL Analytics Exchange Server 3 or 4? Here's a SQL that lets you find out what analytic was started when and if any results were pushed to AX Exception: (Connect to the PostgreSQL database holding the backend of AX, by default it was called AclAuditExchangeDB in AX 3).

select t1.starttime, t2.name as analytic_name, 
t4.name as analytic_project, t6.name as activity, 
t7.name as engagement, t3.resulttable, t3.destinationentity, 
t3.destinationanalytic 
from scriptjobs t1
left outer join
audititems t2
on t1.analyticid = t2.id
left outer join
scriptjobpublish t3
on t1.jobnumber = t3.jobnumber
left outer join
audititems t4
on t2.parentid = t4.id
left outer join
audititems t5
on t4.parentid = t5.id
left outer join
audititems t6
on t5.parentid = t6.id
left outer join
audititems t7
on t6.parentid = t7.id

where t1.starttime > '2014-10-01'
order by engagement, activity, t1.starttime