cancel
Showing results for 
Search instead for 
Did you mean: 

How to export all policy attributes,config,and others such as throughput using the sql query?

Our backup system has been on line for a long time,many demands have changed,so we want to optimize all policy, We tried to use bppllist command to export all policy attributes and config,but the result is not intuitiveusing,so we want to using the sql query to export all policy attribute,config,also contain the throughput last month,Who can  help me to design the right sql query? Thank you!

1 Solution

Accepted Solutions
Accepted Solution!

Hello That is quite a lot of

Hello

 

That is quite a lot of information to put in one query (I'm not an expert in SQL)

 

I came up with this. It gets a bit messy when you try and look at the individual day schedules and calendar date so I've not added that. You might want to grab that as part of a separate query and combine it in your report.

 

SELECT
domain_PolicyClient.policyName as "Policy Name",
lookup_PolicyType.name as "Policy Type",
lookup_policystatus.name as "Status",
domain_Policy.active as "Active",
domain_PolicyClient.clientName as "Client Name",
nb_policy.usefastbackup as "Accelerator",
nb_policy.storageUnitName as "Storage Unit",
nb_policy.volumePoolName as "Volume Pool",
domain_client.hardwaredescription as "Hardware",
domain_client.osdescription as "Operating System",
nb_Policy.fileList as "Backup Selection",
domain_schedule.Name as "Schedule",
lookup_scheduletype.name as "Schedule Type"

FROM
domain_PolicyClient, domain_Client,lookup_PolicyType, domain_Policy, nb_Policy, lookup_policystatus, lookup_scheduletype, domain_schedule

WHERE domain_Policy.masterServerId = domain_Schedule.masterServerId
AND domain_Policy.name =  domain_Schedule.policyName
AND domain_Policy.policyDomainName = domain_Schedule.policyDomainName
AND domain_Policy.versionNo =  domain_Schedule.policyVersionNo
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 lookup_PolicyType.id = domain_Policy.type
AND
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_Schedule.type = lookup_scheduletype.id AND
lookup_policystatus.id in (0) AND
domain_Policy.active in (1) AND
domain_policy.versionNo  in (1) AND
domain_schedule.scheduleid not in (-2147483633)

View solution in original post

11 Replies

You need to use OpsCenter

You need to use OpsCenter Analytics -

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

 

Credit - https://www-secure.symantec.com/connect/user/voropaevpavel

There are quite a lot of

There are quite a lot of attributes, you might want be more specific.

Thank you, I tried your sql

Thank you, I tried your sql query,But no results return。

QQ截图20150901102656.jpg

Hi Riaan.Badenhorst,i want to

Hi Riaan.Badenhorst,i want to get this effect such as the form:

Policy Name Policy Type Active Use Accelerator Storage Unit Volume Pool HW OS Client Backup Selection Schedule Name Schedule Type Retention Schedule Storage Unit Schedule Volume Pool Frequency / Calendar Backup Window Average Throughput 
Oracle_Weekly Oracle yes yes nbuoa-hcart-robot-tld-2 Every_Day_Pool Linux RedHat2.6.18 clienta /tmp/add.sh full Full Backup Retention Level:     3 (1 month) Residence:(specific storage unit not required) Volume Pool: (same as policy volume pool)
    Calendar sched: Enabled
      Friday, Week 1
      Friday, Week 2
      Friday, Week 3
      Friday, Week 4
      Friday, Week 5
    Residence is Storage Lifecycle Policy:         0
    Schedule indexing:     0

    Daily Windows:
          Sunday     00:00:00  -->  Sunday     04:00:00
          Monday     00:00:00  -->  Monday     04:00:00
          Tuesday    00:00:00  -->  Tuesday    04:00:00
          Wednesday  00:00:00  -->  Wednesday  04:00:00
          Thursday   00:00:00  -->  Thursday   04:00:00
          Friday     00:00:00  -->  Friday     04:00:00
          Saturday   00:00:00  -->  Saturday   04:00:00
120GB/Day

Hi Riaan.Badenhorst,The

Hi Riaan.Badenhorst,The default width can not be displayed,the Attached is the format I want.

Accepted Solution!

Hello That is quite a lot of

Hello

 

That is quite a lot of information to put in one query (I'm not an expert in SQL)

 

I came up with this. It gets a bit messy when you try and look at the individual day schedules and calendar date so I've not added that. You might want to grab that as part of a separate query and combine it in your report.

 

SELECT
domain_PolicyClient.policyName as "Policy Name",
lookup_PolicyType.name as "Policy Type",
lookup_policystatus.name as "Status",
domain_Policy.active as "Active",
domain_PolicyClient.clientName as "Client Name",
nb_policy.usefastbackup as "Accelerator",
nb_policy.storageUnitName as "Storage Unit",
nb_policy.volumePoolName as "Volume Pool",
domain_client.hardwaredescription as "Hardware",
domain_client.osdescription as "Operating System",
nb_Policy.fileList as "Backup Selection",
domain_schedule.Name as "Schedule",
lookup_scheduletype.name as "Schedule Type"

FROM
domain_PolicyClient, domain_Client,lookup_PolicyType, domain_Policy, nb_Policy, lookup_policystatus, lookup_scheduletype, domain_schedule

WHERE domain_Policy.masterServerId = domain_Schedule.masterServerId
AND domain_Policy.name =  domain_Schedule.policyName
AND domain_Policy.policyDomainName = domain_Schedule.policyDomainName
AND domain_Policy.versionNo =  domain_Schedule.policyVersionNo
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 lookup_PolicyType.id = domain_Policy.type
AND
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_Schedule.type = lookup_scheduletype.id AND
lookup_policystatus.id in (0) AND
domain_Policy.active in (1) AND
domain_policy.versionNo  in (1) AND
domain_schedule.scheduleid not in (-2147483633)

View solution in original post

Great! Thank you very

Great! Thank you very much,Riaan.Badenhorst,I firmly believe that You're an expert In this area! and If the query results Contains retention,Frequency / Calendar and Backup Window,It will be  very perfect! Can you help me to improve it? Thank you very much,You are my most admired person!

Ohh sure he is!! :)

Ohh sure he is!! :)

No no, you're thinking of

No no, you're thinking of Pavel.

Come on Riaan. Accept the

Come on Riaan. Accept the truth. You are really good at these things. :) 

Pavel is good as well no doubt about that.

Hi Riaan, The script you have

Hi Riaan,

The script you have posted on 2 sep 2015 worked in my environment. Can you please add add few more columns to that report. Additional table required are follows.

Master server 

Client version information.

**********************************************************************************************

Can you please also help me in creating a sepearte sql query, in that report i can see following tables.

 

Master server name

Client

exclude list.

Awaitig a response from you!!