Forum Discussion

Elio_C's avatar
Elio_C
Level 6
11 years ago
Solved

Archive Folder and Item Counts

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

Enterprise Vault
Information Governance
Reporting
Windows Server (2003-2008)
  • Pradeep-Papnai's avatar
    Pradeep-Papnai
    11 years ago

    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'

     

  • Pradeep-Papnai's avatar
    Pradeep-Papnai
    Level 6
    11 years ago

    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's avatar
    Elio_C
    Level 6
    11 years ago

    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.

  • Elio_C's avatar
    Elio_C
    Level 6
    11 years ago

    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'