Overall I have found the PeopleSoft Performance Monitor is worth the trouble of setting up and configuring. It hasn't detracted from system stability or performance, and it does help identify system problems that wouldn't otherwise be apparent. One of my favorite performance monitor success stories was when we discovered that a user was using the system inappropriately and trying to blame the results on a system bug.
On the other hand, I am disappointed in the inability of the Performance Monitor (v 8.45) to notify someone when alarms are raised. As far as I can tell, the only way you can find out about alarms would be to stay logged on and refresh the alarm history page periodically -- hardly a solution after hours or over the weekend.
The ideal solution would be to create a database trigger that would send an e-mail message to the administrator's pager whenever an alarm is inserted into the database. That way the administrator would receive real-time notifications and could respond quickly, possibly before a problem becomes noticeable to the users. This post describes how to accomplish this goal.
Heres a list of functionality I wanted from my trigger:
1) Don't fire unless its an alarm
2) Don't fire if the alarm is related to a Jolt timeout. Most of the time these happen because users inefficient queries time out and I don't want to get paged over the weekend for this reason.
3) Send an e-mail message with relevant information to a user list.
4) Only send an e-mail for the Production domain. Dev/Test domains don't require the same level of support.
5) Only send one e-mail every 10 minutes. Performance Monitor can certainly create hundreds of alerts over a short period of time when things are going bad, so one notification every 10 minutes should be sufficient.
The first step was to figure out how to make a database trigger send an e-mail. Since we are using an Oracle database, I did a Google search for Oracle Database Triggers and E-mail and came up with several good sites. The approach I decided to take is well documented at http://www.dbasupport.com/oracle/ora9i/oracleemail.shtml
The first part of the trigger is as follows:
This creates a trigger called AlarmNotification that fires after each insert on PSPMEVENTHIST, the primary table for PM Events.
The WHEN criteria is:
I got this by SQL Tracing my performance monitor session as I navigated to the Alarm History page and clicked refresh. My "PM_METRIC_VALUE7 not like xxx" statement filters out timeout alarms which meets requirement number 2 above.
The declaration portion of the trigger follows:
l_maicon represents the smtp connection. Alert_datetime provides a variable to hold the last time an alert was e-mailed, and the cursor pulls descriptive text of the event. V_event_descr provides a place to dump the fields from the cursor.
The Begin portion of the trigger starts like this:
Which simply fetches the cursor based on the event row.
Now that we have the cursor information, we need to make sure the alert is related to a production database, and we need to make sure we haven't sent an alert in the last 10 minutes.
Notice I'm reusing a date/time field from PSOPTIONS called STATS_START to keep track of the last time an e-mail was sent. It would probably be better to create a custom one-row table to hold this information, but it didn't look like the field was being used, PSOPTIONS is a convenient one-row table, and I was going for "quick and dirty". Use this at your own risk, though.
OK, the next portion is where we actually build the e-mail and send it using the utl_smtp package.
I won't go into detail about this code since there are so many other good examples on Oracle's web site or other resources on the internet.
Now that the e-mail is sent, we need to update PSOPTIONS.STATS_START to the date/time of the event that were e-mailing.
And thats pretty much it. Heres the full text of the trigger:
IT Consultant
written by Hüseyin Türker, September 24, 2007
...
written by Brent Martin, September 25, 2007
On the other hand, I am disappointed in the inability of the Performance Monitor (v 8.45) to notify someone when alarms are raised. As far as I can tell, the only way you can find out about alarms would be to stay logged on and refresh the alarm history page periodically -- hardly a solution after hours or over the weekend.
The ideal solution would be to create a database trigger that would send an e-mail message to the administrator's pager whenever an alarm is inserted into the database. That way the administrator would receive real-time notifications and could respond quickly, possibly before a problem becomes noticeable to the users. This post describes how to accomplish this goal.
Heres a list of functionality I wanted from my trigger:
1) Don't fire unless its an alarm
2) Don't fire if the alarm is related to a Jolt timeout. Most of the time these happen because users inefficient queries time out and I don't want to get paged over the weekend for this reason.
3) Send an e-mail message with relevant information to a user list.
4) Only send an e-mail for the Production domain. Dev/Test domains don't require the same level of support.
5) Only send one e-mail every 10 minutes. Performance Monitor can certainly create hundreds of alerts over a short period of time when things are going bad, so one notification every 10 minutes should be sufficient.
The first step was to figure out how to make a database trigger send an e-mail. Since we are using an Oracle database, I did a Google search for Oracle Database Triggers and E-mail and came up with several good sites. The approach I decided to take is well documented at http://www.dbasupport.com/oracle/ora9i/oracleemail.shtml
The first part of the trigger is as follows:
CREATE OR REPLACE TRIGGER AlarmNotification
AFTER INSERT ON PSPMEVENTHIST
for each row
This creates a trigger called AlarmNotification that fires after each insert on PSPMEVENTHIST, the primary table for PM Events.
The WHEN criteria is:
when (new.pm_filter_level in ('02', '03') and
new.pm_agentid in (47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,99,104,105,123,124,132,133,134,135)
and new.PM_METRIC_VALUE7 not like '%bea.jolt.JoltRemoteService(ICPanel)call(): Timeout%')
I got this by SQL Tracing my performance monitor session as I navigated to the Alarm History page and clicked refresh. My "PM_METRIC_VALUE7 not like xxx" statement filters out timeout alarms which meets requirement number 2 above.
The declaration portion of the trigger follows:
declare
l_maicon utl_smtp.connection;
alert_datetime date;
CURSOR curs_get_event_descr
IS
SELECT PM_EVENT_DEFN_SET,PM_EVENT_DEFN_ID,DESCR60,PM_EVENT_LABEL,PM_ADDTNL_LABEL,
PM_METRICID_1,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_1) METRICLABEL1,
PM_METRICID_2,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_2) METRICLABEL2,
PM_METRICID_3,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_3) METRICLABEL3,
PM_METRICID_4,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_4) METRICLABEL4,
PM_METRICID_5,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_5) METRICLABEL5,
PM_METRICID_6,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_6) METRICLABEL6,
PM_METRICID_7,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_7) METRICLABEL7,
PM_FILTER_LEVEL,
PM_SAMPLING_ENABLE,
(select dbname from pspmsysdefn b, pspmagent c where c.pm_agentid = :new.PM_AGENTID and b.pm_systemid = c.pm_systemid) DBNAME
FROM PSPMEVENTDEFN a
WHERE PM_EVENT_DEFN_SET=:new.PM_EVENT_DEFN_SET AND PM_EVENT_DEFN_ID=:new.PM_EVENT_DEFN_ID;
v_event_descr curs_get_event_descr%ROWTYPE; l_maicon represents the smtp connection. Alert_datetime provides a variable to hold the last time an alert was e-mailed, and the cursor pulls descriptive text of the event. V_event_descr provides a place to dump the fields from the cursor.
The Begin portion of the trigger starts like this:
begin
OPEN curs_get_event_descr;
FETCH curs_get_event_descr INTO v_event_descr;
CLOSE curs_get_event_descr;Which simply fetches the cursor based on the event row.
Now that we have the cursor information, we need to make sure the alert is related to a production database, and we need to make sure we haven't sent an alert in the last 10 minutes.
if (v_event_descr.DBNAME = 'FPRD88')
then
select STATS_START into alert_datetime
from PSOPTIONS;
if alert_datetime < :new.PM_MON_DTTM - .0034722
thenNotice I'm reusing a date/time field from PSOPTIONS called STATS_START to keep track of the last time an e-mail was sent. It would probably be better to create a custom one-row table to hold this information, but it didn't look like the field was being used, PSOPTIONS is a convenient one-row table, and I was going for "quick and dirty". Use this at your own risk, though.
OK, the next portion is where we actually build the e-mail and send it using the utl_smtp package.
l_maicon :=utl_smtp.open_connection('relay-smtp.acme.com',25);
utl_smtp.helo(l_maicon,'hostname');
utl_smtp.mail(l_maicon,'
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
');
utl_smtp.rcpt(l_maicon,'
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
');
utl_smtp.rcpt(l_maicon,'
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
');
utl_smtp.data(l_maicon,'From:
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' || utl_tcp.crlf||
'To:
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
;
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' || utl_tcp.crlf ||
'Subject: Performance Monitor Alert - ' || v_event_descr.DBNAME || utl_tcp.crlf ||
v_event_descr.METRICLABEL7 || ': ' || :new.PM_METRIC_VALUE7 || utl_tcp.crlf ||
v_event_descr.METRICLABEL2 || ': ' || :new.PM_METRIC_VALUE2 || utl_tcp.crlf ||
v_event_descr.METRICLABEL1 || ': ' || :new.PM_METRIC_VALUE1 || utl_tcp.crlf ||
v_event_descr.METRICLABEL3 || ': ' || :new.PM_METRIC_VALUE3 || utl_tcp.crlf ||
v_event_descr.METRICLABEL4 || ': ' || :new.PM_METRIC_VALUE4 || utl_tcp.crlf ||
v_event_descr.METRICLABEL5 || ': ' || :new.PM_METRIC_VALUE5 || utl_tcp.crlf ||
v_event_descr.METRICLABEL6 || ': ' || :new.PM_METRIC_VALUE6 || utl_tcp.crlf);
utl_smtp.quit(l_maicon);I won't go into detail about this code since there are so many other good examples on Oracle's web site or other resources on the internet.
Now that the e-mail is sent, we need to update PSOPTIONS.STATS_START to the date/time of the event that were e-mailing.
update psoptions set stats_start = :new.PM_MON_DTTM;
end if;
end if;
end;
/
And thats pretty much it. Heres the full text of the trigger:
CREATE OR REPLACE TRIGGER AlarmNotification
BEFORE INSERT ON PSPMEVENTHIST
for each row
when (new.pm_filter_level in ('02', '03') and
new.pm_agentid in (47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,99,104,105,123,124,132,133,134,135)
and new.PM_METRIC_VALUE7 not like '%bea.jolt.JoltRemoteService(ICPanel)call(): Timeout%')
declare
l_maicon utl_smtp.connection;
alert_datetime date;
CURSOR curs_get_event_descr
IS
SELECT PM_EVENT_DEFN_SET,PM_EVENT_DEFN_ID,DESCR60,PM_EVENT_LABEL,PM_ADDTNL_LABEL,
PM_METRICID_1,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_1) METRICLABEL1,
PM_METRICID_2,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_2) METRICLABEL2,
PM_METRICID_3,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_3) METRICLABEL3,
PM_METRICID_4,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_4) METRICLABEL4,
PM_METRICID_5,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_5) METRICLABEL5,
PM_METRICID_6,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_6) METRICLABEL6,
PM_METRICID_7,
(select PM_METRICLABEL from PSPMMETRICDEFN where PM_METRICID = a.PM_METRICID_7) METRICLABEL7,
PM_FILTER_LEVEL,
PM_SAMPLING_ENABLE,
(select dbname from pspmsysdefn b, pspmagent c where c.pm_agentid = :new.PM_AGENTID and b.pm_systemid = c.pm_systemid) DBNAME
FROM PSPMEVENTDEFN a
WHERE PM_EVENT_DEFN_SET=:new.PM_EVENT_DEFN_SET AND PM_EVENT_DEFN_ID=:new.PM_EVENT_DEFN_ID;
v_event_descr curs_get_event_descr%ROWTYPE
begin
OPEN curs_get_event_descr;
FETCH curs_get_event_descr INTO v_event_descr;
CLOSE curs_get_event_descr;
if (v_event_descr.DBNAME = 'FPRD88')
then
select STATS_START into alert_datetime
from PSOPTIONS;
if alert_datetime < :new.PM_MON_DTTM - .0034722
then
l_maicon :=utl_smtp.open_connection('relay-smtp.acme.com',25);
utl_smtp.helo(l_maicon,'hostname');
utl_smtp.mail(l_maicon,'
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
');
utl_smtp.rcpt(l_maicon,'
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
');
utl_smtp.rcpt(l_maicon,'
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
');
utl_smtp.data(l_maicon,'From:
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' || utl_tcp.crlf||
'To:
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
;
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' || utl_tcp.crlf ||
'Subject: Performance Monitor Alert - ' || v_event_descr.DBNAME || utl_tcp.crlf ||
v_event_descr.METRICLABEL7 || ': ' || :new.PM_METRIC_VALUE7 || utl_tcp.crlf ||
v_event_descr.METRICLABEL2 || ': ' || :new.PM_METRIC_VALUE2 || utl_tcp.crlf ||
v_event_descr.METRICLABEL1 || ': ' || :new.PM_METRIC_VALUE1 || utl_tcp.crlf ||
v_event_descr.METRICLABEL3 || ': ' || :new.PM_METRIC_VALUE3 || utl_tcp.crlf ||
v_event_descr.METRICLABEL4 || ': ' || :new.PM_METRIC_VALUE4 || utl_tcp.crlf ||
v_event_descr.METRICLABEL5 || ': ' || :new.PM_METRIC_VALUE5 || utl_tcp.crlf ||
v_event_descr.METRICLABEL6 || ': ' || :new.PM_METRIC_VALUE6 || utl_tcp.crlf);
utl_smtp.quit(l_maicon);
update psoptions set stats_start = :new.PM_MON_DTTM;
end if;
end if;
end;
/
Set as favorite
Bookmark
Email this
Trackback(0)
TrackBack URI for this entry
Comments (2)
Subscribe to this comment's feed
written by Hüseyin Türker, September 24, 2007
We want to send an ERP data string; like last date of some payments, by e-mail.
-
- +0
-
-
written by Brent Martin, September 25, 2007
There are several ways to do that. One good way is to leverage workflow functionality. Another way is to use the e-mail distribution option when you run a report.
To see how the workflow approach would work, you can reverse-engineer the PeopleTools > Workflow > Defaults and Messages > Worklist / Email Message component. Once you know how to send an e-mail via PeopleCode, you can plug it in an app engine process to do it in batch.
Let me know what specific help you might need.
-Brent
To see how the workflow approach would work, you can reverse-engineer the PeopleTools > Workflow > Defaults and Messages > Worklist / Email Message component. Once you know how to send an e-mail via PeopleCode, you can plug it in an app engine process to do it in batch.
Let me know what specific help you might need.
-Brent
-
- +0
-
-
Write comment
| < Prev | Next > |
|---|
Last Updated on Wednesday, 30 November 2005 14:45.