Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Archive > 2017July24 - Cleanup > 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 Gerald.Prado on Jul 24, 2017

Views: 36 Votes: 0 Revisions: 10

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

Tags

Classifications

Internal Use Only