Сводка
Скрипт, доступный в этой статье, является дополнением к сведениям в разделе Управление изменениями в подключениях безопасного канала Netlogon, связанных с CVE-2020-1472. Он предоставляется как есть.
Скрипт обработает файлы EVTX, экспортированные из Просмотр событий, и создаст электронную таблицу Microsoft Excel, содержащую сводные таблицы для различных проблем и устройств в вашей среде, которые активировали события. Для сценария требуется PowerShell и Microsoft Excel 2013 или более поздней версии (рекомендуется использовать 64-разрядную версию для больших наборов данных).
Скрипт обновлен 27 сентября 2020 г.
Использование
Вам потребуется экспортировать EVTX файлы из контроллеров домена , которые вы отслеживаете для устранения проблем, описанных в KB4557222. Для этого откройте Просмотр событий, нажав кнопку Пуск, введите средство просмотра событий и выберите его. После открытия Просмотр событий разверните узел Журналы Windows, щелкните правой кнопкой мыши или нажмите кнопку Система и выберите Сохранить все события как... и сохраните файл в другом месте, чтобы разрешить обработку файла.
При желании скрипт может обрабатывать несколько EVTX файлов одновременно. Выполнение скрипта может занять много времени для больших наборов данных. Мы также рекомендуем использовать EVTX файлы локально на том же устройстве, где выполняется скрипт, чтобы ускорить обработку файлов.
Вам потребуется запустить сценарий в командной строке PowerShell. Его не нужно запускать от имени администратора. Скрипт будет выполняться через запросы, на которые можно ответить по мере необходимости, если не задано значение $mcScriptPrompts$false.
EVTX Если файлы находятся в той же папке, что и скрипт, указывать путь к файлам не нужно.
Настройка скрипта
| Переменной | Описание |
|---|---|
| $mcScriptPrompts | Значения: $true, $falseОписание. Значение по умолчанию — и $true потребует ввода и взаимодействия со стороны пользователя. Можно задать значение , $false если EVTXфайлы находятся в той же папке, что и скрипт, и все остальные параметры будут выполняться по умолчанию. |
| $mcMaxExport | Значение по умолчанию: 50000Описание. Если заданы параметры по умолчанию, скрипт перестанет обрабатывать каждый EVTX файл после экспорта 50 000 событий в CSVфайлы. Если вы получаете ошибки, связанные с памятью, из Microsoft Excel, вам потребуется уменьшить это число или изменить обрабатываемые данные, например изменить $mcStartTime. |
| $mcMaxImport | Значение по умолчанию: 50000Описание. Если заданы параметры по умолчанию, скрипт перестанет обрабатывать события после того, как текущий CSV-файл, который он обрабатывает, при достижении максимального числа событий 50000. Это означает, что при достижении максимального количества событий он завершит текущий CSV запущенный файл, но не начнет обработку нового CSV файла. |
| $mcStartTime | Значение по умолчанию: '2020/05/26 11:06'Описание. Эта переменная позволяет задать время начала, и скрипт будет обрабатывать события только после этой даты и времени. Если вы уже обращались к устройствам до определенного момента, это позволяет ориентироваться только на события, которые вы еще не рассмотрели. |
| $mcMaxThreads | Значение по умолчанию: 4Описание. Эта переменная позволяет увеличить или уменьшить количество потоков, используемых при импорте событий из файлов CSV в EVXT файлы. |
Script
Ниже приведен скрипт 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