Table of Contents

*(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.

Image Modified

Image Modified

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.

Image Modified

Image Modified

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 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. Summaries is 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

summary

Web Application Routes