Exchange 2010 MessageTrackingLog

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Ted Pettit Ted Pettit 2 years, 3 months ago.

  • Author
    Posts
  • #18377
    Profile photo of Ted Pettit
    Ted Pettit
    Participant

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

  • #18380
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    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

  • #18433
    Profile photo of Ted Pettit
    Ted Pettit
    Participant

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

  • #18446
    Profile photo of Eric Bronnert
    Eric Bronnert
    Participant

    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?

  • #18482
    Profile photo of Ted Pettit
    Ted Pettit
    Participant

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

You must be logged in to reply to this topic.