Forum Discussion

goatboy's avatar
goatboy
Level 6
11 years ago

script to get item size in EV

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?

 

  • 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

8 Replies

  • 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

  • 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.

  • 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

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

  • 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

  • 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...

  • 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.

  • 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