cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query to find archived messages & archive item size per mailbox user

kenneth_hansen1
Level 4
Partner Accredited

Hi Guys,

I have a request to find a way to get information out of EV 10 about all the users archived and the items archived bases on size pr mail.
Advanced search does not give me what I need because EV Admin account does not have access to all archives.
So I belive only way to get what I need is thru SQL query.

I need to be able to get infomation on all mail archived pr user, and the size.
More spesific mail larger than 25MB.
And a way to export that information to a spreed sheet.

Anyone have an ide on how to create script like that

Thank  you :)
 

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

You can use this query, it will tell you the following

1. Archive Name
2. Folder Path the item was archived under (i.e. \Sent Items)
3. Transaction ID (a unique identifier for the item)
4. IndexSeqNo (an identifier you can use to find the item easily via Search.asp)
5. ArchivedDate (The date the item was archived, in UTC, so 01/01/14 17:00 UTC would be 12PM EST)
6. IdDateTime (The Sent/Received Date of the item, again in UTC)
7. Item size, in KB

What it won't tell you is things like, who sent the item, who the item was sent to, subjects, names attachments, etc. Only the actual item and the index include those details.

So what you could do is if there are items you want to see, give yourself permission to the users archive, then go to http://yourEVServer/EnterpriseVault/Search.asp?advanced

Then take the "Item Sequence Number" thats posted here
then where it says "Sequence Number" From - To, just put in the number returned from the query and you will get the exact item

 

Also change EVVSYourVaultStore_1 to the Database name of your Vault Store, you should be able to see that pretty easily from your SQL Browser, but don't user EVVSGYourVaultStoreGroup_1_1

 

DECLARE	@sizeInMB int
DECLARE @sizeInB int

SET @sizeInMB = 25
SET @sizeInB = @sizeInMB*1024*1024

