cancel
Showing results for 
Search instead for 
Did you mean: 

Custom SQL query to gather all backup images not expired and the deduplication size + more

daniel_hooper
Level 5
Partner Certified

Hi

I'm very much a noob in SQL queries, and Opscenter custom reports does Not provide this information.

I have created a custom SQL query using Opscenter's schema, to provide the values in the following columns

Reported DateClient NamePolicy NameCopy Type NameRetained SizeProtected SizeisPrimaryidmasterServerIdimageIdMedia ServerTarget Storage TypeSTU Type NameCopy DateExpiry

The SQL query I have put together is as follows:

SELECT DISTINCT
GETDATE() as "Reported Date",

substr(domain_Client.name, 1, len(domain_Client.name)- charindex('(', reverse(domain_Client.name))) as "Client Name",

domain_JobArchive.policyName as 'Policy Name',
lookup_StorageServiceUsageType.name as 'Copy Type Name',
(domain_JobArchive.bytesWritten/1024.0/1024.0/1024.0) as 'Retained Size',
(domain_JobArchive.preSISSize/1024.0/1024.0/1024.0) as 'Protected Size',
domain_ImageCopy.isPrimary,
domain_ImageCopy.id,
domain_ImageCopy.masterServerId,
domain_ImageCopy.imageId,
domain_ImageCopy.mediaServerName as 'Media Server',
domain_ImageCopy.storageUnitType 'Target Storage Type',
lookup_StorageUnitType.name as 'STU Type Name',
"DBA"."UTCBigintToUTCTime"(domain_ImageCopy.copyDate) AS 'Copy Date',
"DBA"."UTCBigintToUTCTime"(domain_ImageCopy.expirationTime) AS Expiry

From domain_ImageCopy, domain_JobImage, domain_JobArchive, domain_Client, domain_Image, domain_SLPImageCopy, lookup_StorageServiceUsageType, lookup_StorageUnitType

Where
domain_Client.isValid = '1'
AND
domain_JobImage.imageId = domain_ImageCopy.imageId
AND
domain_JobImage.clientName = domain_Client.name
AND
domain_JobImage.masterServerId = domain_JobArchive.masterServerId
AND
domain_JobImage.clientName = domain_JobArchive.clientName
AND
domain_Image.masterServerId = domain_JobArchive.masterServerId
AND
domain_JobArchive.policyName = domain_Image.policyName
AND
DATE(Expiry) >= DATE(NOW())
AND
domain_JobArchive.policyName != '-'
AND
domain_JobArchive.id = domain_JobImage.jobid
AND
domain_SLPImageCopy.masterServerId = domain_ImageCopy.masterServerId
AND
domain_SLPImageCopy.imageId = domain_ImageCopy.imageId
AND
domain_SLPImageCopy.copyType=lookup_StorageServiceUsageType.id
AND
domain_ImageCopy.storageUnitType = lookup_StorageUnitType.id
AND NOT
(lookup_StorageServiceUsageType.name ='Duplication'
AND domain_ImageCopy.id = '1')
AND NOT
(lookup_StorageServiceUsageType.name ='Backup'
AND domain_ImageCopy.id = '2')
AND NOT
lookup_StorageServiceUsageType.name ='Replication'
AND domain_JobImage.masterServerId IN (5835210)

The highlighted value I change and create a save report for scheduling.

Now The problem I have is that the query does take a long time to run and Opscenter more often than not, times out. Also if I schedule this, it too is hit or miss as to whether I receive the report.

I raised a case with Veritas and they pointed to possible runaway SQL query but that it is not in scope for support.

What I'm wondering if there is anyone out there who can validate the above, and make any recommendations to improve its run time?

 

Thanks in Advance

 

0 REPLIES 0