Create a SQL Template
From the Portal Menu Bar, select Tools > Templates > SQL Template Designer.
The initial display of the SQL Template Designer window displays:
• Tabs to navigate the configuration steps. Use either the tabs at the top of the window, or click Next at the bottom of the window.
Template Designer | Using the checkboxes, select which parameters you want the user to specify in the Scope Selector window when the report is generated. After you complete the selections within the tabbed Report Designer window, click Next to go to the window where you will Construct the SQL Query. |
Query | Type your SQL statement using the list of valid database views and fields. Click Validate Query to check your SQL statement for valid syntax before moving on to formatting and saving. This Validate Query task actually executes the query in order to validate the syntax. Seemingly simple queries could return a large amount of data and therefore may take some time to validate. See Construct the SQL Query. |
Formatting | To format the output for a report template, select the information that you want included in the report, as well as the report output style—Table, Bar Chart, Donut Chart, Gauge Chart, Area Chart, Stacked Area Chart, Horizontal Bar Chart, Pie Chart, Pivot Table, Stacked Bar Chart, Horizontal Stacked Bar Chart. See Format the SQL Template Output. |
Save & Share | Save and share report templates with others. Assign the category for placement and set up the Inventory Report Configuration. You can also select individual users or groups to share with. |
Configure SQL Template Scope Selector Components
When you create a report template, you must also determine what elements should be provided in the report’s Scope Selector—the interface that enables the user to specify the scope of the data to be displayed in the report. Each time a report is generated from the report template, the Scope Selector enables the selection of parameters to be used for report generation.
Date Range
Check this component to include a Time Period and Data Range selector in the report’s Scope Selector that is displayed when generating the report.
1. Check Date range.
2. Select the Date range row and click Configure to display the Date range window. The Date range window allows you to set a Default Time Period. This becomes the default selection in the scope selector.
3. Configure the Date component to include the time with the date range. Choose
Yes or
No to specify if the time will be shown along with the date range in the Scope Selector. This configuration results in a report Scope Selector that includes the following:
Host Groups and Client Scope
This SQL Template Designer component enables you to customize how the report will display the host groups’ data.
Use this configuration to specify if the cascade to sub-groups option is available and, if available, the default setting—Checked or Unchecked.
Array Scope Selector
This SQL Template Designer component enables you to add a scope selector for reports with Arrays and groups of arrays.
Datastore Scope Selector
This SQL Template Designer component enables you to add a scope selector for reports with Datastores.
VM Servers Scope Selector
This SQL Template Designer component enables you to add a scope selector for reports with VM Servers.
VM Guests Scope Selector
This SQL Template Designer component enables you to add a scope selector for reports with VM Guests.
Custom Text Fields
In the Template Designer pane, a user can define fields that can have values substituted into a query. When the report is generated, the Scope Selector window presents the fields that can be selected, thus providing dynamic input for the report.
This option enables you to create up to three free-form text fields, where you can specify a field that you want to use in your query, plus set a default value. For example, you might enter Host Name, so that when you form the query, you can specify a host name to be inserted into the query.
Example of Custom Text Field Configuration in a SQL Template
The following steps illustrate the advantage of Custom Text Fields.
1. In the SQL Template Designer, select the Custom Text Fields component and click Configure.
2. In the Custom Text Fields window, define a field named Host ID with a data type of Number and click OK.
3. To use this newly configured Custom Text Field, be sure to select the checkbox.
4. In the Query window, enter the following SQL query by typing a partial statement and then double-clicking to select fields:
select * from aps_v_host_volume where host_id > ${freeText1}
This query can be constructed by combining typing with double-clicking selections in the window, as shown in the following example. For a complete list of database views and columns, you can access the Database View Help. See
Access Help for Database Views. Refer to
Help for Database Views for more information.
• Enter a partial query: select * from aps_v_host_volume where
• Double-click a field to insert the name into the query.
• Type an operator: >
• Double-click the freeText1 variable to insert it into the query.
The resulting query will be:
select * from aps_v_host_volume where aps_v_host_volume.host_id > ${freeText1}
5. Click Validate Query.
6. In the Formatting window, select all the fields for a table.
7. Save the report template with a name and a menu group.
8. Generate the report from this report template and in its Scope Selector window, provide a value for the Host ID field. This value will be passed to the query.
Access Help for Database Views
1. Click the icon beside the Available views and fields drop down to access column descriptions for Portal database views.
2. Navigate through descriptions for Base Portal Views, base views for licensed modules, and third-party vendor-specific views for your building your query.
3. If required, download a pdf version of the Views for offline use.
Static Custom Combo Box
The Report Designer Static Combo Box component enables the flexibility of offering the selection of various characteristics when the report is generated—similar to the way the out-of-the-box reports handle options, such as Event Type or Job Type. A Combo Box becomes available in the Scope Selector, enabling a user to select items from a drop-down list. This is particularly useful in environments where custom attributes have been defined for objects, enabling the user to select specific attributes at runtime.
Specify a heading, along with a list of values that will be displayed as a drop-down selection. You can also set the default value to be selected.
To include a blank or no choice option, specify the list of values in the form:
,option 1,option 2
If this no choice option is selected when the report is generated, an empty string will be passed to the SQL expression.
Example of a Combo Box Configuration in a SQL Template
1. Create a Custom Combo Box to enable the user to select all hosts for a particular Make:
2. This Combo Box can be used in a report template query, such as:
select * from apt_v_server where apt_v_server.make = '${freeCombo1}'
Note: Report designer variables are listed at the bottom right of the dialog window. Double-click a freeCombo variable to insert it into the query. If the
${freeCombo1} value is a string, it must be enclosed in single quotes to be evaluated as a text field, as shown in the above example. To determine if a database view field is a string or numeric, refer to the Database Views Help. For more information, see
Help for Database Views.
3. Format this report as a table.
4. Save it as List Hosts by Make in a Menu Group.
5. Generate a report from this newly saved report template.
In this example, a Combo Box heading was specified with a list of values that will be presented in a drop-down list in the scope selector, when the user generates the report:
6. Select a Make from the drop-down list and click Generate.
Query Custom Combo Box
The SQL Template Designer offers a feature to design a combo box that is populated with the results of the query. In addition to configuring the Query Custom Combo Box, a relevant report template query must be constructed to enable accurate report filtering using the combo box.
1. In the Template Designer component, double-click Query custom combo box to access the configuration window.
2. In the Heading field, enter the heading that will appear in the report template’s scope selector with the drop-down list.
3. In the Custom Combo Box query field, supply a query with the following components and then click Validate.
SQL command | SELECT DISTINCT |
Key field, Field value | The comma-separated key-value pair is derived from a published view. For example: Key: storage_array_id Value: array_name The Key in the key-value pair is what will substitute the ${queryCombon} variable and the Value is shown in the Combo box. Use the help for descriptions of the published database views, including the available fields. See Help for Database Views. Access these views and fields using the SQL Template Designer Query tab. See Construct the SQL Query. |
Published view | End the SELECT statement with FROM and the published view, with optional list criteria. For example: FROM aps_v_array_group ORDER BY array_name ASC |
Sample Query Custom Combo Box Queries
SELECT DISTINCT server_id, server_name FROM apt_v_job
SELECT DISTINCT client_id, client_name FROM apt_v_job
SELECT DISTINCT product_type, product_type_name FROM apt_v_job
SELECT DISTINCT job_type, job_type_name FROM apt_v_job
SELECT DISTINCT vendor_status, vendor_status_name FROM apt_v_job
SELECT DISTINCT policy_id, policy_name FROM apt_v_nbu_job_detail
SELECT DISTINCT policy_type, policy_type_name FROM apt_v_nbu_job_detail
SELECT DISTINCT media_server_id, media_host_name FROM apt_v_nbu_job_detail
SELECT DISTINCT storage_array_id, array_name FROM aps_v_array_group
4. In the Query tab, enter a SQL query that will use the value selected in the populated combo box. For example:
select * from aps_v_array_group WHERE storage_array_id='${queryCombo1}'
Note: Report designer variables are listed at the bottom right of the dialog window. Double-click a queryCombo variable to insert it into the query. If the
${queryCombo1} value is a string, it must be enclosed in single quotes to be evaluated as a text field. To determine if a database view field is a string or numeric use the Database Views Help. For more information, see
Help for Database Views.
Example of a Query Custom Combo Configuration in a SQL Template
The following query produces the Ultimate NetBackup Job Status Report.
In the SQL Template Designer Query Custom Combo box, enter: select window_group_id, window_group_name from apt_v_date_window order by window_group_name
Then, in the Query, use the following query to report Ultimate Job Status:
SELECT
a.nbu_job_id,
a.client_id,
b.client_host_name,
b.policy_name,
b.job_type_name,
decode(a.overall_status,0,'Success',
1,'Partial',2,'Queued',3,'Running','Failed') overall_status,
a.start_date,
a.finish_date,
a.kilobytes,
file_pathlist,
decode(was_restarted,1,'Y','N') was_restarted
FROM table (nbu_rtd.listJobSummaryAfterRestart (
${startDate},
${endDate},
${queryCombo1},
${spHosts},
null,
null,
null)) a
, apt_v_nbu_job_detail b
where a.job_id = b.job_id
Construct the SQL Query
The SQL Template Designer supports SQL select statements. You cannot specify UPDATE, DROP, ALTER, or CREATE statements.
To view the available database views and fields that can be used when you create a query, click the Help icon. See
Access Help for Database Views. Also, for examples of queries, see
Sample SQL Queries.
1. In the Query window, construct your select statement using the following tips:
• Use the drop-down list at the bottom left of the window to select views and fields.
• To insert a view name into a query, double-click a field and then delete the field portion. For example, in the aps_v_database_datafiles_log view, click on datafile_id. Then, in the query, delete the .datafile_id portion.
• Double-click on fields to insert them into the query with the correct syntax.
• Double-click on variables in the list at the right of the window, to insert variables with proper syntax into the query—for example, ${endDate}
• If you checked Host Group and Client Scope in the initial tabbed Template Designer window, you will see that the drop-down list of Template Designer Variables (at the right of the window) includes Report Scope selections. Using these selections, you can enumerate a list of values for Host Groups (hostGroups) and Clients (hosts).
• All evaluated columns—for example, nvl(t2.das_capacity,0)—must have an alias name.
2. Click Validate Query.
• Correct the statement before you proceed. Use the available lists to view valid tables, fields, and variables.
Help for Database Views
APTARE provides a set of published read-only database views into the reporting database and related object model. The purpose of these views is to provide a read-only reporting or data extraction mechanism for advanced users of APTARE IT Analytics.
Database views provide a native SQL interface into the reporting database. APTARE publishes a collection of views that overlay the underlying database tables. Using these read-only views, you can write your own reports or stored procedure handlers to query the reporting database. These views provide a simple and fast mechanism to access a read-only view into the raw data maintained within the reporting database.
There are multiple types of database views:
• Base Portal Views. Provides read access to the properties for every inventory item that the APTARE IT Analytics maintains.
• Base Licensed Module Views. Provides read access to information about a given underlying APTARE licensed module.
• Vendor Specific Views. Provides read access to data specific to a third-party vendor, such as NetApp or HDS.
Use the Help specific to the database views to look up the valid tables and fields relevant to your environment. You can use the help as you build the query or you can download it as a PDF to use offline. Access column descriptions for Portal database views by selecting a view and then clicking the icon beside the Available views and fields drop down. You can also just click the icon to see the full listing of all the views.
Navigate through descriptions for Base Portal Views, Base Views for licensed modules, and third-party vendor-specific Views for your building your query.
Accessing the Published Database Views from the CLI
The underlying Oracle database can be accessed via the command-line interface (CLI). APTARE IT Analytics includes a separate Oracle user account that is specifically designed and granted SELECT access to the APTARE IT Analytics Published Database Views.
The oracle user account for the APTARE IT Analytics Published Database Views is called aptare_ro. The default password for this account is set to aptaresoftware123. This user account can ONLY access the published database views and does not have any update or insert privileges.
Special Characters in SQL Queries
Certain characters have a special meaning for Oracle functions. In a SQL Template Designer query, a special character must be
escaped in order for the parser to treat it as its intended literal value. The following examples illustrate how to handle commonly used special characters in a query. See also,
Example of Using a Special Character as a Literal in a SQL Query.
• Backslash ( \ ) - Use "\\" instead of "\"
• Dollar sign ( $ ) - The $ character must be prefaced (escaped) with a backslash: '.\$'
Sample SQL Queries
The following queries can serve as a starting point for how to create your own custom queries. In fact, you simply can copy and paste a query from these examples to demonstrate the results in the SQL Template Designer.
Example of a Query for Host Attributes
The following query lists all the attributes associated with a host/server:
select *
FROM apt_v_server_attribute
WHERE host_id IN (${hosts})
Using the apt_v_server_attribute View in Queries
The database view, apt_v_server_attribute, is dynamically created using the attributes that you create for your environment. When you initially look at this view, you only will see host_id and host_name. This view is recreated during the upgrade process, based on the attributes that you have configured.
If you add or modify server attributes in any way, in order to immediately use this view in queries in the SQL Template Designer, you will need to execute the following steps to manually recreate the apt_v_server_attribute database view:
1. Connect to the database as a Portal user:
sqlplus <userID>/<pwd>
2. Execute the following:
EXECUTE dynsql_pkg.recreateDynAttributeView;
3. Connect to the database as sysdb:
sqlplus / as sysdba
4. Execute the following:
CREATE OR REPLACE VIEW aptare_ro.apt_v_server_attribute AS SELECT * FROM portal.apt_v_server_attribute WITH READ ONLY;
Considerations for Attributes Used in SQL Template Designer Queries
The attribute database view, apt_v_server_attribute, is dynamically created from server attributes you enter in the Portal. During the creation of this database view, several rules are applied to facilitate the use of this view in queries in the SQL Template Designer. When you see the attribute names listed in the SQL Template Designer Formatting tabbed window, you’ll see that the following conversions have been made to your Attribute Names—that is, the view’s column names.
• All characters are lowercase.
• Special characters, such as “:” or “/” or a space, are converted to an underscore.
• If the attribute name begins with a number or a special character, it will be replaced with: c_
• If the conversion process results in duplicate names, the attribute name will have a suffix appended to differentiate the duplicates; for example: _1 or _2
• Names are truncated to 30 characters.
Example of a Query of Failed Backup Jobs
The following example results in a table of failed jobs.
1. In the SQL Template Designer, check both Date Range and Host Groups and Client Scope.
2. In the Query window, enter the following select statement and click Validate Query:
SELECT apt_v_job.job_id,apt_v_job.client_id, apt_v_job.client_name,
apt_v_job.server_id, apt_v_job.server_name,
apt_v_job.start_date,apt_v_job.vendor_state_name,
apt_v_job.vendor_status_name
FROM apt_v_job
WHERE apt_v_job.summary_status = 2 --Failed jobs
AND apt_v_job.start_date > ${startDate}
AND apt_v_job.start_date < ${endDate}
AND apt_v_job.client_id IN (${hosts})
3. In the Formatting window, select the fields to be displayed. For this example, it makes sense to Select All and display the report as a Table.
4. Click Next, enter a report name and select a Menu Group. Then, click Finish.
5. When you run this report, specify either a time period or start and end dates. You also can modify the scope to generate the report for a specific host group. The output will look something like this:
Example of the SQL Custom Join Feature in a SQL Template
Backup Exec data collection does not populate tables related to tape media—for example, apt_v_tape_media. To include this view in a query so that it will work with Backup Exec data, you’ll need to use an “outer join” (as denoted with (+) in the following query).
select apt_v_job.server_name, apt_v_job.job_type, apt_v_job_tape_media.media_name, apt_v_job.client_name, to_char(apt_v_job.start_date, 'YYYY-MM-DD hh:mm:ss AM') start_date, to_char(apt_v_job.finish_date, 'YYYY-MM-DD hh:mm:ss AM') finish_date, apt_v_job.summary_status, apt_v_job_message_log.message, apt_v_job_tape_media.tape_media_id, apt_v_job.kilobytes
from apt_v_job, apt_v_job_tape_media, apt_v_job_message_log
where apt_v_job.job_id = apt_v_job_tape_media.job_id (+)
and apt_v_job.job_id = apt_v_job_message_log.job_id
and apt_v_job.server_id in (${rp.hosts})
and apt_v_job.start_date BETWEEN ${rp.startDate} AND ${rp.endDate}
ORDER BY apt_v_job.server_name, apt_v_job.start_date
Example of a Sparkline Query in a SQL Template
A sparkline presentation of the data can be added as a column to a tabular report to plot a series of values. Sparklines enable an at-a-glance view of data spikes that may indicate an issue that requires attention.
The basic requirements for a sparkline chart in a SQL template include:
• Table template. Sparklines are embedded in a table cell.
• Data over time. The series of values can be derived from: start_date between ${startDate} AND ${endDate} in the query.
• Comma-separated list of string values. Use the
collectString pipelined function to achieve this.
The following example graphs a series of client failure values as a sparkline in a tabular report.
1. In the SQL Template Designer, check Date Range and Host Groups and Client Scope.
2. In the Query window, enter the following select statement and click Validate Query:
with spark as (
select trunc(start_date), client_id, client_name, product_type_name,count(job_id) failed_count
FROM apt_v_job
WHERE client_id in(${hosts})
AND start_date between ${startDate} AND ${endDate}
AND summary_status = 2
group by client_id, client_name, product_type_name, trunc(start_date)
)
select display_name, product_type_name, rtd.collectString(cast(collect(TO_CHAR(failed_count)) as StringListType), ', ') failed_count, rtd.collectString(cast(collect(TO_CHAR(failed_count)) as StringListType), ', ') failed_count_area
from apt_v_server h, spark s
where h.server_id = s.client_id
group by display_name, product_type_name
order by 1,2
3. In the Formatting window, select all the fields to be displayed and display the report as a Table.
4. In the Formatting window, for failed_count, select the Column Sparkline formatter and for the failed_count_area, select the Area Sparkline formatter.
5. Click Next, enter a report name and click Finish.The output will look something like this:
Example of Sums in a SQL Template
The following steps can be used to create the NetBackup Catalog Space by Client List. This example demonstrates how to include sums of a field.
1. In the SQL Template Designer, check Host Groups and Client Scope.
2. In the Query window, enter the following select statement and click Validate Query:
SELECT client_host_name, sum(nbr_of_files),sum(nbr_of_files)*150/1024/1024
FROM apt_v_nbu_job_detail
WHERE client_id in (${hosts})
AND summary_status is not null
AND expiration_date <= sysdate
GROUP BY client_host_name
ORDER BY sum(nbr_of_files) DESC
3. In the Formatting window, select all the fields to be displayed and display the report as a Table.
4. Click Next, enter a report name and click Finish.The output will look something like this:
Example of Using a Special Character as a Literal in a SQL Query
Outside of the Portal’s SQL Template Designer environment, certain characters and symbols have a special meaning to Oracle functions when querying an Oracle database directly. For example, the $ can be used as a parameter in an Oracle function. In the context of the SQL Template Designer, this $ character must be prefaced (escaped) with a backslash, as shown in the following example.
select
REGEXP_REPLACE(APT_V_TSM_JOB.DOMAIN_NAME|| '.' ||
APT_V_TSM_JOB.SCHEDULE_NAME,'.\$','') GROUP_NAME
from APT_V_TSM_JOB
