11-04-2014 03:50 AM
Hi
I need to create a sql query report in OpsCenter, so I am able to locate the clients that are performing poorly regarding backup.
So what I would like your help to create, is a report containing the following:
- Client name
- Masterserver
- Mediaserver (not important, but nice to have if posible)
- Size of backup
- Throughput
- Starttime
- Endtime
- OS
Conditions for the report:
- I would like to run it against a predefined masterserver (so I only get clients from that specifik masterserver)
- I only want to see clients in the report which is running backup slower than 5MB/s
- The report has to be timelimited, so I will be able to look at the backup 1 week back or 1 month.
I really hope one of you guys will help me with some input :)
Br
Tommy
11-05-2014 05:49 AM
Try this one
select
NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.endTime) as "JobDuration",
domain_JobArchive.clientName as 'Client',
domain_JobArchive.filesBackedUp as "Files",
domain_JobArchive.throughput as "Throughput",
domain_JobArchive.scheduleName as "ScheduleName",
domain_MasterServer.friendlyName as "MasterServer",
domain_JobArchive.policyName as "Policy Name",
(domain_JobArchive.preSISSize)/1024 as "Protected",
adjust_timestamp(domain_JobArchive.startTime,14400000 ) as "Start Time",
adjust_timestamp(domain_JobArchive.endTime,14400000 ) as "End Time",
(domain_JobArchive.bytesWritten)/1024 as "KbytesWritten",
nb_JobBackupAttributesArchive.destStorageUnit as "Storage Unit"
from domain_JobArchive left outer join nb_JobArchive nb_JobArchive on domain_JobArchive.masterServerId = nb_JobArchive.masterServerId and domain_JobArchive.clientName = nb_JobArchive.clientName and domain_JobArchive.id = nb_JobArchive.id left outer join nb_JobArchive a1 on domain_JobArchive.masterServerId = a1.masterServerId and domain_JobArchive.clientName = a1.clientName and domain_JobArchive.id = a1.id left outer join nb_JobBackupAttributesArchive nb_JobBackupAttributesArchive on domain_JobArchive.masterServerId = a1.masterServerId and domain_JobArchive.clientName = a1.clientName and domain_JobArchive.id = a1.id AND a1.masterServerId = nb_JobBackupAttributesArchive.masterserverId and a1.clientName = nb_JobBackupAttributesArchive.clientName and a1.id = nb_JobBackupAttributesArchive.jobId inner join domain_Policy domain_Policy on domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo inner join domain_MasterServer domain_MasterServer on domain_MasterServer.id = domain_JobArchive.masterServerId
where
(domain_JobArchive.isValid = '1') AND (domain_JobArchive.filesBackedUp NOT IN (0 ))
AND (domain_JobArchive.policyType NOT IN (-1, 34 ))
AND (domain_JobArchive.throughput <5000)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 30
ORDER BY UPPER("domain_MasterServer"."friendlyName" ) ASC , "Start Time" ASC , "domain_JobArchive"."id" ASC