Daily Archiving Statistics appended to output file. VBScript.
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.