Confio Software has an Ignite demo that runs in the cloud and 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 timestamps.  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;
/

Comments

There are currently no comments for this post.

Leave a Comment

Confio licensed customers with current support contracts are invited to comment. Please log in using the space provided at the right.