Resumen
El script disponible en este artículo es un complemento de la información de Cómo administrar los cambios en las conexiones de canal seguro de Netlogon asociadas con CVE-2020-1472. Se proporciona tal cual.
El script procesará los archivos EVTX exportados desde Visor de eventos y creará una hoja de cálculo de Microsoft Excel que contiene tablas dinámicas para los distintos problemas y los dispositivos del entorno que desencadenaron los eventos. El script requiere PowerShell y Microsoft Excel 2013 o posterior (se recomiendan 64 bits para conjuntos de datos más grandes).
Script actualizado por última vez el 27 de septiembre de 2020.
Uso
Tendrá que exportar EVTX archivos desde los controladores de dominio (DCs) que está supervisando para los problemas descritos en KB4557222. Para ello, abre Visor de eventos seleccionando Inicio, escribe visor de eventos y selecciónalo. Una vez Visor de eventos se abre, expande Registros de Windows, haz clic con el botón derecho o mantén pulsado en Sistema y selecciona Guardar todos los eventos como... y guarda el archivo en algún lugar para permitir el procesamiento del archivo.
Si lo desea, el script puede procesar más de un EVTX archivo a la vez. El script puede tardar mucho tiempo en completarse en grandes conjuntos de datos. También te recomendamos que tengas los EVTX archivos locales en el mismo dispositivo que ejecuta el script, para ayudar a acelerar el procesamiento de los archivos.
Tendrá que ejecutar el script en un símbolo del sistema de PowerShell. No es necesario ejecutarlo como administrador. El script se ejecutará a través de mensajes que puede responder según sea necesario, a menos que establezca en $mcScriptPrompts$false. Si los EVTX archivos están en la misma carpeta que el script, no es necesario proporcionar una ruta de acceso a los archivos.
Personalización del script
| Variable | Descripción |
|---|---|
| $mcScriptPrompts | Valores: $true, $falseDescripción: el valor predeterminado es $true y requiere la entrada/interacción del usuario. Puede establecerse $false en si los EVTXarchivos están en la misma carpeta que el script y todas las demás opciones de configuración se ejecutarán como predeterminadas. |
| $mcMaxExport | Valor predeterminado: 50000Descripción: con la configuración predeterminada, el script dejará de procesar cada EVTX archivo después de exportar 50000 eventos a CSVlos archivos. Si recibe errores relacionados con la memoria de Microsoft Excel, tendrá que bajar este número o ajustar los datos que se están procesando, como cambiar el $mcStartTime. |
| $mcMaxImport | Valor predeterminado: 50000Descripción: con la configuración predeterminada, el script dejará de procesar eventos después del archivo CSV actual que está procesando cuando alcanza el número máximo de eventos 50000. Esto significa que, cuando alcance el número máximo de eventos, completará el archivo actual CSV que ha iniciado, pero no comenzará a procesar un nuevo CSV archivo. |
| $mcStartTime | Valor predeterminado: '2020/05/26 11:06'Descripción: esta variable le permite establecer una hora de inicio y el script solo procesará los eventos después de esa fecha y hora. Si ya has abordado dispositivos hasta un punto determinado, esto te permite dirigirte solo a eventos a los que aún no has tratado. |
| $mcMaxThreads | Valor predeterminado: 4Descripción: esta variable le permite aumentar o disminuir el número de subprocesos utilizados durante la importación de los eventos de los EVXT archivos a CSV los archivos. |
Script
A continuación encontrará el script de PowerShell que puede usar después de personalizar a su entorno. Tendrá que copiar y pegar este script en un CVE-2020-1472EventReader.ps1 archivo en el dispositivo que usa para procesar los eventos.
#
# 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