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
one of our database transaction log generate so much log files. so ideally just pipe out delivery to that one database activities
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,Cli entIp,Clie ntHostname ,ServerIp, ServerHost name,Sourc eContext,C onnectorId ,Source,Ev entId,Mess ageId,@{Na me='Recipi ents';Expr ession={[s tring]::jo in(";", ($_.Recipients))}}, @{Name='RecipientStatus';E xpression= {[string]: :join(";", ($_.RecipientStatus))}},To talBytes,R ecipientCo unt,Messag eSubject,S ender,Dire ctionality ,OriginalC lientIp,Me ssageInfo, MessageLat ency,Messa geLatencyT ype,@{Name ='EventDat a';Express ion={[stri ng]::join( ";", ($_.EventData))}}
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,Cli
Sadly You can't.
here's the full help, open the Exchange console (PowerShell) and run this:
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.
What you can do is
And the help of the command is here: https://docs.microsoft.com/en-us/sql/powershell/invoke-sqlcmd-cmdlet
here's the full help, open the Exchange console (PowerShell) and run this:
get-help Get-MessageTrackingLog -full
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"
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
And the help of the command is here: https://docs.microsoft.com/en-us/sql/powershell/invoke-sqlcmd-cmdlet
ASKER
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
$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
ASKER
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,ServerHostn ame, TotalBytes |sort timestamp –descending} | export-csv c:\temp\OUTrackLogs.csv
$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,ServerHostn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your help.. Much appreciated .. great respond time too..
ASKER
much appreciated
ASKER
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...tHelper Runspace:E xecutionCm dletHelper Runspace) [],
PSInvalidOperationExceptio n
+ FullyQualifiedErrorId : RemotePipelineExecutionFai led
-------------------------- ---------- -------
$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"
--------------------------
Pipeline not executed because a pipeline is already executing. Pipelines cannot be executed concurrently.
+ CategoryInfo : OperationStopped: (Microsoft.Power...tHelper
PSInvalidOperationExceptio
+ FullyQualifiedErrorId : RemotePipelineExecutionFai
--------------------------
$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"
ASKER
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...tHelper Runspace:E xecutionCm dletHelper Runspace) [],
PSInvalidOperationExceptio n
+ FullyQualifiedErrorId : RemotePipelineExecutionFai led
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;tim estamp; MessageSubject;ServerHostn ame;TotalB ytes |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,ServerHostn ame, TotalBytes | export-csv "c:\temp\here.csv"
error received
--------------------------
Pipeline not executed because a pipeline is already executing. Pipelines cannot be executed concurrently.
+ CategoryInfo : OperationStopped: (Microsoft.Power...tHelper
PSInvalidOperationExceptio
+ FullyQualifiedErrorId : RemotePipelineExecutionFai
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;tim
$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,ServerHostn
But the principle is the same, you need to get all the tracking that you need
Open in new window
then actually do the query to the database like:
Open in new window