2 Replies Latest reply: Dec 11, 2012 3:27 AM by lukas.cerny RSS

Transaction alert logic based on name, description fields

cdenuyl

We have a custom SQL alert that relies on "three strikes" logic from 3 playback servers.  It looks for Down or Unknown status on steps 1 and 2.  Right now it's a catch-all alert - every transaction qualifies.  I'd like the ability to target (or exclude) specific transactions based on different criteria, including transaction name and description.  Based on the SQL below, what can I add to filter based on transaction name or description?  These are the fields I have complete control over, without adding custom properties as with NPM, SAM, etc.  I have considered adding phrases like "Datacenter=blah" or "Production" to the Description field, to enhance categorization and target alerts appropriately.

 

Trigger Query = Transaction

 

WHERE

(

  SELECT COUNT(DISTINCT tad.TransactionId) AS FailingTransactionsOnFirstStep

  FROM SEUM_TransactionsAlertsData tad JOIN SEUM_TransactionStepsAlertsData tsad

   ON tad.TransactionId = tsad.TransactionId

  JOIN SEUM_RecordingSteps rs

   ON rs.StepId = tsad.StepId

  WHERE tsad.Status in (0,2)

  AND tad.RecordingId = SEUM_TransactionsAlertsData.RecordingId

  AND rs.StepOrder in (1,2)

)

=

(

  SELECT COUNT(DISTINCT tad2.TransactionId) AS AllTransactions

  FROM SEUM_TransactionsAlertsData tad2

  WHERE tad2.RecordingId = SEUM_TransactionsAlertsData.RecordingId

)

AND SEUM_TransactionsAlertsData.TransactionId = (

  SELECT MIN(tad3.TransactionId) AS FirstTransaction

  FROM SEUM_TransactionsAlertsData tad3

  WHERE tad3.RecordingId = SEUM_TransactionsAlertsData.RecordingId

)

 
  • Re: Transaction alert logic based on name, description fields
    lukas.cerny

    Hello Christian,

     

    you can try the following query. Alert should be triggered only when transactions which contain text Production in their description and are based on the same recording are all down. Due to the change which was made to your query you'll need to set also a custom reset query.

     

    So for the trigger condition use the following query:

    WHERE  (SELECT COUNT(DISTINCT tad.TransactionId) AS FailingTransactionsOnFirstStep
            FROM   SEUM_TransactionsAlertsData AS tad
                   INNER JOIN
                   SEUM_TransactionStepsAlertsData AS tsad
                   ON tad.TransactionId = tsad.TransactionId
                   INNER JOIN
                   SEUM_RecordingSteps AS rs
                   ON rs.StepId = tsad.StepId
            WHERE  tsad.Status IN (0, 2)
                   AND tad.RecordingId = SEUM_TransactionsAlertsData.RecordingId
                   AND rs.StepOrder IN (1, 2)
                   AND tad.Description LIKE '%Production%') = (NULLIF ((SELECT COUNT(DISTINCT tad2.TransactionId) AS AllTransactions
                                                                        FROM   SEUM_TransactionsAlertsData AS tad2
                                                                        WHERE  tad2.RecordingId = SEUM_TransactionsAlertsData.RecordingId
                                                                               AND tad2.Description LIKE '%Production%'), 0))
           AND SEUM_TransactionsAlertsData.TransactionId = (SELECT MIN(tad3.TransactionId) AS FirstTransaction
                                                            FROM   SEUM_TransactionsAlertsData AS tad3
                                                            WHERE  tad3.RecordingId = SEUM_TransactionsAlertsData.RecordingId)
    

     

    And for the reset condition use the following query:

    WHERE NOT ((SELECT COUNT(DISTINCT tad.TransactionId) AS FailingTransactionsOnFirstStep
                 FROM   SEUM_TransactionsAlertsData AS tad
                        INNER JOIN
                        SEUM_TransactionStepsAlertsData AS tsad
                        ON tad.TransactionId = tsad.TransactionId
                        INNER JOIN
                        SEUM_RecordingSteps AS rs
                        ON rs.StepId = tsad.StepId
                 WHERE  tsad.Status IN (0, 2)
                        AND tad.RecordingId = SEUM_TransactionsAlertsData.RecordingId
                        AND rs.StepOrder IN (1, 2)
                        AND tad.Description LIKE '%Production%') = (NULLIF ((SELECT COUNT(DISTINCT tad2.TransactionId) AS AllTransactions
                                                                             FROM   SEUM_TransactionsAlertsData AS tad2
                                                                             WHERE  tad2.RecordingId = SEUM_TransactionsAlertsData.RecordingId
                                                                                    AND tad2.Description LIKE '%Production%'), 0))
                AND SEUM_TransactionsAlertsData.TransactionId = (SELECT MIN(tad3.TransactionId) AS FirstTransaction
                                                                 FROM   SEUM_TransactionsAlertsData AS tad3
                                                                 WHERE  tad3.RecordingId = SEUM_TransactionsAlertsData.RecordingId))
    

     

    Let me know if you need anything else.

     

    Lukas