Learn how to write custom SQL queries in Open-AudIT to extract device information, with examples for common use cases like warranty tracking and open ports.

Creating a Query

Queries are one of the most powerful tools in Open-AudIT. Once you understand the basic structure, you can extract almost any information from your device database.

How Queries Work

A query is a SQL SELECT statement. Open-AudIT runs it against the database and automatically applies:

The current user's organisation filter (so users only see their devices)
Any active filters or limits you've applied

The query is executed by navigating to /open-audit/index.php/queries/{id}/execute, or by clicking the Execute button in the GUI.

The `@filter` Requirement

Your WHERE clause must include @filter. Open-AudIT replaces this placeholder with the appropriate user permission filter at runtime.

WHERE @filter AND your_conditions_here

If you omit @filter, Open-AudIT will warn you. Only Admin-role users can create queries without it.

Creating Your First Query

Go to Menu → Manage → Queries → Create Queries
Give the query a Name — this is what appears in the menu
Choose a Menu Category — which submenu it will appear under in the Reports menu
Set Menu Display to y to make it appear in the GUI (or n to hide it but still allow direct execution)
Write your SQL in the query field
Click Submit

Writing the SQL

The SELECT

Use full dot notation for every column. Each column must be aliased with its full name:

SELECT
  devices.id AS `devices.id`,
  devices.type AS `devices.type`,
  devices.name AS `devices.name`,
  devices.ip AS `devices.ip`,
  devices.manufacturer AS `devices.manufacturer`,
  devices.model AS `devices.model`

This format is required for the GUI to enable column filtering.

Use LEFT JOINs to include component tables. Every component table has a device_id column that links back to devices.id, and a current column ('y' or 'n') that indicates whether that component is still present on the device.

FROM devices
LEFT JOIN software ON (software.device_id = devices.id AND software.current = 'y')

The WHERE

Always include @filter:

WHERE @filter AND software.name LIKE '%Adobe%'

Tip

The backtick character (`) is used for field names, not the single quote. On US keyboards it's in the top-left, next to the 1 key.

Example Queries

Devices Older Than 3 Years

SELECT
  devices.id AS `devices.id`,
  devices.name AS `devices.name`,
  devices.type AS `devices.type`,
  devices.manufacturer AS `devices.manufacturer`,
  devices.model AS `devices.model`,
  devices.purchase_date AS `devices.purchase_date`,
  locations.name AS `locations.name`
FROM devices
LEFT JOIN locations ON (devices.location_id = locations.id)
WHERE @filter
  AND devices.purchase_date < DATE_SUB(NOW(), INTERVAL 3 YEAR)
  AND devices.serial NOT LIKE '%VM%'

Devices with Expired Warranties

SELECT
  devices.id AS `devices.id`,
  devices.name AS `devices.name`,
  devices.warranty_expires AS `devices.warranty_expires`,
  devices.manufacturer AS `devices.manufacturer`,
  devices.model AS `devices.model`
FROM devices
WHERE @filter
  AND devices.warranty_expires <= CURDATE()
  AND devices.serial NOT LIKE '%VM%'

Devices Missing Description or Function

SELECT
  devices.id AS `devices.id`,
  devices.name AS `devices.name`,
  devices.ip AS `devices.ip`,
  devices.description AS `devices.description`,
  devices.function AS `devices.function`,
  devices.purchase_date AS `devices.purchase_date`
FROM devices
WHERE @filter
  AND (devices.purchase_date = '2000-01-01'
    OR devices.function = ''
    OR devices.description = '')

All Open Ports Per Device

SELECT
  devices.id AS `devices.id`,
  devices.name AS `devices.name`,
  devices.ip AS `devices.ip`,
  nmap.port AS `nmap.port`,
  nmap.protocol AS `nmap.protocol`,
  nmap.program AS `nmap.program`
FROM nmap
LEFT JOIN devices ON (nmap.device_id = devices.id)
WHERE @filter

Exploring the Database Schema

Not sure which table or column to use? Go to Menu → Admin → Database → List Tables to browse the full database schema. Click the details icon next to any table to see its columns and types.

Creating a Query in Open-AudIT