Query Statistics Database

For more information on working with the AMPS statistics database, see the section on AMPS Statistics and the descriptions in the AMPS Monitoring Guide.

The AMPS distribution includes a convenience utility, amps-sqlite3, for easily running queries against a statistics database.

Query Mode

The amps-sqlite3 utility query mode takes two parameters, as shown below:

Options and Parameters

Parameter	Description
database (required)	The sqlite3 database file to query.
query (required)	The query to run. Notice that the query must be enclosed in quotes, since this is a command-line program run by the Linux shell.

The amps-sqlite3 script joins the STATIC and DYNAMIC tables together, making a single table that is easier to query on. For example, the script joins the ICLIENTS_DYNAMIC and ICLIENTS_STATIC tables together into a single ICLIENTS table.

The amps-sqlite3 utility also provides a set of convenience functions that can be included in the query.

Convenience Functions

Option	Description
ISO8601(timestamp)	Convert an AMPS statistics timestamp to an ISO8601 format string.
ISO8601_local(timestamp)	Convert an AMPS statistics timestamp to an ISO8601 format string in the local timezone.
timestamp(string)	Convert the provided ISO8601 format string to an AMPS statistics timestamp.

Size Mode

The amps-sqlite3 utility size mode takes two parameters, as shown below:

Options and Parameters

Parameter	Description
database (required)	The sqlite3 database file to query.
size (required)	Print the size of the statistics table.

Usage

Query Mode

To use the amps-sqlite3 utility in query mode, simply provide the file name of the database to query and the query to run.

For example, the following query returns the set of samples AMPS has recorded for the system_percent consumed on each CPU while the instance has been running:

$ amps-sqlite3 stats.db "select iso8601(timestamp),system_percent from hcpus order by timestamp"

Size Mode

To use the amps-sqlite3 utility in size mode, simply provide the file name of the database to query and the size command.

For example, the following command returns the size of the statistics table:

$ amps-sqlite3 stats.db --size