...
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
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.
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%.
...
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 = ''
Custom Fields in Queries
This is possible but rather inelegant at the moment.
You'll need to know the id of the relevant field from the fields table. You can find it by going to Menu -> Admin -> Fields -> List Fields. Once you have the ID of the required field you'll need to make a new Query and use this and a join to the fields table.
NOTE - this section is under development.
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.
...
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} |
This is possible but rather inelegant at the moment.
You'll need to know the id of the relevant field from the additional_field table. YTou can find it by going to Menu -> Admin -> Fields -> List Fields. Once you have the ID of the required field you'll need to make a new Query and use this and a join to the additional_field_item table. You'll also need to define a column to put it in (for display). I've included a sample query (attached and below with highlights) that shows what to do.