cancel
Showing results for 
Search instead for 
Did you mean: 

Insights

I needed an easy way to look at the trends of EV archiving. Number of messages and sizes; So I created this VBScript around MichelZ's SQL script:

SELECT COUNT(*) AS Count, MIN(ArchivedDate) AS FirstArchive, MAX (ArchivedDate) AS LastArchive, SUM(ItemSize)/1024 AS SaveSetSize, SUM(OriginalSize)/1024/1024 AS OriginalSize
FROM Saveset INNER JOIN SavesetProperty ON Saveset.SavesetIdentity = SavesetProperty.SavesetIdentity
WHERE ArchivedDate > YesterdaysDate 00:00' AND ArchivedDate < 'YesterdaysDate 23:59'

I have scheduled this script to run each morning. It gets the previous day's values and writes them to a .csv file (per server) which I can look at from time to time.

This version is a simplified version of one I use. We have two database servers in our organisation, one in each datacentre. I am assuming most users will have one database server running all EV databases as this is the easiest way to go about things.

I have tried to make this script as short as possible and commented as much as possible. I would recommend opening it with a script editor such as notepad++ rather than plain notepad. Within the script you may need to change the following for your environment:

WorkingDirectory = "D:\temp\" -- Line 18

strDatabaseServer = "Your_DB_Server" --Line 50
strDatabases = Array("Your_DB_Database1","Your_DB_Database2","Your_DB_Database3") --Line 51 NOTE: *
strUserName = "Your_DB_Username" -- Line 52
strUserPassword = "Your_DB_Password" -- Line 53

* I am assuming you have more than one database, if this is not the case then change the line to read:
strDatabases = Array("Your_DB_Database1")
Yes you are specifying an array for a single item, but just to make the change easy.

Choose a suitable connection string. The first one is commented out by the ' symbol.

'* Connection string for SQL username and password
'connection = "Driver={SQL Server}; Server=" & strDatabaseServer "; Database=" & strDatabase & "; UID=" & strUserName"; PWD=" & strUserPassword

'* Connection string for pass-through authentication.
connection = "Driver={SQL Server};Server=" & strDatabaseServer & ";Database=" & strDatabase & ";Trusted_Connection=yes;"

Hopefully that should be it. If anyone has any issues send me a message and I will try and respond as quickly as I can.
 

Comments
Thanks for posting the script jprknight.

I tried running your script but getting access denied error on line 96. I have a couple of questions below and would appreciated if you could answer them.

The the connection string. If I select the Pass Through Authentication, do I need to comment out the strUserName and strUserPassword?

Also the part

'* Select Case statement to select the corresponding EV server for each database. Used in the file name.
 select case strDatabase
  case "EVMailboxVS03"
   Server = "D002DVSE1"
  case "EVMailboxVS04"
   Server = "D002DVSE2"
  case "EVMailboxVS06"
   Server = "D002DVSE3"
  case else
   Server = "Error"
 end select

Do I need to change anything here at all? I assume that this has to be changed to each of the Exchange mailbox server that we have and their respective system mailbox account, correct?