Zusammenfassung
Das in diesem Artikel verfügbare Skript ist ein Begleiter zu den Informationen unter Verwalten der Änderungen bei sicheren Netlogon-Kanalverbindungen im Zusammenhang mit CVE-2020-1472. Sie wird ohne Mängelgewähr bereitgestellt.
Das Skript verarbeitet EVTX-Dateien, die aus der Ereignisanzeige exportiert wurden, und erstellt eine Microsoft Excel-Tabelle mit Pivot-Tabellen für die verschiedenen Probleme und die Geräte in Ihrer Umgebung, die die Ereignisse ausgelöst haben. Das Skript erfordert PowerShell und Microsoft Excel 2013 oder höher (64 Bit für größere Datenmengen empfohlen).
Das Skript wurde zuletzt am 27. September 2020 aktualisiert.
Verwendung
Sie müssen Dateien von den Domänencontrollern (DCs) exportieren EVTX , die Sie auf die in KB4557222 beschriebenen Probleme überwachen. Öffnen Sie dazu die Ereignisanzeige, indem Sie Start auswählen, geben Sie Ereignisanzeige ein, und wählen Sie sie aus. Sobald die Ereignisanzeige geöffnet wird, erweitern Sie Windows-Protokolle, klicken Sie mit der rechten Maustaste oder drücken Sie lange auf "System", und wählen Sie "Alle Ereignisse speichern unter..." aus, und speichern Sie die Datei an einem Ort, um die Verarbeitung der Datei zu ermöglichen.
Das Skript kann mehr als eine EVTX Datei gleichzeitig verarbeiten, wenn Sie möchten. Das Skript kann bei großen Datenmengen sehr lange dauern. Es wird außerdem empfohlen, die EVTX Dateien lokal auf demselben Gerät zu haben, auf dem das Skript ausgeführt wird, um die Verarbeitung der Dateien zu beschleunigen.
Sie müssen das Skript an einer PowerShell-Eingabeaufforderung ausführen. Sie muss nicht als Administrator ausgeführt werden. Das Skript führt Sie durch Eingabeaufforderungen, die Sie bei Bedarf beantworten können, es sei denn, Sie legen $mcScriptPrompts fest.$false Wenn sich die EVTX Dateien im selben Ordner wie das Skript befinden, müssen Sie keinen Pfad zu den Dateien angeben.
Anpassen des Skripts
| Variabel | Beschreibung |
|---|---|
| $mcScriptPrompts | Werte: $true, $falseBeschreibung: Der Standardwert ist $true und erfordert Eingaben/Interaktionen vom Benutzer. Kann festgelegt werden, $false wenn sich die EVTXDateien im selben Ordner wie das Skript befinden und alle anderen Einstellungen als Standard ausgeführt werden. |
| $mcMaxExport | Standardwert: 50000Beschreibung: Mit den Standardeinstellungen stoppt das Skript die Verarbeitung jeder EVTX Datei, nachdem 50000 Ereignisse in CSVDateien exportiert wurden. Wenn Sie von Microsoft Excel Arbeitsspeicherfehler erhalten, müssen Sie diese Zahl verringern oder anpassen, welche Daten verarbeitet werden, z. B. Ändern von .$mcStartTime |
| $mcMaxImport | Standardwert: 50000Beschreibung: Mit den Standardeinstellungen stoppt das Skript die Verarbeitung von Ereignissen nach der aktuellen CSV-Datei, die es verarbeitet, wenn es die maximale Anzahl von Ereignissen 50000 erreicht. Dies bedeutet, dass bei Erreichen der maximalen Anzahl von Ereignissen die aktuell CSV gestartete Datei abgeschlossen wird, aber nicht mit der Verarbeitung einer neuen CSV Datei begonnen wird. |
| $mcStartTime | Standardwert: '2020/05/26 11:06'Beschreibung: Mit dieser Variablen können Sie eine Startzeit festlegen, und das Skript verarbeitet nur Ereignisse nach diesem Datum und dieser Uhrzeit. Wenn Sie Geräte bereits bis zu einem bestimmten Punkt adressiert haben, können Sie nur Ereignisse adressieren, die Sie noch nicht adressiert haben. |
| $mcMaxThreads | Standardwert: 4Beschreibung: Mit dieser Variablen können Sie die Anzahl der Threads erhöhen oder verringern, die beim Importieren der Ereignisse aus den EVXT Dateien in CSV Dateien verwendet werden. |
Script
Im Folgenden finden Sie das PowerShell-Skript, das Sie nach der Anpassung an Ihre Umgebung verwenden können. Sie müssen dieses Skript kopieren und in eine CVE-2020-1472EventReader.ps1 Datei auf dem Gerät einfügen, das Sie zum Verarbeiten der Ereignisse verwenden.
#
# 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