Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

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