Powershell: Speaking with Exhange 2007 and SQL Server

Recently I completed a script which picks up message tracking logs from Exchange 2007 and dumps them into a SQL Server. We are creating Analytical reports from the SQL Server e.g. Top Senders/Receivers, monitoring tools and the number of emails sent by them etc. Normally this process of dumping message tracking logs used to take us around 30-60 manual minutes per week. Now it takes around 5 minutes. And if in the future, as planned, I am able to put the script in a task scheduler I would be able to further reduce it to 0 minutes.

Anyhoo, what I wanted to brag, sorry blog, about was how is this magical feat even possible. The key over here is PowerShell. The more I work on Powershell the more I am impressed with it. Most attractive is the ability of Powershell to easily talk with different products. I won't copy/paste the entire script over here, however I will explain the key commands which makes it possible.

First Step: Speak with Exchange 2007

1. Add the MS Exchange Powershell Snapin

Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin

The command adds the Powershell snapin into your scripting interface. Enabling you to run exchange powershell commands. This allows us to run the Exchange commands on our scripting server, rather than directly on the exchange server. 

2. Run the Exchange Tracking Logs

get-MessageTrackingLog -server "" -recipients "" -ResultSize "Unlimited" -Start $StartDateTime -END $EndDateTime | select-object timestamp, serverhostname, MessageSubject, Sender,@{Name=”Recipients”;Expression={$_.recipients}}, TotalBytes, EventID, @{Name=”RecipientStatus”;Expression={$_.recipientstatus}}, recipientcount, internalmessageid, messageid 

Let me explain the different options, so that you may add/remove any option as per your requirement. 

Get-MessageTrackingLog: The command which extracts Message tracking logs from the exchange servers. 

Server: The name of the Hub Server, from which you want to read the logs from. 

Recipients: The recipient e-mail address if you want to restrict the reading of logs for only a particular recipient. 

sender: The sender e-mail address if you want to restrict the reading of logs for only a particular sender. 

resultsize: By default powershell puts a limit on the number of rows that are returned. Putting -resultsize Unlimited, won't place any such restrictions. 

3.  Connecting to SQL 

$connection = New-Object System.Data.SqlClient.SqlConnection

Declares a variable to define the connection string which contains the details of how to connect to the SQL server. 

$connection.ConnectionString = “Server=$dataSource;Database=$database;Integrated Security=True;”

$dataSource, should contain the name of the SQL Server with the instance. 

$database, should contain the name of the database. 

Integrated Security = True, Uses the Windows Authentication. So basically the person running the script, his Windows login id and password will be used. 

$connection.Open()

Opens the connection using the details as defined in $connection.ConnectionString

 

4. Create the SQL Command

    $command = $connection.CreateCommand()

Declares the variable to store the SQL command which will be run against the connection created above.

    $command.CommandText = $SQLquery

$SQLQuery, would be containing the Insert query, which will enter the data into the database. It might look something like below. 

It will be good to include this command in a ForEach block. There are LOCs which can do a bulk insertion as well, but I prefer this since (a) I don't have too many rows (b) I can know the reason when there is an error (c) I can transform the data (d) I can put a progress bar to show the status of the operation.

    $SQLQuery="INSERT INTO AllRCP VALUES ('" + $object.Timestamp + "', '" + $object.ServerHostname + "', '" + $object.MessageSubject.Replace("'", "''") + "', '" + $object.Sender + "', '" + $object.Recipients + "', '" + $object.TotalBytes + "', '" + $object.EventID + "', '" + $object.RecipientStatus + "', '" + $object.RecipientCount + "', '" + $object.InternalMessageID + "', '" + $object.MessageID + "')"

5. Run the SQL Command

        $Result=$command.ExecuteNonQuery() 

$Result, will contain the result/action of the command. It will be good to include the above statement in the Try-Catch-Finally block. 

6. Close the connection

$connection.close