|
Question: How do I run adhoc queries on the Foxhound database?
Answer:
You can use the following connection parameters with dbisql, or with any other query tool that can connect to a SQL Anywhere database:
-c "ENG=foxhound1;DBN=f;UID=ADHOC;PWD=SQL"
(See How do I change the ADHOC password?)
Here are the views you can SELECT from; the ones highlighted in bold may be the most useful:
|
alert |
One row per alert. | |
alert_cancelled |
One row per alert cancellation. | |
alert_union |
A view which gathers data from the alert, alert_cancelled, all_clear and sampling_options
views to make reporting easier. | |
alerts_criteria |
One row containing the Monitor Options page settings for each sampling session, plus rows
for 'Factory Settings' and 'Saved Defaults'. | |
all_clear |
One row per alert all-clear. | |
build_number |
A single-row table containing some attributes of the current installation of the Foxhound database. | |
connection_string |
One row for each connection string displayed on the String tab of the Foxhound main menu. | |
data_upgrade_history |
One row for each time the data in an existing Foxhound database has been copied into a new
Foxhound database as part of the installation process. | |
email_failure |
One row for each time Foxhound failed in an attempt to send an Alert or other email. | |
exception_diagnostic |
One row for each time Foxhound detected an error or other important event. In some cases, a
single underlying error may result in two or more rows in exception_diagnostic recorded by
nested exception handlers in Foxhound. | |
exception_dump |
One row for each time Foxhound stored extra internal diagnostic information associated with an
event recorded in exception_diagnostic. | |
expiry_date |
A single-row table containing some attributes of the current installation of the Foxhound database. | |
global_options |
A single-row table containing some options affecting how Foxhound behaves. | |
monitor_sampler_control |
A single-row table containing some information about current execution of the Foxhound database. | |
peaks |
One row for each Foxhound Monitor target database, holding various peak values and links to
the corresponding samples. | |
purge_run |
One row for each run of the Foxhound database purge process, holding information about the
progress of the current run and the work accomplished by previous runs. | |
run_characteristics |
A single-row table containing some information about current execution of the Foxhound database. | |
sample_detail |
One row for each sample recorded by the Foxhound Database Monitor, holding various additional
server and database-level properties and computed columns. | |
sample_connection |
One row for each connection recorded for each Foxhound Database Monitor sample, holding
various connection-level properties and computed columns. | |
sample_header |
One row for each sample recorded by the Foxhound Database Monitor, holding various server
and database-level properties and computed columns. | |
sampling_options |
One row for each Foxhound Monitor target database, holding various options affecting how the
Monitor behaves and information about the current status of the Monitor session. | |
saved_activation_key |
A single-row table containing data that makes it possible to install a new Beta copy of
Foxhound as an upgrade to an existing Basic or Extended edition, and then install a later
GA copy as an upgrade to the existing Beta copy and have it automatically re-activated as a
Basic or Extended edition. | |
serial_number |
A single-row table containing the Foxhound serial number as shown on the About page. | |
session_options |
One row for each HTTP session established during the current execution of the Foxhound database. | |
used_activation_key |
One row for each different key used in the Foxhound activation process. | |
|
To see what the those views and the underlying tables look like, browse the "Adhoc Schema" database as follows:
-
Select the "String" tab on the Foxhound menu page,
-
choose the connection string name "Adhoc Schema - autostart and connect", and
-
press the Display Schema button.
Here are some examples of queries that display Foxhound Monitor data:
-----------------------------------------------------------------------------------------------------------
-- All sample sessions.
SELECT sampling_id,
IF selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
selected_name AS target_database,
sampling_should_be_running,
connection_status_message,
last_sample_finished_at
FROM sampling_options
ORDER BY selected_name;
-----------------------------------------------------------------------------------------------------------
-- All active alerts.
SELECT IF sampling_options.selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
sampling_options.selected_name AS target_database,
alert_union.*
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 alert_union.sampling_id ASC,
alert_union.sample_set_number DESC;
-----------------------------------------------------------------------------------------------------------
-- Count active alerts for each target database.
SELECT IF sampling_options.selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
sampling_options.selected_name AS target_database,
COUNT(*)
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'
GROUP BY sampling_options.selected_tab,
sampling_options.selected_name,
alert_union.sampling_id
ORDER BY alert_union.sampling_id ASC;
-----------------------------------------------------------------------------------------------------------
-- All Alert #1 activity for a particular target database.
SELECT IF sampling_options.selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
sampling_options.selected_name AS target_database,
alert_union.*
FROM sampling_options
INNER JOIN alert_union
ON alert_union.sampling_id = sampling_options.sampling_id
WHERE alert_union.sampling_id = 2
AND alert_union.alert_number = 1
ORDER BY alert_union.recorded_at ASC;
-----------------------------------------------------------------------------------------------------------
-- Sample header data for the most recent 10 samples in each sample session.
SELECT sample_header.row_order,
IF sampling_options.selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
sampling_options.selected_name AS target_database,
sample_header.*
FROM sampling_options
INNER JOIN ( SELECT *,
RANK() OVER sample_window AS row_order
FROM sample_header
WINDOW sample_window AS (
PARTITION BY sampling_id
ORDER BY sample_set_number DESC )
) AS sample_header
ON sample_header.sampling_id = sampling_options.sampling_id
WHERE sample_header.row_order <= 10
ORDER BY sampling_options.selected_name,
sample_header.sample_set_number DESC;
-----------------------------------------------------------------------------------------------------------
-- Sample header and detail data for the most recent 10 samples in each sample session.
SELECT sample_header.row_order,
IF sampling_options.selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
sampling_options.selected_name AS target_database,
' HEADER:',
sample_header.*,
' DETAIL:',
sample_detail.*
FROM sampling_options
INNER JOIN ( SELECT *,
RANK() OVER sample_window AS row_order
FROM sample_header
WINDOW sample_window AS (
PARTITION BY sampling_id
ORDER BY sample_set_number DESC )
) AS sample_header
ON sample_header.sampling_id = sampling_options.sampling_id
INNER JOIN sample_detail
ON sample_detail.sample_set_number = sample_header.sample_set_number
WHERE sample_header.row_order <= 10
ORDER BY sampling_options.selected_name,
sample_header.sample_set_number DESC;
-----------------------------------------------------------------------------------------------------------
-- Sample header, detail and connection data for the most recent 10 samples in each sample session.
SELECT sample_header.row_order,
IF sampling_options.selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
sampling_options.selected_name AS target_database,
' HEADER:',
sample_header.*,
' DETAIL:',
sample_detail.*,
' CONNECTION:',
sample_connection.*
FROM sampling_options
INNER JOIN ( SELECT *,
RANK() OVER sample_window AS row_order
FROM sample_header
WINDOW sample_window AS (
PARTITION BY sampling_id
ORDER BY sample_set_number DESC )
) AS sample_header
ON sample_header.sampling_id = sampling_options.sampling_id
INNER JOIN sample_detail
ON sample_detail.sample_set_number = sample_header.sample_set_number
LEFT OUTER JOIN sample_connection
ON sample_connection.sample_set_number = sample_detail.sample_set_number
WHERE sample_header.row_order <= 10
ORDER BY sampling_options.selected_name,
sample_header.sample_set_number DESC,
sample_connection.connection_number;
-----------------------------------------------------------------------------------------------------------
-- Recent blocked connections.
SELECT TOP 1000
sample_connection.sampling_id,
IF sampling_options.selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
sampling_options.selected_name AS target_database,
sample_connection.sample_set_number,
sample_header.sample_finished_at AS sample_recorded_at,
sample_connection.connection_number AS blocked_connection_number,
sample_connection.BlockedOn AS blocked_by_connection_number,
sample_connection.LastReqTime,
sample_connection.LastStatement,
sample_connection.blocker_reason AS reason_for_block,
sample_connection.blocker_owner_name AS owner_name,
sample_connection.blocker_table_name AS table_name,
sample_connection.blocker_row_identifier AS row_identifier
FROM sampling_options
INNER JOIN sample_connection
ON sample_connection.sampling_id = sampling_options.sampling_id
INNER JOIN sample_header
ON sample_header.sampling_id = sampling_options.sampling_id
AND sample_header.sample_set_number = sample_connection.sample_set_number
WHERE sample_connection.BlockedOn <> 0
ORDER BY sample_connection.sample_set_number DESC,
sample_connection.connection_number ASC;
-----------------------------------------------------------------------------------------------------------
-- Long-running queries.
-- Each connection is uniquely identified by sampling_id, connection_number and LoginTime.
-- Each query is uniquely identified by sampling_id, connection_number, LoginTime and LastReqTime.
WITH long_running_query AS
( SELECT sample_connection.sampling_id AS sampling_id,
sample_connection.connection_number AS connection_number,
sample_connection.LoginTime AS LoginTime,
sample_connection.LastReqTime AS LastReqTime,
MIN ( sample_connection.sample_set_number ) AS from_sample_set_number,
MAX ( sample_connection.sample_set_number ) AS to_sample_set_number
FROM sample_connection
WHERE sample_connection.ReqStatus = 'Executing'
AND sample_connection.time_since_last_request > 0
AND TRIM ( COALESCE ( sample_connection.LastStatement, '' ) ) <> ''
GROUP BY sample_connection.sampling_id,
sample_connection.connection_number,
sample_connection.LoginTime,
sample_connection.LastReqTime
HAVING from_sample_set_number <> to_sample_set_number )
SELECT long_running_query.sampling_id AS sampling_id,
IF sampling_options.selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
sampling_options.selected_name AS target_database,
long_running_query.connection_number AS connection_number,
long_running_query.to_sample_set_number AS sample_set_number,
long_running_query.LoginTime AS LoginTime,
long_running_query.LastReqTime AS started_at,
sample_header.sample_finished_at AS recorded_at,
CAST ( sample_connection.time_since_last_request / 1000 AS BIGINT ) AS elapsed_seconds,
sample_connection.LastStatement AS LastStatement
FROM sampling_options
INNER JOIN long_running_query
ON long_running_query.sampling_id = sampling_options.sampling_id
INNER JOIN sample_header
ON sample_header.sampling_id = sampling_options.sampling_id
AND sample_header.sample_set_number = long_running_query.to_sample_set_number
INNER JOIN sample_connection
ON sample_connection.sample_set_number = long_running_query.to_sample_set_number
AND sample_connection.connection_number = long_running_query.connection_number;
-----------------------------------------------------------------------------------------------------------
-- Latency and throughput history plus 100-sample moving averages.
SELECT IF sampling_options.selected_tab = 1
THEN 'DSN'
ELSE 'String'
END IF AS connection_type,
sampling_options.selected_name AS target_database,
sample_detail.sample_set_number,
sample_detail.sample_recorded_at,
( sample_detail.interval_msec / 1000.0 ) AS interval_sec,
sample_detail.ConnCount AS connections,
sample_detail.interval_CPU_percent AS CPU_percent,
( sample_detail.canarian_query_elapsed_msec / 1000.0 ) AS heartbeat_sec,
( sample_detail.sample_elapsed_msec / 1000.0 ) AS sample_sec,
sample_detail.interval_Req / interval_sec AS requests_per_sec,
sample_detail.interval_Commit / interval_sec AS commits_per_sec,
( sample_detail.interval_BytesReceived
+ sample_detail.interval_BytesSent ) / interval_sec AS bytes_per_sec,
AVG ( connections ) OVER moving_window AS avg_connections,
AVG ( CPU_percent ) OVER moving_window AS avg_CPU_percent,
AVG ( heartbeat_sec ) OVER moving_window AS avg_heartbeat_sec,
AVG ( sample_sec ) OVER moving_window AS avg_sample_sec,
AVG ( requests_per_sec ) OVER moving_window AS avg_requests_per_sec,
AVG ( commits_per_sec ) OVER moving_window AS avg_commits_per_sec
FROM sampling_options
INNER JOIN sample_detail
ON sample_detail.sampling_id = sampling_options.sampling_id
WHERE sample_detail.sampling_id = 4
AND sample_detail.sample_lost = 'N'
AND sample_detail.sample_recorded_at BETWEEN 'Feb 21 1:14:49 PM' AND 'Feb 21 3:32:34 PM'
WINDOW moving_window AS
( ORDER BY sample_detail.sample_set_number DESC
ROWS BETWEEN CURRENT ROW AND 99 FOLLOWING )
ORDER BY sample_detail.sample_set_number DESC;
See also...
How do I see the schema for adhoc reporting?
How do I change the ADHOC password?
|