Forum Discussion

Dushan_Gomez's avatar
12 years ago

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

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'.
 

 

  • 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
    

5 Replies

  • 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?

  • 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
    
  • i didnt see that you specified what version of EV you were on but it looks like you got this sorted out.