• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 195
  • Last Modified:

Get-MessageTrackingLog send from and send to a database

can someone help me how do I use  Get-MessageTrackingLog  to filter deliver or send to a database ?

one of our database transaction log generate so much log files.  so ideally just pipe out delivery to that one database activities
0
Jimmy Wang
Asked:
Jimmy Wang
  • 7
  • 3
1 Solution
 
Jose Gabriel Ortega CEE Solution Guide - CEO Faru Bonon ITCommented:
Basically, you would need to tell us what kind of database would you want to save it to (since the query are similar but different in MSSQL or MySQL)

But the principle is the same, you need to get all the tracking that you need

$tracking =Get-MessageTrackingLog -Server Mail -Start "07/01/2016 00:00:00" -End "07/22/2016 15:00:00" -Sender "sender@contoso.com" | where{ $_.Directionality  -eq "Incoming" -and $_.OriginalClientIp -ne ""}| select ComputerName,Timestamp,ClientIp,ClientHostname,ServerIp,ServerHostname,SourceContext,ConnectorId,Source,EventId,MessageId,@{Name='Recipients';Expression={[string]::join(";", ($_.Recipients))}}, @{Name='RecipientStatus';Expression={[string]::join(";", ($_.RecipientStatus))}},TotalBytes,RecipientCount,MessageSubject,Sender,Directionality,OriginalClientIp,MessageInfo,MessageLatency,MessageLatencyType,@{Name='EventData';Expression={[string]::join(";", ($_.EventData))}} 

Open in new window


then actually do the query to the database like:

Invoke-sqlcmd -name "name" -server "server" -query "bla bla bla".

Open in new window

0
 
Jimmy WangAuthor Commented:
can I specify the -database instead of -server ? i wanted to tracking any message going or coming into this database ( example db1 ) , so therefore, i do not want to specify -sender or -Recipient ..

can i just tracking on database name only ?

$tracking =Get-MessageTrackingLog -Server Mail -Start "07/01/2016 00:00:00" -End "07/22/2016 15:00:00" -Sender "sender@contoso.com" | where{ $_.Directionality  -eq "Incoming" -and $_.OriginalClientIp -ne ""}| select ComputerName,Timestamp,ClientIp,ClientHostname,ServerIp,ServerHostname,SourceContext,ConnectorId,Source,EventId,MessageId,@{Name='Recipients';Expression={[string]::join(";", ($_.Recipients))}}, @{Name='RecipientStatus';Expression={[string]::join(";", ($_.RecipientStatus))}},TotalBytes,RecipientCount,MessageSubject,Sender,Directionality,OriginalClientIp,MessageInfo,MessageLatency,MessageLatencyType,@{Name='EventData';Expression={[string]::join(";", ($_.EventData))}}
0
 
Jose Gabriel Ortega CEE Solution Guide - CEO Faru Bonon ITCommented:
Sadly You can't.

here's the full help, open the Exchange console (PowerShell) and run this:
get-help Get-MessageTrackingLog -full

Open in new window

you will get the full help of the cmdlet.

This is the example that you should get
  This example searches the message tracking logs on the Mailbox server named Mailbox01 for information about all
  messages sent from March 13, 2013, 09:00 to March 15, 2013, 17:00 by the sender john@contoso.com.


 
Get-MessageTrackingLog -Server Mailbox01 -Start "03/13/2013 09:00:00" -End "03/15/2013 17:00:00" -Sender
  "john@contoso.com"

Open in new window


What you can do is
$entries=@()
Get-mailbox -database "dbname" | foreach{
 $entries+= Get-MessageTrackingLog -Server Mailbox01 -Start "03/13/2013 09:00:00" -End "03/15/2013 17:00:00" -Sender $_
}

$entries

Open in new window



And the help of the command is here: https://docs.microsoft.com/en-us/sql/powershell/invoke-sqlcmd-cmdlet
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
Jimmy WangAuthor Commented:
I can use one liner command like this ?

$entries=@() Get-mailbox -database "dbname" | foreach{$entries+= Get-MessageTrackingLog -Server Mailbox01 -Start "03/13/2013 09:00:00" -End "03/15/2013 17:00:00" -Sender $_} $entries
0
 
Jimmy WangAuthor Commented:
here is the example.. is this ok ?

$entries=@() Get-mailbox -database "dbname" | foreach{$entries+= Get-MessageTrackingLog -Server Mailbox01 -Start "03/13/2013 09:00:00" -End "03/15/2013 17:00:00" -Sender $_} $entries | select-object {$_.Recipients},sender, timestamp, MessageSubject,ServerHostname, TotalBytes |sort timestamp –descending} | export-csv c:\temp\OUTrackLogs.csv
0
 
Jose Gabriel Ortega CEE Solution Guide - CEO Faru Bonon ITCommented:
Jimmy, Please use the code formatting
It's not meant to be oneliner if you want a oneliner (you need to separate commands with ";").
$entries=@(); Get-mailbox -database "dbname" | foreach{$entries+= Get-MessageTrackingLog -Server Mailbox01 -Start "03/13/2013 09:00:00" -End "03/15/2013 17:00:00" -Sender $_} |export-csv -notypeinformation "here.csv"

Open in new window

0
 
Jimmy WangAuthor Commented:
thanks for your help.. Much appreciated .. great respond time too..
0
 
Jimmy WangAuthor Commented:
much appreciated
0
 
Jimmy WangAuthor Commented:
I'm getting this error message..  can you let me know where is wrong with this pipe is complaining ? below is where the one liner script

-------------------------------------------
Pipeline not executed because a pipeline is already executing. Pipelines cannot be executed concurrently.
    + CategoryInfo          : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [],
   PSInvalidOperationException
    + FullyQualifiedErrorId : RemotePipelineExecutionFailed


-------------------------------------------

$entries=@(); Get-mailbox -database "DB01" | foreach{$entries+= Get-MessageTrackingLog -Server NYMBX01 -Start "12/5/2017 09:00:00" -End "12/06/2017 17:00:00" -Sender $_} |export-csv -notypeinformation "here.csv"
0
 
Jimmy WangAuthor Commented:
correction: this is the modify script I change it to..


error received
----------------------------------------------------------------------------------------------------------------------------------------------
Pipeline not executed because a pipeline is already executing. Pipelines cannot be executed concurrently.
    + CategoryInfo          : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [],
   PSInvalidOperationException
    + FullyQualifiedErrorId : RemotePipelineExecutionFailed

Pipeline not executed because a pipeline is already executing. Pipelines cannot be executed concurrently.

----------------------------------------------------------------------------------------------------------------------------------------------

$entries=@(); Get-mailbox -database "dbname" | foreach{$entries+= Get-MessageTrackingLog -Server NYMB01 -Start "12/5/2017 09:00:00" -End "12/06/2017 17:00:00" -Sender $_} |select-object {$_.Recipients};sender;timestamp; MessageSubject;ServerHostname;TotalBytes |export-csv -notypeinformation "c:\temp\here.csv"



$entries=@(); Get-mailbox -database "NADAG01-NADB05" | foreach{$entries+= Get-MessageTrackingLog -Server NYMB01 -Start "12/5/2017 09:00:00" -End "12/06/2017 17:00:00" -Sender $_} |select-object {$_.Recipients},sender, timestamp, MessageSubject,ServerHostname, TotalBytes | export-csv "c:\temp\here.csv"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now