02-25-2011 07:35 AM
Hello,
I'm looking for an SQL Query which will query my Vault Stores for the number and size of all emails whose dates are for the last ten years. I have this query:
"use enterprisevaultdirectory
use EVVSMailboxstore_1
select a.ArchiveName,count(*) as 'ItemsArchived' ,sum(s.Itemsize)as 'Itemsize'
from enterprisevaultdirectory.dbo.root r, EVVSMailboxstore_1.dbo.ArchivePoint ap,enterprisevaultdirectory.dbo.archive a ,EVVSMailboxstore_1.dbo.saveset s
where s.archiveddate >'01-01-2001' and s.archiveddate < '01-01-2011' and
s.archivepointidentity = ap.archivepointidentity and
r.vaultentryid = ap.archivepointid
and r.rootidentity=a.rootidentity
group by a.archivename
order by a.archivename desc"
It returns the name of the archive followed by the size and number of items. However, it is returning emails based on the date it was archived and not the date it was sent/received. The EV system was put in place in 2005.
Does anyone have any ideas on how to run such a script? I have found a table called IndexVolume which gives the date of the youngest and oldest items....
Thanks
Solved! Go to Solution.
02-25-2011 07:55 AM
Change s.archiveddate to s.IdDateTime
"use enterprisevaultdirectory
use EVVSMailboxstore_1
select a.ArchiveName,count(*) as 'ItemsArchived' ,sum(s.Itemsize)as 'Itemsize'
from enterprisevaultdirectory.dbo.root r, EVVSMailboxstore_1.dbo.ArchivePoint ap,enterprisevaultdirectory.dbo.archive a ,EVVSMailboxstore_1.dbo.saveset s
where s.IdDateTime between '2001-01-01' and '2011-01-01' and
s.archivepointidentity = ap.archivepointidentity and
r.vaultentryid = ap.archivepointid
and r.rootidentity=a.rootidentity
group by a.archivename
order by a.archivename desc"
02-25-2011 07:55 AM
Change s.archiveddate to s.IdDateTime
"use enterprisevaultdirectory
use EVVSMailboxstore_1
select a.ArchiveName,count(*) as 'ItemsArchived' ,sum(s.Itemsize)as 'Itemsize'
from enterprisevaultdirectory.dbo.root r, EVVSMailboxstore_1.dbo.ArchivePoint ap,enterprisevaultdirectory.dbo.archive a ,EVVSMailboxstore_1.dbo.saveset s
where s.IdDateTime between '2001-01-01' and '2011-01-01' and
s.archivepointidentity = ap.archivepointidentity and
r.vaultentryid = ap.archivepointid
and r.rootidentity=a.rootidentity
group by a.archivename
order by a.archivename desc"
02-25-2011 08:00 AM
Hi Rinku
Try this...
"use enterprisevaultdirectory
use EVVSMailboxstore_1
select a.ArchiveName,count(*) as 'ItemsArchived' ,sum(s.Itemsize)as 'Itemsize'
from enterprisevaultdirectory.dbo.root r, EVVSMailboxstore_1.dbo.ArchivePoint ap,enterprisevaultdirectory.dbo.archive a ,EVVSMailboxstore_1.dbo.saveset s
where s.iddatetime >'01-01-2001' and s.iddatetime < '01-01-2011' and
s.archivepointidentity = ap.archivepointidentity and
r.vaultentryid = ap.archivepointid
and r.rootidentity=a.rootidentity
group by a.archivename
order by a.archivename desc"
Let me know if you get any issues....
02-25-2011 08:14 AM
Yup tony is right, only thing i can add is to remove both USE statements at the top :)