Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

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