Reviewing Spotlight Alerts

Quest’s Spotlight on SQL Server is a tool for monitoring SQL Server and Windows Servers. One of the features is the automatic sending of emails whenever various alert conditions are met and sending them to a distribution group or just me. Some days I receive hundreds of them and put them all in one folder with the intention of looking through and finding patterns. Of course, I don’t get around to doing this so I wrote this Powershell script to help;

Powershell script to visit a folder in Outlook, parse out the relevant columns and write it to a CSV file.

#Set up the connection to Outlook

$olFolderInbox = 6

$outlook = new-object-com outlook.application;

$ns = $outlook.GetNameSpace("MAPI");

$inbox = $ns.GetDefaultFolder($olFolderInbox)

#Set the path to the Spotlight Folder

$msgs = $inbox.Folders.Item("Automatic Messages").Folders.Item("From Me").Folders.Item("Spotlight")

$counter = 1

$items = $msgs.items

#Go through each email and process based on the rules

foreach ($item in $items) {
$status = "Processing item {0} of {1}" -f $counter,$items.Count

Write-Progress "Processing items" $status -PercentComplete ($counter / $items.Count * 100)
$b = $item.Body

#These two lines remove the new line carriage return

$b = $b.Replace('

',',')

#Tidy up the empty columns

$b = $b.Replace(', ',',')

$b = $b.Replace(',,',',')

$b = $b.Replace(',,,',',')

$b = $b.Replace(',,',',')

#These will contain the events

$b = $b.Replace('Connection: ','')

$b = $b.Replace('Time: ','')

$b = $b.Replace('Severity: ','')

#The emails come from my account so I need to remove my signature from the result

$b = $b.Replace(',<My Signature from outlook>,','')

$b | Out-File "C:\PS\spotlight emails.csv" -Append

$counter++

}

The CSV can then be altered in Excel using Data > Text to Columns and setting a comma as the delimiter. The results can appended to an existing Excel log of processed emails for long term pattern analysis.