06-01-2017 08:44 PM
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
06-01-2017 09:32 PM
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
06-01-2017 09:54 PM
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.
06-02-2017 09:37 AM
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!
06-06-2017 01:09 PM
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"
06-07-2017 08:57 AM
In your first query you included the master server name but did not group by it.
06-07-2017 04:18 PM
"friendlyName" is for the master and called out right before the time variable in the group by.