Working with the SQL Template Designer > Using Functions in Queries
   
Version 9.1.01
Using Functions in 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
Backup Manager Functions
Policy Auditing Functions
Capacity Functions
General Functions
APTlistOfDates
aptStringConcat
getLicenseClientDetail
getServerAttributeValue
getObjectAttributeValue
getChildServerGroupContextById
getServerGroupContextById
secsToHoursMinSecs
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
APTgetTapeDriveStatusName
getFullPathname
listJobSummaryAfterRestart
listJobSummaryAfterRestart for NetWorker Backup Jobs
listOfBackupWindowDates
listOfBackupWindowDates (by Backup Window ID)
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 timeframe (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
To determine the backupWindowId, see listOfBackupWindowDates (by Backup Window ID).
 
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
returns a list of backup windows gleaned from the windows defined in the Portal - See listOfBackupWindowDates (by Backup Window ID)
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
Capacity Functions
listPolicyChanges
listScheduleChanges
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
listChargebackByLUNDetail
listChargebackCatByLUNSummary
listChargebackCatByLUNDetail
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.