cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query - Enterprise Vault

Rinku_Lalli
Level 4

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

1 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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"

View solution in original post

3 REPLIES 3

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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"

TSO
Level 4

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....

JesusWept3
Level 6
Partner Accredited Certified

Yup tony is right, only thing i can add is to remove both USE statements at the top :)

https://www.linkedin.com/in/alex-allen-turl-07370146