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

Web Application Routes