Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Need Media ID's added to this report

Created: 04 Sep 2013 • Updated: 06 Sep 2013 | 4 comments
This issue has been solved. See solution.

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]%'
 

Operating Systems:

Comments 4 CommentsJump to latest comment

Arojasbe's picture

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

lisareinhart's picture

This does help thank you, but can the duplicate tape id's be weeded out and only appear once?

Arojasbe's picture

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

SOLUTION