SQL-based reports are defined using explicit SQL. After creating and saving a report, it can be scheduled to run automatically in the Automation Rules module.
The following describes how to create a SQL-based Report to report on a device and variable parameters will be used to prompt the user to type in the name of a device when running the report.
-
Navigate to Management > Reports.
-
Select Add, then select Add SQL-based Report. Name the report and add a brief description.
-
Set Max. Result to a manageable maximum. A Max. result of -1 displays unlimited results.
-
For SQL, enter a SQL query to generate the desired report variables. For this example, enter the following query into the SQL field:
select * from vx_dev where name=? (Select all from the vx_dev table where the device name matches the user selection)-
Note: For every parameter defined using Variable Parameters, use a prepared statement (question mark placeholder) to bind variables to user input.
-
-
Select Variable Parameters.
-
In Type, select the parameter type. For this example, select Devices and press OK.
-
Enter a user prompt for the parameter, then specify the Device type.
-
Note: Because variable parameters will replace the question mark placeholders in the SQL query, the number of parameters must match the number of question marks in the query.
-
-
In Parameter, select the parameter and press OK.
-
Press Save and Close.
-
To run the report, go to Reports and press Run. The Report Parameters window opens and prompts the user to select a predefined device to report on. Select the device and press OK.