Archive Folder and Item Counts

[Elio_C]

Hi,
I'm looking for a way to display some archive information. I'd like to report on folders and the count of items in each.
I have the below to list the folders, which table/tables can I link to get an item count?

USE EnterpriseVaultDirectory
SELECT * from ArchiveFolderView
WHERE ArchiveVEID = 'UserArchiveID'

I'm guessing RootIdentity or VaultEntryID from the above can be used to join with another table and return a count?
Any help would be greatly appreciated.
Thanks

[Pradeep-Papnai · Accepted solution]

Ran following SQL query against VS database.

Select afv.FolderName, vt.VaultID Folder_id, ap.ArchivedItems, ap.ArchivedItemsSize 'Size(kb)' from ArchivePoint ap
inner join Vault vt on vt.ArchivePointIdentity = ap.ArchivePointIdentity
inner join EnterpriseVaultDirectory.dbo.ArchiveFolderView afv on afv.VaultEntryId = vt.VaultID
where ap.ArchivePointId = 'UserARchiveId'

[Elio_C]

Thanks for the speedy response Pradeep.

I had to make a small change as it reported the overal archive size and items count for each folder, simply changed the 2 "ap." to "vt." in the Select line, as below in BOLD, and it's perfect.

Select afv.FolderName, vt.VaultID Folder_id, VT.ArchivedItems, VT.ArchivedItemsSize 'Size(kb)' from ArchivePoint ap
inner join Vault vt on vt.ArchivePointIdentity = ap.ArchivePointIdentity
inner join EnterpriseVaultDirectory.dbo.ArchiveFolderView afv on afv.VaultEntryId = vt.VaultID
where ap.ArchivePointId = 'UserARchiveId'

Now to add the delimeters to the folder paths and sorted in order..... think I'll do this after exporting the results.

Thanks again.

[Pradeep-Papnai]

Thanks, I just selected archivepoints alias. Many thanks for improving it.
 

[Elio_C]

For Information.
Updated T-SQL, formatted and removing aliases:

USE 'Vault Store DB'
SELECT
    ArchiveFolderView.FolderName,
    ArchiveFolderView.FolderPath,
    Vault.VaultID,
    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 ArchivePoint.ArchivePointId = 'Archive ID'