Pipelined Functions for Report Query Building > General Functions
  
Version 10.2.01P10
General Functions
Function Name
Description
Provides a list of dates based on the start date, end date, and grouping aggregation interval to a query.
Concatenates string values returned from a table.
Determines backup license usage details. It returns the host ID, host name, backup vendor, host type, and the last updated date.
Returns a string—the host/server attribute value.
Returns a string—the object’s attribute value.
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.
Provides the capability for getting specific host group data.
Useful for converting job duration to a readable format.
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: 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. This can be especially useful for including a sparkline chart in a tabular report.
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: 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: 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: 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.
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: 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.
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. For example, 622 seconds would return 00:10:22.
secsToHoursMinSecs(totalSecs IN NUMBER)
Example Query
SELECT rtd.secsToHoursMinSecs (duration_secs) FROM apt_v_job