cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

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

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 ?
 

 

1 Solution

Accepted Solutions
Highlighted
Accepted Solution!

You can try this one. I dont

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 
For the best O365 Reporting Solution click here Radar-Reporting

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

If you are on EV 8 I don't

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.

For the best O365 Reporting Solution click here Radar-Reporting
Highlighted
Accepted Solution!

You can try this one. I dont

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 
For the best O365 Reporting Solution click here Radar-Reporting

View solution in original post

Highlighted

Yes I'm on EV 8.0 SP4 at the

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

Highlighted

Yes it works Tony ! many

Yes it works Tony !

many thanks for the assistance smiley