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

 

1 Solution

Accepted Solutions
Highlighted
Accepted Solution!

Its this bit here AND

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
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

5 Replies
Highlighted

i copied and pasted this

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?

Highlighted

Hi Andrew, Perhaps it is due

Hi Andrew,

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

Highlighted
Accepted Solution!

Its this bit here AND

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
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

Highlighted

Thank you Jesus :-)

Thank you Jesus :-)

Highlighted

i didnt see that you

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