概要
この記事で使用できるスクリプトは、「 CVE-2020-1472 に関連付けられている Netlogon セキュリティで保護されたチャネル接続の変更を管理する方法」の情報に関連しています。 そのまま提供されます。
スクリプトは、イベント ビューアーからエクスポートされた EVTX ファイルを処理し、さまざまな問題と、イベントをトリガーした環境内のデバイスのピボット テーブルを含む Microsoft Excel スプレッドシートを作成します。 このスクリプトには、PowerShell と Microsoft Excel 2013 以降が必要です (大規模なデータ セットには 64 ビットをお勧めします)。
スクリプトの最終更新日: 2020 年 9 月 27 日。
用途
KB4557222で説明されている問題を監視しているドメイン コントローラー (DC) から EVTX ファイルをエクスポート する必要があります。 これを行うには、[スタート] を選択してイベント ビューアーを開き、「イベント ビューアー」と入力して選択します。 イベント ビューアー開いたら、[Windows ログ] を展開し、[システム] を右クリックまたは長押しし、[すべてのイベントを名前を付けて保存] を選択し、ファイルをどこかに保存してファイルの処理を許可します。
必要に応じて、スクリプトで一度に複数の EVTX ファイルを処理できます。 このスクリプトは、大規模なデータ セットで完了するまでに長い時間がかかる場合があります。 また、ファイルの処理を高速化するために、スクリプトを実行しているのと同じデバイス上に EVTX files をローカルにすることをお勧めします。
PowerShell コマンド プロンプトでスクリプトを実行する必要があります。 管理者として実行する必要はありません。 スクリプトは、 $mcScriptPrompts を $false に設定しない限り、必要に応じて応答できるプロンプトを実行します。
EVTX ファイルがスクリプトと同じフォルダーにある場合は、ファイルへのパスを指定する必要はありません。
スクリプトのカスタマイズ
| 変数 | 説明 |
|---|---|
| $mcScriptPrompts | 値: $true、 $false説明: 既定値は $true であり、ユーザーからの入力/操作が必要です。
EVTXファイルがスクリプトと同じフォルダーにあり、その他のすべての設定が既定で実行される場合は、$falseに設定できます。 |
| $mcMaxExport | 既定値: 50000説明: 既定の設定では、スクリプトは 5,0000 イベントを CSVfiles にエクスポートした後、各EVTX ファイルの処理を停止します。 Microsoft Excel からメモリに関連するエラーを受け取る場合は、この数を減らすか、 $mcStartTimeの変更など、処理中のデータを調整する必要があります。 |
| $mcMaxImport | 既定値: 50000説明: 既定の設定では、スクリプトは、現在の CSV ファイルが処理されているイベントの最大数 50000 に達すると、イベントの処理を停止します。 つまり、イベントの最大数に達すると、起動した現在の CSV ファイルが完了しますが、新しい CSV ファイルの処理は開始されません。 |
| $mcStartTime | 既定値: '2020/05/26 11:06'説明: この変数を使用すると、開始時刻を設定でき、スクリプトはその日時以降のイベントのみを処理します。 特定のポイントまでデバイスに既にアドレス指定している場合、これにより、まだアドレス指定していないイベントのみをターゲットにできます。 |
| $mcMaxThreads | 既定値: 4説明: この変数を使用すると、 EVXT ファイルから CSV ファイルにイベントをインポートするときに使用されるスレッドの数を増減できます。 |
スクリプト
環境に合わせてカスタマイズした後に使用できる PowerShell スクリプトを次に示します。 このスクリプトをコピーして、イベントの処理に使用しているデバイス上の CVE-2020-1472EventReader.ps1 ファイルに貼り付ける必要があります。
#
# CVE-2020-1472 Event Reader v1.1 9/27/2020
# This script will:
# 1. Scan system evtx in input file folder for event 5827, 5828,5829,5830 and 5831, exact data fields, export to 582#-*.CSV.
# 2. Calls Excel to import resulting 582#-*.CSV, create pivot tables for common secure RPC analysis scenarios. Delete 582#-*.CSV afterward.
# Feel free to modify to fit your need.
#
# Script tries best effort match to help admin, feel free to modify.
#
# Script requires Excel 2013 or later.
# 64 bits Excel will allow creation of larger data import to XLS.
#
# To use the script:
# 1. Save system log as system.evtx to file.
# 2. Follow on screen prompt to enter Path containing *.evtx and final xlsx.
#
# More info https://support.microsoft.com/en-us/kb/4557222
# Change log > 1.1
# Fixed error level
# Switch Get-WinEvent over to FilterXPath for faster search
# Added script knob for LookbackDays for schduled review of progress in script
# Added %GrandTotal in Excel Pivot to help setting priority.
# Added script knobs for enable/disable ColorScale and DataBar
# Added $mcLookbackDays, $mcScriptPrompts, $mcServerEvents and $mcEvtPath for remote deployment to send data back to central share.
#
# Script variables block, modify to fit your need
$mcStartTime = '2020/05/10 06:06' # TimeStamp to start searching for 5827-5831, in the form of 'yyyy/MM/dd HH:mm', where 1pm is 13, example: '2020/05/26 13:06' will list events starting 2020/05/26 1:06 PM, use it to measure progress.
$mcLookbackDays = 0 # When set to 0, script will use $mcStartTime. When set to a positive number, script will start search events in last x days. For examle: 1 will search events in last 24 hours.
$mcScriptPrompts = $true # Script default, $false for non-interactive mode, intended for schduled jobs or right click, run with Powershell.
$mcCSVImport = $true # Set to $false for generating Evtx only, intended for saving Evtx from server's event service.
$mcServerEvents = $false # Generate related events from Local Server (LocalHost), intented for generating smaller EVTX & CSV for later processing (on another machine) instead of entire EVTX (when use in combo with $mcCSVImport)
$mcEvtPath = "" # Default report path when $mcScriptPrompts=false, set to "" for script path.
#Performace related knobs
$mcMaxThreads = 4 # Max concurrent Evtx to CSV export threads (jobs), hight number might hit File/IO bottleneck since all files are in one directory.
$mcMaxExport = 1000 # 1000 Max events to export per each EVTX, modify this if we are getting Excel memory error (or we just need first few evt for samples)
$mcMaxImport = 1000 # 1000 max events to import into excel, ##NOTE##: Once maxed out, Excel will finish current import file and stop future import(s), since import is single threaded, import might take a long time if set to high number.
$mcColorScale = $true # Set to false to disable column ColorScale = speed up excel import & reduce memory requirement
$mcDataBar = $true # Set to false to disable column DataBar = speed up excel import & reduce memory requirement (Note: DataBar is somewhat buggy compare to ColorScale on import, best to do it manually afterward if needed.)
#--Default for remote Evtx collection-------------------------------------------------------
# $mcLookbackDays = 0
# $mcEvtPath = ""
# $mcScriptPrompts = $mcCSVImport = $false
# $mcServerEvents = $true
#--Default for local non-inteactive report generation.--------------------------------------
# $mcLookbackDays = 0
# $mcEvtPath = ""
# $mcScriptPrompts = $mcServerEvents = $false
# $mcCSVImport = $true
#----Functions--------------------------
Function mcSetPivotField($mcPivotFieldSetting) { #Set pivot field attributes per MSDN https://msdn.microsoft.com/en-us/library/office/ff820762.aspx
if ($null -ne $mcPivotFieldSetting[1]) { $mcPivotFieldSetting[0].Orientation = $mcPivotFieldSetting[1]} # 1 Orientation { $xlRowField | $xlDataField |$xlColumnField }, in XlPivotFieldOrientation
if ($null -ne $mcPivotFieldSetting[2]) { $mcPivotFieldSetting[0].NumberFormat = $mcPivotFieldSetting[2]} # 2 NumberFormat { $mcNumberF | $mcPercentF }
if ($null -ne $mcPivotFieldSetting[3]) { $mcPivotFieldSetting[0].Function = $mcPivotFieldSetting[3]} # 3 Function { $xlAverage | $xlSum | $xlCount }, in XlConsolidationFunction
if ($null -ne $mcPivotFieldSetting[4]) { $mcPivotFieldSetting[0].Calculation = $mcPivotFieldSetting[4]} # 4 Calculation { $xlPercentOfTotal | $xlPercentRunningTotal }, in XlPivotFieldCalculation
if ($null -ne $mcPivotFieldSetting[5]) { $mcPivotFieldSetting[0].BaseField = $mcPivotFieldSetting[5]} # 5 BaseField <String>
if ($null -ne $mcPivotFieldSetting[6]) { $mcPivotFieldSetting[0].Name = $mcPivotFieldSetting[6]} # 6 Name <String>
if ($null -ne $mcPivotFieldSetting[7]) { $mcPivotFieldSetting[0].Position = $mcPivotFieldSetting[7]} # 7 Position
}
Function mcSetPivotTableFormat($mcPivotTable) { # Set pivotTable cosmetics and sheet name
$mcPT=$mcPivotTable[0].PivotTables($mcPivotTable[1])
$mcPT.HasAutoFormat = $False #2.turn of AutoColumnWidth
for ($i=2; $i -lt 9; $i++) { #3. SetColumnWidth for Sheet($mcPivotTable[0]),PivotTable($mcPivotTable[1]),Column($mcPivotTable[2-8])
if ($null -ne $mcPivotTable[$i]) { $mcPivotTable[0].columns.item(($i-1)).columnWidth = $mcPivotTable[$i]}
}
$mcPivotTable[0].Application.ActiveWindow.SplitRow = 3
$mcPivotTable[0].Application.ActiveWindow.SplitColumn = 2
$mcPivotTable[0].Application.ActiveWindow.FreezePanes = $true # Freeze first 2 columns
$mcPivotTable[0].Cells.Item(1,1)="Filter"
$mcPivotTable[0].Cells.Item(3,1)=$mcPivotTable[9] # $mcPivotTable[9] = Table Name @ R3C1
$mcPivotTable[0].Name=$mcPivotTable[10] # $mcPivotTable[10] = Sheet Name
$mcRC = ($mcPivotTable[0].UsedRange.Cells).Rows.Count-1
if (($null -ne $mcPivotTable[11]) -and ($mcColorScale -eq $true)) { # $mcPivotTable[11] = ColorScale column
$mColorScaleRange='$'+$mcPivotTable[11]+'$4:$'+$mcPivotTable[11]+'$'+$mcRC
[Void]$mcPivotTable[0].Range($mColorScaleRange).FormatConditions.AddColorScale(3)
$mcPivotTable[0].Range($mColorScaleRange).FormatConditions.item(1).ColorScaleCriteria.item(1).type = 1 #xlConditionValueLowestValue
$mcPivotTable[0].Range($mColorScaleRange).FormatConditions.item(1).ColorScaleCriteria.item(1).FormatColor.Color = 8109667
$mcPivotTable[0].Range($mColorScaleRange).FormatConditions.item(1).ColorScaleCriteria.item(2).FormatColor.Color = 8711167
$mcPivotTable[0].Range($mColorScaleRange).FormatConditions.item(1).ColorScaleCriteria.item(3).type = 2 #xlConditionValueHighestValue
$mcPivotTable[0].Range($mColorScaleRange).FormatConditions.item(1).ColorScaleCriteria.item(3).FormatColor.Color = 7039480
}
if (($null -ne $mcPivotTable[12]) -and ($mcDataBar -eq $true)) { # $mcPivotTable[12] = DataBar column
$mcDataBarRange='$'+$mcPivotTable[12]+'$4:$'+$mcPivotTable[12]+'$'+$mcRC
[void]$mcPivotTable[0].Range($mcDataBarRange).FormatConditions.AddDatabar()
}
}
Function mcSortPivotFields($mcPF) { #Sort on $mcPF and Expand later pivot fields
for ($i=2; $i -lt 5; $i++) { #Expand pivot fields
if ($null -ne $mcPF[$i]) {
$mcPF[$i].showDetail = $true
}
}
[void]($mcPF[0].Cells.Item(4, 2)).sort(($mcPF[0].Cells.Item(4, 2)), 2) # Decending counts
[void]($mcPF[0].Cells.Item(4, 1)).sort(($mcPF[0].Cells.Item(4, 1)), 1) # Accending alpha
}
Function mcSetPivotTableHeaderColor($mcSheet) { #Set PiviotTable Header Color for easier reading
for ($i=1; $i -lt 5; $i++) { #Set header(s) color
if ($null -ne $mcSheet[$i]) { $mcSheet[0].Range(($mcSheet[$i]+"3")).interior.Colorindex = 37 }
}
}
#---- Main ------------------------------------------------------------------------------------------------------------
$mcProgressBarDelay = 0 # Delay in seconds after each ProgressBar update
#---- init prompts-------
$mcScriptPath = Split-Path ((Get-Variable MyInvocation -Scope 0).Value).MyCommand.Path
if ([string]::IsNullOrWhiteSpace($mcEvtPath)) {
$mcEvtPath = $mcScriptPath
}
if ($mcScriptPrompts) { #Interactive mcScriptPrompts
$mcPromptedServerEvent = Read-Host "Read system log from [D]isk or [S]erver's event service? For example, 'D' to read from disk. `n Or press [Enter] to read events from Disk (*.evtx).`n"
if ([string]::IsNullOrWhiteSpace($mcPromptedServerEvent) -or ($mcPromptedServerEvent -eq 'D')) {
$mcServerEvents = $false
} else {
$mcServerEvents = $true
}
$mcPromptedEvtPath = Read-Host "Enter local, mapped or UNC path to saved system evtxs or previously generated 582-*.csv. Be sure to remove trailing blank. For Example (c:\EventData)`n Or press [Enter] to use script path for data files.`n"
if (-not [string]::IsNullOrWhiteSpace($mcPromptedEvtPath)) {
$mcEvtPath= $mcPromptedEvtPath
}
$mcPromptedLookbackDays = Read-Host "How many days in recent events we want to report? For example [2] for last 48 hours.`n or press [Enter] for all past events.`n"
if (-not [string]::IsNullOrWhiteSpace($mcPromptedLookbackDays)) {
$mcLookbackDays= $mcPromptedLookbackDays
}
}
if ($mcLookbackDays -gt 0) { $mcStartTime=(get-date).AddDays(0-$mcLookbackDays)}
#Echo script parameters
if ($mcServerEvents) { Write-Host 'Reading events 5827-5831 from Server:'$env:computername -ForegroundColor Blue }
else { Write-Host 'Reading events 5827-5831 from disk.' -ForegroundColor Green }
Write-Host ' Using folder:'$mcEvtPath 'for events starting:'$mcStartTime 'under UserAccount:'$Env:UserDomain'\'$Env:UserName -ForegroundColor Cyan
# Read-Host 'Ready?'
#---- Write evtx from Server's event service to disk ---------------------------------------------------
if ($mcServerEvents) {
$mUStartTime = ([datetime]$mcStartTime).ToUniversalTime().ToString("s")
$mToday=Get-Date
$mcEvtFileName=$mcEvtPath+'\'+'582#-'+[string]$mToday.Year+'-'+([string]$mToday.Month).PadLeft(2,'0')+'-'+([string]$mToday.Day).PadLeft(2,'0')+' Netlogon RPC events - '+$env:computername+'.evtx'
Remove-Item $mcEvtFileName -ErrorAction SilentlyContinue # remove same day report.
wevtutil epl System $mcEvtFileName /q:"Event[ System[ (Level=2 or Level=3) and (EventID=5827 or EventID=5828 or EventID=5829 or EventID=5830 or EventID=5831 or EventID=6005) and TimeCreated[@SystemTime>='$mUStartTime'] ] ]" #Note added 6005 to test time range.
}
#---- Export evtx to CSV---------------------------------------------------
$mFiles = Get-ChildItem -Path $mcEvtPath | Where-Object {$_.name -clike '*.evtx'}
if (-not $mcCSVImport) { $mFiles = $null } #Skip generation of CSV if excel import is not needed.
ForEach ($mFile in $mFiles) { #Loop through *.evtx
$mFile | Add-Member -MemberType NoteProperty -Name MaxEvents -force -Value $mcMaxExport
$mFile | Add-Member -MemberType NoteProperty -Name StartTime -force -Value $mcStartTime
########### Job [ Job start code 1/3 ###########
Start-Job -ArgumentList (,$mFile) -ScriptBlock { #Start-Job for reading each files
param ($mFile)
########### Job ] ###########
Write-Host "Reading $mFile"
$mUStartTime = ([datetime]$mFile.StartTime).ToUniversalTime().ToString("s")
$mc582s = Get-WinEvent -Path $mFile.FullName -FilterXPath "Event[ System[ (Level=2 or Level=3) and (EventID=5827 or EventID=5828 or EventID=5829 or EventID=5830 or EventID=5831) and TimeCreated[@SystemTime>='$mUStartTime'] ] ]" -MaxEvents $mfile.MaxEvents -ErrorAction SilentlyContinue
If ($null -ne $mc582s) {
$mcOutFile = $mFile.DirectoryName+'\582-'+$mFile.Name+'.csv'
Write-host ' Event 5827~5831, found, generating '$mcOutFile -ForegroundColor Green
$mc582sOut = @() #Array for exporting CSV items
ForEach ($mc582 in $mc582s) {
$mObject = New-Object System.Object
$mObject | Add-Member -MemberType NoteProperty -Name EventSource -force -value $mc582.MachineName
$mObject | Add-Member -MemberType NoteProperty -Name EventTime -force -value $mc582.TimeCreated
$mObject | Add-Member -MemberType NoteProperty -Name EventID -force -value $mc582.ID
switch ($mc582.ID) {
5827 { #Denided Unsecure RPC machine client
$mObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '2 - Denied Unsecure NRPC Evt:5827,5828 -->> Action: Enable secure NRPC'
$mObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[0].value
$mObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[1].value
$mObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[2].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value $mc582.Properties[3].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value $mc582.Properties[4].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value $mc582.Properties[5].value
}
5829 { #Temp Allowed Unsecure RPC machine client
$mObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '1 - TempAllowed Unsecure NRPC Evt:5829 -->> Action: Contact vendor'
$mObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[0].value
$mObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[1].value
$mObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[2].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value $mc582.Properties[3].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value $mc582.Properties[4].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value $mc582.Properties[5].value
}
5830 { #Allowed Unsecure RPC machine client
$mObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '3 - Allowed Unsecure NRPC Evt:5830,5831 -->> Action: Enable secure NRPC'
$mObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[0].value
$mObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[1].value
$mObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[2].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value $mc582.Properties[3].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value $mc582.Properties[4].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value $mc582.Properties[5].value
}
5828 { #Temp Allowed Unsecure RPC trust
$mObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '2 - Denied Unsecure NRPC Evt:5827,5828 -->> Action: Enable secure NRPC'
$mObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[1].value
$mObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[2].value
$mObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[0].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value 'Domain Trust'
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value 'N/A'
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value 'N/A'
}
5831 { #Allowed Unsecure RPC trust
$mObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '3 - Allowed Unsecure NRPC Evt:5830,5831 -->> Action: Enable secure NRPC'
$mObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[1].value
$mObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[2].value
$mObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[0].value
$mObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value 'Domain Trust'
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value 'N/A'
$mObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value 'N/A'
}
}
$mObject.Name = $mObject.Name.Trim()
$mObject.DomainName = $mObject.DomainName.Trim()
$mObject.AccountType = $mObject.AccountType.Trim()
$mObject.MachineOS = $mObject.MachineOS.Trim()
$mObject.MachineOsBuild = $mObject.MachineOsBuild.Trim()
$mObject.MachineOsServicePack = $mObject.MachineOsServicePack.Trim()
$mc582sOut += $mObject
}
$mc582sout | ConvertTo-Csv -NoTypeInformation | Out-File $mcOutFile
} else {
Write-Host ' Event 5827~5831 not found in' $mfile -ForegroundColor Red
}
###########Job [ # Job Maxthread code 2/3 ###########
} | Out-Null #Job output #Job output
Write-host ' Processing: '$mFile.name
While((Get-Job -State 'Running').Count -ge $mcMaxThreads) { Start-Sleep -Milliseconds 10 } # Pause, only run $mcMaxThreads number of jobs.
###########Job ] ###########
}
##########Job [ # Job clean out code 3/3 ###########
While((Get-Job -State 'Running').Count -gt 0) {
Write-Progress -Activity "Exporting evtx" -PercentComplete ((($mfiles.Count-(Get-Job -State 'Running').Count)/$mfiles.Count)*100)
Start-Sleep -Milliseconds 10
} # wait for last job to complete before excel import
Get-Job -State Completed | Remove-Job
##########Job ] ###########
#---- Import csv to excel-----------------------------------------------------
if ($mcCSVImport -eq $true) {
Write-Host 'Connect to Excel for import CSV..' -ForegroundColor Blue
$mFiles = Get-ChildItem -Path $mcEvtPath | Where-Object {$_.name -clike '582-*.csv'}
If ($null -ne $mFiles) { #Create a new Excel workbook if there are CSV in directory.
Set-Location $mcEvtPath #for Import-Csv to read all files.
$mObject = Import-Csv $mFiles -Delimiter ','
Set-Location $mcScriptPath #Back to script path
$mcExcel = New-Object -ComObject excel.application
$mcWorkbooks = $mcExcel.Workbooks.Add()
$Sheet1 = $mcWorkbooks.worksheets.Item(1)
$mcCurrentRow = $mFileProcessed = 1
ForEach ($mFile in $mFiles) { #Define Excel TXT connector and import/append
Write-Progress -Activity "Importing $mFile" -PercentComplete (($mFileProcessed/$mFiles.Count)*100)
Start-Sleep $mcProgressBarDelay
$mFileProcessed=$mFileProcessed+1
if ($mcCurrentRow -le $mcMaxImport){ #Import only up to $mcMaxImport number of records.
$mcConnector = $Sheet1.QueryTables.add(("TEXT;" + $mcEvtPath+'\'+$mFile),$Sheet1.Range(('a'+($mcCurrentRow))))
$Sheet1.QueryTables.item($mcConnector.name).TextFileCommaDelimiter = $True
$Sheet1.QueryTables.item($mcConnector.name).TextFileParseType = 1
[void]$Sheet1.QueryTables.item($mcConnector.name).Refresh()
if ($mcCurrentRow -ne 1) { [void]($Sheet1.Cells.Item($mcCurrentRow,1).entireRow).delete()} # Delete header on 2nd and later CSV.
$mcCurrentRow = $Sheet1.UsedRange.EntireRow.Count+1
} else {
Write-Output " *** Max [ $mcMaxImport ] import events reached, skipping: $mFile"
}
}
#---- Customize XLS, Excel Globals-----------------------------------------https://msdn.microsoft.com/en-us/library/bb241425(v=office.12).aspx, https://msdn.microsoft.com/en-us/library/office/ff838592.aspx
$xlRowField = 1 #XlPivotFieldOrientation
$xlPageField = 3 #XlPivotFieldOrientation
$xlDataField = 4 #XlPivotFieldOrientation
$xlColumnField = 2 #XlPivotFieldOrientation
$xlAverage = -4106 #XlConsolidationFunction
$xlSum = -4157 #XlConsolidationFunction
$xlCount = -4112 #XlConsolidationFunction
$xlPercentOfTotal = 8 #XlPivotFieldCalculation
$xlPercentRunningTotal = 13 #XlPivotFieldCalculation
$mcNumberF = "###,###,###,###,###"
$mcPercentF = "#0.00%"
$mcDateF = "yyyy/m/d h:mm"
$mcDateGroupFlags=($false, $false, $true, $true, $true, $false, $false) # Months, Days, Hours, https://msdn.microsoft.com/en-us/library/office/ff839808.aspx
#-------#Sheet1 - RawData---------------------------------------------------------------
$Sheet1.Range("A1").Autofilter() | Out-Null
$Sheet1.Application.ActiveWindow.SplitRow = 1
$Sheet1.Application.ActiveWindow.FreezePanes = $true
Write-Progress -Activity "Created 0.Raw RPC events Tab" -PercentComplete 30
Start-Sleep $mcProgressBarDelay
#-------#Sheet2 - PivotTable1---------------------------------------------------------------
$Sheet2 = $mcWorkbooks.Worksheets.add()
$PivotTable1 = $mcWorkbooks.PivotCaches().Create(1,"Sheet1!R1C1:R$($Sheet1.UsedRange.Rows.count)C$($Sheet1.UsedRange.Columns.count)",5) # xlDatabase=1 xlPivotTableVersion15=5 Excel2013
$PivotTable1.CreatePivotTable("Sheet2!R1C1") | Out-Null
$mcPF00 = $Sheet2.PivotTables("PivotTable1").PivotFields("EventSource")
mcSetPivotField($mcPF00, $xlPageField, $null, $null, $null, $null, $null)
$mcPF0 = $Sheet2.PivotTables("PivotTable1").PivotFields("NRPCType")
mcSetPivotField($mcPF0, $xlRowField, $null, $null, $null, $null, $null)
$mcPF1 = $Sheet2.PivotTables("PivotTable1").PivotFields("MachineOS")
mcSetPivotField($mcPF1, $xlRowField, $null, $null, $null, $null, $null)
$mcPF2 = $Sheet2.PivotTables("PivotTable1").PivotFields("DomainName")
mcSetPivotField($mcPF2, $xlRowField, $null, $null, $null, $null, $null)
$mcPF3 = $Sheet2.PivotTables("PivotTable1").PivotFields("Name")
mcSetPivotField($mcPF3, $xlRowField, $null, $null, $null, $null, $null)
# Sum Values
$mcPF = $Sheet2.PivotTables("PivotTable1").PivotFields("EventTime")
mcSetPivotField($mcPF, $xlDataField, $mcNumberF, $null, $null, $null, "Event Count",1) # Sum Values 1
$mcPF = $Sheet2.PivotTables("PivotTable1").PivotFields("EventTime")
mcSetPivotField($mcPF, $xlDataField, $mcPercentF, $null, $xlPercentOfTotal, $null, "%GrandTotal",2) # Sum Values 2
mcSetPivotTableFormat($Sheet2, "PivotTable1", 70, 12, $null, $null, $null, $null, $null,"NRPC Type", "1.OS,Domain,Client", "C","C") #Last 2 Param are ColorBar, DataBar
mcSortPivotFields($sheet2,$mcPF,$mcPF0)
mcSetPivotTableHeaderColor($Sheet2, "A","C")
[void]$Sheet2.Hyperlinks.add($Sheet2.Cells.Item(2,1)," https://support.microsoft.com/en-us/kb/4557222","KB 4557222: How to manage the changes in Netlogon secure channel connections associated with CVE-2020-1472","Click to read KB 4557222: How to manage the changes in Netlogon secure channel connections associated with CVE-2020-1472", "KB 4557222: How to manage the changes in Netlogon secure channel connections associated with CVE-2020-1472")
$Sheet2.Cells.Item(1,1).Formula='Source DC'
Write-Progress -Activity "Created Tab 1.NRPCType-OS,Domain,Client pivot table" -PercentComplete 60
Start-Sleep $mcProgressBarDelay
#-------#Sheet3 - PivotTable2---------------------------------------------------------------
$Sheet3 = $mcWorkbooks.Worksheets.add()
$PivotTable2 = $mcWorkbooks.PivotCaches().Create(1,"Sheet1!R1C1:R$($Sheet1.UsedRange.Rows.count)C$($Sheet1.UsedRange.Columns.count)",5) # xlDatabase=1 xlPivotTableVersion15=5 Excel2013
$PivotTable2.CreatePivotTable("Sheet3!R1C1") | Out-Null
$mcPF00 = $Sheet3.PivotTables("PivotTable2").PivotFields("EventSource")
mcSetPivotField($mcPF00, $xlPageField, $null, $null, $null, $null, $null)
$mcPF0 = $Sheet3.PivotTables("PivotTable2").PivotFields("NRPCType")
mcSetPivotField($mcPF0, $xlRowField, $null, $null, $null, $null, $null)
$mcPF1 = $Sheet3.PivotTables("PivotTable2").PivotFields("DomainName")
mcSetPivotField($mcPF1, $xlRowField, $null, $null, $null, $null, $null)
$mcPF2 = $Sheet3.PivotTables("PivotTable2").PivotFields("Name")
mcSetPivotField($mcPF2, $xlRowField, $null, $null, $null, $null, $null)
# Sum Values
$mcPF = $Sheet3.PivotTables("PivotTable2").PivotFields("EventTime")
mcSetPivotField($mcPF, $xlDataField, $mcNumberF, $null, $null, $null, "Event Count",1) # Sum Values 1
$mcPF = $Sheet3.PivotTables("PivotTable2").PivotFields("EventTime")
mcSetPivotField($mcPF, $xlDataField, $mcPercentF, $null, $xlPercentOfTotal, $null, "%GrandTotal",2) # Sum Values 2
mcSetPivotTableFormat($Sheet3, "PivotTable2", 70, 12, $null, $null, $null, $null, $null,"NRPC Type", "2.Domain,Client","C","C") #Last 2 Param are ColorBar, DataBar
mcSortPivotFields($Sheet3,$mcPF,$mcPF0)
mcSetPivotTableHeaderColor($Sheet3, "A","C")
[void]$Sheet3.Hyperlinks.add($Sheet3.Cells.Item(2,1)," https://support.microsoft.com/en-us/kb/4557222","KB 4557222: How to manage the changes in Netlogon secure channel connections associated with CVE-2020-1472","Click to read KB 4557222: How to manage the changes in Netlogon secure channel connections associated with CVE-2020-1472", "KB 4557222: How to manage the changes in Netlogon secure channel connections associated with CVE-2020-1472")
$Sheet3.Cells.Item(1,1).Formula='Source DC'
Write-Progress -Activity "Created Tab 2.NRPCType-Domain,Client pivot table" -PercentComplete 90
Start-Sleep $mcProgressBarDelay
#-------#$Sheet4 - $PivotTable3---------------------------------------------------------------
$Sheet4 = $mcWorkbooks.Worksheets.add()
$PivotTable3 = $mcWorkbooks.PivotCaches().Create(1,"Sheet1!R1C1:R$($Sheet1.UsedRange.Rows.count)C$($Sheet1.UsedRange.Columns.count)",5) # xlDatabase=1 xlPivotTableVersion15=5 Excel2013
$PivotTable3.CreatePivotTable("Sheet4!R1C1") | Out-Null
$Sheet4.name = "3.CustomPivot"
#--------#MainSheet work - et Sort sheet names in reverse--------------------------------------
$Sheet1.Name = "0.Raw NRPC events"
$Sheet2.Tab.ColorIndex = $Sheet3.Tab.ColorIndex =35
$mcWorkSheetNames = New-Object System.Collections.ArrayList
foreach ($mcWorkSheet in $mcWorkbooks.Worksheets) { $mcWorkSheetNames.add($mcWorkSheet.Name) | Out-null }
$mctmp = $mcWorkSheetNames.Sort() | Out-Null
For ($i=0; $i -lt $mcWorkSheetNames.Count-1; $i++){ #Sort name.
$mcTmp = $mcWorkSheetNames[$i]
$mcBefore = $mcWorkbooks.Worksheets.Item($mcTmp)
$mcAfter = $mcWorkbooks.Worksheets.Item($i+1)
$mcBefore.Move($mcAfter)
}
$Sheet1.Activate()
Write-Progress -Activity "Creating Excel sheets" -PercentComplete 100
Start-Sleep $mcProgressBarDelay
#-------General clean up. In non-interactive mode, delete CSV if CSV is imported to Excel.
$mcFileName = $null
if ($mcScriptPrompts ) {
$mcFileName = Read-Host "Enter a FileName to save extracted 5827-5831 events xlsx.`n Or press [Enter] to save as 582# %Year-%Month-%Day Netlogon RPC report.xlsx.`n"
$mcCleanup = Read-Host "Delete 582-*.CSV? ([Enter]/[Y] to delete, [N] to keep csv)`n"
}
if ([string]::IsNullOrWhiteSpace($mcFileName)) {
$mToday=Get-Date
$mcFileName='582# '+[string]$mToday.Year+'-'+([string]$mToday.Month).PadLeft(2,'0')+'-'+([string]$mToday.Day).PadLeft(2,'0')+' Netlogon RPC report'
}
Write-Host "Saving file to $mcEvtPath\$mcFileName.xlsx" -ForegroundColor Green
$mcWorkbooks.SaveAs($mcEvtPath+'\'+$mcFileName)
if ($mcCleanup -ne 'n') { #remove CSV in non-interactive and interactive's default
Set-Location $mcEvtPath
(Get-ChildItem -Path $mcEvtPath).name -clike '582-*.csv' | Remove-Item
}
# $mcExcel.visible = $true
$mcExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($mcExcel) | Out-Null
} else { #end of mFiles
Write-Host " No event found in $mcEvtPath." -ForegroundColor Red
}
}
Write-Host 'Script completed.' -ForegroundColor Green