All,
Perhaps someone can assist. We are using OpsCenter 7.6 I am looking for a report that shows the amount of data, number of jobs, number of clients, storage unit used, master server name, schedule name, policy type PER policy name. Here is my code. Please assist as this times out.
SELECT
domain_MasterServer.networkName as [Master Server],
domain_policy.name as [Policy Name],
lookup_PolicyType.name as [Policy Type],
domain_Schedule.name as [Schedule Name],
nb_JobAttemptArchive.destStorageUnit as [Storage Unit],
SUM(domain_jobArchive.bytesWritten) as "KB",
COUNT (domain_JobArchive.endTime) as [# of Jobs],
COUNT (DISTINCT domain_jobarchive.clientid) as [# of Clients]
FROM domain_masterserver, domain_policy, domain_Schedule, domain_jobarchive, nb_jobattemptarchive, nb_jobarchive, lookup_PolicyType
WHERE
and domain_JobArchive.clientName = nb_JobArchive.clientName
and domain_JobArchive.id = nb_JobArchive.id
and domain_JobArchive.masterServerId = nb_JobArchive.masterServerId
and domain_MasterServer.id = domain_Client.masterServerId
and domain_MasterServer.id = domain_JobArchive.masterServerId
and domain_MasterServer.id = domain_Policy.masterServerId
and domain_Policy.masterServerId = domain_JobArchive.masterServerId
AND domain_Policy.masterServerId = domain_Schedule.masterServerId
and domain_Policy.name = domain_JobArchive.policyName
and domain_Policy.name = domain_Schedule.policyName
and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName
and domain_Policy.policyDomainName = domain_Schedule.policyDomainName
and domain_Policy.versionNo = domain_JobArchive.policyVersionNo
and domain_Policy.versionNo = domain_Schedule.policyVersionNo
and domain_Schedule.masterServerId = domain_JobArchive.masterServerId
and domain_Schedule.name = domain_JobArchive.scheduleName
and domain_Schedule.name = domain_JobArchive.scheduleName
and domain_Schedule.policyDomainName = domain_JobArchive.policyDomainName
and domain_Schedule.policyName = domain_JobArchive.policyName
and domain_Schedule.policyVersionNo = domain_JobArchive.policyVersionNo
and nb_JobArchive.clientName = nb_JobAttemptArchive.clientName
and nb_JobArchive.id = nb_JobAttemptArchive.jobId
AND nb_JobArchive.masterServerId = nb_JobAttemptArchive.masterserverId
AND domain_policy.name = lookup_policytype.name
AND domain_policy.active = 1
AND ((domain_JobArchive.isValid = '1')
AND (UTCBigIntToNomTime(domain_JobArchive.EndTime) between '9/20/2015 8:00:00' AND '9/21/2015 8:00:00'))
GROUP BY
domain_policy.name, domain_MasterServer.networkName, domain_policy.type, lookup_PolicyType.name, domain_Schedule.name, nb_JobAttemptArchive.destStorageUnit
ORDER BY
domain_MasterServer.NetworkName, domain_policy.name DESC
