HP Neoview Release 2.4 Software - Manual (Page 70)

Completed Queries Executed on a System in the Previous Two Days
To count queries that executed from one midnight to the next, instead of within a 24 hour period
relative to the current time, the following query uses the Neoview DATE_TRUNC function in
the SQL WHERE clause.
SELECT DISTINCT(QUERY_START_DATE) AS START_DATE,
COUNT(*) AS NUM_OF_QUERIES,
MAX(QUERY_START_TIME) AS MAX_START_TIME,
MIN(QUERY_START_TIME) AS MIN_START_TIME
FROM NEO.HP_METRICS.ODBC_QUERY_STATS_V2
WHERE QUERY_START_DATETIME <= CURRENT
AND QUERY_START_DATETIME >= DATE_TRUNC('DAY', CURRENT - INTERVAL '1' DAY)
AND STATEMENT_STATE = 'COMPLETE'
GROUP BY 1
FOR READ UNCOMMITTED ACCESS;
MIN_START_TIME MAX_START_TIME NUM_OF_QUERIES START_DATE
00:23:59 12:48:46.879774 479 2007–05–23
13:15:14 18:44:05.390777 49 2007–05–22
Queries within the Past 24 Hours, Grouped by Client ID
This example indicates how many queries different clients ran within a 24-hour period.
SELECT client_id, COUNT(*) AS NUM_OF_QUERIES
FROM NEO.HP_METRICS.ODBC_QUERY_STATS_V2
WHERE QUERY_START_DATETIME <= CURRENT
AND QUERY_START_DATETIME >= CURRENT - INTERVAL '1' DAY
AND STATEMENT_STATE = 'COMPLETE'
GROUP BY client_id
ORDER BY client_id
FOR READ UNCOMMITTED ACCESS;
NUM_OF_QUERIES CLIENT_ID
16 EXPTC082205
51 ORDROBERTSC-P2
162 ORDHROTHGAR-P2
28 ordbantam-p2
Queries within the Past 24 Hours, Grouped by Data Source
This example indicates how many queries within the 24-hour period were associated with each
data source.
SELECT datasource, COUNT(*) AS NUM_OF_QUERIES
FROM NEO.HP_METRICS.ODBC_QUERY_STATS_V2
WHERE QUERY_START_DATETIME <= CURRENT
AND QUERY_START_DATETIME >= CURRENT - INTERVAL '1' DAY
AND STATEMENT_STATE = 'COMPLETE'
GROUP BY datasource
70 Examples and Guidelines for Creating Repository Queries