Link to home
Start Free TrialLog in
Avatar of Jimmy Wang
Jimmy Wang

asked on

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
Avatar of J0rtIT
J0rtIT
Flag of Venezuela, Bolivarian Republic of image

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

Avatar of Jimmy Wang
Jimmy Wang

ASKER

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))}}
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
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
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
ASKER CERTIFIED SOLUTION
Avatar of J0rtIT
J0rtIT
Flag of Venezuela, Bolivarian Republic of image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for your help.. Much appreciated .. great respond time too..
much appreciated
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"
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"