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?

zhuqingedu
Level 4

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 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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 11

GulzarShaikhAUS
Level 6
Partner Accredited Certified

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

zhuqingedu
Level 4

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

QQ截图20150901102656.jpg

zhuqingedu
Level 4

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

zhuqingedu
Level 4

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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)

zhuqingedu
Level 4

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!

GulzarShaikhAUS
Level 6
Partner Accredited Certified

Ohh sure he is!! :)

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

No no, you're thinking of Pavel.

GulzarShaikhAUS
Level 6
Partner Accredited Certified

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

Pavel is good as well no doubt about that.

Manumohan
Level 4

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!!