#edit these vaules 
$FromSender = "sender@yourdomain.com" #enter sender address
$ToRecipients = "recipient1@yourdomain.com","recipient2@yourdomain.com" #enter recipient addresses
$SMTPServer = "Your SMTP Server Name" #enter you SMTP Server Name
$Database = @('YourVaultStore1','YourVaultStore2','YourVaultStore3') #enter the Vault Store Database names you wish to run the report against
$ServerInstance = "SQLServerName" #Server hosting your Vault Store Databases

#no change required below
$SubjectDate = (Get-Date).adddays(-1).ToString("dd-MM-yyyy")
$Header = "<style> TABLE {font-size: 10pt;font-family: Arial;border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;} TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #D3D3D3;} TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black}</style>"
$ReportDate = (Get-Date).adddays(-1).ToString("dd-MM-yyyy-hh-mm-ss")
$Database | foreach-object {$AttachmentPath = "C:\Temp\$_.csv";$Database =$_;$ConnectionTimeout = 300; $SQLQueryEV = "SELECT Archive.ArchiveName,COUNT(*)AS 'Number of items',MIN(RecordCreationDate) AS 'Oldest Item Date',MAX(RecordCreationDate) AS 'Youngest Item Date',MIN(IndexSeqNo) AS 'Lowest Sequence Number',MAX(IndexSeqNo) AS 'Higest Sequence Number',IV.FailedVolumeReason,CASE WHEN IV.FailedVolumeReason = 0 THEN 'The Index Volume is Not Failed' WHEN IV.FailedVolumeReason = 1 THEN 'The Index Volume Cannot be Found' WHEN IV.FailedVolumeReason = 2 THEN 'There are too many Consecutive Items that have failed' WHEN IV.FailedVolumeReason = 3 THEN 'There is a sequence mismatch between the Index and the Storage' WHEN IV.FailedVolumeReason = 4 THEN 'The Index Volume has been partially Deleted' WHEN IV.FailedVolumeReason = 5 THEN 'The dataset file is missing' WHEN IV.FailedVolumeReason = 6 THEN 'The dataset file is empty'   WHEN IV.FailedVolumeReason = 7 THEN 'The dataset file is invalid' WHEN IV.FailedVolumeReason = 8 THEN 'Unable to open the Index'   WHEN IV.FailedVolumeReason = 9 THEN 'The checksumfile is invalid' WHEN IV.FailedVolumeReason = 10 THEN 'An error occurred while saving the index data to disk'   WHEN IV.FailedVolumeReason = 11 THEN 'An error occurred while saving dynamic atribute indexing data to disk' WHEN IV.FailedVolumeReason = 12 THEN 'The index compaction has failed' WHEN IV.FailedVolumeReason = 13 THEN 'There has been an indexing engine error' WHEN IV.FailedVolumeReason = 14 THEN 'Failed to Audit items'   WHEN IV.FailedVolumeReason = 15 THEN 'There are duplicate items in the index' WHEN IV.FailedVolumeReason = 16 THEN 'The State file is missing from the Index' WHEN IV.FailedVolumeReason = 17 THEN 'There was an error accessing or updating the index engine metadata'   ELSE 'Unknown Reason' END AS 'Failure Reason' FROM Journalarchive, EnterpriseVaultDirectory.dbo.Archive Archive JOIN EnterpriseVaultDirectory.dbo.Root RT ON Archive.RootIdentity = RT.RootIdentity JOIN EnterpriseVaultDirectory.dbo.IndexVolume IV ON IV.RootIdentity = RT.RootIdentity JOIN ArchivePoint ON RT.VaultEntryId = ArchivePoint.ArchivePointId WHERE IndexCommited = 0 AND ArchivePoint.ArchivePointIdentity = JournalArchive.ArchivePointIdentity GROUP BY Archive.ArchiveName, IV.FailedVolumeReason order by 'number of items' desc"; $QueryTimeout = 600; $conn=new-object System.Data.SqlClient.SQLConnection; $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout; $conn.ConnectionString=$ConnectionString; $conn.Open(); $cmd=new-object system.Data.SqlClient.SqlCommand($SQLQueryEV,$conn); $cmd.CommandTimeout=$QueryTimeout; $ds=New-Object system.Data.DataSet; $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd); $da.fill($ds) > $null | out-null; $conn.Close(); $ds.Tables[0] | export-csv $AttachmentPath -Notypeinformation}

#insert database name into csv file
Get-ChildItem C:\Temp\*.csv | ForEach-Object {$CSV = Import-CSV -Path $_.FullName;$filename = $_.name.trim(".csv");$CSV | Select-Object *,@{N='Database';E={$FileName}} | Export-CSV $_.FullName -NTI}

#merge multiple csv files
Get-ChildItem C:\Temp\*.csv | ForEach-Object {Import-Csv $_} | Export-Csv -NoTypeInformation C:\Temp\TempMergedCSVFile.csv

#convertcsvtohtml
Import-Csv C:\Temp\TempMergedCSVFile.csv | convertto-html -Head $Header | out-file C:\Temp\IndexPending.html -append

#createhtml
$body = "";$body = "<br><br>"
$body = $body + (get-content C:\Temp\IndexPending.html | Out-String)

#sendmail
Send-MailMessage -From $FromSender -To $ToRecipients -Subject "EV Index Pending Statistics $SubjectDate" -SMTPServer $SMTPServer -bodyashtml -body $body   

#cleanup temp files
Rename-Item C:\Temp\TempMergedCSVFile.csv IndexPending_$ReportDate.csv
DEL C:\Temp\IndexPending.html
DEL C:\Temp\*.csv