02-11-2016 01:04 PM
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!
02-11-2016 11:40 PM
/* 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
02-18-2016 02:29 PM
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