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.
How Does It Work?
A query is essentially a SQL statement. This statement is run against the database with the automatic addition of the limit, filtered to apply to only those requested items and only those items the user has permission to view. A Query can be created using menu -> Manage -> Queries -> Create Queries. Queries contain an org_id and are hence restricted to the appropriate users. A user must have the org_admin or reporter role(s) to create, update or delete a query. All users can execute a query. A query has a name attribute used for the menu item as well as a menu category attribute. This tells the Open-Audit GUI which submenu to place the query in. There is also menu display which should be set to 'y' to enable the query in the GUI ('n' to prevent the query from appearing at all). The query would still run if called using it's $id, regardless of menu display's value.
To view the details of a query, the standard URL structure of /open-audit/index.php/queries/{$id} should be used.
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
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.
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`
The FROM
You should use only those tables that contain attributes you need. I usually use a LEFT JOIN. IE - SELECT system.id AS `system.id`, ip.ip AS `ip.ip` FROM system LEFT JOIN ip ON (system.id = ip.system_id).
All device subtables contain a couple of important columns. $table.system_id and $table.current. $table.system_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'.
FROM user_group LEFT JOIN system ON (user_group.system_id = system.id AND user_group.current = 'y')
The WHERE
In order for Open-AudIT to be able to apply user permissions on items, we mandate the user of WHERE @filter. If you do not use this format, the query::create form will throw a warning. Only users with the Admin role are permitted to create queries that lack this attribute.
Other than that restriction, you are free to select attributes as required. It's a good idea to use the menu -> Admin -> Database -> List Tables item to view the specific tables and their columns. This will enable you to find exactly what you need, rather than trawling through the MySQL console of the schema creation script.
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
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.
An Example
The Elevated User query.
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