Widgets are designed to be used on Dashboards.

Enterprise users have complete freedom to create, update and delete widgets as required.

NOTE - As at Open-AudIT 6.0.0, widgets have been (slightly) redesigned.

1 Creating
2 Pie Chart
3 Line Graph
4 Traffic Light
5 Status
6 Database Schema
7 API / Web Access
8 Default Items

Creating

Each widget has a list of properties that are required - and some optional properties.

The common properties are below.

Property	Description

Property	Description
id	The identifier column (integer) in the database (read only).
name	The name given to this item. Ideally it should be unique.
org_id	The Organisation that owns this item. Links to `orgs.id`.
description	Your description of this item.
type	Can be `line`, `pie`, `status` or `traffic`.

Specific fields for each widget type are below. note - some fields may apply to more than one type (as the above apply to all types).

Pie Chart

Property	Description

Property	Description
link	The template for the link to be generated per result line.
primary_text	The heading for display.
where	Any required filter.
pie_column	The column to summarise in the widget.
pie_limit	The number of items to report on.
sql	For advanced entry of a raw SQL query. As per Queries, you must include `WHERE @filter AND` in your SQL.

The above is a good example of a pie chart. This example is a simple widget, designed to show the count and percent of devices by manufacturer. All pie widgets display a count and percent and contain a link for each slice.

The link property is used when you click on a section. It should be the attribute from the devices table that the pie chart is displaying. In the above example, it is devices.manufacturer.

The primary_text in this case is Devices by Manufacturer. If this is not populated, the name will be used instead.

The where column is not used in this example.

The pie_column is devices.manufacturer.

The pie_limit is not used in this case.

For this widget, the sql is not used. Any SQL that is used should provide ‘name’, ‘description’ and ‘count’ as properties in the returned rows. Those properties will be used to populate the widgets attributes. Where SQL is not provided, the attributes are derived using the pie_column.

And example from the Devices by Org widget is below.

SELECT orgs.name as `name`, orgs.id AS `description`, count(devices.id) AS `count` FROM orgs LEFT JOIN devices ON (orgs.id = devices.org_id) WHERE @filter GROUP BY orgs.name, orgs.id

Line Graph

Property	Description

Property	Description
primary_text	The heading for display.
where	Any required filter.
line_table	The primary database table upon which to base this widget.
line_days	How many days in the past should we report on.
line_event	The event to test.
sql	For advanced entry of a raw SQL query. As per Queries, you must include `WHERE @filter AND` in your SQL.

The Line Graph widget is for displaying events - device found, new software found, hardware removed, etc. It uses the change_log table in conjunction with an attribute to test. A simple example is the graph above.

The primary_text is used for the left side ‘Devices’ text.

The name is used for the graph title and in this case is Devices by Manufacturer.

For this widget we have used sql as below. This SQL (for Line Graphs) needs to populate the date and count items for each row.

Also we set line_days to restrict the graph to the last 30 days.

SELECT DATE(audit_log.timestamp) AS `date`, COUNT(DISTINCT audit_log.device_id) AS `count` FROM `audit_log` LEFT JOIN `devices` ON (audit_log.device_id = devices.id) WHERE DATE(audit_log.timestamp) >  DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY DATE(audit_log.timestamp)

Traffic Light


link
icon
help_text
primary_text
secondary_text
primary_query_id
secondary_query_id
ternary_query_id

A traffic light widget has 3x items of note. These are the queries or SQL for each of the red, yellow and green queries. If the red query (or SQL) produces any result above 0 (or a blank), the the widget will be colored in the ‘danger’ theme. If red is empty and yellow is populated, the ‘warning’ color is used. And if both red and yellow are empty, the ‘success’ color is used.

For the widget above, we use a link field of

The icon is set to icon-monitor-cloud. When the field starts with icon- we use the corresponding icon from the Lucide icon set. If it does not start with icon-, we use the svg image of the same name from the /open-audit/public/icons directory.

Status


link
icon
help_text
primary_text
secondary_text
primary_color
secondary_color
sql
secondary_sql
query_id
link_query_id

The icon is set to icon-monitor-cloud. When the field starts with icon- we use the corresponding icon from the Lucide icon set. If it does not start with icon-, we use the svg image of the same name from the /open-audit/public/icons directory.

