'***********************************************************************************
'*
'*	14.07.2009 - Jeremy Knight -- EV-User-Report.vbs.
'*
'***********************************************************************************
'* Setup Environment.

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
WorkingDirectory = "D:\Scripts\EV-User-Report\"
'Fix folder path for WorkingDirectory (make sure there is a trailing backslash)
If right(WorkingDirectory,1) <> "\" then 
	WorkingDirectory = WorkingDirectory & "\"
End if

Const ForReading = 1, ForWriting = 2, ForAppending = 8 

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

Set OutputFile = fso.CreateTextFile(WorkingDirectory & DateTime & "_EV-User-Report.csv", ForWriting)

OutputFile.writeline "Mailbox,Exchange Server,EV Server, #Items (Mailbox), #Items (Archive)," & _
	"Mbx Size (MB),Archive Size (MB),Total Size (MB),Archive Created,Archive Updated,Exchange State," & _
	"DB Server, Database"
'***********************************************************************************
'* Set SQL Queries.
Set SQLQueryFile = fso.OpenTextFile(WorkingDirectory & "SQLQuery.sql", ForReading)
SQLQuery = SQLQueryFile.ReadAll

'***********************************************************************************
'* Set database servers and databases.
strDatabaseServer = "Your_DB_Server"
strDatabaseNames = "EV_Database_1,EV_Database_2,EV_Database_3"

'***********************************************************************************
'* Run Queries and write data.
DatabaseNamesArray = Split(strDatabaseNames,",")
For each strdatabase in DatabaseNamesArray
	connection = "Driver={SQL Server};Server=" & strDatabaseServer & ";Database=" & strDatabase & ";Trusted_Connection=yes;"
		
	'************************************
	'* 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
	
	GetEVServerName()
	
	While EVServer = ""
		GetEVServerName()
	Wend
	
	'************************************
	'* Write data.
	While not result.EOF
		OutputFile.writeline (result("Mailbox")) & "," & _
			(result("Exchange Server")) & "," & _
			EVServer & "," & _
			(result("#Items (Mailbox)")) & "," & _
			(result("#Items (Archive)")) & "," & _
			(result("Mbx Size (MB)")) & "," & _
			(result("Archive Size(MB)")) & "," & _
			(result("Total Size(MB)")) & "," & _
			(result("Archive Created")) & "," & _
			(result("Archive Updated")) & "," & _
			(result("Exchange State")) & "," & _
			strDatabaseServerLytham & "," & _
			strdatabase

		result.movenext()
	Wend
Next
'***********************************************************************************
Function GetEVServerName
	select case strDatabase
		case "EV_Database_1"
			EVServer = "Your_EV_Server1"
		case "EV_Database_2"
			EVServer = "Your_EV_Server2"
		case "EV_Database_3"
			EVServer = "Your_EV_Server3"
		case else
			EVServer = "Error"
	end select
End Function
'***********************************************************************************