Video Screencast Help

Help with SQL Query

Created: 08 Mar 2012 • Updated: 08 Mar 2012 | 8 comments
waldowilliams's picture
This issue has been solved. See solution.

Can someone help me with a SQL query. I'm trying to find archives that have not been "used" for 3 months or so.

I have tried to find something that will present archives in a vault store with modified date older that some period of time, but I'm stuck.

Any help is appreciated.


Discussion Filed Under:

Comments 8 CommentsJump to latest comment

JesusWept3's picture

Here you go

USE EnterpriseVaultDirectory
SELECT A.ArchiveName "Archive Name",
       IV.YoungestArchivedDateUTC "Last Archived Date"
FROM Archive A, IndexVolume IV
WHERE IV.RootIdentity = A.RootIdentity
  AND IV.YoungestArchivedDateUTC <= DATEADD(MM, -3, getdate())
ORDER BY A.ArchiveName
AndrewB's picture

this query will show you all archives that havent had data added to them in the time frame you specified.

are you looking for last access by a user?

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner |

JesusWept3's picture

If you want last accessed you'll have to have auditing enabled or trawl through IIS logs, you could use vbscript to extract out the archiveids and link them to the root and archive tables but neither that nor auditing will account for anyone just reading items from virtual vault

waldowilliams's picture

First off, thank you very much.

I am trying to query the Archives in a Vault Store to see what archives are not active.. or haven't been written to in sometime.

I think this query requires that you run it against each archive you specify in the query, no?


JesusWept3's picture

nope, just give it a run and look at the results

waldowilliams's picture

When I run the query, I get ~ 745 returns, (archives) Of that, 715 are Journal Archives with different modified date stamps. That's OK really, but I have 64 mailbox archives and  the query only reports on 30 or so.

Can you offer some guidance?


JesusWept3's picture

well it will only show you archives that have had nothing archived in 3 months
so is it possible the other 34 archives have had things archived in the last 3 months or less?