This article is the third in a series about extending Foxhound with adhoc queries:
- The first article showed how to run adhoc queries in ISQL to simulate a single Monitor dashboard tab,
- the second article showed how to use proxy tables to combine data from multiple Foxhound databases into one display, and
- this article shows how to write a web service to show the results in a web browser like Chrome or, in this case, Vivaldi...
The first step is to enable SQL Anywhere's built-in HTTP server with the
dbsrv17 -xs option:
"%SQLANY17%\bin64\dbspawn.exe"^
-f "%SQLANY17%\bin64\dbsrv17.exe"^
-o dbsrv17_log_dashboard17.txt^
-x tcpip^
-xs http(port=12345)^
dashboard17.db
Tip: This is a separate SQL Anywhere 17 database, created by you, that is different from any of the Foxhound databases, and different from any of your target databases being monitored by Foxhound. It is the same database as the one created in Step 1 of the previous article, except for the dbsrv17 -xs option which turns on HTTP processing.
The second step is to use the
CREATE SERVICE statement to set up a web service in SQL Anywhere that calls a procedure:
CREATE SERVICE dashboard
TYPE 'RAW' AUTHORIZATION OFF URL ON USER DBA
AS CALL dashboard ( :refresh_count );
Tip: Don't get the CREATE SERVICE statement confused with CREATE SERVER. The CREATE SERVICE statement creates a web service in the local database whereas CREATE SERVER creates a pointer to a remote server, and both are used in this article.
The third step is to create a procedure that builds the HTML text in a single LONG VARCHAR string and returns that string to the browser when the service calls the procedure:
- The "Refresh count:" display field is implemented by
- the @in_refresh_count parameter defined on line 2 below
- which is updated by the code on lines 12 through 18,
- passed to the next (automatic) execution of the web service by the META HTTP-EQUIV="refresh" tag on line 42 and
- displayed in the browser window on line 58.
- The code on lines 20 through 127 is a rewrite of the code in Step 5: Query Data In Local Tables in the previous article.
- The call on line 32 uses a whole whackload of code from the previous article, which you can see at the bottom of this article in the Supporting Code appendices.
- The long LIST ( STRING ( ... calls starting on lines 73 and 107 turn the result sets from Step 5 into strings of HTML text.
- The SELECT starting on line 39 fills the local variable @result with HTML text built from data retrieved from the "Summary List Of Target Databases" table.
- The SELECT starting on line 95 appends more HTML text build from the "Active Alerts For Each Target Database" table to @result and returns the whole thing to the caller...
- ...which is the CREATE SERVICE statement, which is how SQL Anywhere's HTTP server gets the HTML and returns it to the browser.
CREATE PROCEDURE dashboard (
IN @in_refresh_count LONG VARCHAR )
RESULT ( html_string LONG VARCHAR )
BEGIN
DECLARE @refresh_count INTEGER;
DECLARE @result LONG VARCHAR;
----------------------------------------------------------------------------------------
-- Initialize or increment the refresh count to pass to the next execution.
IF ISNUMERIC ( COALESCE ( @in_refresh_count, '0' ) ) = 1 THEN
SET @refresh_count = CAST ( COALESCE ( @in_refresh_count, '0' ) AS INTEGER );
ELSE
SET @refresh_count = 0;
END IF;
SET @refresh_count = @refresh_count + 1;
----------------------------------------------------------------------------------------
-- Fill in the list of remote Foxhound server names.
TRUNCATE TABLE foxhound_server_list;
INSERT foxhound_server_list VALUES ( 'Foxhound4' );
INSERT foxhound_server_list VALUES ( 'Foxhound4b' );
INSERT foxhound_server_list VALUES ( 'Foxhound4c' );
COMMIT;
----------------------------------------------------------------------------------------
-- Fill "Summary List Of Target Databases" and "Active Alerts For Each Target Database".
CALL refresh_dashboard_data();
----------------------------------------------------------------------------------------
-- Build part of the return HTML.
CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );
SELECT STRING (
'<HTML>',
'<HEAD>',
'<META HTTP-EQUIV="refresh" CONTENT="10; URL=dashboard?refresh_count=', @refresh_count, '">',
'<TITLE>Foxhound Dashboard</TITLE>',
'<STYLE>',
'BODY { margin: 0; padding: 0; font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF";
font-size: 100%; }',
'IMG { border: 0; margin: 0; }',
'INPUT { font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF"; font-size: 1em; }',
'P { font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF"; font-size: 1em; }',
'TABLE { margin: 0; padding: 0; }',
'TD { margin: 0; padding: 0 0 0 0.6em; vertical-align: bottom;
font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF"; font-size: 1em; }',
'TR { margin: 0; padding: 0; }',
'</STYLE>',
'</HEAD>',
'<BODY>',
'<P STYLE="padding-left: 0.6em;">',
'Refresh count: ', @refresh_count,
'<P>',
'<TABLE>',
'<TR>',
'<TD STYLE="text-align: center;"><B>Foxhound<BR />Server</B></TD>',
'<TD STYLE="text-align: center;"><B>ID</B></TD>',
'<TD STYLE="text-align: center;"><B>Target Database</B></TD>',
'<TD STYLE="text-align: center;"><B>Monitor Status</B></TD>',
'<TD STYLE="text-align: center;"><B>Active<BR />Alerts</B></TD>',
'<TD STYLE="text-align: center;"><B>Heartbeat</B></TD>',
'<TD STYLE="text-align: center;"><B>Unsch<BR />Req</B></TD>',
'<TD STYLE="text-align: center;"><B>Conns</B></TD>',
'<TD STYLE="text-align: center;"><B>Blocked<BR />Conns</B></TD>',
'<TD STYLE="text-align: center;"><B>CPU Time</B></TD>',
'</TR>',
LIST ( STRING (
'<TR>',
'<TD STYLE="text-align: left;">', "Summary List Of Target Databases"."Foxhound Server", '</TD>',
'<TD STYLE="text-align: center;">', "Summary List Of Target Databases"."ID", '</TD>',
'<TD STYLE="text-align: left;">', "Summary List Of Target Databases"."Target Database", '</TD>',
'<TD STYLE="text-align: left;">', "Summary List Of Target Databases"."Monitor Status", '</TD>',
'<TD STYLE="text-align: center;">', "Summary List Of Target Databases"."Active Alerts", '</TD>',
'<TD STYLE="text-align: center;">', "Summary List Of Target Databases".Heartbeat, '</TD>',
'<TD STYLE="text-align: center;">', "Summary List Of Target Databases"."Unsch Req", '</TD>',
'<TD STYLE="text-align: center;">', "Summary List Of Target Databases".Conns, '</TD>',
'<TD STYLE="text-align: center;">', "Summary List Of Target Databases".Blocked, '</TD>',
'<TD STYLE="text-align: center;">', "Summary List Of Target Databases"."CPU Time", '</TD>',
'</TR>' ),
'' -- empty LIST row delimiter
ORDER BY "Target Database", "ID" ),
'</TABLE>' )
INTO @result
FROM "Summary List Of Target Databases";
----------------------------------------------------------------------------------------
-- Finish building the HTML page and return it to the browser.
SELECT STRING (
@result,
'<P>',
'<TABLE>',
'<TR>',
'<TD STYLE="text-align: center;"><B>Foxhound<BR />Server</B></TD>',
'<TD STYLE="text-align: center;"><B>ID</B></TD>',
'<TD STYLE="text-align: center;"><B>Target Database</B></TD>',
'<TD STYLE="text-align: center;"><B>Time Since<BR />Alert Recorded</B></TD>',
'<TD STYLE="text-align: center;"><B>Active<BR />Alert #,</B></TD>',
'<TD STYLE="text-align: left;" ><B>Description</B></TD>',
'</TR>',
LIST ( STRING (
'<TR>',
'<TD STYLE="text-align: left;">',
"Active Alerts For Each Target Database"."Foxhound Server", '</TD>',
'<TD STYLE="text-align: center;">',
"Active Alerts For Each Target Database"."ID", '</TD>',
'<TD STYLE="text-align: left;">',
"Active Alerts For Each Target Database"."Target Database", '</TD>',
'<TD STYLE="text-align: center;">',
"Active Alerts For Each Target Database"."Time Since Alert Recorded", '</TD>',
'<TD STYLE="text-align: right;">',
"Active Alerts For Each Target Database"."Alert #", '</TD>',
'<TD STYLE="text-align: left;" NOWRAP>',
"Active Alerts For Each Target Database"."Alert Description", '</TD>',
'</TR>' ),
'' -- empty LIST row delimiter
ORDER BY "Target Database", "ID", "Alert #" ),
'</TABLE>',
'</BODY>',
'</HTML>' )
FROM "Active Alerts For Each Target Database";
END;
Here's the URL for launching the web service:
http://localhost:12345/dashboard
Supporting Code
The following appendices show the code
described in the previous article and used here.
Appendix 1: Create Remote Server And Proxy Tables
CREATE SERVER Foxhound_server CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere Native; ENG=Foxhound4; DBN=f; UID=ADHOC; PWD=SQL;';
CREATE EXISTING TABLE sampling_options AT 'Foxhound_server...sampling_options';
CREATE EXISTING TABLE sample_detail AT 'Foxhound_server...sample_detail';
CREATE EXISTING TABLE alert_union AT 'Foxhound_server...alert_union';
CREATE FUNCTION rroad_f_msecs_as_abbreviated_d_h_m_s_ms
( IN @msecs INTEGER )
RETURNS VARCHAR ( 20 )
AT 'Foxhound_server...rroad_f_msecs_as_abbreviated_d_h_m_s_ms';
Appendix 2: Create Local Views
CREATE VIEW v_latest_primary_key AS
SELECT sample_detail.sampling_id AS sampling_id,
MAX ( sample_detail.sample_set_number ) AS sample_set_number
FROM sample_detail
GROUP BY sample_detail.sampling_id;
CREATE VIEW v_sample_detail AS
SELECT sample_detail.*
FROM sample_detail
INNER JOIN v_latest_primary_key
ON v_latest_primary_key.sampling_id = sample_detail.sampling_id
AND v_latest_primary_key.sample_set_number = sample_detail.sample_set_number;
CREATE VIEW v_active_alerts AS
SELECT alert_union.sampling_id AS sampling_id,
LIST ( DISTINCT STRING (
'#',
alert_union.alert_number ),
', '
ORDER BY alert_union.alert_number ) AS active_alert_number_list
FROM alert_union
WHERE alert_union.record_type = 'Alert'
AND alert_union.alert_is_clear_or_cancelled = 'N'
GROUP BY alert_union.sampling_id;
CREATE VIEW "vSummary List Of Target Databases" AS
SELECT sampling_options.sampling_id AS "ID",
STRING (
sampling_options.selected_name,
IF sampling_options.selected_tab = '1'
THEN '(DSN)'
ELSE ''
END IF ) AS "Target Database",
sampling_options.connection_status_message AS "Monitor Status",
COALESCE ( v_active_alerts.active_alert_number_list, '-' ) AS "Active Alerts",
IF sampling_options.sampling_should_be_running = 'Y'
AND sampling_options.connection_status_message = 'Sampling OK'
THEN rroad_f_msecs_as_abbreviated_d_h_m_s_ms (
v_sample_detail.canarian_query_elapsed_msec )
ELSE '-'
ENDIF AS "Heartbeat",
IF COALESCE ( v_sample_detail.UnschReq, 0 ) = 0
THEN '-'
ELSE STRING ( v_sample_detail.UnschReq )
ENDIF AS "Unsch Req",
IF COALESCE ( v_sample_detail.ConnCount, 0 ) = 0
THEN '-'
ELSE STRING ( v_sample_detail.ConnCount )
ENDIF AS "Conns",
IF COALESCE ( v_sample_detail.total_blocked_connection_count, 0 ) = 0
THEN '-'
ELSE STRING (
v_sample_detail.total_blocked_connection_count )
ENDIF AS "Blocked",
CASE
WHEN COALESCE ( v_sample_detail.interval_CPU_percent, 0.0 ) = 0.0
THEN '-'
ELSE STRING ( ROUND (
v_sample_detail.interval_CPU_percent, 1 ), '%' )
END AS "CPU Time"
FROM sampling_options
LEFT OUTER JOIN v_sample_detail
ON v_sample_detail.sampling_id = sampling_options.sampling_id
LEFT OUTER JOIN v_active_alerts
ON v_active_alerts.sampling_id = sampling_options.sampling_id;
CREATE VIEW "vActive Alerts For Each Target Database" AS
SELECT sampling_options.sampling_id AS "ID",
STRING (
sampling_options.selected_name,
IF sampling_options.selected_tab = '1'
THEN '(DSN)'
ELSE ''
END IF ) AS "Target Database",
rroad_f_msecs_as_abbreviated_d_h_m_s_ms (
DATEDIFF ( MILLISECOND,
alert_union.recorded_at,
CURRENT TIMESTAMP ) ) AS "Time Since Alert Recorded",
alert_union.alert_number AS "Alert #",
alert_union.alert_description AS "Alert Description"
FROM sampling_options
INNER JOIN alert_union
ON alert_union.sampling_id = sampling_options.sampling_id
WHERE alert_union.record_type = 'Alert'
AND alert_union.alert_is_clear_or_cancelled = 'N';
Appendix 3: Create Local Permanent Tables
CREATE TABLE "Summary List Of Target Databases" (
"Foxhound Server" VARCHAR ( 128 ) NOT NULL,
"ID" UNSIGNED INT NOT NULL,
"Target Database" VARCHAR ( 133 ) NOT NULL,
"Monitor Status" VARCHAR ( 32767 ) NOT NULL,
"Active Alerts" LONG VARCHAR NOT NULL,
Heartbeat VARCHAR ( 20 ) NOT NULL,
"Unsch Req" VARCHAR ( 21 ) NOT NULL,
Conns VARCHAR ( 21 ) NOT NULL,
Blocked VARCHAR ( 21 ) NOT NULL,
"CPU Time" VARCHAR ( 34 ) NOT NULL,
PRIMARY KEY ( "Foxhound Server", "ID" ) );
CREATE TABLE "Active Alerts For Each Target Database" (
"Foxhound Server" VARCHAR ( 128 ) NOT NULL,
"ID" UNSIGNED INT NOT NULL,
"Target Database" VARCHAR ( 133 ) NOT NULL,
"Time Since Alert Recorded" VARCHAR ( 20 ) NOT NULL,
"Alert #" INTEGER NOT NULL,
"Alert Description" LONG VARCHAR NOT NULL,
PRIMARY KEY ( "Foxhound Server", "ID", "Alert #" ) );
CREATE TABLE foxhound_server_list (
foxhound_server VARCHAR ( 128 ) NOT NULL PRIMARY KEY );
Appendix 4: CREATE PROCEDURE refresh_dashboard_data
CREATE PROCEDURE refresh_dashboard_data()
BEGIN
TRUNCATE TABLE "Summary List Of Target Databases";
TRUNCATE TABLE "Active Alerts For Each Target Database";
FOR f_server AS c_server INSENSITIVE CURSOR FOR
SELECT foxhound_server AS @foxhound_server
FROM foxhound_server_list
ORDER BY foxhound_server
FOR READ ONLY
DO
DROP REMOTE CONNECTION TO Foxhound_server CLOSE ALL;
ALTER SERVER Foxhound_server
USING 'DRIVER=SQL Anywhere Native; ENG={@foxhound_server}; DBN=f; UID=ADHOC; PWD=SQL;';
INSERT "Summary List Of Target Databases"
SELECT @foxhound_server,
*
FROM "vSummary List Of Target Databases";
INSERT "Active Alerts for each target database"
SELECT @foxhound_server,
*
FROM "vActive Alerts for each target database";
COMMIT;
END FOR;
END;
-- The "unsetup" process gets rid of the Windows shortcuts and other items,
-- but it leaves the Foxhound database alone... and even leaves it running.
