Foxhound 2.0 FAQ
Foxhound 2.0 FAQ Home      Foxhound Home      RisingRoad           Breck.Carter@gmail.com     


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=foxhound2;DBN=f;UID=ADHOC;PWD=SQL"

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.

See also...
How do I see the schema for adhoc reporting?
How do I change the ADHOC password?

Here are some examples of queries that display Foxhound Monitor data:

-----------------------------------------------------------------------------------------------------------
[Top]   alert_union, sampling_options
-----------------------------------------------------------------------------------------------------------
-- 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;

-----------------------------------------------------------------------------------------------------------
[Top]   alert_union, sampling_options
-----------------------------------------------------------------------------------------------------------
-- 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;

-----------------------------------------------------------------------------------------------------------
[Top]   alert_union, sampling_options
-----------------------------------------------------------------------------------------------------------
-- 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;

-----------------------------------------------------------------------------------------------------------
[Top]   purge_run
-----------------------------------------------------------------------------------------------------------
-- Show the most recent 100 purge runs.
SELECT TOP 100 * 
  FROM purge_run
 ORDER BY run_number DESC;

-----------------------------------------------------------------------------------------------------------
[Top]   purge_run
-----------------------------------------------------------------------------------------------------------
-- Show purge runs up to a particlar point in time, with some columns copied to the front of the SELECT.

SELECT TOP 100
       run_number,
       progress,
       started_at,
       DATEDIFF ( second, started_at, completed_at ) AS sec,
       is_complete,
       old_sample_set_delete_count,
       uninteresting_connections_delete_count,
       sample_purge_interval,
       uninteresting_connections_purge_interval,
       purge_speed,
       * 
  FROM purge_run
 WHERE started_at <= '2012-07-27 8:20 PM'
 ORDER BY run_number DESC;

-----------------------------------------------------------------------------------------------------------
[Top]   sampling_options
-----------------------------------------------------------------------------------------------------------
-- 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;

-----------------------------------------------------------------------------------------------------------
[Top]   sample_header, sampling_options
-----------------------------------------------------------------------------------------------------------
-- 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;

-----------------------------------------------------------------------------------------------------------
[Top]   sample_header, sample_detail, sampling_options
-----------------------------------------------------------------------------------------------------------
-- 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;

-----------------------------------------------------------------------------------------------------------
[Top]   sample_header, sample_detail, sample_connection, sampling_options
-----------------------------------------------------------------------------------------------------------
-- 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;

-----------------------------------------------------------------------------------------------------------
[Top]   sample_detail
-----------------------------------------------------------------------------------------------------------
-- Sample detail data for selected samples from one target database.
--    [1,438407]
--    [1,438406]
--    [1,1497]
--    [1,89]
--    [1,88]

SELECT sample_detail.*
  FROM sample_detail
 WHERE sample_detail.sampling_id = 1
   AND sample_detail.sample_set_number IN ( 88, 89, 1497, 438406, 438407 )
 ORDER BY sample_detail.sample_set_number DESC;

-----------------------------------------------------------------------------------------------------------
[Top]   sample_header, sample_detail, sample_connection, sampling_options
-----------------------------------------------------------------------------------------------------------
-- Sample header, detail and connection data for selected samples from one target database.
--    [1,438407]
--    [1,438406]
--    [1,1497]
--    [1,89]
--    [1,88]

SELECT 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 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.sampling_id = 1
   AND sample_header.sample_set_number IN ( 88, 89, 1497, 438406, 438407 )
 ORDER BY sample_header.sample_set_number DESC,
       sample_connection.connection_number;

-----------------------------------------------------------------------------------------------------------
[Top]   sample_connection, sample_header
-----------------------------------------------------------------------------------------------------------
-- 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;

-----------------------------------------------------------------------------------------------------------
[Top]   sample_connection, sample_header, sampling_options
-----------------------------------------------------------------------------------------------------------
-- 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;

-----------------------------------------------------------------------------------------------------------
[Top]   sample_detail, sampling_options
-----------------------------------------------------------------------------------------------------------
-- 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?


This page was last updated on October 21, 2012. Foxhound 2.0 FAQ Home      Foxhound Home      RisingRoad      Breck.Carter@gmail.com