SQL Query needed for Folder size report

[jbehr]

Version: 10.0.3

I would like to get a report on the size of each folder for a particular archive.

For instance:

Inbox (and all subfolders)= 20,000,000 KB

Deleted Items (and all subfolders) = 15,555,555 KB

I don't really care about the subfolders I just would like a list of all the major folders on the root of the mailboxes and how much space each is dedicated to.

My real goal is I need to see how much space is wasted by backing up user's Deleted Items..but i would rather have more information and than I need than less.

I found this (which works) https://www.veritas.com/community/forums/archive-folder-and-item-counts but it's a little garbled.

Thank you!

[Manideep_Neeli]

/* use EVVS-DB. Replace with your vault store name.
NOTE: the size is in KB */
  
 SELECT    
     Archive.ArchiveName,
     ArchiveFolder.FolderName,
     COUNT(*) as itemsPerFolder, 
     Vault.ArchivedItems,
     vault.ArchivedItemsSize
     
 FROM
     EnterpriseVaultDirectory.dbo.root r1
     LEFT JOIN EnterpriseVaultDirectory.dbo.Root r2 on r1.RootIdentity = r2.ContainerRootIdentity
     JOIN EnterpriseVaultDirectory.dbo.Archive on r1.RootIdentity = archive.RootIdentity
     LEFT JOIN EnterpriseVaultDirectory.dbo.ArchiveFolder on r2.RootIdentity = ArchiveFolder.RootIdentity
     RIGHT JOIN Vault on r2.VaultEntryId = Vault.Vaultid
     JOIN ArchivePoint AP on Vault.ArchivePointIdentity = AP.ArchivePointIdentity
     JOIN Saveset on Vault.VaultIdentity = Saveset.VaultIdentity
/*use below where conditions to narrow down. If two archives have same name, use archive id
WHERE
FolderName='Inbox'
ArchiveName='User 1'
ArchivePointId='1747A700B5B451040A71499B81C3D232C1110000evserver.xx.xx' */
 GROUP BY
     Archive.ArchiveName,
     ArchiveFolder.FolderName,
     Vault.ArchivedItems,
     vault.ArchivedItemsSize

[JesusWept3]

The query you want is as follows

SELECT [A].[ArchiveName] [Archive Name],
       [Folder Path] = '\Deleted Items',
       COUNT([AFV].[RootIdentity]) [Folder Count],
       SUM([V].[ArchivedItemsSize]) [Folder Size],
       SUM([V].[ArchivedItems]) [Item Count]
  FROM [EnterpriseVaultDirectory].[dbo].[ArchiveFolderViewWithDeletedFolders] [AFV] WITH (NOLOCK)
INNER JOIN [EnterpriseVaultDirectory].[dbo].[Archive] [A] WITH (NOLOCK) ON [A].[RootIdentity] = [AFV].[ContainerRootIdentity]
INNER JOIN [EVVSYourVaultStore1_1].[dbo].[Vault] [V] WITH (NOLOCK) ON [V].[VaultID] = [AFV].[VaultEntryId] 
WHERE REPLACE(CAST([AFV].[FolderPath] AS varchar(255)), '?', '\') LIKE '\Deleted Items%'
GROUP BY [A].[ArchiveName]

The things that may skew your results in terms on making sure you're not wasting your time archiving \Deleted Items or what not, is if they use Virtual Vault and they delete a bunch of items, also with the way this query works, its going by the English \Deleted Items name, that will have different names in different languages etc