Forum Discussion

kenneth_hansen1's avatar
kenneth_hansen1
Level 4
11 years ago

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

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 :)
 

Enterprise Vault
Information Governance
Reporting
Windows Server (2003-2008)
  • JesusWept3's avatar
    JesusWept3
    11 years ago

    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

     

  • GertjanA's avatar
    GertjanA
    Moderator
    11 years ago

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

  • A_J1's avatar
    A_J1
    Level 6
    11 years ago

    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's avatar
    GabeV
    Level 6
    11 years ago

    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?

  • JesusWept3's avatar
    JesusWept3
    Level 6
    11 years ago

    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

     

  • kenneth_hansen1's avatar
    kenneth_hansen1
    Level 4
    11 years ago

    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's avatar
    AndrewB
    Moderator
    11 years ago

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

  • Mohit_Pal's avatar
    Mohit_Pal
    Level 3
    11 years ago

    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?