05-30-2012 12:09 AM
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
Solved! Go to Solution.
05-30-2012 05:07 AM
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
05-30-2012 04:38 AM
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?
05-30-2012 04:58 AM
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
05-30-2012 05:07 AM
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
06-01-2012 02:55 AM
@ Tony & @ Jesus - Both the Queries worked !!
Thanks again