2 Replies Latest reply on Dec 5, 2014 4:34 PM by jaminsql

    Alerts - job alerts on a table/report

    nicolasdiogo

      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,

        • Re: Alerts - job alerts on a table/report
          nicolasdiogo

          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