SQL query to get selected folder's size and items quantity for all users

[MikeWilc]

Hi,

Anyone can help with ready SQL query to get the summary size and amount of items of of selected archived folder i.e. Conflicts or Deleted Items for all users?

So far created smth as follows:

SELECT
ArchiveFolderView.FolderName,
ArchiveFolderView.FolderPath,
Vault.VaultIdentity,
Vault.ArchivedItems 'Items',
Vault.ArchivedItemsSize 'Size (KB)'
FROM ArchivePoint
INNER JOIN Vault on Vault.ArchivePointIdentity = ArchivePoint.ArchivePointIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveFolderView on ArchiveFolderView.VaultEntryId = Vault.VaultID
where ArchiveFolderView.FolderName like 'Conflicts'

But no idea what to INNER JOIN to get the real names as first value.

Thanks,

Mike

[ChrisLangevin · Accepted solution]

Mike,

How's this?

USE VaultStoreDB --Substitute the name of your Vault Store database
GO

SELECT 
a.ArchiveName AS [Archive Name]
,af.FolderName AS [Folder Name]
,af.FolderPath AS [Folder Path]
,v.ArchivedItems AS [Number of Archived Items]
,v.ArchivedItemsSize  AS [Size of Archived Items (KB)]

FROM Vault AS v
JOIN EnterpriseVaultDirectory.dbo.Root AS rf
	ON rf.VaultEntryId = v.VaultId
JOIN EnterpriseVaultDirectory.dbo.Root AS ra
	ON ra.RootIdentity = rf.ContainerRootIdentity
JOIN EnterpriseVaultDirectory.dbo.ArchiveFolder AS af
	ON af.RootIdentity = rf.RootIdentity
JOIN EnterpriseVaultDirectory.dbo.Archive AS a
	ON a.RootIdentity = ra.RootIdentity

WHERE af.FolderName = 'Sent Items'

Example output:

--Chris