cancel
Showing results for 
Search instead for 
Did you mean: 

OpsCenter SQL query, to find client with low throughput

TBeckDK
Not applicable
Partner

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

 

 

1 REPLY 1

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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