while i wait for a better alternative this is what i have done.
created a function that returns the data set that we require
dump this into Excel
it would be nice if i could use this data set as a basis for a report in DPA!
ALTER FUNCTION adhoc.UDF_ALERT_JOBFAILURES ( @LAST_N_DAYS INT = 0 ) /* CREATED BY: nicolas @ brainpowered.net CREATED ON: 20141201 DESCRIPTION: list of sqlserver job that have failed by server with job error description V1: added parameter to filter by "last <n> days", from request */ RETURNS TABLE RETURN ( SELECT ignite.CON_ALERT.ALERTNAME , ignite.CON_ALERT_HISTORY.DBNAME AS SQLSERVER_INSTANCE , ignite.CON_ALERT_HISTORY.ACTIONDATE AS ALERT_DTTM , ignite.CON_ALERT_HISTORY.LEVELNAME AS ALERT_LEVEL , ignite.CON_ALERT_HISTORY_RESULTS.PARAMETERNAME AS ALERT_DESCR FROM ignite.CON_ALERT_HISTORY_RESULTS INNER JOIN ignite.CON_ALERT_HISTORY ON ignite.CON_ALERT_HISTORY_RESULTS.HISTORYID = ignite.CON_ALERT_HISTORY.HISTORYID INNER JOIN ignite.CON_ALERT ON ignite.CON_ALERT_HISTORY.ALERTID = ignite.CON_ALERT.ID WHERE (ignite.CON_ALERT.TEMPLATEID = 33) AND ( DATEDIFF( DAY, Ignite.CON_ALERT_HISTORY.ACTIONDATE, CURRENT_TIMESTAMP ) <= @LAST_N_DAYS ) /* ORDER BY SQLSERVER_INSTANCE, ALERT_DTTM DESC */ ) GO SELECT * FROM dpa_ignite_repository.adhoc.UDF_ALERT_JOBFAILURES( 1 ) ORDER BY SQLSERVER_INSTANCE, ALERT_DTTM DESC
1 of 1 people found this helpful
It looks like you did a good job here of pulling the data from the repository. When looking at your first reply I would have suggested pulling this data from the repository from this same table set. Reports mentioned as custom are really just queries against our repository. Please feel free to turn this in as a feature request Database Performance Analyzer Feature Requests