Adhoc queries are commonly run against the Foxhound Monitor database for one of two reasons:
- to search historical data for performance anomalies that aren't apparent in the standard Foxhound displays, and
- to duplicate and enhance Foxhound features.
Here's where the data for the top list comes from, in the screenshot above:
- The ID column comes from sampling_id, the primary key column of the rroad_sampling_options table inside Foxhound.
Each target database that Foxhound is monitoring is represented by one row in the rroad_sampling_options table, which is available for adhoc reporting as the sampling_options view.
Tip: To get started writing adhoc queries in Foxhound, see Section 8. Adhoc Queries in the Help. To find out what's available, see the Foxhound Adhoc Schema page.
- The Target Database column comes from selected_name in the rroad_sampling_options table, available via the sampling_options view.
- The Open Page and Monitor columns are links to Foxhound pages; they are omitted from this article.
- The Monitor Status column comes from connection_status_message in the rroad_sampling_options table, available via the sampling_options view.
- The Active Alerts column is a list constructed from alert_number column values in the rroad_alert table via the alert_union view.
Each Foxhound Alert, Cancelled Alert and All Clear message is recorded in the rroad_alert, rroad_alert_cancelled and rroad_all_clear tables respectively, available via the alert, alert_cancelled and all_clear views.
An "Active Alert" is an Alert that has not been Cancelled or marked All Clear, and the rroad_alert_union view and alert_union adhoc view make it easy to find those rows.
- The Heartbeat, Unsch Req columns come from canarian_query_elapsed_msec and UnschReq in the (very poorly named) rroad_group_1_property_pivot table in Foxhound, which is available for adhoc reporting as the (much better named) sample_detail view.
Each database-level sample recorded by Foxhound is represented by one row in the rroad_group_1_property_pivot table, uniquely identified by the sample_set_number column.
- The Conns, Blocked columns come from ConnCount and total_blocked_connection_count in the rroad_group_1_property_pivot table, available as the sample_detail view.
- The CPU Time column comes from interval_CPU_percent in the rroad_group_1_property_pivot table, available as the sample_detail
view.
Query 1: Summary List Of Target Databases
Jumping ahead, here's what the custom Summary List Of Target Databases query looks like when it's run in All Programs - Foxhound4 - Tools - Adhoc Query Foxhound Database via ISQL:
The SELECT below uses a WITH clause on lines 1 through 23 to create three local views:
- v_latest_primary_key finds the latest sample_detail row for each target database; this is a big deal since Foxhound can store many thousands of sample_detail rows for each target database.
- v_sample_detail makes use of v_latest_primary_key to return all the columns in those latest sample_detail rows.
- v_active_alerts uses SQL Anywhere's LIST function to gather the alert_number values for all the Alerts that aren't Cancelled or All Clear; the predicate alert_union.alert_is_clear_or_cancelled = 'N' is an prime example of the value added by Foxhound's underlying rroad_alert_union view.
- Tip: Foxhound doesn't let you create permanent views via CREATE VIEW, but it does let you create temporary views as "common table expressions" in the WITH clause. It also lets you code temporary views as "derived tables" inside the FROM clause, but the WITH clause is sometimes easier to understand and maintain.
WITH
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 ),
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 ),
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 )
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
ORDER BY "Target Database",
"ID";
The SELECT statement on lines 24 through 64 gathers columns from sampling_options, v_sample_detail and v_active_alerts; in particular:- The STRING function call on lines 25 through 30 determines if the sampling_options.selected_name column contains a DSN or a Foxhound connection string name.
- The IF expression on lines 33 through 38 makes use of the Foxhound function rroad_f_msecs_as_abbreviated_d_h_m_s_ms() to display the sample_detail.canarian_query_elapsed_msec column.
Query 2: Active Alerts For Each Target Database
Here's what the ISQL output looks like for the second custom query, the one listing "Active Alerts":
Here's where the data comes from:
- The ID and Target Database columns come from sampling_id and selected_name in the sampling_options view.
- The Time Since Alert Recorded column is calculated from the recorded_at column in the alert_union view, using DATEDIFF(), CURRENT TIMESTAMP and the Foxhound function rroad_f_msecs_as_abbreviated_d_h_m_s_ms().
- The Alert # and Alert Description columns come from alert_number and alert_description in the alert_union view.
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'
ORDER BY "Target Database",
"ID",
"Alert #";
