Alerts - job alerts on a table/report

Question

hello guys,

here is my predicament.

we have emails alerting us of jobs that failed on a regular basis.

this is causing 'alert burning/annoying/'what da heck feeling'

instead, i would like to have a way of collecting the jobs that have failed for each servers instance with their details.

so i am looking for a table (exportable to CSV/Spreadsheet) containing

server instance

job error details

in addition, to details available on =:

<DPA_SERVER_IP>:8123/iwc/alertMain.iwc?tab=0&db_id=&pm=P

since this info is already collected by DPA.

i could run a query on its repository (until this request becomes a feature)!!

*** this is my question ***

could i please ask for advice on how i can use the information collected on job failures' alerts (using sqlserver) from DPA's repository?

thanks,

nicolasdiogo · Accepted Answer

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

Alerts - job alerts on a table/report

Top Replies