Forum Discussion

Dushan_Gomez's avatar
12 years ago

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 ?
 

 

  • 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 

5 Replies

  • 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?

  • 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.

  • 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