'******************************************************************************
'*
'* 	Jeremy Knight -- EV-Failed-Indexes.vbs.
'*
'******************************************************************************

Message = "<table width=" & chr(34) & "550" & chr(34) & "><tr><td><b><font size=" & _
			chr(34) & "2" & chr(34) & " face=" & chr(34) & "Arial" & chr(34) & _
			">Mailbox Displayname</b></font></td><td><b><font size=" & chr(34) & "2" & _
			chr(34) & " face=" & chr(34) & "Arial" & chr(34) & ">" & _
			"Failed, Offline & Rebuilding</b></font></td></tr>"	
			
sSQL = "Select RootIdentity,Failed,Offline,Rebuilding " & _
	"from IndexVolume where failed <> 0 or Offline <> 0 or Rebuilding <> 0"

strDatabaseServer = "Your_DB_Server"
strDatabaseName = "EnterpriseVaultDirectory"

connection = "Driver={SQL Server};Server=" & strDatabaseServer & _
	";Database=" & strDatabaseName & ";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 & " / " & strDatabaseName & ".  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(sSQL)
If err.number <> 0 then msgbox err.description


While not result.EOF
	LineCount = LineCount + 1
	FailedUserIndexesData = FailedUserIndexesData & (result("RootIdentity")) & "-" & _
		(result("Failed")) & "+" & (result("Offline")) & "*" & (result("Rebuilding")) & vbcrlf
	result.movenext()
Wend

FailedUserIndexesDataArray = Split(FailedUserIndexesData,vbcrlf)

For each Line in FailedUserIndexesDataArray
	if Len(Line) > 0 then
				
		Q1 = Instr(Line,"-")
		RootIdentity = mid(Line,1,Q1 - 1)
	
		Q2 = Instr(Line,"+")
		Q3 = Instr(Line,"*")
		
		StatusCount = 0
		
		'* Failed
		FailedLength = Q2 - Q1 - 1
		Failed = mid(Line,Q1 + 1,FailedLength)
		'msgbox "Failed = " & Failed
		If Failed = "True" then
			StatusCount = StatusCount + 1
			Failed = vbcrlf & "<font size=" & chr(34) & "2" & chr(34) & " face=" & _
				chr(34) & "Arial" & chr(34) & ">Status: Failed</font>"
		Else
			Failed = ""
		End if
		
		'*Offline
		OfflineLength = Q3 - Q2 - 1
		Offline = mid(Line,Q2 + 1,OfflineLength)
		'msgbox "Offline = " & Offline
		If Offline = "True" then
			StatusCount = StatusCount + 1
			If StatusCount > 1 then
				Offline = "<font size=" & chr(34) & "2" & chr(34) & " face=" & _
					chr(34) & "Arial" & chr(34) & ">, Offline</font>"
			Else
				Offline = vbcrlf & "<font size=" & chr(34) & "2" & chr(34) & " face=" & _
					chr(34) & "Arial" & chr(34) & ">Status: Offline</font>"
			End if
		Else
			Offline = ""
		End if
		
		'* Rebuilding
		Rebuilding = mid(Line,Q3 + 1)
		'msgbox "Rebuilding = " & rebuilding
		If Rebuilding = "True" then
			StatusCount = StatusCount + 1
			If StatusCount > 1 then
				Rebuilding = "<font size=" & chr(34) & "2" & chr(34) & " face=" & _
					chr(34) & "Arial" & chr(34) & ">, Rebuilding</font>"
			Else
				Rebuilding = vbcrlf & "<font size=" & chr(34) & "2" & chr(34) & " face=" & _
					chr(34) & "Arial" & chr(34) & ">Status: Rebuilding</font>"
			End if
		Else
			Rebuilding = ""
		End if
		
		sSQL2 = "Use EnterpriseVaultDirectory Select MbxDisplayName from ExchangeMailboxEntry " & _
			"Inner Join Root On Root.VaultEntryId=ExchangemailboxEntry.DefaultVaultid Where Root.RootIdentity = " & RootIdentity
		
		'* Execute the query
		Set result = myconn.execute(sSQL2)
		If err.number <> 0 then msgbox err.description
		
		Message = Message & "<tr><td><font size=" & chr(34) & "2" & chr(34) & _
			" face=" & chr(34) & "Arial" & chr(34) & ">" & result("MbxDisplayName") & "</td><td>" & _
			Failed & Offline & Rebuilding & "</td></tr></font>"	
	End if
Next
'******************************************************************************
Message = Message & "</table>"

EmailFooter = "<font size=" & chr(34) & "2" & chr(34) & " face=" & Chr( 34 ) & "Arial" & Chr( 34 ) & _
			"><tr><td><BR><BR><b>Database Server: </b>" & strDatabaseServer & "<BR><b>Database Name: </b>EnterpriseVaultDirectory<BR>" & _
			"<b>SQL: </b><BR>SELECT RootIdentity, Failed, Offline, Rebuilding FROM IndexVolume" & _
			"<BR>WHERE failed <> 0 OR Offline <> 0 OR Rebuilding <> 0</td></tr>"

'******************************************************************************
If LineCount >= 1 then
	Set objMessage = CreateObject("CDO.Message") 
	objMessage.Subject = "Enterprise Vault -- User Indexes..."
	objMessage.From = "Your_From_Address"
	objMessage.To =  "Your_To_Address"
	objMessage.HTMLBody = Message & EmailFooter

	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Your_Email_Server"
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
	objMessage.Configuration.Fields.Update
	objMessage.Send
Else
	DayNo = WeekDay(Now)
	DayName = WeekDayName(DayNo,False)
	If Dayname = "Monday" then
		Message = "<font size=" & chr(34) & "2" & chr(34) & " face=" & Chr( 34 ) & "Arial" & Chr( 34 ) & _
		"><b>Enterprise Vault:</b> There are no faults on user's indexes.<font>" & "</font>"
		Set objMessage = CreateObject("CDO.Message") 
		objMessage.Subject = "Enterprise Vault -- No User Index Faults."
		objMessage.From = "Your_From_Address"
		objMessage.To =  "Your_To_Address"
		objMessage.HTMLBody = Message & EmailFooter

		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Your_Email_Server"
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
		objMessage.Configuration.Fields.Update
		objMessage.Send
	End If
End if
wscript.quit
'******************************************************************************