When creating a widget, if you select the 'Advanced' button you will have the ability to upload your own custom SQL.

The SELECT section of your SQL must contain fully qualified columns and specify certain columns.

Pie charts must return name or my_name, description or my_description and count.

Line charts must return date or my_date, description or my_description and count.

The WHERE section of your SQL must contain WHERE @filter so Open-AudIT knows to restrict your query to the appropriate Orgs.

SQL not containing this condition will result in the widget failing to be executed.

The SQL cannot contain WHERE @filter OR. That SQL will not be executed, however WHERE @filter AND queries are allowed.

An example widget SQL showing devices counted per location.

SELECT locations.name as `name`, 
    locations.id AS `description`, 
    count(system.id) AS `count` 
FROM locations LEFT JOIN system ON (locations.id = system.location_id) 
WHERE @filter 
GROUP BY locations.name

More elaborate SQL can used to group attributes within a range. Below shows the SQL for devices not seen in a pie chart grouped by last seen date ranges.

SELECT IF ( system.last_seen = "2000-01-01", "unknown", ( IF ( system.last_seen < DATE(NOW() - INTERVAL 180 day), "180 Days or more", 
( IF ( system.last_seen < DATE(NOW() - INTERVAL 150 day), "150-180 days", ( IF ( system.last_seen < DATE(NOW() - INTERVAL 120 day), "120-150 days", 
( IF ( system.last_seen < DATE(NOW() - INTERVAL 90 day), "90-120 days", ( IF ( system.last_seen < DATE(NOW() - INTERVAL 60 day), "60-90 days", 
( IF ( system.last_seen < DATE(NOW() - INTERVAL 30 day), "30-60 days", "7-30 days" ) ) ) ) ) ) ) ) ) ) ) ) ) AS `my_name`, 
IF ( system.last_seen = "2000-01-01", "system.last_seen=", 
( IF ( system.last_seen < DATE(NOW() - INTERVAL 180 day), CONCAT("system.last_seen=<", DATE(NOW() - INTERVAL 180 day)), 
( IF ( system.last_seen < DATE(NOW() - INTERVAL 150 day), CONCAT("system.last_seen=>", DATE(NOW() - INTERVAL 180 day), "&system.last_seen=<", DATE(NOW() - INTERVAL 150 day)), 
( IF ( system.last_seen < DATE(NOW() - INTERVAL 120 day), CONCAT("system.last_seen=>", DATE(NOW() - INTERVAL 150 day), "&system.last_seen=<", DATE(NOW() - INTERVAL 120 day)), 
( IF ( system.last_seen < DATE(NOW() - INTERVAL 90 day), CONCAT("system.last_seen=>", DATE(NOW() - INTERVAL 120 day), "&system.last_seen=<", DATE(NOW() - INTERVAL 90 day)), 
( IF ( system.last_seen < DATE(NOW() - INTERVAL 60 day), CONCAT("system.last_seen=>", DATE(NOW() - INTERVAL 90 day), "&system.last_seen=<", DATE(NOW() - INTERVAL 60 day)), 
( IF ( system.last_seen < DATE(NOW() - INTERVAL 30 day), CONCAT("system.last_seen=>", DATE(NOW() - INTERVAL 60 day), "&system.last_seen=<", DATE(NOW() - INTERVAL 30 day)), 
CONCAT("system.last_seen=>", DATE(NOW() - INTERVAL 30 day), "&system.last_seen=<", DATE(NOW() - INTERVAL 7 day))) ) ) ) ) ) ) ) ) ) ) ) ) AS `my_description`, 
count(system.id) AS `count` 
FROM system 
WHERE @filter AND DATE(system.last_seen) < DATE(NOW() - INTERVAL 7 day) 
GROUP BY `my_name` ORDER BY system.last_seen;

Database Schema

The database schema can be found in the application is the user has database::read permission by going to menu: Admin -> Database -> List Tables, then clicking on the details button for the table.

API / Web Access

You can access the collection using the normal Open-AudIT JSON based API. Just like any other collection. Please see The Open-AudIT API documentation for further details.

Default Items

Shipped are a set of default items. These can be found by going to menu: Help → Defaults → Widgets.