Opscenter custom script help !

[mikg23]

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 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?

SELECT
domain_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#"

FROM
domain_JobArchive , domain_Policy, lookup_PolicyType, domain_MasterServer

WHERE
domain_JobArchive.clientName LIKE 'client1'
AND DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1
AND domain_Policy.type= lookup_PolicyType.id
AND domain_JobArchive.masterServerId = domain_MasterServer.id
AND domain_Policy.type = domain_JobArchive.policyType
AND domain_JobArchive.scheduleName != 'unknown'
AND domain_JobArchive.scheduleName != ' '
AND domain_Policy.policyDomainName = domain_JobArchive.policyDomainName
AND domain_Policy.versionNo = domain_JobArchive.policyVersionNo
AND domain_JobArchive.statusCode IN (0,1)
AND domain_JobArchive.type IN (0)
AND domain_JobArchive.presissize !=32768

GROUP 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)

Master Server,  Client Name,     Policy Type,   Schedule,    GB Written,      Jobs Ran,      Week#
Master1             client1               Standard        full,              30,414              1                    0
Master1             client1               Standard        incr              4,685                4                    0
Master1             client1               Standard        full               43,361               1                   1
Master1             client1               Standard        incr              7,709                 6                   1
Master1             client1               Standard        full               45,751               1                   2
Master1             client1               Standard        incr              8,555                 5                   2
Master1             client1               Standard        full               44,000               1                   3
Master1             client1               Standard        incr               7,641                5                   3
Master1             client1               Standard        incr               7,791                5                   4

Sizes expected to be ~ 15 TB for fulls and ~ 2.5 TB for the incs

[RiaanBadenhorst]

Hello

My advice would be to take one step back and run it with out this 

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.

HTH

[mikg23]

RiaanBadenhorst 

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 is policy type and amount of data per week.

Ultimately i want to just report on second highest amount per client per policy type ... baby steps.

[RiaanBadenhorst]

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]

I ended up tweaking the query to use inner joins ...

SELECT
domain_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#"  -- supplies year week #

FROM
domain_JobArchive
    inner join lookup_PolicyType on domain_JobArchive.policyType = lookup_PolicyType.id
    inner join domain_MasterServer on domain_JobArchive.masterServerId = domain_MasterServer.id

WHERE

DATEDIFF(MONTH,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1
AND domain_JobArchive.scheduleName != 'unknown'
AND domain_JobArchive.scheduleName != ' '
AND domain_JobArchive.statusCode IN (0,1)  -- partial or successful jobs
AND domain_JobArchive.type = 0          -- backup only (not archive or other job types)
AND domain_JobArchive.presissize !=32768

GROUP BY
DATEPART(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"

[TonyDavids]

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

[mikg23]

"friendlyName" is for the master and called out right before the time variable in the group by.