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.

EV SQL query script for total number of message archived in the past day ?

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

Same as in article: https://www-secure.symantec.com/connect/forums/need-sql-query

Hi can anyone here please let me know what could go wrong with the following script:

SELECT CE.computername        "EV Server", 

       T.name                 "Task Name", 

       ESE.exchangecomputer   "Exchange Server", 

       Count(s.idtransaction) "Items Archived" 

FROM   enterprisevaultdirectory.dbo.exchangemailboxentry EME, 

       enterprisevaultdirectory.dbo.exchangemailboxstore EMS, 

       enterprisevaultdirectory.dbo.exchangeserverentry ESE, 

       enterprisevaultdirectory.dbo.archivingretrievaltask ART, 

       enterprisevaultdirectory.dbo.task T, 

       enterprisevaultdirectory.dbo.computerentry CE, 

       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 ART.exchangeserverentryid = ESE.exchangeserverentryid 

       AND ART.taskentryid = T.taskentryid 

       AND T.computerentryid = CE.computerentryid 

       AND T.tasktype = 0 

       AND T.name = 'Exchange Mailbox Archiving Task for yourExchangeServer' 

       AND S.archiveddate > Dateadd(d, -3, Getdate()) 

GROUP  BY CE.computername, 

          T.name, 

          ESE.exchangecomputer 
 
I have changed the two lines from:
       yourvaultstore.dbo.archivepoint AP, 

       yourvaultstore.dbo.saveset S 
into:
       EVVaultstore1.dbo.archivepoint AP, 

       EVVaultstore1.dbo.saveset S 
 
to match my EV VaultStore Database, but still I got this error:
Msg 207, Level 16, State 1, Line 16

Invalid column name 'exchangeserveridentity'.
 
any help please ?
 

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

TonySterling's picture

If you are on EV 8 I don't think that will work for you as the tables changed in EV 9 and above.  I will have a look around to see if I have a copy for EV 8.

Dushan Gomez's picture

Yes I'm on EV 8.0 SP4 at the moment, the SQL Server DB is on SQL 2005 SP4

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

TonySterling's picture

You can try this one. I dont have an EV 8 lab to check it in but it should work.

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

Yes it works Tony !

many thanks for the assistance smiley

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