Need a SQL query

[carrs654]

Hi,

I am trying to find a SQL query using which i can find the following

1) Count of items archived per Archive based since certain dates & archived by a specific archiving task.

I have 4 archiving task which run on a schedule. I want to confirm how many items were archived by that task no 3 in 'X' no of days.

EV - 9.0.2

SQL - SQL 2005

Thanks

[Dead-Data]

Hi,

Do you have SQL Reporting Services and Enterprise Vault Reporting installed and configured?

This may well provide the information you're wanting.

http://www.symantec.com/business/support/index?page=content&id=HOWTO56492

http://www.symantec.com/business/support/index?page=content&id=TECH63335

Also have you checked out the EV Monitoring information?

[TonySterling]

You can use the query from this post:

https://www-secure.symantec.com/connect/forums/sql-query-find-archived-messages-archive-item-size-specified-period

Just change the dates and yourVaultStore to the actual name of your vault store.

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 BETWEEN '2012-01-01' AND '2012-01-31'
GROUP BY ESE.ExchangeComputer

[JesusWept3 · Accepted solution]

Thats a difficult one to provide due to the fact that the task can archive users spread across multiple vault stores.

So you may have a task that targets 1000 users on a single exchange server, but dependant on how they're provisioned you have may 250 users going to 1 vault store, 250 to another etc.

The accurate counts are all based on the vault store database.
So if you run a query you would have to do a union query to go against every vault store and if you have twelve of them, then it could be quite large.

But this is the query you're looking for

 

SELECT CE.ComputerName "EV Server",
       T.Name "Task Name",
       ESE.ExchangeComputer "Exchange Server",
       COUNT(s.IdTransaction) "Items Archived"
FROM  EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
      EnterpriseVaultDirectory.dbo.ExchangeMailboxStore EMS,
      EnterpriseVaultDirectory.dbo.ExchangeServerEntry ESE,
      EnterpriseVaultDirectory.dbo.ArchivingRetrievalTask ART,
      EnterpriseVaultDirectory.dbo.Task T,
      EnterpriseVaultDirectory.dbo.ComputerEntry CE,
      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 ART.ExchangeServerEntryId = ESE.ExchangeServerEntryId
  AND ART.TaskEntryId = T.TaskEntryId
  AND T.ComputerEntryId = CE.ComputerEntryId
  AND T.TaskType = 0
  AND T.Name = 'Exchange Mailbox Archiving Task for yourExchangeServer'
  AND S.ArchivedDate > DATEADD(d, -3, getDate())
GROUP BY CE.ComputerName, T.Name, ESE.ExchangeComputer

[carrs654]

@ Tony & @ Jesus - Both the Queries worked !!

Thanks again