Archive History

[San_Bangalore]

Looking for a SQL query which can find the history of number of items archived for particular user. We are using EV – 10.0.4

[Rob_Wilcox1]

I tried these:

http://www.symantec.com/connect/articles/enterprise-vault-useful-sql-queries

[San_Bangalore]

As I could see below query will give the total items achived from the vault store.

SELECT COUNT(*) AS 'No. of Items Archived'

FROM saveset

--WHERE archiveddate > '2014-11-10 00:00:00.000' and archiveddate < '2014-11-11 00:00:00.000'

my requirement is for a paticular user I want to get a history of number of items archived  like below format

Date            Number of items archived.

[AndrewB]

try this query below. change <VaultStore> to the name of your vault store database and change the date range to whatever you are looking for.

SELECT A.ArchiveName, 
       COUNT(S.ItemSize) "Item Count",
       SUM(S.ItemSize)/1024 "Total Size (MB)"
FROM   <VaultStore>.dbo.Saveset S,
       <VaultStore>.dbo.ArchivePoint AP,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Archive A
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  S.ArchivedDate between '2012-09-01' and '2012-09-02'
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

[JesusWept3 · Accepted solution]

I think the query you'd want would be something like this, it gets the archiving rate for the last month

SELECT A.ArchiveName, 
       CONVERT(DATE, S.archivedDate) "Archived Date", 
       COUNT(S.IdTransaction) "Items Archived",
       SUM(S.ItemSize) "Compressed Size (KB)",
       SUM(SP.OriginalSize) "Uncompressed Size (KB)"
  FROM EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       EVVSMyVaultStore_1.dbo.ArchivePoint AP,
       EVVSMyVaultStore_1.dbo.Saveset S,
       EVVSMyVaultStore_1.dbo.SavesetProperty SP
 WHERE A.RootIdentity = R.RootIdentity
   AND R.VaultEntryId = AP.ArchivePointId
   AND AP.ArchivePointIdentity = S.ArchivePointIdentity
   AND S.SavesetIdentity = SP.SavesetIdentity
   AND S.ArchivedDate > DATEADD(MONTH, -1, GETDATE())
   AND A.ArchiveName = 'Users Name'
 GROUP BY A.ArchiveName, CONVERT(DATE, S.archivedDate)
 ORDER BY "Archived Date" ASC
 

The only caveat is that if no archiving occurred on that day then it will be skipped.
So its possible you'll see something like

2014-11-09 .....
2014-11-12 ......
2014-11-13 .......
2014-11-15 ......

So there would be visible gaps for the days that didn't archive anything