Skrypt pomagający w monitorowaniu identyfikatorów zdarzeń związanych ze zmianami w połączeniach bezpiecznego kanału usługi Netlogon skojarzonych z CVE-2020-1472

Dotyczy
Windows Server 2019, all editions Windows Server 2016, all editions Windows Server, version 1909, all editions Windows Server, version 1903, all editions Windows Server, version 1809 (Datacenter, Standard) Windows Server 2012 R2 Windows Server 2012 Windows Server 2008 R2 Service Pack 1

Podsumowanie

Skrypt dostępny w tym artykule jest uzupełnieniem informacji w artykule Jak zarządzać zmianami w połączeniach bezpiecznych kanałów usługi Netlogon skojarzonych z CVE-2020-1472.  Jest on dostarczany w stanie takim, w jakim jest.

Skrypt przetworzy pliki EVTX wyeksportowane z Podglądu zdarzeń i utworzy arkusz kalkulacyjny programu Microsoft Excel zawierający tabele przestawne dla różnych problemów i urządzeń w środowisku, które wywołały zdarzenia.  Skrypt wymaga programu PowerShell i programu Microsoft Excel 2013 lub nowszego (zalecane w przypadku większych zestawów danych w wersji 64-bitowej).

Ostatnia aktualizacja skryptu: 27 września 2020 r.

Zastosowanie

Musisz wyeksportować EVTX pliki z kontrolerów domeny, które monitorujesz pod kątem problemów opisanych w KB4557222.  W tym celu otwórz Podgląd zdarzeń, wybierając przycisk Start, wpisz podgląd zdarzeń i wybierz go.  Po otwarciu podglądu zdarzeń rozwiń pozycję Dzienniki systemu Windows, kliknij prawym przyciskiem myszy lub długo naciśnij pozycję System i wybierz pozycję Zapisz wszystkie zdarzenia jako... i zapisz plik w miejscu, aby umożliwić przetwarzanie pliku.

Jeśli chcesz, skrypt może przetwarzać więcej niż jeden EVTX plik jednocześnie.  W przypadku dużych zestawów danych wykonanie skryptu może zająć dużo czasu.  Zalecane jest również, aby pliki były EVTX przechowywane lokalnie na tym samym urządzeniu, na którym jest uruchomiony skrypt, aby przyspieszyć przetwarzanie plików.

Skrypt należy uruchomić w wierszu polecenia programu PowerShell.  Nie trzeba go uruchamiać jako administrator. Skrypt zostanie uruchomiony przez monity, na które możesz odpowiedzieć w razie potrzeby, chyba że ustawisz $mcScriptPrompts wartość .$false  EVTX Jeśli pliki znajdują się w tym samym folderze co skrypt, nie trzeba podawać ścieżki do plików.

Dostosowywanie skryptu

Zmienna Opis
$mcScriptPrompts Wartości: $true, $false
Opis: Wartość domyślna jest $true i będzie wymagać danych wejściowych/interakcji ze strony użytkownika. Można ustawić, czy $false pliki znajdują się EVTXw tym samym folderze co skrypt, a wszystkie inne ustawienia zostaną uruchomione jako domyślne.
$mcMaxExport Wartość domyślna: 50000
Opis: przy ustawieniach domyślnych skrypt przestaje przetwarzać każdy EVTX plik po wyeksportowaniu do plików 50000 zdarzeń CSV. W przypadku otrzymywania błędów związanych z pamięcią z programu Microsoft Excel należy zmniejszyć tę liczbę lub dostosować przetwarzane dane, na przykład zmienić wartość parametru $mcStartTime.
$mcMaxImport Wartość domyślna: 50000
Opis: przy ustawieniach domyślnych skrypt przestanie przetwarzać zdarzenia po aktualnie przetwarzanym pliku CSV, gdy osiągnie maksymalną liczbę zdarzeń wynoszącą 50 000. Oznacza to, że po osiągnięciu maksymalnej liczby zdarzeń zakończy bieżący CSV plik, który został uruchomiony, ale nie rozpocznie przetwarzania nowego CSV pliku.
$mcStartTime Wartość domyślna: '2020/05/26 11:06'
Opis: Ta zmienna pozwala ustawić czas rozpoczęcia, a skrypt będzie przetwarzał zdarzenia tylko po tej dacie i godzinie. Jeśli do pewnego momentu zaadresowano już urządzenia, pozwala to na kierowanie tylko tych zdarzeń, które nie zostały jeszcze zaadresowane.
$mcMaxThreads Wartość domyślna: 4
Opis: Ta zmienna umożliwia zwiększenie lub zmniejszenie liczby wątków używanych podczas importowania zdarzeń z EVXT plików do CSV plików.

Script

Poniżej znajdziesz skrypt PowerShell, którego możesz użyć po dostosowaniu do swojego środowiska.  Należy skopiować ten skrypt i wkleić go do CVE-2020-1472EventReader.ps1 pliku na urządzeniu używanym do przetwarzania zdarzeń.


#
# 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