Finding Devices With Custom Queries in Open-AudIT

When considering what a typical network is and what is monitored in it, a few devices usually come to mind; Switches, Routers, HUBs, etc. With the advent of the Internet of Things (IoT) the variety of monitored devices is increasing even further, adding IoT devices such as thermostats along with the devices that regulate them (Microcontrollers, Single-Board-Computers, etc.). 

Without having inventory management software, it becomes challenging to keep track of where and what devices are on your network. Opmantek’s Open-AudIT software not only provides in-depth network auditing; it is also easy to create custom queries with this information. These queries allow you to find and organize devices in whatever manner you or your team find best.

Open-AudIT used as a configuration management database system (one of its many uses) ships with 33 pre-built queries to assist in finding and organizing devices. These queries range from what memory and processors are used, what software is currently installed, to what IP addresses are consumed on the network. 

Understanding that no two networks or organizations are alike, there is a need to have the ability to customize queries to fit your team’s needs. Open-AudIT accomplishes this through the use of Custom Queries. A query is essentially a SQL statement that runs against the database to only display the requested filtered items the user has permission to view. 

A query can be created in Open-AudIT by navigating to the menu -> Manage -> Queries -> Create Queries. 

The SQL query can be broken into three parts:

1. The SELECT statement is used to select the data from a database.

The SELECT section of the query should use full dot notation and also request the field with its full dot name. For example;

SELECT system.id AS `system.id`,

system.name AS `system.name`,

`system.os_name` AS `system.os_name`

This would cause the query to display the system id’s, system name, and operating system.

2.The FROM clause lists the tables, and any joins required for the SQL statement.

The FROM clause will decide what database you are getting these specific system id’s and should only contain the tables with the attributes you need.

For example – FROM system specifies which table the system.id and system.name is coming from.

3. The WHERE clause extracts only those records that fulfil a specified condition. For Open-AudIT to apply user permissions on items, we mandate the use of the WHERE @filter. If you do not use this format, the query::create form will show a warning.

Only users with the Admin role are permitted to create queries that lack this attribute. Continuing using our earlier examples we can add the clause WHERE @filter AND system.type = ‘computer’.  Specifying the type to equal computer will only display devices that are computers quickly allowing you to filter out all other irrelevant devices. The query has now reached its final form:

SELECT system.id AS `system.id`,
system.name AS `system.name`,
system.os_name AS `system.os_name`
FROM system
WHERE @filter
AND system.type = ‘computer’

This will result in displaying only the system names, id’s, and operating systems of devices that are the system type of computer.

NOTE – You are free to select any attributes desired when creating your custom query. To further help in creating queries it is a good idea to use the menu -> Admin -> Database -> List Tables to view the specific table structure. This provides a valuable resource for finding the attributes necessary when creating any custom query.

Open-AudIT grants you the ability to scan your entire network for devices and organize them in ways that make sense for your team. Inventory management is a breeze with the ability to find, filter, and group any device using custom queries. The growing number of different devices added to networks makes having tools like Open-AudIT in your kit more valuable than ever.

For more information on FirstWave’s Open-AudIT, other FirstWave solutions, or to schedule a demonstration, please visit our website at www.firstwave.com. You can also email us at contact@firstwave.com.