...

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, which can result in unpredictable results. 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.

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.

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 system.purchase_date < DATE_SUB(NOW(),INTERVAL 3 YEAR)

...

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 system.warranty_expires <= CURDATE()

...

This example creates a list of devices where the Function or Description fields are blank OR the Purchase Date is the default.

SELECT system.id AS 'system`system.id'id`, system.ip AS 'system`system.ip'ip`, system.name AS 'system`system.name'name`, system.description AS 'system`system.description'description`, system.function AS 'system`system.function'function`, system.purchase_date AS 'system`system.purchase_date'date`, system.type AS 'system`system.type'type`, locations.name AS 'locations`locations.name' 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.

...