Configure Automic to only send alerts after a certain number of job failures?

Question

My intention is to send an email notification of failure only after x number of failures in a row. At this moment my CA automic post process script sends a failure notification for each failure, but I have a use case where in we have to send an email failure notification only if there are 3 failures in a row.

Basically I am trying to save the last 3 runs info and then based on that use if condition to trigger alert after 3 failures in a row but unable to do so.

Below is my post process script I tried and is not working (breaking my head).

Any help on this is really appreciated.

:print &usedhost#
:SET &RUNNR# = SYS_ACT_ME_NR()
:SET &STATUS# = GET_UC_OBJECT_STATUS(JOBS,&RUNNR#)
:print &STATUS#
:IF &STATUS# = '1900' or 'ENDED_OK'
:SET &STATUS# = 'ENDED_OK'
:ELSE
:SET &STATUS# = 'ENDED_NOTOK'
:SET &DUMMYSTATUS# = 'ENDED_OK'
:print "&STATUS#"
:ENDIF
:SET &CURRENTRUNSTATUS# = &CURRENTRUNSTATUS#
:SET &CURRENTRUNSTATUS1# = &CURRENTRUNSTATUS#
:SET &PREVIOUSRUNSTATUS# = &PREVIOUSRUNSTATUS#
:SET &PREVIOUSRUNSTATUS1# = &PREVIOUSRUNSTATUS#
:SET &P2PRUNSTATUS# = &P2PRUNSTATUS#
:SET &P2PRUNSTATUS1# = &P2PRUNSTATUS#
:IF &CURRENTRUNSTATUS# = ""
:SET &CURRENTRUNSTATUS# = &STATUS#
:ELSE
:SET &CURRENTRUNSTATUS# = &STATUS#
:ENDIF
:IF &PREVIOUSRUNSTATUS# = ""
:SET &PREVIOUSRUNSTATUS# = &DUMMYSTATUS#
:ELSE
:SET &PREVIOUSRUNSTATUS# = &CURRENTRUNSTATUS1#
:ENDIF
:IF &P2PRUNSTATUS# = ""
:SET &P2PRUNSTATUS# = &DUMMYSTATUS#
:ELSE
:SET &P2PRUNSTATUS# = &P2PRUNSTATUS1#
:ENDIF
:print "&CURRENTRUNSTATUS#"
:print "&PREVIOUSRUNSTATUS#"
:print "&P2PRUNSTATUS#" ```

Answer 1

Create SQLI Variable "ANDYDROID.TEST.VARA" with the following SQL-Statement

--- ORACLE SQL ---
SELECT 'COUNTER', COUNT(*) COUNTER FROM
(
  SELECT * FROM (
    SELECT 
      AH_IDNR, AH_STATUS 
      FROM AH   
    WHERE 
      -- change "JOBNAME"
      AH_NAME = 'ANDYDROID.TEST.JOBS'
    ORDER BY AH_TIMESTAMP1 DESC
  )
  WHERE 
    ROWNUM <= 3
)
WHERE AH_STATUS != '1900'

In Your POST-SCRIPT get the COUNTER from VARA-Object

:SET &COUNTER# = GET_VAR("ANDYDROID.TEST.VARA", "COUNTER", 2)
:IF &COUNTER# = 3
! SEND_MAIL
: SET &MAILER# = ACTIVATE_UC_OBJECT("YOUR_MAIL_JOB")
:ENDIF