Résumé
Le script disponible dans cet article est un complément aux informations de la rubrique Comment gérer les modifications apportées aux connexions de canal sécurisé Netlogon associées à CVE-2020-1472. Il est fourni tel qu’il est.
Le script traite les fichiers EVTX exportés à partir de observateur d'événements et crée une feuille de calcul Microsoft Excel contenant des tableaux croisés dynamiques pour les différents problèmes et les appareils de votre environnement qui ont déclenché les événements. Le script nécessite PowerShell et Microsoft Excel 2013 ou version ultérieure (64 bits recommandé pour les jeux de données plus volumineux).
Dernière mise à jour du script le 27 septembre 2020.
Utilisation
Vous devez exporter EVTX des fichiers à partir des contrôleurs de domaine que vous surveillez pour les problèmes décrits dans KB4557222. Pour ce faire, ouvrez observateur d'événements en sélectionnant Démarrer, tapez Observateur d’événements et sélectionnez-la. Une fois que observateur d'événements s’ouvre, développez Journaux Windows, cliquez avec le bouton droit ou appuyez longuement sur Système, puis sélectionnez Enregistrer tous les événements sous... et enregistrez le fichier quelque part pour permettre le traitement du fichier.
Le script peut traiter plusieurs fichiers à la EVTX fois si vous le souhaitez. L’exécution du script peut prendre beaucoup de temps sur des jeux de données volumineux. Nous vous recommandons également d’avoir les EVTX fichiers locaux sur le même appareil exécutant le script, afin d’accélérer le traitement des fichiers.
Vous devez exécuter le script dans une invite de commandes PowerShell. Il n’a pas besoin d’être exécuté en tant qu’administrateur. Le script s’exécute par le biais d’invites auxquelles vous pouvez répondre en fonction des besoins, sauf si vous définissez $mcScriptPrompts sur $false. Si les EVTX fichiers se trouvent dans le même dossier que le script, vous n’avez pas besoin de fournir un chemin d’accès aux fichiers.
Personnalisation du script
| Variable | Description |
|---|---|
| $mcScriptPrompts | Valeurs : $true, $falseDescription : la valeur par défaut est $true et nécessite une entrée/une interaction de la part de l’utilisateur. Peut être défini sur $false si les EVTXfichiers se trouvent dans le même dossier que le script et que tous les autres paramètres s’exécutent comme valeurs par défaut. |
| $mcMaxExport | Valeur par défaut : 50000Description : avec les paramètres par défaut, le script arrête le traitement de chaque EVTX fichier après avoir exporté 50 000 événements vers des CSVfichiers. Si vous recevez des erreurs liées à la mémoire de Microsoft Excel, vous devez réduire ce nombre ou ajuster les données en cours de traitement, par exemple en modifiant le $mcStartTime. |
| $mcMaxImport | Valeur par défaut : 50000Description : avec les paramètres par défaut, le script arrête le traitement des événements après le fichier CSV actuel qu’il traite lorsqu’il atteint le nombre maximal d’événements 50000. Cela signifie que lorsqu’il atteint le nombre maximal d’événements, il termine le fichier actuel CSV qu’il a démarré, mais ne commence pas à traiter un nouveau CSV fichier. |
| $mcStartTime | Valeur par défaut : '2020/05/26 11:06'Description : cette variable vous permet de définir une heure de début et le script traite uniquement les événements après cette date et cette heure. Si vous avez déjà adressé des appareils jusqu’à un certain point, cela vous permet de cibler uniquement les événements que vous n’avez pas encore traités. |
| $mcMaxThreads | Valeur par défaut : 4Description : cette variable vous permet d’augmenter ou de diminuer le nombre de threads utilisés lors de l’importation des événements des EVXT fichiers vers CSV des fichiers. |
Script
Vous trouverez ci-dessous le script PowerShell que vous pouvez utiliser après la personnalisation de votre environnement. Vous devez copier et coller ce script dans un CVE-2020-1472EventReader.ps1 fichier sur l’appareil que vous utilisez pour traiter les événements.
#
# 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