12-09-2016 02:02 PM
Hi All,
im trying to get figure out this report and i have it working as i want it to but i cant for the life of me figure out how to translate the MasterServer ID into the name when the report generates, im hopeing someone on here might be able to take a quick look and tell me what im doing wrong.
Working report:
SELECT TOP 10 START at 1
masterServerId as "Master Server",
clientName as 'Client',
policyName as "Policy",
COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size in GB',
COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size in GB',
COALESCE(CAST((SUM(preSISSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings in GB',
IF sum(domain_JobArchive.preSISSize)=0 THEN 0 ELSE ((sum(domain_JobArchive.preSISSize)-sum(domain_JobArchive.bytesWritten)) * 100.0/sum(domain_JobArchive.preSISSize)) ENDIF 'Percent Savings'
FROM
domain_JobArchive
WHERE
DATEDIFF(day, UtcBigIntToNomTime(endTime), GETDATE()) < 7 and presisSize != bytesWritten and presissize !=32768
GROUP BY
masterServerId,
clientName,
policyName
ORDER BY
"Percent Savings";
What i think i need to do:
SELECT TOP 10 START at 1
masterServerId as "Master Server",
clientName as 'Client',
policyName as "Policy",
COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Pre Dedup Size in GB',
COALESCE(CAST(SUM(bytesWritten)/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Post Dedup Size in GB',
COALESCE(CAST((SUM(preSISSize) - SUM(bytesWritten))/1024.0/1024.0/1024.0 AS NUMERIC(20,2)), 0) AS 'Total Savings in GB',
IF sum(domain_JobArchive.preSISSize)=0 THEN 0 ELSE ((sum(domain_JobArchive.preSISSize)-sum(domain_JobArchive.bytesWritten)) * 100.0/sum(domain_JobArchive.preSISSize)) ENDIF 'Percent Savings'
FROM
domain_JobArchive, domain_Entity, domain_MasterServer
WHERE
DATEDIFF(day, UtcBigIntToNomTime(endTime), GETDATE()) < 7 and presisSize != bytesWritten and presissize !=32768
AND
domain_Entity where domain_MasterServer.id = domain_JobArchive.masterServerId
GROUP BY
masterServerId,
clientName,
policyName
ORDER BY
"Percent Savings";
Any Help is really appreciated.
12-13-2016 12:57 AM
12-13-2016 08:26 PM
You need to have
select
dja.xxx as "blah"
dms.friendlyName as "Master Server Name"
dja.yyy as "blah blah"
from
domain_MasterServer dms, domain_JobArchive dja
where
dms.id=dja.masterServerId