09-04-2013 12:22 PM
Can someone assist in getting the media id's added into this report?
SELECT ms.networkName AS 'Master Server'
, di.id AS 'Image'
, utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
, utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
, utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
FROM domain_Image di
INNER JOIN domain_MasterServer ms
ON di.masterServerId = ms.id
WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30)
OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
AND di.id LIKE '%[ClientName]%'
Solved! Go to Solution.
09-05-2013 11:26 AM
I think what you're looking for is
SELECT DISTINCT ms.networkName AS 'Master Server'
, di.id AS 'Image'
, imf.mediaId AS 'Media Id'
, utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
, utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
, utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
FROM domain_Image di
INNER JOIN domain_ImageFragment imf
ON di.id = imf.imageId
INNER JOIN domain_MasterServer ms
ON di.masterServerId = ms.id
WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30)
OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
AND di.id LIKE '%[ClientName]%'
Regards
09-04-2013 10:48 PM
I think what you're looking for is this script:
SELECT ms.networkName AS 'Master Server'
, di.id AS 'Image'
, imf.mediaId AS 'Media Id'
, utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
, utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
, utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
FROM domain_Image di
INNER JOIN domain_ImageFragment imf
ON di.id = imf.imageId
INNER JOIN domain_MasterServer ms
ON di.masterServerId = ms.id
WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30)
OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
AND di.id LIKE '%[ClientName]%'
I hope this is what you want
09-05-2013 06:52 AM
This does help thank you, but can the duplicate tape id's be weeded out and only appear once?
09-05-2013 11:26 AM
I think what you're looking for is
SELECT DISTINCT ms.networkName AS 'Master Server'
, di.id AS 'Image'
, imf.mediaId AS 'Media Id'
, utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
, utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
, utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
FROM domain_Image di
INNER JOIN domain_ImageFragment imf
ON di.id = imf.imageId
INNER JOIN domain_MasterServer ms
ON di.masterServerId = ms.id
WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30)
OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
AND di.id LIKE '%[ClientName]%'
Regards
09-06-2013 11:33 AM
Thank you!