Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

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

Created: 29 Jul 2012 • Updated: 30 Jul 2012 | 5 comments
This issue has been solved. See solution.

In Regards to the same article: https://www-secure.symantec.com/connect/forums/sql-query-find-archived-messages-archive-item-size-specified-period

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 
 
but then i got this error:
 
Msg 207, Level 16, State 1, Line 12
Invalid column name 'exchangeserveridentity'.
 

Comments 5 CommentsJump to latest comment

AndrewB's picture

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?

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

Dushan Gomez's picture

Hi Andrew,

Perhaps it is due to my Exchange server is archived by EV 8.0 SP4 ?

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

AndrewB's picture

i didnt see that you specified what version of EV you were on but it looks like you got this sorted out.

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

JesusWept3's picture

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
SOLUTION
Dushan Gomez's picture

Thank you Jesus :-)

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP