*(Under Review for V2)
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.
How Does it Work?
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 collection page.
{img}
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.
{img}
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: 8 org_id: 1 name: Consumed IP Addresses category: Network description: The ip addresses used by a group. 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.os_family AS `system.os_family`, system.description AS `system.description`, ip.ip as `ip.ip` FROM ip LEFT JOIN system ON (system.id = ip.system_id AND ip.current = 'y') WHERE @filter AND ip.ip IS NOT NULL AND ip.ip != '127.000.000.001' AND ip.ip != '' AND ip.ip != '0.0.0.0' AND ip.ip != '000.000.000.000' AND ip.version = '4' GROUP BY ip.id, ip.ip ORDER BY ip.ip link: expose: y edited_by: system edited_date: 2000-01-01 00:00:00
API / Web Access
You can access the /summaries 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. Summaries 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 | summaries::create | /summaries | Insert a new summaries entry. | ||
GET | y | read | summaries::read | /summaries/{id} | Returns a summaries details. | ||
PATCH | y | update | summaries::update | /summaries/{id} | Update an attribute of a summaries entry. | ||
DELETE | y | delete | summaries::delete | /summaries/{id} | Delete a summaries entry. | ||
GET | n | collection | summaries::read | /summaries | Returns a list of summaries. | ||
POST | n | import | import | summaries::create | /summaries/import | Import multiple connections using a CSV. | |
GET | y | execute | execute | summaries::read | /summaries/2/execute | Execute (run) a summary and show the result. |
Web Application Routes
Request Method | ID | Action | Resulting Function | Permission Required | URL Example | Notes |
---|---|---|---|---|---|---|
GET | n | create | create_form | summaries::create | /summaries/create | Displays a standard web form for submission to POST /summaries. |
GET | y | update | update_form | summaries::update | /summaries/{id}/update | Show the summaries details with the option to update attributes using PATCH to /summaries/{id} |
GET | n | import | import_form | summaries::create | /summaries/import | Displays a standard web form for submission to POST /summaries/import. |