| Table of Contents |
|---|
NOTE
As at Open-AudIT 5.x, we no longer have a 'system' table. We now use 'devices'.
Our foreign keys are no longer named $table.system_id, rather $table.device_id.
Introduction
Queries are an important part of Open-AudIT. They are what enables you to get meaningful information from all the device data you have discovered. Along with summaries and groups, they provide a powerful mechanism to extract crucial information.
...
To actually execute the query, append a /execute, thus /open-audit/index.php/queries/{$id}/execute. Alternatively, a URL format of /open-audit/index.php/devices?sub_resource=query&sub_resource_id={$id} can be used. That is how the Open-AudIT GUI does it.
Breaking it Down
The SQL query is essentially broken into three parts.
...
The SELECT section of the query should use full dot notation and also request the field with it's full dot name. IE - SELECT system.id AS `system.id`. Each field should be selected in this fashion to enable GUI side attribute filtering.
| Code Block |
|---|
SELECT systemdevices.id AS `system`devices.id`, systemdevices.icon AS `system`devices.icon`, systemdevices.type AS `system`devices.type`, systemsysdevicestem.name AS `system`devices.name`, systemdevices.domain AS `system`devices.domain`, systemdevices.ip AS `system`devices.ip`, user_group.name as `user_group.name`, user_group.members AS `user_group.members` |
...
You should use only those tables that contain attributes you need. I usually use a LEFT JOIN. IE - SELECT systemdevices.id AS `system`devices.id`, ip.ip AS `ip.ip` FROM system devices LEFT JOIN ip ON (systemdevices.id = ip.systemdevice_id).
All device subtables sub-tables contain a couple of important columns. $table.systemdevice_id and $table.current. $table.systemdevice_id is the link to the devices system.id column. The $table.current column will contain either 'y' or 'n'. This indicates if this row is currently present on the device. For example software may be installed (which would result in software.current = 'y'), but on a subsequent audit it may not be detected. Open-AudIT will then change this rows current attribute to 'n'.
| Code Block |
|---|
FROM user_group LEFT JOIN systemdevices ON (user_group.systemdevice_id = systemdevices.id AND user_group.current = 'y') |
...
| Code Block |
|---|
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 systemdevices.id, user_group.name ORDER BY systemdevices.name |
Refining the Query
Using the web GUI, you can select items to be included or excluded. You can also select a group of devices, then run the query upon that group.
...
Database Schema
The database schema can be found in the application if the user has database::read permission by going to menu: Admin -> Database -> List Tables, then clicking on the details button for the table. Device details are stored in the devices table.
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.
...
The Elevated User query
| Code Block |
|---|
SELECT systemdevices.id AS `system`devices.id`, systemdevices.icon AS `system`devices.icon`, systemdevices.type AS `system`devices.type`, systemdevices.name AS `system`devices.name`, systemdevices.domain AS `system`devices.domain`, systemdevices.ip AS `system`devices.ip`, user_group.name as `user_group.name`, user_group.members AS `user_group.members` FROM user_group LEFT JOIN systemdevices ON (user_group.systemdevice_id = systemdevices.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 systemdevices.id, user_group.name ORDER BY systemdevices.name |
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%.
| Code Block |
|---|
SELECT systemdevices.id AS `system`devices.id`, systemdevices.purchase_date AS `system`devices.purchase_date`, systemdevices.type AS `system`devices.type`, systemdevices.name AS `system`devices.name`, systemdevices.last_seen AS `system`devices.last_seen`, systemdevices.manufacturer AS `system`devices.manufacturer`, systemdevices.model AS `system`devices.model`, systemdevices.description AS `system`devices.description`, systemdevices.function AS `system`devices.function`, locations.name AS `locations.name` FROM systemdevices LEFT JOIN locations ON (systemdevices.location_id = locations.id) LEFT JOIN windows ON (systemdevices.id = windows.systemdevice_id AND windows.current = 'y') LEFT JOIN orgs ON (systemdevices.org_id = orgs.id) WHERE @filter AND systemdevices.purchase_date < DATE_SUB(NOW(),INTERVAL 3 YEAR) AND systemdevices.serial NOT LIKE '%VM%' |
Devices with Expired Warranties
This example uses system.warranty_expires and looks for a warranty expiration date prior to today.
| Code Block |
|---|
SELECT systemdevices.id AS `system`devices.id`, systemdevices.warranty_expires AS `system`devices.warranty_expires`, systemdevices.type AS `system`devices.type`, systemdevices.name AS `system`devices.name`, systemdevices.last_seen AS `system`devices.last_seen`, systemdevices.manufacturer AS `system`devices.manufacturer`, systemdevices.model AS `system`devices.model`, systemdevices.description AS `system`devices.description`, systemdevices.function AS `system`devices.function`, locations.name AS `locations.name` FROM systemdevices LEFT JOIN locations ON (systemdevices.location_id = locations.id) LEFT JOIN windows ON (systemdevices.id = windows.systemdevice_id AND windows.current = 'y') LEFT JOIN orgs ON (systemdevices.org_id = orgs.id) WHERE @filter AND systemdevices.warranty_expires <= CURDATE() AND systemdevices.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.
| Code Block |
|---|
SELECT systemdevices.id AS `system`devices.id`, systemdevices.ip AS `system`devices.ip`, systemdevices.name AS `system`devices.name`, systemdevices.description AS `system`devices.description`, systemdevices.function AS `system`devices.function`, systemdevices.purchase_date AS `system`devices.purchase_date`, systemdevices.type AS `system`devices.type`, locations.name AS `locations.name` FROM system LEFT JOIN locations ON (systemdevices.location_id = locations.id) WHERE @filter AND systemdevices.purchase_date = '2000-01-01' OR systemdevices.function = '' OR systemdevices.description = '' |
List All NMAP Ports, Protocols and Programs for Each Device
This example creates a list of devices and the open Ports, Protocols, and Programs found by the NMAP scan.
| Code Block |
|---|
SELECT systemdevices.id AS `system`devices.id`, systemdevices.type AS `system`devices.type`, systemdevices.name AS `system`devices.name`, systemdevices.domain AS `system`devices.domain`, systemdevices.ip AS `system`devices.ip`, nmap.first_seen AS `nmap.first_seen`, nmap.last_seen AS `nmap.last_seen`, nmap.port AS `nmap.port`, nmap.protocol AS `nmap.protocol`, nmap.program AS `nmap.program` FROM nmap LEFT JOIN systemdevices ON (nmap.systemdevice_id = systemdevices.id) WHERE @filter |
Return a list of all Modules installed on Routers
This example creates a list of all Modules marked as current='y' on devices of type 'router'
| Code Block |
|---|
SELECT systemdevices.id AS `system`devices.id`, systemdevices.type AS `system`devices.type`, systemdevices.name AS `system`devices.name`, systemdevices.manufacturer AS `system`devices.manufacturer`, systemdevices.model AS `system`devices.model`, systemdevices.serial AS `system`devices.serial`, module.description AS `module.description` FROM systemdevices LEFT JOIN module ON (module.systemdevice_id = systemdevices.id AND module.current = 'y') WHERE @filter AND systemdevices.type = 'router' ORDER BY systemdevices.name |