Foxhound 3.0 FAQ: How do I run adhoc queries on the Foxhound database?

Foxhound 3.0 FAQ
	Foxhound 3.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=foxhound3;DBN=f;UID=ADHOC;PWD=SQL"

Here's a Windows command line for running adhoc queries via dbisql:

"%SQLANY16%\bin64\dbisql.com" -c "ENG=foxhound3; DBN=f; UID=ADHOC; PWD=SQL; CON=Foxhound3-ADHOC"

How do I change the ADHOC password?

Views available for adhoc queries.

Examples of Adhoc Queries

Active alerts for each target database.

Count active alerts for each target database.

Alert #1 Database unresponsive activity for a particular target database.

Recent purge runs.

Selected columns from the most recent purge runs.

Sample sessions for all target databases.

Recent sample headers for each target database.

Recent sample headers and details for each target database.

Recent sample headers, details and connections for each target database.

Sample details selected by exact primary key values.

Sample header, details and connections selected by exact primary key values.

Recent blocked connections.

Long-running queries.

Latency and throughput with 100-sample moving averages.

Busy SQL statements.

Views available for adhoc queries. [Top]

alert One row per alert.
alert_cancelled One row per alert cancellation.
alert_union A UNION of all the rows in 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.
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.

Active alerts for each target database. [Top]

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 target_database ASC,
       alert_union.sample_set_number DESC;

Count active alerts for each target database. [Top]

SELECT IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       COUNT(*) AS "Active Alerts"
  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 target_database ASC;

Alert #1 Database unresponsive activity for a particular target database. [Top]

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 target_database = 'Inventory'
   AND alert_union.alert_number = 1
 ORDER BY alert_union.recorded_at ASC;

Recent purge runs. [Top]

SELECT TOP 100 * 
  FROM purge_run
 ORDER BY run_number DESC;

Selected columns from the most recent purge runs. [Top]

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
 ORDER BY run_number DESC;

Sample sessions for all target databases. [Top]

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 target_database;

Recent sample headers for each target database. [Top]

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 target_database,
       sample_header.sample_set_number DESC;

Recent sample headers and details for each target database. [Top]

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 target_database,
       sample_header.sample_set_number DESC;

Recent sample headers, details and connections for each target database. [Top]

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 target_database,
       sample_header.sample_set_number DESC,
       sample_connection.connection_number;

Sample details selected by exact primary key values. [Top]

-- Samples selected by these [sampling_id,sample_set_number] key values shown on the Monitor and History pages:
--    [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;

Sample header, details and connections selected by exact primary key values. [Top]

-- Samples selected by these [sampling_id,sample_set_number] key values shown on the Monitor and History pages:
--    [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;

Recent blocked connections. [Top]

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. [Top]

-- 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,
       sample_connection.LastPlanText                AS LastPlanText 
  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 with 100-sample moving averages. [Top]

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;

Busy SQL statements. [Top]

SELECT sampling_options.selected_name         AS target_database,
       sample_connection.LoginTime,
       sample_connection.connection_number, 
       sample_connection.Name                 AS connection_name,
       sample_connection.Userid,
       MAX ( sample_connection.busy_percent ) AS busy_percent,
       sample_connection.LastStatement,  
       sample_connection.LastPlanText  
  FROM sampling_options
          INNER JOIN sample_connection
                  ON sample_connection.sampling_id = sampling_options.sampling_id
 WHERE sample_connection.busy_percent >= 5
   AND sample_connection.LastStatement <> ''
   AND sample_connection.Name NOT LIKE 'Foxhound%'
 GROUP BY sampling_options.selected_name,
       sample_connection.LoginTime,
       sample_connection.connection_number, 
       sample_connection.Name,
       sample_connection.Userid,
       sample_connection.LastStatement, 
       sample_connection.LastPlanText  
 ORDER BY target_database,
       sample_connection.LoginTime,
       sample_connection.connection_number;

This page was last updated on September 28, 2013.

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