04-08-2014 05:34 AM
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 :)
Solved! Go to Solution.
04-08-2014 07:46 AM
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
04-08-2014 05:42 AM
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 ??
04-08-2014 05:53 AM
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?
04-08-2014 06:15 AM
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..
04-08-2014 07:46 AM
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
04-10-2014 10:49 PM
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?
04-11-2014 12:05 AM
you can right click and 'save results as' as csv or copy and paste the results into to excel.
04-11-2014 01:21 AM
He Kenneth, from me is indeed per archive, not item
04-17-2014 03:51 AM
Thaks for all help guys :)
JesusWept3,came up with the closest soultion for me :)
04-28-2014 04:34 AM
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?
04-28-2014 04:49 AM
It is the original size of the item so that would be uncompressed.
04-28-2014 05:05 AM
Thanks Tony for confirming this.
04-28-2014 06:35 AM
Is it possible to selectively rehrdate only these items which are equal to or greater than 25 MB into user's mailbox?
04-28-2014 07:18 AM
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.
04-28-2014 07:36 AM
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?