PowerShell, Reporting and Notifications

PowerShell and Reporting. Microsoft has provided Exchange admins / engineers with a feature rich scripting language that allows for some great reporting. However, one off reports and spur of the moment reports are one thing, but an active monitoring system is another. How can these reports be made useful to the average engineer and how can information be disseminated in a useful manner?

Security
Scripts created for reporting should also be secured in order to prevent manipulation. Clients I’ve consulted with have either resorted to using file based security or a third party product like Script Repository. The advantage of a repository is that it can be centrally managed and manipulated as well as controlled. This enables a change control process to be put into to place which can facilitate better troubleshooting in case a script does something unexpected. Also, a complete Dev, QA and production work flow can be implemented.

With NTFS permissions, you can restrict who has access to the file. These permissions should be limited to the accounts that need to run and/or edit the script. This will ensure better file integrity for your script. Other remote connections like Remote Desktop and File Shares should also be restricted when creating a file based script repository.

Email Notifications
One of the simplest forms of information dissemination is an email. Reports through email can be sent on a daily, weekly, monthly or yearly basis. With the proper PowerShell script an email reporting on mailbox quota limits, ActiveSync devices connected to Exchange and free space of the database and log volumes.

A sample of how to send an email notification with PowerShell is given below:

#
#	Send Email
#	-----------------
$a = get-date
$month= $a.month
$day= $a.day
$year= $a.year
$subject = "Report: "+"$month"+"-"+"$day"+"-"+"$year"
$body = get-content "D:\PowerShell\Scripts\Report-Weekly.html" | out-string
$filename1 = "D:\PowerShell\Scripts\Report-Weekly.xls"
send-mailmessage -to <SMTP address 1> -from <reporting email address> -subject $subject -bodyashtml -body $body -smtpserver <smtp FQDN> -attachments $filename1

Basically we can use this script to specify recipients, subjects, body content, attachments, and mail server. This same block could be used to schedule a weekly report to your IT team about disk space, mailbox usage, quotas etc. Key not here is that the body is actually the contents of an HTML file generated as part of another process that runs prior to the email going out. This allows us to create professional looking emails.

Along the same lines of HTML formatted emails, PowerShell provides a method for converting your CSV files into a nice HTML report:

#	Convert the CSV file to HTML
#	__________________________________________
$a = "<style>"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:black;color:white;font-weight:bold;font-family:Arial}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;font-family:Minion}"
$a = $a + "<align='center'>"
$a = $a + "</style>"
import-csv "D:\PowerShell\Scripts\ArchiveReport.csv" |convertto-html -head $a | set-content "D:\PowerShell\Scripts\ArchiveReport.html"

Once converted, the $body variable can be populated with this new file name to be placed in the email.

Event Log Logging
With the above example we’ve sent a report to our IT Support on a scheduled basis. Another way to log or notify that there is an issue is to use the Event Logs.

The PowerShell command for this notification method is new-eventlog. This can be used to log a certain message which can be later retrieved with PowerShell or some monitoring software.

An advantage of this method is these events can then be consolidated with Event Subscriptions or reported on via SCOM or with another PowerShell script.

Samples of how to do this can be found HERE.


Further Reading
Send-MailMessage
Event Log writing with PowerShell