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
