Forum Discussion

rajesh_velagapu's avatar
rajesh_velagapu
Level 4
13 years ago

SQL Query to find archived messages & archive item size in a specified period?

1) SQL Query to find archived messages & archive item size in a specified period?

  And

 2) EV server has 2 differnt mailbox archiving tasks, SQL query to find archive items and
    size for each task (each mailbox server) in a specified period?


 Example: SQL Query to find archived messages processed in a specified period? 


select count(*) as '4/12'

from saveset

where archiveddate >= '4/12/2011' and archiveddate < '4/13/2011'


 

Enterprise Vault
Information Governance
Tip-How to
  • JesusWept3's avatar
    JesusWept3
    13 years ago

    This query should work for you

     

     

    SELECT ESE.ExchangeComputer "Exchange Server", 
       COUNT(S.IdTransaction) "Items Archived",
       SUM(S.ItemSize)/1024 "Size of Items (MB)"
FROM   EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
       EnterpriseVaultDirectory.dbo.ExchangeMailboxStore EMS,
       EnterpriseVaultDirectory.dbo.ExchangeServerEntry ESE,
       yourVaultStore.dbo.ArchivePoint AP,
       yourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = EME.DefaultVaultId
  AND  EME.MbxStoreIdentity = EMS.MbxStoreIdentity
  AND  EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity 
  AND  S.ArchivedDate > DATEADD(d, -1, getDate())
GROUP BY ESE.ExchangeComputer
  • RahulG's avatar
    RahulG
    Level 6
    13 years ago

     

    SELECT SUM(ItemSize) AS Expr1, MIN(ArchivedDate) AS Expr2, MAX(ArchivedDate) AS Expr3
    FROM Saveset


    WHERE (ArchivedDate > '2010-01-01 18:00') AND (ArchivedDate < '2010-06-01 5:00')

    The stored procedure usps_gettablesize saveset can be used to get the number of archived items stored in a specific Vault Store

  • TonySterling's avatar
    TonySterling
    Moderator
    13 years ago

    few questions,

    What version of EV? 

    How many EV Servers?

    How many Vault Stores?

  • rajesh_velagapu's avatar
    rajesh_velagapu
    Level 4
    13 years ago

    Thanks!

    2) EV server has 2 differnt mailbox archiving tasks, SQL query to find archive items and
        size for each task (each mailbox server) in a specified period?

  • TonySterling's avatar
    TonySterling
    Moderator
    13 years ago

    So if each mailbox task uses a different vault store it is pretty easy, just run this against each vault store database:

    -- This one gives a hourly rate:
    --Runs against the VaultStore Database
    select "Archived Date" = left (convert (varchar, archiveddate,20),14),
    "Hourly Rate" = count (*),
    "Av Size" = sum (itemsize)/count (*)
    from saveset
    where archiveddate between '2000-05-01' and '2006-07-31'
    group by left (convert (varchar, archiveddate,20),14)
    order by "Archived Date" desc

    -- This one gives a daily rate:
    --Runs against the VaultStore Database
    select "Archived Date" = left (convert (varchar, archiveddate,20),10),
    "Daily Rate" = count (*),
    "Av Size" = sum (itemsize)/count (*)
    from saveset
    where archiveddate between '2004-05-01' and '2006-05-31'
    group by left (convert (varchar, archiveddate,20),10)
    order by "Archived Date" desc

  • rajesh_velagapu's avatar
    rajesh_velagapu
    Level 4
    13 years ago

    Thanks Tony!!!

    We have 2 mailbox servers (2 tasks on the EV server) pointing to same vault store. 

     

  • RahulG's avatar
    RahulG
    Level 6
    13 years ago

    I am not sure , but I think it is not possible to get the query to 2nd question.

  • JesusWept3's avatar
    JesusWept3
    Level 6
    13 years ago

    This query should work for you

     

     

    SELECT ESE.ExchangeComputer "Exchange Server", 
       COUNT(S.IdTransaction) "Items Archived",
       SUM(S.ItemSize)/1024 "Size of Items (MB)"
FROM   EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
       EnterpriseVaultDirectory.dbo.ExchangeMailboxStore EMS,
       EnterpriseVaultDirectory.dbo.ExchangeServerEntry ESE,
       yourVaultStore.dbo.ArchivePoint AP,
       yourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = EME.DefaultVaultId
  AND  EME.MbxStoreIdentity = EMS.MbxStoreIdentity
  AND  EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity 
  AND  S.ArchivedDate > DATEADD(d, -1, getDate())
GROUP BY ESE.ExchangeComputer
  • JesusWept3's avatar
    JesusWept3
    Level 6
    13 years ago

    run it against each Vault Store, you are going to have to change the "yourVaultStore.dbo...." to the name of your actual vault store database name. and if you have 12 vault store databases, then you will have to run it 12 times , each time changing the yourVaultStore to the db name of your vault store database.

    As for the dates, just change

    AND  S.ArchivedDate > DATEADD(d, -1, getDate())

    To

    AND S.ArchivedDate > '2012-01-01 00:00:00.0000' AND S.ArchivedDate < '2012-01-31 23:59:59.0000'


    That would give you all the items archived for the month of january 2012 on all your exchange servers that have been archived by that vault store