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
Jimmy WangAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jose Gabriel Ortega CastroCEOCommented:
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 CastroCEOCommented:
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
Defend Against the Q2 Top Security Threats

Were you aware that overall malware worldwide was down a surprising 42% from Q1'18? Every quarter, the WatchGuard Threat Lab releases an Internet Security Report that analyzes the top threat trends impacting companies worldwide. Learn more by viewing our on-demand webinar today!

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 CastroCEOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.