Exchange 2010 MessageTrackingLog

PowerShell Help
1

I am trying to search the MessageTrackingLog on an exchange 2010 server, and insert the results into a database. The Get-MessageTrackingLog part works but I am not sure how to get the data into the table.

Any help would be greatly appreciated.

Open Connection

$conn = New-Object System.Data.SqlClient.SqlConnection
$connectionString = “Server=DATA-SERVER;Database=databasename;user=user;pwd=password;”
$conn.ConnectionString = $connectionString
$conn.Open()

Create the Command object to execute the queries

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]::Text

$nDate Timestamp

$nTime Time

$nIP clientip

$nEID EventId

$nRecip RecipientCount

$nSAddress sender

$nRecipAddress Recipents

$nMessSub MessageSubject

$totalb TotalBytes

#$nRecipAddress = ‘you4@att.com’
$nTime = Get-Date -Format “hhmmss”

|
$Test = Get-MessageTrackingLog -Start “07/21/2014 08:00:00” -End “07/21/2014 16:10:00” -resultsize unlimited |
select Timestamp,sender,clientip,RecipientCount,EventId,MessageSubject,TotalBytes,@{Name=”Recipents”;Expression={$.recipients}} |
Where-Object {$.clientip -eq “192.168.1.100” -AND $_.EventId -eq “Receive”} | Foreach {

$nDate = $Test | Group-Object -Property Timestamp | Select-Object name | sort

#$nTime = $Test | Group-Object -Property Timestamp | Select-Object name | sort

$nIP = $Test | Group-Object -Property clientip | Select-Object name | sort

$nEID = $Test | Group-Object -Property EventId | Select-Object name | sort

$nRecip = $Test | Group-Object -Property RecipientCount | Select-Object name | sort

$nSAddress = $Test | Group-Object -Property sender | Select-Object name | sort

$nRecipAddress = $Test | Group-Object -Property Recipents | Select-Object name | sort

$nMessSub = $Test | Group-Object -Property MessageSubject | Select-Object name | sort

$totalb = $Test | Group-Object -Property TotalBytes | Select-Object name | sort

}

$query = “INSERT INTO TrackingLog (Date, Time, [client-ip], [Event-ID], NoRecipients, [Sender-Address], [Recipient-Address], [Message-Subject], [total-bytes])
VALUES(‘$nDate’, $nTime, ‘$nIP’, ‘$nEID’, ‘$nRecip’, ‘$nSAddress’, ‘$nRecipAddress’, ‘$nMessSub’, '$totalb ')”

Uncomment next line to display query for checking

#$query

Setup Command

$cmd.CommandText = $query

Execute Command to insert data for this $drive

$cmd.executenonquery()

$conn.close()

2

At first glance your code looks OK - what errors are you getting. if you can capture the contents of $query try running it directly in SQL Server Management studio - you often get better error messages

3

I am not getting any errors, but the data is not going into the table

4

Taken from your code

Uncomment next line to display query for checking

#$query

I’m not sure if this is on purpose or not, but you have a #$query here. Is this supposed to just be $query?

5

#$query that is just for testing. $query line will just show the insert statement.