Page History

...

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.

The Elevated User query

...

Code Block

SELECT system.id AS `system.id`, system.icon AS `system.icon`, system.type AS `system.type`, system.name AS `system.name`, system.domain AS `system.domain`, system.ip AS `system.ip`, user_group.name as `user_group.name`, user_group.members AS `user_group.members` FROM user_group LEFT JOIN system ON (user_group.system_id = system.id AND user_group.current = 'y') WHERE @filter AND (user_group.name = 'Administrators' OR user_group.name = 'Power Users' OR user_group.name = 'Remote Desktop Users' OR user_group.name = 'wheel' OR user_group.name = 'sudo') AND user_group.members > '' GROUP BY system.id, user_group.name ORDER BY system.name

...

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%.

Code Block
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 AND system.purchase_date < DATE_SUB(NOW(),INTERVAL 3 YEAR) AND system.serial NOT LIKE '%VM%'

Devices with Expired Warranties

This example uses system.warranty_expires and looks for a warranty expiration date prior to today.

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 AND system.warranty_expires <= CURDATE() AND system.serial NOT LIKE '%VM%'

Devices Missing Information

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.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 AND system.purchase_date = '2000-01-01' OR system.function = '' OR system.description = ''

...