PIPER-Rx - Custom Foglight® 5 OEBS Alerts

PIPER-Rx
Oracle E-Business Suite Resource Centre

Foglight® 5 Oracle E-Business Suite Cartridge - How to Generate Custom Alerts

Foglight® 5 Oracle E-Business Suite Cartridge - How to Generate Custom Alerts

After spending many years with Quest Software (great job) as their Oracle E-Business Suite domain expert and designer of their
Oracle E-business Suite monitoring tool offerings (culminating in the postition of Product Manager for same) it would remiss of me
not to show you one of the more powerful but underutilised features of the Foglight® OEBS cartridge.

As I no longer work for Quest Software, the following does not in any way represent the views, recommendations or opinions of Quest Software. I do recall however that this information is in the OEBS module help files (I wrote them!)

A feature of the Foglight® Oracle E-Business Suite cartridge is that if a large number of alerts were going to be generated, Foglight® will create a single summary alert and place that alert in a summary table. If however you use this feature YOUR ENTRIES WILL NOT BE SUMMARISED, so you need to be extremely careful when using this custom alerts feature as there are a couple of places in the Foglight® Oracle E-Business Suite cartridge that have the potential to cause an alert storm if there were a large number of exceptions generated from a single check.

The Foglight® OEBS cartridge's Health Check process wakes up every 5 minutes (from memory) and will scan the content of the summary table for any new entries and then display these on the Foglight® console.

All this is described in the Foglight® Cartridge for Oracle E-Business User Guide - something like “Adding your own Health Check”

The Foglight® summary table name is QUEST_FG_OEBS_ALERT_SUMMARY_T and has four columns:

Timestamp The time the alert was generated - usually sysdate
Alertid Alert ID to appear in the Foglight® alerts console
Severity Warning, Critical, and Fatal (spelling and case sensitive).
Details The alert message that will appear in the Foglight® alerts console

So if you manually add a record to this table the next time the Foglight® Oracle E-Business Suite cartridge health check wakes up the summary message will be sent to the Foglight® alerts console.

To capture the attention of those of you that bore easily, I have historically presented the power of this feature by showing the following humerous example

INSERT into QUEST_FG_OEBS_ALERT_SUMMARY_T
VALUES ( sysdate, ‘OEBS001’, ‘Critical’, ‘Foglight has detected there is insufficient beer in the fridge’);

In a few minutes this alert will show up in the Foglight® console.

Oh the fun…, but remember one person's humour can be another person's insult so it is best to only use this feature for legitimate business purposes.

Please ensure you refrain from using a severity of “Fatal” frivolously as this will generate a Foglight® SLA breach and you will need to explain that one.

By the way, the summary table is self purging, from memory it holds up to 60 days history. You can therefore manually delete an entry if appropriate.

On a more serious note - You can also use this feature to create your own Oracle E-Business Suite Foglight module alerts. All you have to do is write some code that performs your check/s and on exception writes a record to the summary table. The the Foglight® cartridge was deliberately designed this way so you can add your own custom alerts. Note: This feature is only available for the Foglight® Oracle E-Business Suite cartridge as the summary table does not exist in other modules.

THE PLSQL EXAMPLES I HAVE PROVIDED BELOW ARE NOT SUPPORTED OR RECOMMENDED BY PIPER-Rx OR QUEST SOFTWARE. They are provided simply to give you ideas and get you thinking as to how custom alerts might be applied.

The example package piper_rx_fog_custom_alerts.pkb contains four simple examples. These are deliberately simple and may contain PLSQL errors so be warned!

Example 1 – CHECK_ACTIVE_USERS

The first example is an example of a check that is run in 5 minute intervals and checks the number of current user connections alerting when the number of connections exceeds an alert value provided as an input variable. It will continue to alert until the number of connections drops below the threshold.

To run the check every 15 minutes, you could use a database job 

 
  DECLARE
    X NUMBER;
  BEGIN
    SYS.DBMS_JOB.SUBMIT
      (
        job        => X
        what       => 'PIPER_RX_FOG_CUSTOM_ALERTS.CHECK_ACTIVE_USERS (50);',
        next_date  => to_date('01-29-2009 07:00:00','mm/dd/yyyy hh24:mi:ss'),
       ,interval   => 'SYSDATE+15/1440',
        no_parse   => TRUE
      );
    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || TO_CHAR(x));
  END;
  /
This example will continue to alert every check interval whilst the number of users remains above the alert level.

Example 2 – CHECK_COMPLETED_ERROR

The second example shows how to alert only once per day on the first failure.
In this example we will alert when the number of concurrent requests that complete with a status of error exceeds a threshold value provided as an input variable, but not repeat the message every 15 minutes for the rest of the day.

To run the check every 15 minutes, you could use a database job 

  DECLARE
    X NUMBER;
  BEGIN
    SYS.DBMS_JOB.SUBMIT
      (
        job        => X,
        what       => 'PIPER_RX_FOG_CUSTOM_ALERTS.CHECK_COMPLETED_ERROR (10);',
        next_date  => to_date('01-29-2009 07:00:00','mm/dd/yyyy hh24:mi:ss'),
        interval   => 'SYSDATE+15/1440 ',
        no_parse   => TRUE
      );
    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || TO_CHAR(x));
  END;
  /

Example 3 – CHECK_CONCURRENT_PROGRAM

In this example I want the same alert to apply throughout the day but not report on prior alerted items i.e. I want to report every time an FSG is submitted but not report on any prior reported FSGs.

In this example, alerts are keyed on the Foglight® alertid by adding the concurrent request ID to the alert_id. The Foglight alertid will be unique for each FSG and will only report on new FSGs submitted. In this example we also only report on requests submitted after the last time an entry was added to the Foglight® summmary table.

Example 4 – CHECK_FOR_MY_BIRTHDAY

The third example is an example of a daily check. In this example we check for my birthday and alert the administrator to buy me a present :-)

To run the check daily at 7:00am, you could use a database job 

  DECLARE
    X NUMBER;
  BEGIN
    SYS.DBMS_JOB.SUBMIT
      (
        job        => X,
        what       => 'PIPER_RX_FOG_CUSTOM_ALERTS.CHECK_FOR_MY_BIRTHDAY;',
        next_date  => to_date('01-29-2009 07:00:00','mm/dd/yyyy hh24:mi:ss'),
        interval   => 'TRUNC(SYSDATE+1)+7/24',
        no_parse   => TRUE
      );
    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || TO_CHAR(x));
  END;
  /
You can configure the standard Foglight® application so that selected alert IDs (your custom alerts) send notifications (e-mail, SMS etc) to selected individuals. i.e. All alerts with alerted = ‘SPECIAL-001’ are to notify user X

This principle can be used to check and alert on any application or functional activity limited only by your imagination.E.g.

  • Over 200 PO’s were raised yesterday with a value over $5000
  • User X has approved a million dollar PO – Add the PO number as part of the alertid to prevent the same PO being alerted
  • Concurrent Program ABC is running. Put the request ID as part of the alertid to prevent the same request being alerted

Let your imagination run riot.

Last update: May 2009