Opscenter custom script help !
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 wee...
Forum Discussion
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!
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"
