tool for checking archives

[jpergola329]

hello,

are there any tools or scripts out there that can check to see if an archive is working the way it should, we had an issue with a user, now we need to check them all.

i.e. archiving is working properly, retention catagories are bring applied correctly to the folders.

thanks

[Rob_Wilcox1]

Have a search for EVSVR, that may do what you're after.

[AndrewB]

just curious to know if you found what you're looking for and if so can share your findings.

[jpergola329]

no i have not found a solution yet.

[TonySterling]

What version are you?  If running EV 10 sp2 there is a nice archiving report created after the schedule completes.

As for Retention categories, you could use this sql, it will run against the Vault Store database and you need to place the name of the archive where it says Your ArchiveName.

If you comment that line out it will run against all achives but may take some time.

SELECT A.ArchiveName
       ,CAST(AF.FolderPath AS NVARCHAR(MAX))
       ,AF.FolderName
       ,COUNT(S.IdTransaction) "Number of Archived Items"
       ,rc.RetentionCategoryName AS "Retention Category"
      -- ,SUM(S.ItemSize) "Total Size (KB)"
FROM   Saveset S,
       ArchivePoint Ap,
       Vault V,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Root R2,
       EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointID = R.VaultEntryID
  AND  V.VaultID = R2.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
  AND  A.ArchiveName = 'Your ArchiveName'
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName, rc.RetentionCategoryName

[Chris_Warren]

Tony - Nice Query!  

Correct me if I'm wrong, but it looks like this query will work only if the Vault Store and Directory DBs are on the same SQL instance, correct?

[TonySterling]

Thanks Chris!

Yes, this is assuming that all the databases are on the same sql instance, if yours are spread out you would need to adjust accordingly.

Something like this I think will work:

--select * from archivefolder

SELECT A.ArchiveName
       ,CAST(AF.FolderPath AS NVARCHAR(MAX))
       ,AF.FolderName
       ,COUNT(S.IdTransaction) "Number of Archived Items"
       ,rc.RetentionCategoryName AS "Retention Category"
      -- ,SUM(S.ItemSize) "Total Size (KB)"
FROM   Saveset S,
       ArchivePoint Ap,
       Vault V,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.Root R,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.Root R2,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.Archive A,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointID = R.VaultEntryID
  AND  V.VaultID = R2.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
  AND  A.ArchiveName = 'Amy Miller'
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName, rc.RetentionCategoryName
 

[jpergola329]

tony,

would i need to change the archive name for each user i want to check?

thanks

im using ev 8 sp3 sql for the databases.

[AndrewB]

the way it is now, yes. or you could run it without the "AND A.ArchiveName =" and it would run against all the archives. you could also substitute an IN instead of the = and give it a few archive names...

[jpergola329]

andrewb what would that look like

thanks

[TonySterling · Accepted solution]

It would look like this:

AND  A.ArchiveName IN ( 'Your ArchiveName', 'Your ArchiveName2','Your ArchiveName3')

So the complete query would be:

SELECT A.ArchiveName
       ,CAST(AF.FolderPath AS NVARCHAR(MAX))
       ,AF.FolderName
       ,COUNT(S.IdTransaction) "Number of Archived Items"
       ,rc.RetentionCategoryName AS "Retention Category"
      -- ,SUM(S.ItemSize) "Total Size (KB)"
FROM   Saveset S,
       ArchivePoint Ap,
       Vault V,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Root R2,
       EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointID = R.VaultEntryID
  AND  V.VaultID = R2.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
  AND  A.ArchiveName IN ( 'Your ArchiveName', 'Your ArchiveName2','Your ArchiveName3')
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName, rc.RetentionCategoryName

[jpergola329]

THANK YOU!!!!

thank you very much to everyone who answered...

i got what i needed.

these fourms are great.

[jpergola329]

hey,

would this work with ev6?

thanks

[Rob_Wilcox1]

Doubtful - SQL Schema is completely different.

What is it you are trying to do?  Suggest a new forum post too :)