Introduction
Open-AudIT comes with many queries inbuilt. If you require a specific query and none of the pre-packaged queries fit your needs, it's quite easy to create a new one and load it into Open-AudIT for running.
View Query Details
Go to menu: Manage -> Queries -> List Queries.
You will see a list of queries. You can view a query by clicking on the blue view button.
You can execute a query by clicking the Execute button in blue on the right side of the screen, the results will be displayed immediately.
You can also edit or delete any query.
Creating a Query Entry
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
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)
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 HAVING system.warranty_expires <= CURDATE()
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.
CREATE TABLE `queries` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `org_id` int(10) unsigned NOT NULL DEFAULT '1', `name` varchar(200) NOT NULL DEFAULT '', `category` enum('Change','Device','Hardware','Network','Other','Server','Software','User','') NOT NULL DEFAULT '', `description` text NOT NULL, `sql` text NOT NULL, `link` text NOT NULL, `expose` enum('y','n') NOT NULL DEFAULT 'y', `edited_by` varchar(200) NOT NULL DEFAULT '', `edited_date` datetime NOT NULL DEFAULT '2000-01-01 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;
A typical entry looks as below.
id: 39 org_id: 1 name: AD Controllers category: Server description: Active Directory Domain Controllers sql: 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`, system.description AS `system.description`, system.os_family AS `system.os_family`, system.status AS `system.status` FROM system LEFT JOIN windows ON (system.id = windows.system_id AND windows.current = 'y') WHERE @filter AND windows.domain_role LIKE '%Domain Controller' AND system.status = 'production' link: expose: y edited_by: system edited_date: 2000-01-01 00:00:00
API / Web Access
You can access the /queries collection using the normal Open-AudIT JSON based API. Just like any other collection. Please see the API documentation for further details.
Access is provided as part of a roles permissions. Queries is a standard resource and can have create, read, update and delete permissions.
The API routes below are usable from both a JSON Restful API and the web interface. The Web application routes are specifically designed to be called from the web interface (a browser).
API Routes
Request Method | ID | Action | Resulting Function | Permission Required | URL Example | Notes | Example Response |
---|---|---|---|---|---|---|---|
POST | n | create | queries::create | /queries | Insert a new query entry. | queries_create.json | |
GET | y | read | queries::read | /queries/{id} | Returns a query details. | queries_read.json | |
PATCH | y | update | queries::update | /queries/{id} | Update an attribute of a query entry. | queries_update.json | |
DELETE | y | delete | queries::delete | /queries/{id} | Delete a query entry. | queries_delete.json | |
GET | n | collection | queries::read | /queries | Returns a list of queries. | queries_collection.json | |
GET | y | execute | execute | queries::read | /queries/{id}/execute | Execute (run) a query and show the results. | queries_execute.json |
Web Application Routes
Request Method | ID | Action | Resulting Function | Permission Required | URL Example | Notes |
---|---|---|---|---|---|---|
GET | n | create | create_form | queries::create | /queries/create | Displays a standard web form for submission to POST /queries. |
GET | y | update | update_form | queries::update | /queries/{id}/update | Show the query details with the option to update attributes using PATCH to /queries/{id} |