cancel
Showing results for 
Search instead for 
Did you mean: 

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

Dushan_Gomez
Level 6

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 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

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 5

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

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?

Dushan_Gomez
Level 6

Hi Andrew,

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

JesusWept3
Level 6
Partner Accredited Certified

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

Dushan_Gomez
Level 6

Thank you Jesus :)

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

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