Using Pipeline Functions for Report Queries
The following functions, sometimes called pipelined functions, can be used to query the StorageConsole database to further expand the capabilities of the SQL Template Designer custom report templates.
NOTE: In most cases, when inserting a function into an SQL query, it must be prefaced with: rtd.
The functions fall into the following categories:
General Functions
APTlistOfDates
This function enables you to provide a list of dates to a query.
FUNCTION APTlistOfDates(
startDate IN DATE,
endDate IN DATE,
groupBy IN NUMBER)
This function returns a character string.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
Example Query
select * from table(rtd.APTlistOfDates(to_Date('10012008','MMDDYYYY'), to_date('10032008','MMDDYYYY'), 11))
The output from this example:
THE_DATE
--------------------
01-Oct-2008 00:00:00
02-Oct-2008 00:00:00
03-Oct-2008 00:00:00
The third argument—groupBy—controls the granularity of the results, as follows:
Value | Description |
1 | Group by 15 minutes |
2 | Group by 30 minutes |
10 | Group by hour |
11 | Group by day |
12 | Group by week |
13 | Group by month |
14 | Group by quarter |
15 | Group by year |
aptStringConcat
Use this function in a SQL Template Designer query or the Dynamic Template Designer to concatenate string values returned from a table.
Example Query
select aptStringConcat(hostname) from apt_v_server where rownum < 10
This query in the SQL Template Designer will generate output similar to:
collectString
Use this function in a SQL Template Designer query or in the Method Designer to concatenate distinct values.
SELECT rtd.collectString(SET(CAST(COLLECT(<column name>) AS stringListType)), <delimeter>) from <table name>;
The following restrictions apply:
• While concatenating NUMBER fields, explicitly cast the column to VARCHARs() as shown in the following example. While casting, we need to specify the size of the column.
Example:
SELECT rtd.collectString(SET(CAST(COLLECT(cast(client_id as varchar2(10))) AS stringListType)),', ') name from apt_v_job;
• For STRING or NUMBER columns, this function is restricted to up to 512 characters.
Example:
SELECT rtd.collectString(SET(CAST(COLLECT(hostname) AS stringListType)),', ') name from apt_v_server;
getLicenseClientDetail
This function can be used to determine backup license usage details. It returns the host ID, host name, backup vendor, host type, and the last updated date.
select * from TABLE(rtd.getLicenseClientDetail)
getServerAttributeValue
This function returns a string—the host/server attribute value.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
getServerAttributeValue(
hostID,
attributeName)
Argument Name | Type |
hostID | Number |
attributeName | String in single quotes |
Example Query
SELECT server_id, status
FROM (
SELECT server_id, rtd.getserverattributevalue(server_id,'Status') Status
FROM apt_v_server s
)
WHERE status IS NOT NULL
ORDER BY server_id
The function listed above provides a reliable method of reporting on host/server attributes via the SQL Template Designer.
The apt_v_server_attribute is a dynamically created view, specific to your environment.
getObjectAttributeValue
This function returns a string—the object’s attribute value.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
getObjectAttributeValue(
objectID,
attributeName,
attributeType)
Argument Name | Type |
objectID | Number |
attributeName | String in single quotes |
attributeType | String in single quotes |
Example Query
In the following SQL Template Designer query, it assumes that you have configured a Location attribute for your storage arrays and you want to return the location of the array’s data center for your report.
SELECT
rtd.getObjectAttributeValue(sa.storage_array_id,'Location','A') AS " Data Center Location",
sa.array_name AS "Array Name",
sa.allocated_gb AS "Allocated (GB)"
FROM aps_v_storage_array sa
WHERE
rtd.getObjectAttributeValue(sa.storage_array_id,'Location','A') IS NOT NULL
The resultant report will display a table with three columns: Data Center, Array Name, and Allocated (GB).
getChildServerGroupContextById
This function provides the capability for getting data for a host group that is not at the top level of the host group hierarchy. This limits the scope of the query to a sub-group.
FUNCTION getChildServerGroupContextById(
groupID,
clientID,
depthLevel)
This function returns a character string.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
Specify the following arguments:
Argument Name | Type | Length | Description |
groupID | Number | 6 | This is the highest level host group to be accessed. See Identifying a Host Group’s ID. |
clientID | Number | 6 | Specify a specific host ID. See Identifying a Host ID. |
depthLevel | Number | | This number indicates the number of levels down the hierarchical host group tree you want the query to search. |
Example Query:
SELECT rtd.getChildServerGroupContextById(100209,server_id,3) FROM apt_v_server
getServerGroupContextById
This function provides the capability for getting specific host group data.
FUNCTION getServerGroupContextById(
groupID,
clientID,
depthLevel)
This function returns a character string. Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
Specify the following arguments:
Argument Name | Type | Length | Description |
groupID | Number | 6 | This is the highest level host group to be accessed. See Identifying a Host Group’s ID. |
clientID | Number | 6 | Specify a specific host/server ID. See Identifying a Host ID. |
depthLevel | Number | | This number indicates the number of levels down the hierarchical host group tree you want the query to search. |
Example Query:
SELECT rtd.getServerGroupContextById(100000,server_id,3) FROM apt_v_server
secsToHoursMinSecs
This function is useful for converting job duration to a readable format.
secsToHoursMinSecs(totalSecs IN NUMBER)
Example Query:
SELECT rtd.secsToHoursMinSecs (duration_secs) FROM apt_v_job
Backup Manager Functions
APTgetJobTypeName
This function can be used to query database tables related to backup job details, such as apt_v_job and apt_v_nbu_job.
FUNCTION APTgetJobTypeName(
productType,
jobType,
vendorJobType)
This function returns a character string. Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
Argument Name | Type | Length |
productType | Number | 2 |
jobType | Number | 4 |
vendorJobType | Number | |
Example Query 1
SELECT rtd.APTgetJobTypeName(j.product_type, j.job_type, NULL) FROM apt_v_job j
Example Query 2
SELECT rtd.APTgetJobTypeName(j.product_type, j.job_type, n.vendor_job_type)
FROM apt_v_nbu_job n, apt_v_Job j
WHERE j.job_id = n.job_id
The output from this example:
Full Backup
Appl Backup
Appl Backup
APTgetTapeDriveStatusName
Use this function to access data from:
apt_v_tape_drive
This function returns a character string. Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
FUNCTION APTgetTapeDriveStatusName(
vendorProductType,
vendorDriveStatus)
Argument Name | Type | Length |
vendorProductType | Number | |
vendorDriveStatus | Character | 1 |
Example Query
SELECT rtd.APTgetTapeDriveStatusName(d.product_type, d.vendor_drive_status) FROM apt_v_tape_drive d
The output from this example:
Up
In-Use
In-Use
Mounting
getFullPathname
Use this function to access data from:
apt_v_client_file
This function returns a character string. Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
FUNCTION getFullPathname(
clientID,
clientFileID)
Argument Name | Type | Length |
clientID | Number | 6 |
clientFileID | Number | 10 |
Example Query
SELECT rtd.getFullPathname(d.client_id, d.client_file_id) FROM apt_v_client_file d
listJobSummaryAfterRestart
This function returns a list of NetBackup jobs. It could be used, for example, to determine ultimate success for NetBackup jobs within a backup window. For example, if the backup window was set for 4:00 p.m. to 4:00 p.m. the next day, if the restart completed before the end of the backup window, it is considered to be successful.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
listJobSummaryAfterRestart(
startDate,
finishDate,
backupWindowId,
clientList,
excludeStatusList,
excludePolicyList,
excludeClientList)
Argument Name | Type |
startDate | DATE with the value derived from the Scope Selector |
finishDate | DATE with the value derived from the Scope Selector |
backupWindowId | Numeric |
clientList | numberListType Example: numberListType(1,2,3) |
excludeStatusList | numberListType Example: numberListType(1,2,3) |
excludePolicyList | numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2)) |
excludeClientList | numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2))) |
listJobSummaryAfterRestartNBW
This function returns a list of NetBackup jobs that do not have a backup window. It could be used, for example, to determine ultimate success for NetBackup jobs. If the job restart finishes within the timeframe (startDate - finishDate), it is considered successful.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
listJobSummaryAfterRestartNBW
startDate,
finishDate,
clientList,
excludeStatusList,
excludePolicyList,
excludeClientList,
jobTypeList)
Argument Name | Type |
startDate | DATE with the value derived from the Scope Selector |
finishDate | DATE with the value derived from the Scope Selector |
clientList | numberListType Example: numberListType(1,2,3) |
excludeStatusList | numberListType Example: numberListType(1,2,3) |
excludePolicyList | numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2)) |
excludeClientList | numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2))) |
jobTypeList | numberListType Example: numberListType(1,2,3) |
listJobSummaryAfterRestart for NetWorker Backup Jobs
This function returns a list of NetWorker jobs within a backup window. It could be used, for example, to determine ultimate client backup success for EMC NetWorker jobs. If the job restart finishes within the time frame (startDate - finishDate), it is considered successful.
In a SQL Template Designer query, the function name must be prefaced with: leg_rtd.
listJobSummaryAfterRestart(
startDate,
finishDate,
backupWindowId,
clientList,
excludeStatusList,
excludePolicyList,
excludeClientList)
Argument Name | Type |
startDate | DATE with the value derived from the Scope Selector |
finishDate | DATE with the value derived from the Scope Selector |
backupWindowId | If no backup window is defined, the default is 12 hours from the start date. • To configure a Backup Window via the Portal: Admin > Backup Windows > Add |
clientList | numberListType Example: numberListType(1,2,3) |
excludeStatusList | numberListType Example: numberListType(1,2,3) |
excludePolicyList | numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2)) |
excludeClientList | numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2))) |
Example Query
NOTE: In the following sample Report Template query, spHosts is the same as Hosts, but is specifically for use in stored procedures.
SELECT job_id, c.hostname client, s.hostname server, sc.schedule_name, start_date, finish_date, kilobytes, DECODE(overall_status,0, 'Successful',1,'Partial',3,'Running',4,'Failed', NULL) overall_status,
DECODE(was_restarted,0,'No','Yes') was_restarted, vendor_status, orig_vendor_status, client_resource_name, window_start_date, window_finish_date
FROM TABLE(leg_rtd.listJobSummaryAfterRestart(${startDate},${endDate},100000,${spHosts},
null,null,null)) t, apt_v_server s, apt_v_server c, apt_v_leg_schedule sc
WHERE t.server_id = s.server_id
AND t.client_id = c.server_id
AND t.schedule_id = sc.schedule_id(+) ORDER BY t.start_date
listOfBackupWindowDates
Two versions of this function enable the following functionality:
• returns a list of backup windows explicitly supplied in the function
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
Explicitly Supply the Backup Windows
FUNCTION listOfBackupWindowDates(
startDate,
finishDate,
backupWindowList IN apt_BackupWindowListType)
Argument Name | Type |
startDate | Date |
finishDate | Date |
backupWindowList | |
Based on the backup window definition, the above function returns a list of the adjusted start and finish dates.
Example Query
SELECT start_date, finish_date
FROM TABLE(rtd.LISTOFBACKUPWINDOWDATES(TO_DATE('01012008','MMDDYYYY'), to_DATE('01072008','MMDDYYYY'),
apt_BackupWindowListType(
APT_BACKUPWINDOWTYPE('Mon', 40, 64),
APT_BACKUPWINDOWTYPE('Tue', 64, 88),
APT_BACKUPWINDOWTYPE('Wed', 88, 112),
APT_BACKUPWINDOWTYPE('Thu', 112, 136),
APT_BACKUPWINDOWTYPE('Fri', 136, 160),
APT_BACKUPWINDOWTYPE('Sat', 160, 184),
APT_BACKUPWINDOWTYPE('Sun', 16, 40)
)
))
The output from this example:
12/31/2007 4:00:00 PM 1/1/2008 3:59:59 PM
1/1/2008 4:00:00 PM 1/2/2008 3:59:59 PM
1/2/2008 4:00:00 PM 1/3/2008 3:59:59 PM
1/3/2008 4:00:00 PM 1/4/2008 3:59:59 PM
1/4/2008 4:00:00 PM 1/5/2008 3:59:59 PM
1/5/2008 4:00:00 PM 1/6/2008 3:59:59 PM
1/6/2008 4:00:00 PM 1/7/2008 3:59:59 PM
listOfBackupWindowDates (by Backup Window ID)
Retrieve Backup Windows (Defined in the Portal) with a Backup Window ID
FUNCTION listOfBackupWindowDates(
startDate,
finishDate,
windowGroupID IN apt_date_window.windows_group_id%TYPE)
Argument Name | Type |
startDate | Date |
finishDate | Date |
windowGroupID | |
Based on the backup window definition, the above function returns a list of the adjusted start and finish dates.
Example Query
The following examples provide a basic idea of the function. These queries should serve as only an example of how the function might be incorporated into a more sophisticated query.
In a SQL Template Designer query, first determine the Backup Window IDs:
select window_group_id, window_group_name from apt_v_date_window
Then, use the Backup Window ID (window_group_id value from above example) in the following query:
select * from TABLE(rtd.listOfBackupWindowDates(TO_DATE('01012010','MMDDYYYY'), to_DATE('01072010','MMDDYYYY'), 100011 ))
Policy Auditing Functions
Use the following functions to monitor NetBackup policy changes.
listClientChanges
This function returns a list of NetBackup jobs for which policy clients have changed.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
listClientChanges(
startDate,
finishDate,
clientList,
policyList,
groupID,
cascade)
Argument Name | Type |
startDate | DATE with the value derived from the Scope Selector |
finishDate | DATE with the value derived from the Scope Selector |
clientList | numberListType Example: numberListType(1,2,3) |
policyList | numberListType Example: numberListType(1,2,3) |
groupID | |
cascade | Specify 1 to designate that you want to cascade through the host group hierarchy; Otherwise, set to 0. |
listPathnameChanges
This function returns a list of NetBackup jobs for which policy path names have changed.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
listPathnameChanges(
startDate,
finishDate,
clientList,
policyList,
groupID,
cascade)
Argument Name | Type |
startDate | DATE with the value derived from the Scope Selector |
finishDate | DATE with the value derived from the Scope Selector |
clientList | numberListType Example: numberListType(1,2,3) |
policyList | numberListType Example: numberListType(1,2,3) |
groupID | |
cascade | Specify 1 to designate that you want to cascade through the host group hierarchy; Otherwise, set to 0. |
listPolicyChanges
This function returns a list of NetBackup jobs for which policies have changed.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
listPolicyChanges(
startDate,
finishDate,
clientList,
policyList,
groupID,
cascade)
Argument Name | Type |
startDate | DATE with the value derived from the Scope Selector |
finishDate | DATE with the value derived from the Scope Selector |
clientList | numberListType Example: numberListType(1,2,3) |
policyList | numberListType Example: numberListType(1,2,3) |
groupID | |
cascade | Specify 1 to designate that you want to cascade through the host group hierarchy; Otherwise, set to 0. |
listScheduleChanges
This function returns a list of NetBackup jobs for which policy schedules have changed.
Note that in the SQL Template Designer query, the function name must be prefaced with: rtd.
listScheduleChanges(
startDate,
finishDate,
clientList,
policyList,
groupID,
cascade)
Argument Name | Type |
startDate | DATE with the value derived from the Scope Selector |
finishDate | DATE with the value derived from the Scope Selector |
clientList | numberListType Example: numberListType(1,2,3) |
policyList | numberListType Example: numberListType(1,2,3) |
groupID | |
cascade | Specify 1 to designate that you want to cascade through the host group hierarchy; Otherwise, set to 0. |
Capacity Functions
Use the following functions to determine capacity chargebacks:
listChargebackByLUNSummary
This function provides the capability for listing capacity chargebacks by LUNs.
FUNCTION listChargebackByLUNSummary(
userID,
listOfHosts,
listOfDomains,
listOfArrays,
listOfHostGroups,
cascade)
This function returns a list of hosts and related chargeback policy data, similar to what is displayed in the Chargeback By Host report. Note that in the SQL Template Designer query, the function name must be prefaced with: srm_rtd.
Example Query:
SELECT * FROM table (srm_rtd.listChargebackByLUNSummary(100000,
numberListType(),
numberListType(),
numberListType(),
numberListType(${serverGroups}),
1))
Specify the following arguments:
Argument Name | Description |
userId | Number |
listOfHosts | numberListType Example: numberListType(1,2,3) |
listOfDomains | numberListType Example: numberListType(1,2,3) |
listOfArrays | numberListType Example: numberListType(1,2,3) |
listOfHostGroups | numberListType Example: numberListType(1,2,3) |
cascade | Specify 1 to designate that you want to cascade through the host group hierarchy; Otherwise, set to 0. |
This function returns rows that contain: host ID, host name, policy ID, policy name, unit cost, total GB, total cost
listChargebackByLUNDetail
This function provides the capability for listing capacity details for chargebacks by LUNs.
FUNCTION listChargebackByLUNDetail(
userID,
listOfHosts,
listOfDomains,
listOfArrays,
listOfHostGroups,
cascade,
listOfPolicy)
This function returns a list of each host LUN chargeback policy. This is the detail that would be available as a drill down from the
Chargeback By Host report. See also,
listChargebackByLUNSummary. Note that in the SQL Template Designer query, the function name must be prefaced with:
srm_rtd.Example Query:
SELECT *
FROM table (srm_rtd.listChargebackByLUNDetail(100000,
numberListType(),
numberListType(),
numberListType(),
numberListType(${hostGroups}),
1,
numberListType()
))
Specify the following arguments:
Argument Name | Description |
userID | Number |
listOfHosts | numberListType Example: numberListType(1,2,3) |
listOfDomains | numberListType Example: numberListType(1,2,3) |
listOfArrays | numberListType Example: numberListType(1,2,3) |
listOfHostGroups | numberListType Example: numberListType(1,2,3) |
cascade | Specify 1 to designate that you want to cascade through the host group hierarchy; Otherwise, set to 0. |
listOfPolicy | numberListType Example: numberListType(1,2,3) |
This function returns rows that contain: host ID, host name, policy ID, policy name, storage array ID, storage array name, LUN ID, LUN name, unit cost, total GB, total cost.
listChargebackCatByLUNSummary
This function provides the capability for listing capacity chargebacks by LUNs by category; for example, for each storage tier, list the capacity chargeback. This function provides much of the same data that is displayed in the Chargeback Policy Capacity report.
FUNCTION listChargebackCatByLUNSummary(
userID,
listOfDomains,
listOfArrays)
Note that in the SQL Template Designer query, the function name must be prefaced with: srm_rtd.
Example Query
SELECT * FROM table (srm_rtd.listChargebackCatByLUNSummary(100000,
numberListType(),
numberListType()
))
Specify the following arguments:
Argument Name | Type | Length | Description |
userId | Number | | |
listOfDomains | | | numberListType Example: numberListType(1,2,3) |
listOfArrays | | | numberListType Example: numberListType(1,2,3) |
This function returns rows that contain: policy ID, policy name, policy ID, unit cost, total KB, unallocated KB, allocated KB, HDP capacity KB, non-HDP capacity KB, HDP free capacity KB, array group capacity KB, array group used KB, array group available KB, array group virtual capacity KB, array group PDEV capacity KB.
listChargebackCatByLUNDetail
This function provides the capability for listing capacity chargebacks by LUNs for array capacity (without regard to host usage). This function provides much of the same data that is displayed in the Chargeback Array Capacity report.
FUNCTION listChargebackCatByLUNDetail(
userID,
listOfDomains,
listOfArrays,
listOfPolicy)
Note that in the SQL Template Designer query, the function name must be prefaced with: srm_rtd.
Example Query
SELECT *
FROM table (srm_rtd.listChargebackCatByLUNSDetail(100000,
numberListType(),
numberListType(),
numberListType()
))
Specify the following arguments:
Argument Name | Type | Length | Description |
userId | Number | | |
listOfDomains | | | numberListType Example: numberListType(1,2,3) |
listOfArrays | | | numberListType Example: numberListType(1,2,3) |
listOfPolicy | | | numberListType Example: numberListType(1,2,3) |
This function returns rows that contain: storage array ID, array name, policy ID, policy name, unit cost, total KB, unallocated KB, allocated KB, HDP capacity KB, non-HDP capacity KB, HDP free capacity KB, array group capacity KB, array group used KB, array group available KB, array group virtual capacity KB, array group PDEV capacity KB.
Identifying a Host Group’s ID
To identify a host group’s ID
1. Admin > Hosts and Domains > Hosts and Host Groups.
2. Expand the top-level Host Groups folder. Hover your mouse over the Host Group folder. The ID is displayed.
Identifying a Host ID
To identify a host’s ID
1. Admin > Hosts and Domains > Hosts and Host Groups.
2. Click a specific host group.
The host group list appears. The Host ID is listed in the first column of this list.