Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

Home > Success Center > Database Performance Analyzer (DPA) > Web Monitoring from within PL/SQL and DPA

Web Monitoring from within PL/SQL and DPA

Table of contents
No headers
Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 7 Votes: 0 Revisions: 8

A cloud-based DPA demo is available at http://demo.confio.com:8123. We wanted to monitor both the Oracle database and the web application itself to make sure it stays up and functional. The database monitoring was easy, we just registered the cloud database with Ignite and setup a couple of alerts to tell us if the database was down or if it was not performing well. However, was there also a way to add an alert into Ignite so it could monitor the web application as well?

After a little research I found the UTL_HTTP package that could retrieve the results of a web page. I wrote the simple procedure below that uses this package and times the response using time stamps. This procedure accepts two main parameters, the URL to access and a search string to look for that verifies the web page is operational. In our case, a call to this procedure from an Ignite alert looked similar to:

Web_Monitor(‘http://demo.confio.com:8123′,’Ignite Login’, #ALERTVALUE#, #ALERTSTRING#)

The last two parameters are Ignite keywords, and the ALERTVALUE contains the time the web page took to respond and ALERTSTRING contains messages. If you were to call this procedure from your own PL/SQL, substitute your local variables in these locations and it will work as well. If the ALERTVALUE parameter is -1, that indicates the web page results did not contain the search string, i.e, the web page is not working. Otherwise it will contain the number of seconds (down to the millisecond) it took for that web page to respond.

This is an example of a straightfoward way to monitor a web page from inside the Oracle database and Ignite.

CREATE OR REPLACE PROCEDURE Web_Monitor (
pURL IN VARCHAR2,
pSearch IN VARCHAR2,
pAlertValue OUT NUMBER,
pAlertString OUT VARCHAR2) AS

sResponse VARCHAR2(2000);
t1 TIMESTAMP;
t2 TIMESTAMP;

BEGIN
– access the web page and time the response
t1 := CURRENT_TIMESTAMP;
sResponse := UTL_HTTP.REQUEST(pURL);
t2 := CURRENT_TIMESTAMP;

– make sure the web page HTML results contain the search string
IF INSTR(sResponse, pSearch) > 0 THEN
– it worked, the page came back and we found what we were looking for in the HTML stream
– calculate the time delta in seconds and pass that back
pAlertValue := EXTRACT(MINUTE FROM (t2-t1))*60 + EXTRACT(SECOND FROM (t2-t1));
pAlertString := pURL || ‘ responded in ‘ || pAlertValue || ‘ seconds.’;
ELSE
– the web page is not working, i.e. we did not find what we were looking for in the HTML that came back.
pAlertValue := -1;
pAlertString := ‘The string ”’||pSearch||”’ was not found in the response from URL ”’||pURL||”’.';
END IF;
END;
/

Last modified
19:19, 22 Jun 2016

Tags

Classifications

Public