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 !

Related Content

opscenter customized sccript issue

Looking for custom OpsCenter Analytics SQL Script

Create custom alerts in OpsCenter 8.1.2

OpsCenter Custom Report

Custom Monitoring Scripts