Opscenter custom script help !

Question

Oh powerful SQL query experts, please help me with my conumdrum...I have written the following query to produce a list of current NBU clients hand how much data is protected in a given&nbsp;month by week including the number of jobs ran of a given schedule. When i run the script though the data is grossly larger then actuals ... what am i doing wrong?SELECTdomain_MasterServer.friendlyName as "Master Server",domain_JobArchive.clientName as "Client Name",lookup_PolicyType.name as "Policy Type" ,domain_JobArchive.scheduleName as "Schedule",coalesce(CAST(SUM(domain_JobArchive.preSISSize)/1024/1024/1024 AS NUMERIC (20,0)),0) AS "GB",(count(distinct domain_JobArchive.parentJobId)) as "Jobs Ran",DATEDIFF(WEEK, UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) as "Week#"FROMdomain_JobArchive , domain_Policy, lookup_PolicyType, domain_MasterServerWHEREdomain_JobArchive.clientName LIKE 'client1'AND DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) &lt;= 1AND domain_Policy.type= lookup_PolicyType.idAND domain_JobArchive.masterServerId = domain_MasterServer.idAND domain_Policy.type = domain_JobArchive.policyTypeAND domain_JobArchive.scheduleName != 'unknown'AND domain_JobArchive.scheduleName != ' 'AND domain_Policy.policyDomainName = domain_JobArchive.policyDomainNameAND domain_Policy.versionNo = domain_JobArchive.policyVersionNoAND domain_JobArchive.statusCode IN (0,1)AND domain_JobArchive.type IN (0)AND domain_JobArchive.presissize !=32768GROUP BY"domain_JobArchive"."clientName","lookup_PolicyType"."name","scheduleName","friendlyName",(DATEDIFF(WEEK, UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()))ORDER BY"domain_JobArchive"."clientName",(DATEDIFF(WEEK, UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE())),"lookup_PolicyType"."name","scheduleName"------ output ---- (sanitized to protect the innocent)&nbsp;Master Server, &nbsp;Client Name, &nbsp; &nbsp; Policy Type, &nbsp; Schedule, &nbsp; &nbsp;GB Written, &nbsp; &nbsp; &nbsp;Jobs Ran, &nbsp; &nbsp; &nbsp;Week#Master1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;client1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Standard &nbsp; &nbsp; &nbsp; &nbsp;full, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30,414 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0Master1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;client1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Standard &nbsp; &nbsp; &nbsp; &nbsp;incr &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4,685 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0Master1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Standard &nbsp; &nbsp; &nbsp; &nbsp;full &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 43,361 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Master1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;client1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Standard &nbsp; &nbsp; &nbsp; &nbsp;incr &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7,709 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Master1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Standard &nbsp; &nbsp; &nbsp; &nbsp;full &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 45,751 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2Master1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;client1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Standard &nbsp; &nbsp; &nbsp; &nbsp;incr &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8,555 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2Master1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;client1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Standard &nbsp; &nbsp; &nbsp; &nbsp;full &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 44,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3Master1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;client1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Standard &nbsp; &nbsp; &nbsp; &nbsp;incr &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7,641 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3Master1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Standard &nbsp; &nbsp; &nbsp; &nbsp;incr &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7,791 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp;Sizes expected to be ~ 15 TB for fulls and ~ 2.5 TB for the incs

riaanbadenhorst · Answer

HelloMy advice would be to take one step back and run it with out this&nbsp;coalesce(CAST(SUM(domain_JobArchive.preSISSize)/1024/1024/1024 AS NUMERIC (20,0)),0) AS "GB",(count(distinct domain_JobArchive.parentJobId)) as "Jobs Ran",First check if your results are correct before doing the sum or count. I sometimes find that this helps when you're grouping stuff together and you see it comes back with 2x or 3x.&nbsp;HTH

mikg23 · Answer

RiaanBadenhorst&nbsp;Thank you for the response, yes I agree i am trying not to over complicate things. I will say the job counts are accurate. I've also limited the search to one client as i troubleshoot (there are over 4k clients that will eventually be reported on). I also broke it into schedules to help with troubleshooting and verification as all I really need&nbsp;is policy type and amount of data per week.Ultimately i want to just report on second highest amount per client per&nbsp;policy type ... baby steps.&nbsp;

riaanbadenhorst · Answer

Hi,
Then I'm sure its adding it up more than it should. Being a non-dba with a google degree in sql, i've had a few instances where i ran into issues like this :)
hope you figure it out!

mikg23 · Answer

I ended up tweaking the query to use inner joins ...SELECTdomain_MasterServer.friendlyName as "Master Server",domain_JobArchive.clientName as "Client Name",lookup_PolicyType.name as "Policy Type" ,domain_JobArchive.scheduleName as "Schedule",count (*) as "Jobs RAN",sum((domain_JobArchive.preSISSize))/1024/1024/1024 AS "GB",DATEPART(week,(UTCBigIntToNomTime(domain_JobArchive.endTime))) as "Week#"&nbsp; -- supplies year week #FROMdomain_JobArchive&nbsp;&nbsp;&nbsp; inner join lookup_PolicyType on domain_JobArchive.policyType = lookup_PolicyType.id&nbsp;&nbsp;&nbsp; inner join domain_MasterServer on domain_JobArchive.masterServerId = domain_MasterServer.idWHEREDATEDIFF(MONTH,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) &lt;= 1AND domain_JobArchive.scheduleName != 'unknown'AND domain_JobArchive.scheduleName != ' 'AND domain_JobArchive.statusCode IN (0,1)&nbsp; -- partial or successful jobsAND domain_JobArchive.type = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- backup only (not archive or other job types)AND domain_JobArchive.presissize !=32768GROUP BYDATEPART(week,(UTCBigIntToNomTime(domain_JobArchive.endTime))),"domain_JobArchive"."clientName","lookup_PolicyType"."name","scheduleName","friendlyName"ORDER BY"domain_JobArchive"."clientName",DATEPART(week,(UTCBigIntToNomTime(domain_JobArchive.endTime))),"lookup_PolicyType"."name","scheduleName"&nbsp;

tonydavids · Answer

In your first query you included the master server name but did not group by it.

Forum Discussion

Opscenter custom script help !

6 Replies

Related Content

opscenter customized sccript issue

Looking for custom OpsCenter Analytics SQL Script

Create custom alerts in OpsCenter 8.1.2

Custom backup_notify.bat script

OpsCenter Custom Report

Recent Discussions

netbackup opscenter 10.0.01 able to fetch data collection from netbackup master10.1

OpCenter alerts with blank report. SQL Query not fetching with the reports.

How to Remove Duplicates Emails from MS Outlook 2010

OpsCenter - 10939 error when adding new master server

Open your spellbook or skill menu DaD