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

script to get item size in EV

Created: 07 Jul 2014 • Updated: 28 Jul 2014 | 8 comments
This issue has been solved. See solution.

Hi

EV 9.0.2/10.0.3

I have the following script:

 

USE EnterpriseVaultDirectory

SELECT A.ArchiveName "Archive Name",

IV.IndexVolumeIdentity "Index ID",

IV.FolderName "Index Folder",

IV.OldestItemDateUTC "Oldest Sent Item",

IV.YoungestItemDateUTC "Newest Sent Item",

IV.OldestArchivedDateUTC "Item First Archived On",

IV.YoungestArchivedDateUTC "Item Last Archived On",

IV.IndexedItems "Item Count"

FROM Archive A, Root R, IndexVolume IV

WHERE IV.RootIdentity = R.RootIdentity

AND R.RootIdentity = A.RootIdentity

ORDER BY A.ArchiveName, IV.IndexVolumeIdentity

 

But I also need to get item sizes. Can someone please assist with modifying the query?

 

Operating Systems:

Comments 8 CommentsJump to latest comment

AndrewB's picture

you can add the ItemSize field from the Saveset table for the Vault Store Database where the archive lives. something like this:

USE EnterpriseVaultDirectory
SELECT A.ArchiveName "Archive Name",
IV.IndexVolumeIdentity "Index ID",
IV.FolderName "Index Folder",
IV.OldestItemDateUTC "Oldest Sent Item",
IV.YoungestItemDateUTC "Newest Sent Item",
IV.OldestArchivedDateUTC "Item First Archived On",
IV.YoungestArchivedDateUTC "Item Last Archived On",
IV.IndexedItems "Item Count",
SUM(S.ItemSize)/1024 "Item Size (MB)"
FROM Archive A, Root R, IndexVolume IV,
<VaultStore>.dbo.Saveset S
WHERE IV.RootIdentity = R.RootIdentity
AND R.RootIdentity = A.RootIdentity
ORDER BY A.ArchiveName, IV.IndexVolumeIdentity

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

goatboy's picture

Thank you. I get this error:

 

Msg 8120, Level 16, State 1, Line 2

Column 'Archive.ArchiveName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

AndrewB's picture

comment out your ORDER BY line for now and see if it works.. or what tony suggested :-)

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

TonySterling's picture

Should just need to add GROUP BY:

 

USE EnterpriseVaultDirectory
SELECT A.ArchiveName "Archive Name",
IV.IndexVolumeIdentity "Index ID",
IV.FolderName "Index Folder",
IV.OldestItemDateUTC "Oldest Sent Item",
IV.YoungestItemDateUTC "Newest Sent Item",
IV.OldestArchivedDateUTC "Item First Archived On",
IV.YoungestArchivedDateUTC "Item Last Archived On",
IV.IndexedItems "Item Count",
SUM(S.ItemSize)/1024 "Item Size (MB)"
FROM Archive A, Root R, IndexVolume IV,
<VaultStore>.dbo.Saveset S
WHERE IV.RootIdentity = R.RootIdentity
AND R.RootIdentity = A.RootIdentity
ORDER BY A.ArchiveName, IV.IndexVolumeIdentity

GROUP BY A.ArchiveName

goatboy's picture

commenting out ORDER BY:

Msg 8120, Level 16, State 1, Line 2

Column 'Archive.ArchiveName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Tony's suggestion:

Msg 156, Level 15, State 1, Line 18

Incorrect syntax near the keyword 'GROUP'.

 

Thanks for your help

TonySterling's picture

USE EnterpriseVaultDirectory
SELECT A.ArchiveName "Archive Name",
IV.IndexVolumeIdentity "Index ID",
IV.FolderName "Index Folder",
IV.OldestItemDateUTC "Oldest Sent Item",
IV.YoungestItemDateUTC "Newest Sent Item",
IV.OldestArchivedDateUTC "Item First Archived On",
IV.YoungestArchivedDateUTC "Item Last Archived On",
IV.IndexedItems "Item Count",
SUM(S.ItemSize)/1024 "Item Size (MB)"
FROM Archive A, Root R, IndexVolume IV,
<VaultStore>.dbo.Saveset S
WHERE IV.RootIdentity = R.RootIdentity
AND R.RootIdentity = A.RootIdentity

GROUP BY A.ArchiveName
ORDER BY A.ArchiveName, IV.IndexVolumeIdentity

one more time...

goatboy's picture

Msg 8120, Level 16, State 1, Line 4

Column 'IndexVolume.IndexVolumeIdentity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

goatboy's picture

This does not result in an error... but all of the item sizes are exactly the same, which can't be right:

 

USE EnterpriseVaultDirectory

SELECT A.ArchiveName "Archive Name",

IV.IndexVolumeIdentity "Index ID",

IV.FolderName "Index Folder",

IV.OldestItemDateUTC "Oldest Sent Item",

IV.YoungestItemDateUTC "Newest Sent Item",

IV.OldestArchivedDateUTC "Item First Archived On",

IV.YoungestArchivedDateUTC "Item Last Archived On",

IV.IndexedItems "Item Count",

SUM(S.ItemSize)/1024 "Item Size (MB)"

FROM Archive A, Root R, IndexVolume IV,

myvaultstore.dbo.Saveset S

WHERE IV.RootIdentity = R.RootIdentity

AND R.RootIdentity = A.RootIdentity

GROUP BY A.ArchiveName, IV.IndexVolumeIdentity, IV.FolderName, IV.OldestItemDateUTC, IV.YoungestItemDateUTC, IV.OldestArchivedDateUTC, IV.YoungestArchivedDateUTC, IV.IndexedItems

ORDER BY A.ArchiveName, IV.IndexVolumeIdentity

SOLUTION