Miscellaneous Ramblings on Hacking

September 3, 2010

Unica Monitoring SQL

Filed under: Unica — Zack Bethem @ 11:43 pm

Doing some Unica troubleshooting today and wrote this little SQL gem. It’s a basic query that does a fine job of retrieving run log errors for flowchart runs. I figure it could come in handy as an Administrator asking the question:

What are my most common errors and where should I focus my troubleshooting?

The SQL:

select to_char(r.runstarttime,'yyyy-mm-dd') runday
, c.name campaign, f.name flowchart
, substr(rr.resultxml,instr(rr.resultxml,'<Process>')+9, instr(rr.resultxml,'<RunStatusCode>')-(instr(rr.resultxml,'<Process>')+9)) as process_name
, substr(rr.resultxml,instr(rr.resultxml,'<ErrorMessage>')+14, instr(rr.resultxml,'</ErrorMessage>')-(instr(rr.resultxml,'<ErrorMessage>')+14)) as result_error
, count(*)
from uacsys.ua_ccrunlog r
join uacsys.ua_runresult rr on r.runid=rr.runid
join uacsys.ua_flowchart f on r.flowchartid=f.flowchartid
join uacsys.ua_campaign c on f.campaignid=c.campaignid
where r.runstatus = 'Run Failed'
and r.runstarttime >to_date('2010-08-01','yyyy-mm-dd')
and instr(rr.resultxml,'<ErrorMessage>') &gt; 0
group by to_char(r.runstarttime,'yyyy-mm-dd')
, c.name, f.name
, substr(rr.resultxml,instr(rr.resultxml,'<Process>')+9, instr(rr.resultxml,'<RunStatusCode>')-(instr(rr.resultxml,'<Process>')+9))
, substr(rr.resultxml,instr(rr.resultxml,'<ErrorMessage>')+14, instr(rr.resultxml,'</ErrorMessage>')-(instr(rr.resultxml,'<ErrorMessage>')+14))
order by runday desc


This in turn creates some nice little results for analysis:


Blog at WordPress.com.