08-31-2015 12:02 AM
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!
Solved! Go to Solution.
09-02-2015 05:54 AM
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)
08-31-2015 07:55 AM
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
08-31-2015 09:23 AM
There are quite a lot of attributes, you might want be more specific.
08-31-2015 07:27 PM
Thank you, I tried your sql query,But no results return。
08-31-2015 08:12 PM
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 |
09-01-2015 06:31 PM
Hi Riaan.Badenhorst,The default width can not be displayed,the Attached is the format I want.
09-02-2015 05:54 AM
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)
09-04-2015 10:43 PM
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!
09-05-2015 02:13 AM
Ohh sure he is!! :)
09-05-2015 03:07 AM
No no, you're thinking of Pavel.
09-05-2015 03:53 AM
Come on Riaan. Accept the truth. You are really good at these things. :)
Pavel is good as well no doubt about that.
09-30-2015 05:24 AM
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!!