Opscenter -SQL Query to list the directories

[Gnana_Sekaran]

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

[M_henriksen · Accepted solution]

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

[Gnana_Sekaran]

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 

[M_henriksen]

Do not think it is possible at less you individually select a folder directly into the selection list.

Br Morten

[RiaanBadenhorst]

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.