OpsCenter policy configuration report

Wanted to share a OpsCenter SQL query I've been using to answer question like

One of the drawbacks of using bpplist -byclient - it doesn't filter by active policies and doen't display policy selection.

 

1) List of clients for Oracle polcies

2) List all polcies and selection for AIX clients

3) And so on.

 

SELECT 
domain_PolicyClient.clientName as "Client Name",
domain_PolicyClient.policyName as "Policy Name",
lookup_PolicyType.name as "Policy Type",
nb_Policy.fileList as "Backup Selection"

FROM
domain_PolicyClient, domain_Client,lookup_PolicyType, domain_Policy, nb_Policy
WHERE
lookup_PolicyType.id = domain_Policy.type AND

domain_PolicyClient.clientName = domain_Client.name  AND
domain_PolicyClient.masterServerId = domain_Client.masterServerId  AND

domain_PolicyClient.policyName = domain_Policy.name AND
domain_PolicyClient.masterServerId = domain_Policy.masterServerId  AND
domain_PolicyClient.policyDomainName = domain_Policy.policyDomainName  AND
domain_PolicyClient.policyVersionNo= domain_Policy.versionNo  AND

domain_Policy.name = nb_Policy.name AND
domain_Policy.masterServerId = nb_Policy.masterServerId  AND
domain_Policy.versionNo = nb_Policy.versionNo  AND
domain_Policy.policyDomainName = nb_Policy.policyDomainName  AND

domain_Policy.status = 0 AND
domain_Policy.active = 1 AND

lookup_PolicyType.name  LIKE '%DB2%'
-- domain_Policy.name LIKE '%PR%' 
-- domain_PolicyClient.clientName LIKE '%winprd%'

 
ORDER BY domain_PolicyClient.clientName