반응형
I. Get-MessageTrackingLog 기본형태
II. Read-Host Script
III. Windows Forms Script
I. Get-MessageTrackingLog 기본형태
기존에는 아래와 같은 형태로 Get-MessageTrackingLog 로 CSV 추출하여 분석하는 방식으로 진행하였습니다.
Get-TransportService | Get-MessageTrackinglog -Start (Get-Date).AddDays(-5) -ResultSize Unlimited |Select-Object Timestamp,ClientIp,ClientHostname,ServerIp,ServerHostname,SourceContext,ConnectorId,Source,EventId,InternalMessageId,MessageId,NetworkMessageId,@{Name="Recipients";Expression={$_.recipients}},@{Name="RecipientStatus";Expression={$_.recipientstatus}},TotalBytes,RecipientCount,RelatedRecipientAddress,@{Name="Reference";Expression={$_.Reference}},MessageSubject,Sender,ReturnPath,Directionality,TenantId,OriginalClientIp,MessageInfo,MessageLatency,MessageLatencyType,@{Name="EventData";Expression={$_.EventData}},TransportTrafficType | export-csv c:\csv\Tracking.csv -Encoding UTF8
II. Read-Host Script
조금은 쉽게 활용할 수 있게 ChatGPT의 도움을 받아서 Read-Host로 입력하는 방식으로 수정하였습니다.
#Load Exchange Powershell Module
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn
# Prompt user to enter search criteria
$sender = Read-Host "Enter sender's email address (press Enter to skip)"
$receiver = Read-Host "Enter receiver's email address (press Enter to skip)"
$sentDate = Read-Host "Enter sent date (format: mm/dd/yyyy) (press Enter to skip)"
$receivedDate = Read-Host "Enter received date (format: mm/dd/yyyy) (press Enter to skip)"
$subject = Read-Host "Enter subject (press Enter to skip)"
# Prompt user to enter CSV file path
$csvPath = Read-Host "Enter CSV file path and name"
# Construct search query based on input values
$searchQuery = "Get-MessageTrackingLog -ResultSize Unlimited"
if ($sender) {
$searchQuery += " -Sender '$sender'"
}
if ($receiver) {
$searchQuery += " -Recipients '$receiver'"
}
if ($sentDate) {
$searchQuery += " -Start '$sentDate'"
}
if ($receivedDate) {
$searchQuery += " -End '$receivedDate'"
}
if ($subject) {
$searchQuery += " -MessageSubject '$subject'"
}
# Select desired properties and export to CSV
$searchQuery += " | Select-Object Timestamp,MessageSubject,Sender,@{Name='Recipients';Expression={$_.recipients}},@{Name='RecipientStatus';Expression={$_.recipientstatus}},RecipientCount,RelatedRecipientAddress,ClientIp,ClientHostname,ServerIp,ServerHostname,SourceContext,ConnectorId,Source,EventId,InternalMessageId,MessageId,NetworkMessageId,TotalBytes,@{Name='Reference';Expression={$_.Reference}},ReturnPath,Directionality,TenantId,OriginalClientIp,MessageInfo,MessageLatency,MessageLatencyType,@{Name='EventData';Expression={$_.EventData}},TransportTrafficType | Export-Csv -Path $csvPath -Encoding UTF8 -NoTypeInformation"
# Run search query and export results to CSV
Invoke-Expression $searchQuery
스크립트를 실행하면 아래와 같은 형태로 진행됩니다.
III. Windows Forms Script
위 스크립트를 GPT에게 문의하여 Windows.Forms.Label, Windows.Forms.TextBox를 이용해서 Form 형태로 스크립트 작성을 요청한 뒤, 아래와 같이 수정하였습니다.
# Load Exchange PowerShell module
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn
# Create Windows Form
Add-Type -AssemblyName System.Windows.Forms
$form = New-Object System.Windows.Forms.Form
$form.Text = "Message Tracking Log Search"
$form.Width = 550
$form.Height = 350
# Add labels and text boxes for search criteria
$senderLabel = New-Object System.Windows.Forms.Label
$senderLabel.Location = New-Object System.Drawing.Point(10, 10)
$senderLabel.Size = New-Object System.Drawing.Size(200, 20)
$senderLabel.Text = "Enter sender's email address:"
$form.Controls.Add($senderLabel)
$senderBox = New-Object System.Windows.Forms.TextBox
$senderBox.Location = New-Object System.Drawing.Point(10, 30)
$senderBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($senderBox)
$receiverLabel = New-Object System.Windows.Forms.Label
$receiverLabel.Location = New-Object System.Drawing.Point(10, 60)
$receiverLabel.Size = New-Object System.Drawing.Size(200, 20)
$receiverLabel.Text = "Enter receiver's email address:"
$form.Controls.Add($receiverLabel)
$receiverBox = New-Object System.Windows.Forms.TextBox
$receiverBox.Location = New-Object System.Drawing.Point(10, 80)
$receiverBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($receiverBox)
$sentDateLabel = New-Object System.Windows.Forms.Label
$sentDateLabel.Location = New-Object System.Drawing.Point(10, 110)
$sentDateLabel.Size = New-Object System.Drawing.Size(200, 20)
$sentDateLabel.Text = "Enter sent date (mm/dd/yyyy):"
$form.Controls.Add($sentDateLabel)
$sentDateBox = New-Object System.Windows.Forms.TextBox
$sentDateBox.Location = New-Object System.Drawing.Point(10, 130)
$sentDateBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($sentDateBox)
$receivedDateLabel = New-Object System.Windows.Forms.Label
$receivedDateLabel.Location = New-Object System.Drawing.Point(10, 160)
$receivedDateLabel.Size = New-Object System.Drawing.Size(200, 20)
$receivedDateLabel.Text = "Enter received date (mm/dd/yyyy):"
$form.Controls.Add($receivedDateLabel)
$receivedDateBox = New-Object System.Windows.Forms.TextBox
$receivedDateBox.Location = New-Object System.Drawing.Point(10, 180)
$receivedDateBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($receivedDateBox)
$subjectLabel = New-Object System.Windows.Forms.Label
$subjectLabel.Location = New-Object System.Drawing.Point(10, 210)
$subjectLabel.Size = New-Object System.Drawing.Size(200, 20)
$subjectLabel.Text = "Enter subject:"
$form.Controls.Add($subjectLabel)
$subjectBox = New-Object System.Windows.Forms.TextBox
$subjectBox.Location = New-Object System.Drawing.Point(10, 230)
$subjectBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($subjectBox)
# Add button to initiate search
$searchButton = New-Object System.Windows.Forms.Button
$searchButton.Location = New-Object System.Drawing.Point(10, 260)
$searchButton.Size = New-Object System.Drawing.Size(75, 23)
$searchButton.Text = "Search"
$searchButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $searchButton
$form.Controls.Add($searchButton)
# Prompt user to enter CSV file path and name
$csvPathLabel = New-Object System.Windows.Forms.Label
$csvPathLabel.Location = New-Object System.Drawing.Point(220, 10)
$csvPathLabel.Size = New-Object System.Drawing.Size(200, 20)
$csvPathLabel.Text = "Enter CSV file path and name:"
$form.Controls.Add($csvPathLabel)
$csvPathBox = New-Object System.Windows.Forms.TextBox
$csvPathBox.Location = New-Object System.Drawing.Point(220, 30)
$csvPathBox.Size = New-Object System.Drawing.Size(250, 20)
$form.Controls.Add($csvPathBox)
# Show form and wait for user to click search button
$form.ShowDialog() | Out-Null
# Construct search query based on input values
$searchQuery = "Get-TransportService|Get-MessageTrackingLog -ResultSize Unlimited"
if ($senderBox.Text) {
$searchQuery += " -Sender '$($senderBox.Text)'"
}
if ($receiverBox.Text) {
$searchQuery += " -Recipients '$($receiverBox.Text)'"
}
if ($sentDateBox.Text) {
$searchQuery += " -Start '$($sentDateBox.Text)'"
}
if ($receivedDateBox.Text) {
$searchQuery += " -End '$($receivedDateBox.Text)'"
}
if ($subjectBox.Text) {
$searchQuery += " -MessageSubject '$($subjectBox.Text)'"
}
# Select desired properties and export to CSV
$searchQuery += " | Select-Object Timestamp,MessageSubject,Sender,@{Name='Recipients';Expression={$_.recipients}},@{Name='RecipientStatus';Expression={$_.recipientstatus}},RecipientCount,RelatedRecipientAddress,ClientIp,ClientHostname,ServerIp,ServerHostname,SourceContext,ConnectorId,Source,EventId,InternalMessageId,MessageId,NetworkMessageId,TotalBytes,@{Name='Reference';Expression={$_.Reference}},ReturnPath,Directionality,TenantId,OriginalClientIp,MessageInfo,MessageLatency,MessageLatencyType,@{Name='EventData';Expression={$_.EventData}},TransportTrafficType | Export-Csv -Path $($csvPathBox.Text) -Encoding UTF8 -NoTypeInformation"
# Run search query and export results to CSV
Invoke-Expression $searchQuery스크립트를 실행하면 아래와 같이 확인됩니다.
추가로 CSV 파일 경로를 지정하는 부분을 Windows.Forms.TextBox 가 아닌 SaveFileDialog로 수정요청 하였고, 이때
InitialDirectory를 C:\Ps1 경로로 지정하도록 수정 요청하여 아래와 같이 작성하였습니다.
# Load Exchange PowerShell module
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn
# Create Windows Form
Add-Type -AssemblyName System.Windows.Forms
$form = New-Object System.Windows.Forms.Form
$form.Text = "Message Tracking Log Search"
$form.Width = 550
$form.Height = 350
# Add labels and text boxes for search criteria
$senderLabel = New-Object System.Windows.Forms.Label
$senderLabel.Location = New-Object System.Drawing.Point(10, 10)
$senderLabel.Size = New-Object System.Drawing.Size(200, 20)
$senderLabel.Text = "Enter sender's email address:"
$form.Controls.Add($senderLabel)
$senderBox = New-Object System.Windows.Forms.TextBox
$senderBox.Location = New-Object System.Drawing.Point(10, 30)
$senderBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($senderBox)
$receiverLabel = New-Object System.Windows.Forms.Label
$receiverLabel.Location = New-Object System.Drawing.Point(10, 60)
$receiverLabel.Size = New-Object System.Drawing.Size(200, 20)
$receiverLabel.Text = "Enter receiver's email address:"
$form.Controls.Add($receiverLabel)
$receiverBox = New-Object System.Windows.Forms.TextBox
$receiverBox.Location = New-Object System.Drawing.Point(10, 80)
$receiverBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($receiverBox)
$sentDateLabel = New-Object System.Windows.Forms.Label
$sentDateLabel.Location = New-Object System.Drawing.Point(10, 110)
$sentDateLabel.Size = New-Object System.Drawing.Size(200, 20)
$sentDateLabel.Text = "Enter sent date (mm/dd/yyyy):"
$form.Controls.Add($sentDateLabel)
$sentDateBox = New-Object System.Windows.Forms.TextBox
$sentDateBox.Location = New-Object System.Drawing.Point(10, 130)
$sentDateBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($sentDateBox)
$receivedDateLabel = New-Object System.Windows.Forms.Label
$receivedDateLabel.Location = New-Object System.Drawing.Point(10, 160)
$receivedDateLabel.Size = New-Object System.Drawing.Size(200, 20)
$receivedDateLabel.Text = "Enter received date (mm/dd/yyyy):"
$form.Controls.Add($receivedDateLabel)
$receivedDateBox = New-Object System.Windows.Forms.TextBox
$receivedDateBox.Location = New-Object System.Drawing.Point(10, 180)
$receivedDateBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($receivedDateBox)
$subjectLabel = New-Object System.Windows.Forms.Label
$subjectLabel.Location = New-Object System.Drawing.Point(10, 210)
$subjectLabel.Size = New-Object System.Drawing.Size(200, 20)
$subjectLabel.Text = "Enter subject:"
$form.Controls.Add($subjectLabel)
$subjectBox = New-Object System.Windows.Forms.TextBox
$subjectBox.Location = New-Object System.Drawing.Point(10, 230)
$subjectBox.Size = New-Object System.Drawing.Size(200, 20)
$form.Controls.Add($subjectBox)
# Add button to initiate search
$searchButton = New-Object System.Windows.Forms.Button
$searchButton.Location = New-Object System.Drawing.Point(10, 260)
$searchButton.Size = New-Object System.Drawing.Size(75, 23)
$searchButton.Text = "Search"
$searchButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $searchButton
$form.Controls.Add($searchButton)
#Add button to select CSV file path and name
$csvPathButton = New-Object System.Windows.Forms.Button
$csvPathButton.Location = New-Object System.Drawing.Point(220, 60)
$csvPathButton.Size = New-Object System.Drawing.Size(120, 23)
$csvPathButton.Text = "Select CSV File"
$csvPathButton.Add_Click({
$saveFileDialog = New-Object System.Windows.Forms.SaveFileDialog
$saveFileDialog.InitialDirectory = "C:\Ps1"
$saveFileDialog.Filter = "CSV files (.csv)|.csv|All files (.)|."
$saveFileDialog.Title = "Select CSV file path and name"
$saveFileDialog.ShowDialog() | Out-Null
$csvPathBox.Text = $saveFileDialog.FileName
})
$form.Controls.Add($csvPathButton)
#Prompt user to enter CSV file path and name
$csvPathLabel = New-Object System.Windows.Forms.Label
$csvPathLabel.Location = New-Object System.Drawing.Point(220, 10)
$csvPathLabel.Size = New-Object System.Drawing.Size(200, 20)
$csvPathLabel.Text = "Selected CSV file path and name:"
$form.Controls.Add($csvPathLabel)
$csvPathBox = New-Object System.Windows.Forms.TextBox
$csvPathBox.Location = New-Object System.Drawing.Point(220, 30)
$csvPathBox.Size = New-Object System.Drawing.Size(250, 20)
$form.Controls.Add($csvPathBox)
#Show form and wait for user to click search button
$form.ShowDialog() | Out-Null
#Construct search query based on input values
$searchQuery = "Get-TransportService|Get-MessageTrackingLog -ResultSize Unlimited"
if ($senderBox.Text) {
$searchQuery += " -Sender '$($senderBox.Text)'"
}
if ($receiverBox.Text) {
$searchQuery += " -Recipients '$($receiverBox.Text)'"
}
if ($sentDateBox.Text) {
$searchQuery += " -Start '$($sentDateBox.Text)'"
}
if ($receivedDateBox.Text) {
$searchQuery += " -End '$($receivedDateBox.Text)'"
}
if ($subjectBox.Text) {
$searchQuery += " -MessageSubject '$($subjectBox.Text)'"
}
#Select desired properties and export to CSV
$searchQuery += " | Select-Object Timestamp,MessageSubject,Sender,@{Name='Recipients';Expression={$.recipients}},@{Name='RecipientStatus';Expression={$.recipientstatus}},RecipientCount,RelatedRecipientAddress,ClientIp,ClientHostname,ServerIp,ServerHostname,SourceContext,ConnectorId,Source,EventId,InternalMessageId,MessageId,NetworkMessageId,TotalBytes,@{Name='Reference';Expression={$.Reference}},ReturnPath,Directionality,TenantId,OriginalClientIp,MessageInfo,MessageLatency,MessageLatencyType,@{Name='EventData';Expression={$.EventData}},TransportTrafficType | Export-Csv -Path $($csvPathBox.Text) -Encoding UTF8 -NoTypeInformation"
#Run search query and export results to CSV
Invoke-Expression $searchQuery
#Show confirmation message box
[System.Windows.Forms.MessageBox]::Show("Search completed and results exported to $($csvPathBox.Text)", "Search Completed") | Out-Null
#Close the form
$form.Close() | Out-Null스크립트를 실행하면 다음과 같이 확인됩니다.
반응형
'Exchange' 카테고리의 다른 글
| Exchange Server 2019. Enabling Modern Auth in Exchange on-premises (0) | 2023.05.08 |
|---|---|
| Exchange Server. IIS Log 를 CSV 파일로 병합하기 (0) | 2023.04.06 |
| Exchange Online. Cross-tenant mailbox migration (0) | 2023.02.06 |
| Exchange Server. Password Expiration Notification (0) | 2023.01.31 |
| Exchange Online. Enhanced Filtering for Connectors (0) | 2022.12.27 |