Author Posts

August 29, 2014 at 7:36 am

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()

August 29, 2014 at 9:00 am

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

September 2, 2014 at 5:54 am

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

September 2, 2014 at 8:53 am

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?

September 3, 2014 at 7:46 am

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