SQL query on number of items in subfolders in SEV

[pbyrne]

Hi,

I've got a user who has hundreds of subfolders of a particular folder in their vault. I've looked at SQL and can't see an obvious way of reporting on these sub folders and the number of items in them. I can report on the folders by using ArchiveFolderView and the  ArchiveVEID and  ParentFolderRootIdentity values.

How do I report on the number of items in these subfolders?

Thanks in advance

[GabeV · Accepted solution]

Hi,

Try this query and make sure you select the Vault Store database:

SELECT EnterpriseVaultDirectory.dbo.ArchiveFolderView.FolderName, EnterpriseVaultDirectory.dbo.ArchiveFolderView.FolderPath, Vault.ArchivedItems,
       Vault.ArchivedItemsSize, EnterpriseVaultDirectory.dbo.ArchiveFolderView.ArchiveVEID
FROM   Vault INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveFolderView ON Vault.VaultID = EnterpriseVaultDirectory.dbo.ArchiveFolderView.VaultEntryId
WHERE  EnterpriseVaultDirectory.dbo.ArchiveFolderView.ArchiveVEID = 'Archive-ID'

just replace Archive-ID and see if you get the info you are looking for.

I hope this helps.

[Rob_Wilcox1]

The query works for me at least (using EV 10.0.4)

[pbyrne]

Thanks a lot that worked perfectly.

Would you know of a way to then report on the creation dates of the items in the folders as we may need to also report on number of items in specific folders in a vault between a certain date range?

Or should I start a new thread about this as you already provided me a solution to my first query?

Thanks again

[GabeV]

If you want the creation date for each item on each folder that would be a very large output since you would need to scan every single item archived. May I ask why do you need this data?

[pbyrne]

I have an executive user who has a large number of subfolders under a particular client folder. They are asking to get a report on the number of items per folder within a specific date range.

I don't need to query the whole system just the items in subfolders of a particuar folder.

Thanks again

[GabeV]

Try this one:

SELECT DISTINCT EnterpriseVaultDirectory.dbo.ArchiveFolderView.FolderName, Vault.ArchivedItems, Vault.ArchivedItemsSize
FROM   Vault INNER JOIN
       EnterpriseVaultDirectory.dbo.ArchiveFolderView ON Vault.VaultID = EnterpriseVaultDirectory.dbo.ArchiveFolderView.VaultEntryId INNER JOIN
       Saveset ON Vault.VaultIdentity = Saveset.VaultIdentity
WHERE  (EnterpriseVaultDirectory.dbo.ArchiveFolderView.ArchiveVEID = 'Archive-ID') 
        AND (Saveset.ArchivedDate BETWEEN 'YYY-MM-DD' AND 'YYYY-MM-DD')

Let me know if this one helps.

[JesusWept3]

This query will do what you want 
just be sure to change EVVSYourVaultStore_1 to be the actual Vault Store Database name
also with the folder, so if you wanted everything under \Inbox it would be 
LIKE '\Inbox%' (% is a wildcard character)

Also note that this uses idDateTime which is the sent/Received date of the email.
If you wanted it based of the archived date then replace S.idDateTime with S.ArchivedDate

 

SELECT A.ArchiveName "Archive",
       REPLACE(CAST(FolderPath AS varchar(max)),'?','\') "Folder Path",
       V.CreatedDate "Folder Created",
       COUNT(S.IdTransaction) "Item Count",
       SUM(S.ItemSize) "Folder Size (KB)",
       MIN(S.IdDateTime) "Oldest Item",
       MAX(S.IdDateTime) "Newest Item"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.Root R1,
       EnterpriseVaultDirectory.dbo.Root R2,
       EVVSYourVaultStore_1.dbo.ArchivePoint AP,
       EVVSYourVaultStore_1.dbo.Vault V,
       EVVSYourVaultStore_1.dbo.Saveset S
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R1.VaultEntryId
  AND  V.VaultID = R2.VaultEntryId
  AND  R1.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  A.ArchiveName = 'Your User'
  AND  REPLACE(CAST(FolderPath AS varchar(max)),'?','\') LIKE '\Inbox%'
  AND  S.IdDateTime > '2013-01-01 00:00:00.000'
  AND  S.IdDateTime < '2013-12-30 23:59:59.000'
GROUP BY A.ArchiveName, REPLACE(CAST(FolderPath AS varchar(max)),'?','\'), V.CreatedDate
ORDER BY "Folder Path"

[pbyrne]

Thanks JesusWept3. That is exactly what I'm looking for.

I appreciate all your help. Thanks all