07-29-2012 06:16 PM
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
yourvaultstore.dbo.archivepoint AP, yourvaultstore.dbo.saveset S
EVVaultstore1.dbo.archivepoint AP, EVVaultstore1.dbo.saveset S
Msg 207, Level 16, State 1, Line 16 Invalid column name 'exchangeserveridentity'.
Solved! Go to Solution.
07-29-2012 07:04 PM
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
07-29-2012 06:25 PM
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?
07-29-2012 07:00 PM
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.
07-29-2012 07:04 PM
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
07-29-2012 07:45 PM
Yes I'm on EV 8.0 SP4 at the moment, the SQL Server DB is on SQL 2005 SP4
07-29-2012 07:47 PM
Yes it works Tony !
many thanks for the assistance