Archiving Reports

[Amanda_H_]

Hi all,

For EV6.0 sp2... I am trying to get a report which will show me a monthly/quarterly number of the amount we've archived. I don't want to re-invent the wheel! Is there a query already out there that I can copy? Or is the sum of (itemsize) written into a select statement constrained with a min/max date all I need? If that's the query in a nutshell, I can eventually build a job that will run the query and email me the results but I am having a hard time finding any real insight on the various EV DB tables and I don't know for sure that this is the right value to be querying for.

Also is there a good reference out there for the various tables, etc. I'm not intending to change any of them (heavens no!) but I have a really good SQL guy on my team and we could probably write some great reports but we are not sure if we're interpreting the database/relationships/tables etc properly.

Thanks in advance!

Amanda

[Robert_Ton]

How many vault stores do you have? If you don't have too many, you can just manually run a vault store report on the first of every month, and then compute the difference.

[Amanda_H_]

That idea will never fly with my bosses.

I know how to write a SQL query that packages the prior month's date into the query and I know how to set it up to run on the first of the month. I just need confirmation that (itemsize) from the vault store DB is the right value to be tallying. Can anyone confirm?

Thanks!

[Robert_Ton]

I am running SP3, and the SQL table which contains the size of each archive is the ArchivePoint table. There is a column in that table named ArchivedItemsSize.

[bob_cratchet_2]

SQL script to tell how much has been archived in the last 2 weeks for each archive. If you want it for any other time then just edit the 14 to whatever amount of days you want

select ArchiveName,count(*) as 'ItemsArchived' from enterprisevaultdirectory.dbo.root,enterprisevaultdirectory.dbo.archive,archivepoint ,saveset where archiveddate >getdate()-14 and
saveset.archivepointidentity = archivepoint.archivepointidentity and enterprisevaultdirectory.dbo.root.vaultentryid = archivepoint.archivepointid and enterprisevaultdirectory.dbo.root.rootidentity=enterprisevaultdirectory.dbo.archive.rootidentity
group by archivename
order by archivename desc