05-11-2015 06:08 PM
Hi ,
Please help me in getting the directories list ,client name and their pre-post de-duplication from opscenter using the SQL query.
I was trying with the below query (tried myself using other forums) and not aware how to get output from two tables and results in one column
Can you help me in getting ouput in below format please.
Client Name DirectoryList Pre-Dedup Post-De-Dup
Query :
********
SELECT domain_JobArchive.clientname as 'Client',nb_JobFiles.fileinfo as 'FileList'
COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size (GiB)',
COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size (GiB)',
COALESCE(CAST((SUM(preSisSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings (GiB)'
FROM
domain_JobArchive,nb_JobFiles
WHERE domain_JobArchive.clientname = nb_JobFiles.fileinfo
presisSize != bytesWritten and
presissize !=32768
ORDER BY
clientName
FileList
Solved! Go to Solution.
05-12-2015 01:20 AM
Hi Gnana Sekaran.
You can try this.
SELECT
A.clientname as 'Client',
B.filelist as 'FileList',
COALESCE(CAST(SUM(A.preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size (GiB)',
COALESCE(CAST(SUM(A.bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size (GiB)',
COALESCE(CAST((SUM(A.preSisSize) - SUM(A.bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings (GiB)'
FROM
"domain_JobArchive" A, "nb_policy" B
WHERE A.masterserverId = B.masterserverId AND A.policyName = B.name AND A.policyDomainName = B.policyDomainName AND A.policyVersionNo = B.VersionNo AND
A.presissize !=32768 AND
group by A.clientname,B.filelist
ORDER BY clientName
05-12-2015 01:20 AM
Hi Gnana Sekaran.
You can try this.
SELECT
A.clientname as 'Client',
B.filelist as 'FileList',
COALESCE(CAST(SUM(A.preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size (GiB)',
COALESCE(CAST(SUM(A.bytesWritten)/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size (GiB)',
COALESCE(CAST((SUM(A.preSisSize) - SUM(A.bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings (GiB)'
FROM
"domain_JobArchive" A, "nb_policy" B
WHERE A.masterserverId = B.masterserverId AND A.policyName = B.name AND A.policyDomainName = B.policyDomainName AND A.policyVersionNo = B.VersionNo AND
A.presissize !=32768 AND
group by A.clientname,B.filelist
ORDER BY clientName
05-12-2015 01:50 PM
Thanks for your help .
I got the output list as below
Client FileList De-Deup
ABC All the selection list in policy i.e vol\abc vol\abcd \vol\xyz 98.99 98.99
Would that be possible to list invidual directories and their pre & post de-dup rates
05-12-2015 11:19 PM
Do not think it is possible at less you individually select a folder directly into the selection list.
Br Morten
05-19-2015 01:47 AM
Hi
M_Henriksen is correct, OpsCenter is only reporting vol\abc vol\abcd \vol\xyz because it is specifically listed in the policy as a backup selection. If you had put ALL_LOCAL_DRIVES would not see the folders.
OpsCenter does not collect details about the individual files and folders backed up.