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 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:
