'**************************************************************************************************
'*
'*	08.07.2009 - Jeremy Knight -- EV-Daily-Archiving.vbs.
'*
'*	This script creates and then appends archiving data for 24 hour periods. It should show over a period of time
'*	how busy the EV servers are in terms of the number of items they archive per day.
'*
'**************************************************************************************************
'* Setup Environment.
Option Explicit

Dim Styr, Stmo, Stdt, DayNo, DayName, Sthr, Stmin, Stsec, ProcessRun, YesterdaysDate
Dim SQLQuery, strDatabaseServer, strUserName, strUserPassword
Dim strDatabases, strDatabase, connection, result, Server, CompresionRatio
Dim fso, wshShell, WorkingDirectory, myconn, OutputFile, objOutputFile
Const ForWriting = 2, ForAppending = 8

WorkingDirectory = "D:\temp\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set wshShell = WScript.CreateObject ("WSCript.shell")
'Fix folder path for WorkingDirectory (make sure there is a trailing backslash)
If right(WorkingDirectory,1) <> "\" then WorkingDirectory = WorkingDirectory & "\"

'**************************************************************************************************
'* Get Date & Time Data.
Styr = Year(Now)
Stmo = Month(Now)
if Stmo < 10 Then Stmo = "0" & Stmo
Stdt = Day(Now)
if Stdt < 10 Then Stdt = "0" & Stdt
DayNo = WeekDay(Now) - 1
DayName = WeekDayName(DayNo,False)
Sthr = Hour(Now)
if Sthr < 10 Then Sthr = "0" & Sthr
Stmin = Minute(Now)
if Stmin < 10 Then Stmin = "0" & Stmin
Stsec = Second(Now)
if Stsec < 10 Then Stsec = "0" & Stsec
ProcessRun = Styr & "/" & Stmo & "/" & Stdt & " " & Sthr & ":" & Stmin & ":" & Stsec     
YesterdaysDate = Styr & "-" & Stmo & "-" & Stdt - 1

'**************************************************************************************************
'* Set SQL Query and database settings.
SQLQuery = "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'"

strDatabaseServer = "Your_DB_Server"
strDatabases = Array("Your_Database1","Your_Database2","Your_Database3")
strUserName = "Your_DB_Username"
strUserPassword = "Your_DB_Password"

'**************************************************************************************************
'* Setup and open database connection.
For each strDatabase in strDatabases

	'************************************
	'* Choose your connection string.
	
	'* 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;"
	'************************************
	'* 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
	
	'* Setup output file.
	OutputFile = Server & "-EV-Daily-Archiving.csv"
	
	'* If the output file does not exist, create it and write the headings in the file. Otherwise open the file for appending.
	If Not fso.FileExists(OutputFile) Then
		Set objOutputFile = fso.CreateTextFile(WorkingDirectory & OutputFile, ForWriting)
		objOutputFile.writeline "ProcessRunOnDateTime,Archive Day,Number of Items," & _
			"First Archived,Last Archived,Saveset Size (MB),Orignal Size (MB)"
	Else
		Set objOutputFile = fso.OpenTextFile(WorkingDirectory & OutputFile, ForAppending)
	End if
	
	'************************************
	'* Open database connection
	Set myconn = CreateObject("adodb.connection")
	myconn.open (connection)

	If cstr(err.number) <> 0 Then
		wscript.echo (" - Error creating connection to " _
			& "database server " & strDatabaseServer & " / " & strDatabase & ".  Check your connection string " _
			& "or database server name/IP and try again.")
		wscript.quit
	End if
		
	On Error Goto 0
		
	Set result = CreateObject("adodb.recordset")
	If err.number <> 0 then msgbox err.description

	'* Execute the query
	Set result = myconn.execute(SQLQuery)
	If err.number <> 0 then msgbox err.description
	
	'************************************
	'* Run through each record retrieved and write data to output file.
	While not result.EOF
		objOutputFile.writeline ProcessRun & "," & _
			DayName & "," & _
			(result("Count")) & "," & _
			(result("FirstArchive")) & "," & _
			(result("LastArchive")) & "," & _
			(result("SaveSetSize")) & "," & _
			(result("OriginalSize"))
		result.movenext()
	Wend
Next
'**************************************************************************************************