...

A query can be created using the web interface if a user has a role that contains the queries::create permission. Go to menu: Manage -> Queries -> Create Queries. There is also a create button on the Queries collection page.

Examples

NOTE - The SQL queries used in Open-AudIT require the use of the backtick - ` character and NOT the standard single quote

...

for fields. On most US Windows keyboards the backtick key is located in the top-left of the keyboard along with the tilde ~. On a US Mac keyboard the backtick key is located next to the SHIFT key. The standard single quote is still used to enclose values as the examples below illustrate.

Devices Older Than X

This example query retrieves a list of devices OVER 3 years old. The query uses today (NOW) and system.purchase_date as the reference point and filters out all virtual machines via a check of the system.serial field for %VM%.

SELECT system.id AS `system.id`, system.purchase_date AS `system.purchase_date`, system.type AS `system.type`, system.name AS `system.name`, system.last_seen AS `system.last_seen`, system.manufacturer AS `system.manufacturer`, system.model AS `system.model`, system.description AS `system.description`, system.function AS `system.function`, locations.name AS `locations.name` FROM system LEFT JOIN locations ON (system.location_id = locations.id) LEFT JOIN windows ON (system.id = windows.system_id AND windows.current = 'y') LEFT JOIN orgs ON (system.org_id = orgs.id) WHERE @filter HAVING AND system.purchase_date < DATE_SUB(NOW(),INTERVAL 3 YEAR) AND system.serial NOT LIKE '%VM%'

Devices with Expired Warranties

...

SELECT system.id AS `system.id`, system.warranty_expires AS `system.warranty_expires`, system.type AS `system.type`, system.name AS `system.name`, system.last_seen AS `system.last_seen`, system.manufacturer AS `system.manufacturer`, system.model AS `system.model`, system.description AS `system.description`, system.function AS `system.function`, locations.name AS `locations.name` FROM system LEFT JOIN locations ON (system.location_id = locations.id) LEFT JOIN windows ON (system.id = windows.system_id AND windows.current = 'y') LEFT JOIN orgs ON (system.org_id = orgs.id) WHERE @filter HAVING AND system.warranty_expires <= CURDATE() AND system.serial NOT LIKE '%VM%'

Devices Missing Information

...

SELECT system.id AS `system.id`, system.ip AS `system.ip`, system.name AS `system.name`, system.description AS `system.description`, system.function AS `system.function`, system.purchase_date AS `system.purchase_date`, system.type AS `system.type`, locations.name AS `locations.name` FROM system LEFT JOIN locations ON (system.location_id = locations.id) WHERE @filter HAVING system.purchase_date = `2001'2001-01-01` 01' OR system.function = `` '' OR system.description = ``''

Database Schema

The schema for the database is below. It can also be found in the application if the user has database::read permission by going to menu: Manage -> Database -> List Database, then clicking on the "queries" table.

...