cancel
Showing results for 
Search instead for 
Did you mean: 

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

Dushan_Gomez
Level 6

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

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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 

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?

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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.

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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 

Dushan_Gomez
Level 6

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

Dushan_Gomez
Level 6

Yes it works Tony !

many thanks for the assistance smiley