SELECT A.ArchiveName "Archive Name",
       REPLACE(CAST(AF.FolderPath AS Varchar(max)), '?', '\') "Folder Path",
       S.IdTransaction "Transaction ID",
       S.IndexSeqNo "Index Sequence Number",
       S.ArchivedDate "Archived Date",
       S.IdDateTime "Sent/Received Date",
       SP.OriginalSize/1024 "Size (KB)"
  FROM EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.Root R1,
       EnterpriseVaultDirectory.dbo.Root R2,
       EVVSYourVaultStore_!.dbo.ArchivePoint AP,
       EVVSYourVaultStore_1.dbo.Vault V,
       EVVSYourVaultStore_1.dbo.Saveset S,
       EVVSYourVaultStore_1.dbo.SavesetProperty SP
 WHERE S.SavesetIdentity = SP.SavesetIdentity
   AND S.ArchivePointIdentity = AP.ArchivePointIdentity
   AND S.VaultIdentity = V.VaultIdentity
   AND AP.ArchivePointId = R1.VaultEntryId
   AND V.VaultID = R2.VaultEntryId
   AND R1.RootIdentity = A.RootIdentity
   AND R2.RootIdentity = AF.RootIdentity
   AND SP.OriginalSize >= @sizeInB  

 

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

View solution in original post

14 REPLIES 14

A_J1
Level 6
Employee Accredited Certified

Hello Kenneth,

 

From SQL you can get the item saveset id of all the items which are greater than 25 MB on per user basis

is that what you want ??

 

GabeV
Level 6
Employee Accredited

Hello Kenneth,

When you say "I need to be able to get infomation on all mail archived per user", are you referring to all the information from the email: subject, send/received date, content, etc? If so, a SQL query might not give you all the info you are looking for.

What information exactly do you need?

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

I use this query:

SELECT A.ArchiveName, COUNT(S.IdTransaction) AS ArchivedItemCount, SUM(S.ItemSize)/1024 AS ArchivedItemSizeMB
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
-- YOURVAULTSTORE.dbo.ArchivePoint AP,
-- YOURVAULTSTORE.dbo.Saveset S

WHERE (A.ArchiveName like '%Alink%')

AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryID
AND R.RootIdentity = A.RootIdentity
-- AND S.ArchivedDate > dateAdd("m",-1,getDate())
AND S.ArchivedDate < getDate()
GROUP BY A.ArchiveName

Change YOURVAULTSTORE to reflect the Vaultstore databasename.

If you want this for a period of time (last month) you uncomment the line

-- AND S.ArchivedDate > dateAdd("m",-1,getDate())

You can also use "d" for days..
 

Regards. Gertjan

JesusWept3
Level 6
Partner Accredited Certified

You can use this query, it will tell you the following

1. Archive Name
2. Folder Path the item was archived under (i.e. \Sent Items)
3. Transaction ID (a unique identifier for the item)
4. IndexSeqNo (an identifier you can use to find the item easily via Search.asp)
5. ArchivedDate (The date the item was archived, in UTC, so 01/01/14 17:00 UTC would be 12PM EST)
6. IdDateTime (The Sent/Received Date of the item, again in UTC)
7. Item size, in KB

What it won't tell you is things like, who sent the item, who the item was sent to, subjects, names attachments, etc. Only the actual item and the index include those details.

So what you could do is if there are items you want to see, give yourself permission to the users archive, then go to http://yourEVServer/EnterpriseVault/Search.asp?advanced

Then take the "Item Sequence Number" thats posted here
then where it says "Sequence Number" From - To, just put in the number returned from the query and you will get the exact item

 

Also change EVVSYourVaultStore_1 to the Database name of your Vault Store, you should be able to see that pretty easily from your SQL Browser, but don't user EVVSGYourVaultStoreGroup_1_1

 

DECLARE	@sizeInMB int
DECLARE @sizeInB int

SET @sizeInMB = 25
SET @sizeInB = @sizeInMB*1024*1024

SELECT A.ArchiveName "Archive Name",
       REPLACE(CAST(AF.FolderPath AS Varchar(max)), '?', '\') "Folder Path",
       S.IdTransaction "Transaction ID",
       S.IndexSeqNo "Index Sequence Number",
       S.ArchivedDate "Archived Date",
       S.IdDateTime "Sent/Received Date",
       SP.OriginalSize/1024 "Size (KB)"
  FROM EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.Root R1,
       EnterpriseVaultDirectory.dbo.Root R2,
       EVVSYourVaultStore_!.dbo.ArchivePoint AP,
       EVVSYourVaultStore_1.dbo.Vault V,
       EVVSYourVaultStore_1.dbo.Saveset S,
       EVVSYourVaultStore_1.dbo.SavesetProperty SP
 WHERE S.SavesetIdentity = SP.SavesetIdentity
   AND S.ArchivePointIdentity = AP.ArchivePointIdentity
   AND S.VaultIdentity = V.VaultIdentity
   AND AP.ArchivePointId = R1.VaultEntryId
   AND V.VaultID = R2.VaultEntryId
   AND R1.RootIdentity = A.RootIdentity
   AND R2.RootIdentity = AF.RootIdentity
   AND SP.OriginalSize >= @sizeInB  

 

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

kenneth_hansen1
Level 4
Partner Accredited

Hi Guys,

Sorry for late respons.

A_J, almost, you are corret, but I need to know what email as well.

GabeV, no I just need the user, the email and the size greater that 25MB, no contet of the mail.

GertjanA, This would not give me per email? looks like the total size of a archive, is that correct

JesusWept3, thaks I'll have allok at you script.

Is there also anyway to get the output to something like notepad, excel?
 

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

you can right click and 'save results as' as csv or copy and paste the results into to excel.

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

He Kenneth, from me is indeed per archive, not item

Regards. Gertjan

kenneth_hansen1
Level 4
Partner Accredited

Thaks for all help guys :)

JesusWept3,came up with the closest soultion for me :)
 

Mohit_Pal
Level 3

Hi All,

Please let me know if the above query will return the compressed or uncompressed size of the mail items? I mean if it will give us the email items greater than 25 MB, that size will be the compressed or uncompressed one?

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

It is the original size of the item so that would be uncompressed.

Mohit_Pal
Level 3

Thanks Tony for confirming this.

Mohit_Pal
Level 3

Is it possible to selectively rehrdate only these items which are equal to or greater than 25 MB into user's mailbox?

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

natively? no. but maybe some 3rd party tools that can filter on size or a custom tool could be written if you wanted to commission one.

Mohit_Pal
Level 3

Thanks for replying

Is there no way or workaround through which we can accomplish this natively?

Do you know the name of any third party tool which can do this? If I want to get a custom tool written, how should I proceed?