07-29-2012 06:03 PM
In Regards to the same article: https://www-secure.symantec.com/connect/forums/sql-query-find-archived-messages-archive-item-size-sp...
How Can I execute the following SQL code to do the same with EV 8.0 SP4:
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, EVVaultstore1.dbo.archivepoint AP, EVVaultstore1.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
Solved! Go to Solution.
07-30-2012 05:22 AM
Its this bit here
AND EME.mbxstoreidentity = EMS.mbxstoreidentity
AND EMS.exchangeserveridentity = ESE.exchangeserveridentity
Those tables and linkages were added for Exchange 2010 compatibility (EV9+ only) and don't exist in EV8
So it should be
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.exchangeserverentry ESE, EVVaultstore1.dbo.archivepoint AP, EVVaultstore1.dbo.saveset S WHERE S.archivepointidentity = AP.archivepointidentity AND AP.archivepointid = EME.defaultvaultid AND EME.exchangeserveridentity = ESE.exchangeserveridentity AND S.archiveddate > Dateadd(d, -1, Getdate()) GROUP BY ESE.exchangecomputer
07-29-2012 06:37 PM
i copied and pasted this script into my lab and made the changes like you did. no errors here. are you not archiving from exchange maybe?
07-30-2012 01:17 AM
Hi Andrew,
Perhaps it is due to my Exchange server is archived by EV 8.0 SP4 ?
07-30-2012 05:22 AM
Its this bit here
AND EME.mbxstoreidentity = EMS.mbxstoreidentity
AND EMS.exchangeserveridentity = ESE.exchangeserveridentity
Those tables and linkages were added for Exchange 2010 compatibility (EV9+ only) and don't exist in EV8
So it should be
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.exchangeserverentry ESE, EVVaultstore1.dbo.archivepoint AP, EVVaultstore1.dbo.saveset S WHERE S.archivepointidentity = AP.archivepointidentity AND AP.archivepointid = EME.defaultvaultid AND EME.exchangeserveridentity = ESE.exchangeserveridentity AND S.archiveddate > Dateadd(d, -1, Getdate()) GROUP BY ESE.exchangecomputer
07-30-2012 06:49 AM
Thank you Jesus :)
07-30-2012 07:28 AM
i didnt see that you specified what version of EV you were on but it looks like you got this sorted out.