11-26-2013 07:51 AM
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
Solved! Go to Solution.
11-26-2013 08:25 AM
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.
11-26-2013 08:25 AM
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.
11-26-2013 08:30 AM
The query works for me at least (using EV 10.0.4)
11-26-2013 10:15 AM
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
11-26-2013 10:42 AM
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?
11-27-2013 05:05 AM
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
11-27-2013 06:04 AM
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.
11-27-2013 12:30 PM
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"
11-28-2013 04:03 AM
Thanks JesusWept3. That is exactly what I'm looking for.
I appreciate all your help. Thanks all