Using Functions in Queries

Working with the SQL Template Designer > Using Functions in Queries

Version 9.1.01

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

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:

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	Host group ID. See Identifying a Host Group’s ID.
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	Host group ID. See Identifying a Host Group’s ID.
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	Host group ID. See Identifying a Host Group’s ID.
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	Host group ID. See Identifying a Host Group’s ID.
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(${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(${hostGroups}),

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()

))

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.