Video Screencast Help

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