Podsumowanie
W tym artykule opisano, jak to zrobić, aby uzyskać informacje o tym, jak zarządzać zmianami w połączeniach bezpiecznych kanałów Netlogon powiązanych z opisem w sekcji CVE-2020-1472. Jest ona podana jako.
Skrypt przetworzy pliki EVTX wyeksportowane z podglądu zdarzeń i utworzy arkusz kalkulacyjny programu Microsoft Excel z tabelami przestawnymi w celu odróżnienia poszczególnych problemów i urządzeń w środowisku, które wyzwoliły zdarzenia. Skrypt wymaga programu PowerShell i programu Microsoft Excel 2013 lub nowszego (zalecane są wersje 64-bitowe dla większych zestawów danych).
Zastosowanie
Konieczne będzie wyeksportowanie EVTX plików z kontrolerów domen (DCS) monitorowanych na potrzeby problemów opisanych w KB4557222. W tym celu otwórz Podgląd zdarzeń, wybierając pozycję Starti wpisz Podgląd zdarzeń , a następnie wybierz go. Po otwarciu podglądu zdarzeń rozwiń pozycję Dzienniki systemu Windows, kliknij prawym przyciskiem myszy lub naciśnij klawisze długie w systemie , a następnie wybierz pozycję Zapisz wszystkie zdarzenia jako i Zapisz plik w innym miejscu, aby zezwolić na przetwarzanie pliku.
W razie takiej czynności skrypt może przetwarzać więcej niż jeden EVTX plik. Wykonanie skryptu może zająć dużo czasu na dużych zestawach danych. Zaleca się również EVTX , aby pliki były ustawione lokalnie na tym samym urządzeniu, na którym jest uruchomiony skrypt, aby przyspieszyć proces przetwarzania tych plików.
Należy uruchomić skrypt w wierszu polecenia programu PowerShell. Nie trzeba uruchamiać usługi jako administrator. Skrypt będzie uruchamiał się po wyświetleniu monitu, że możesz odebrać odpowiedź stosownie do potrzeb, jeśli nie zostanie wybrana opcja $mcScriptPrompts$false . Jeśli EVTX pliki znajdują się w tym samym folderze co skrypt, nie musisz podawać ścieżek do tych plików.
Dostosowywanie skryptu
|
Różne |
Opis |
| $mcScriptPrompts |
Wartości $true,$false Opis Wartość domyślna to $true i będą wymagać interakcji ze strony użytkownika. $falseJeśli EVTX pliki znajdują się w tym samym folderze co skrypt, a wszystkie inne ustawienia będą uruchamiane jako domyślne. |
| $mcMaxExport |
Wartość domyślna: 50000 Opis W przypadku ustawień domyślnych skrypt przestanie zatrzymywać wszystkie EVTX pliki po wyeksportowaniu 50000 zdarzeń do CSV pliku. W przypadku otrzymywania błędów związanych z pamięcią w programie Microsoft Excel należy obniżyć tę liczbę lub dostosować sposób przetwarzania danych, na przykład w celu zmiany tego sposobu $mcStartTime . |
| $mcMaxImport |
Wartość domyślna: 50000 Opis W przypadku ustawień domyślnych skrypt nie zatrzyma przetwarzania zdarzeń po bieżącym pliku CSV, który przetwarza po przekroczeniu maksymalnej liczby zdarzeń 50000. Oznacza to, że po przekroczeniu maksymalnej liczby zdarzeń program zakończy CSV pracę bieżącego pliku, ale nie rozpocznie przetwarzania nowego CSV pliku. |
| $mcStartTime |
Wartość domyślna: '2020/05/26 11:06' Opis Ta zmienna pozwala ustawić godzinę rozpoczęcia, a skrypt może przetwarzać tylko zdarzenia po tym dniu i czasie. Jeśli urządzenia zostały już zaadresowane do określonego punktu, pozwala to na kierowanie tylko tych zdarzeń, które jeszcze nie zostały przekazane. |
| $mcMaxThreads |
Wartość domyślna: 4 Opis Ta zmienna pozwala zwiększyć lub zmniejszyć liczbę wątków używanych podczas importowania zdarzeń z plików EVXT do CSV pliku. |
Skrypt
Poniżej znajduje się skrypt programu PowerShell, którego można użyć po dostosowaniu do środowiska programu. Należy skopiować i wkleić ten skrypt do CVE-2020-1472EventReader.ps1 pliku na urządzeniu, którego używasz do przetwarzania zdarzeń.
# ---------------------------------
# CVE-2020-1472 Event Reader v1.0 8/4/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 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/kb/4557222
#---------------------------------------
#Script Globals/ Tuning variables.
#---------------------------------------
# - $mcScriptPrompts > when set to $false, script will process all evtx in the script file folder without prompting. Good for dropping evtx, right-click script to "Run with powershell"
# - $mcMaxExport > max events to export per EVTX, set to low number for quick sample/spot checks.
# - $mcMaxImport > max events to import into excel, script will finish current file and stop importing next file(s). Set to lower number if Excel is returning memory error during import.
# - $mcStartTime > Earliest event to export, in the form of 'yyyy/MM/dd HH:mm', where HH 13 is 1pm, example: '2017/05/26 13:01'. Use this to filter progress.
# - $mcMaxThreads > max number of evtx files to export at the same time. 4 default, set to 8 and experiment if you have fast SSD HDD.
#
# Script variables block, modify to fit your need ---------------------------------------------------------------------
$mcScriptPrompts = $True #Script default, $false for non-interactive input & output.
$mcMaxExport = 50000 #50000 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 = 50000 #50000 max events to import into excel, ##NOTE##: Once maxed out, script will finish current import file and stop future import(s).
$mcStartTime = '2020/05/26 11:06' #Earliest 582* event to export, in the form of 'yyyy/MM/dd HH:mm', where HH 13 is 1pm, example: '2020/05/26 11:06' will list events starting 05/26 11:06, use this to filter new events.
$mcMaxThreads = 4 #Max concurrent Evt to CSV export threads (jobs)
# Internal values init, do not modify -------------------------------------------------------------------------------------
$mcCSVImport = $true
$mcEvtPath = $null
$mcStartTime=[datetime]::ParseExact($mcStartTime,'yyyy/MM/dd HH:mm',$null) #Convert string to timestamp
$mcProgressBarDelay = 0 #Delay in seconds after each ProgressBar update
#----Functions--------------------------
Function mcSetPivotField($mcPivotFieldSetting) { #Set pivot field attributes per MSDN https://msdn.microsoft.com/en-us/library/office/ff820762.aspx
if ($mcPivotFieldSetting[1] -ne $null) { $mcPivotFieldSetting[0].Orientation = $mcPivotFieldSetting[1]} # 1 Orientation { $xlRowField | $xlDataField |$xlColumnField }, in XlPivotFieldOrientation
if ($mcPivotFieldSetting[2] -ne $null) { $mcPivotFieldSetting[0].NumberFormat = $mcPivotFieldSetting[2]} # 2 NumberFormat { $mcNumberF | $mcPercentF }
if ($mcPivotFieldSetting[3] -ne $null) { $mcPivotFieldSetting[0].Function = $mcPivotFieldSetting[3]} # 3 Function { $xlAverage | $xlSum | $xlCount }, in XlConsolidationFunction
if ($mcPivotFieldSetting[4] -ne $null) { $mcPivotFieldSetting[0].Calculation = $mcPivotFieldSetting[4]} # 4 Calculation { $xlPercentOfTotal | $xlPercentRunningTotal }, in XlPivotFieldCalculation
if ($mcPivotFieldSetting[5] -ne $null) { $mcPivotFieldSetting[0].BaseField = $mcPivotFieldSetting[5]} # 5 BaseField <String>
if ($mcPivotFieldSetting[6] -ne $null) { $mcPivotFieldSetting[0].Name = $mcPivotFieldSetting[6]} # 6 Name <String>
if ($mcPivotFieldSetting[7] -ne $null) { $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 ($mcPivotTable[$i] -ne $null) { $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 #1.Freeze R1C1
$mcPivotTable[0].Cells.Item(1,1)="Filter"
$mcPivotTable[0].Cells.Item(3,1)=$mcPivotTable[9] #4 set TXT at R3C1 with PivotTableName$mcPivotTable[9]
$mcPivotTable[0].Name=$mcPivotTable[10] #5 Set Sheet Name to $mcPivotTable[10]
$mcRC = ($mcPivotTable[0].UsedRange.Cells).Rows.Count-1
if ($mcPivotTable[11] -ne $null) { # $mcPivotTable[11] Set ColorScale
$mColorScaleRange='$'+$mcPivotTable[11]+'$4:$'+$mcPivotTable[11]+'$'+$mcRC
[Void]$mcPivotTable[0].Range($mColorScaleRange).FormatConditions.AddColorScale(3) #$mcPivotTable[11]=ColorScale
$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 ($mcPivotTable[12] -ne $null) { # $mcPivotTable[12] Set DataBar
$mcDataBarRange='$'+$mcPivotTable[12]+'$4:$'+$mcPivotTable[12]+'$'+$mcRC
[void]$mcPivotTable[0].Range($mcDataBarRange).FormatConditions.AddDatabar() #$mcPivotTable[12]:Set DataBar
}
}
Function mcSortPivotFields($mcPF) { #Sort on $mcPF and collapse later pivot fields
for ($i=2; $i -lt 5; $i++) { #collapse later pivot fields
if ($mcPF[$i] -ne $null) {
$mcPF[$i].showDetail = $false
}
}
[void]($mcPF[0].Cells.Item(4, 2)).sort(($mcPF[0].Cells.Item(4, 2)), 2)
[void]($mcPF[0].Cells.Item(4, 1)).sort(($mcPF[0].Cells.Item(4, 1)), 1)
}
Function mcSetPivotTableHeaderColor($mcSheet) { #Set PiviotTable Header Color for easier reading
for ($i=1; $i -lt 5; $i++) { #Set header(s) color
if ($mcSheet[$i] -ne $null) { $mcSheet[0].Range(($mcSheet[$i]+"3")).interior.Colorindex = 37 }
}
}
#----Main---------
#---- script info-------
$mcScriptPath = Split-Path ((Get-Variable MyInvocation -Scope 0).Value).MyCommand.Path
if ($mcScriptPrompts) { #Interactive mcScriptPrompts
$mcEvtPath = Read-Host "Enter local, mapped or UNC path to saved system evtx or previously generated 582-*.csv. Be sure to remove trailing blank. For Example (c:\EventData)`n Or press [Enter] if script is in the same file folder as evtx or csv.`n"
if ($mcEvtPath -eq '') { #If there is no Path entered, we will use the same file folder as script path.
$mcEvtPath= $mcScriptPath
Write-Output "No path entered, default to " $mcEvtPath
}
} else {
$mcEvtPath= $mcScriptPath #Non-interactive script uses script path for Event path
}
#---- Export evtx to CSV---------------------------------------------------
Write-Output 'Convert evtx to csv.'
$mcFiles = Get-ChildItem -Path $mcEvtPath | Where {$_.name -clike '*.evtx'}
ForEach ($mcFile in $mcFiles) { #Loop through *.evtx
$mcFile | Add-Member -MemberType NoteProperty -Name MaxEvents -force -Value $mcMaxExport
$mcFile | Add-Member -MemberType NoteProperty -Name StartTime -force -Value $mcStartTime
#Job [ #Job start code 1
Start-Job -ArgumentList (,$mcFile) -ScriptBlock { #Start-Job for reading each files
param ($mcFile)
#Job ]
Write-Output "Reading $mcFile"
$mc582s = Get-WinEvent -FilterHashtable @{Path=$mcfile.FullName; LogName="System"; Level=3,4; StartTime=$mcFile.StartTime; id=5827,5828,5829,5830,5831} -MaxEvents $mcFile.MaxEvents -ErrorAction SilentlyContinue
If ($mc582s -ne $null) {
$mcOutFile = $mcFile.DirectoryName+'\582-'+$mcFile.Name+'.csv'
Write-Output " Event 5826-28 found, generating $mcOutFile"
$mc582sOut = @() #Array for exporting CSV items
ForEach ($mc582 in $mc582s) {
$mcObject = New-Object System.Object
$mcObject | Add-Member -MemberType NoteProperty -Name EventSource -force -value $mc582.MachineName
$mcObject | Add-Member -MemberType NoteProperty -Name EventTime -force -value $mc582.TimeCreated
$mcObject | Add-Member -MemberType NoteProperty -Name EventID -force -value $mc582.ID
switch ($mc582.ID) {
5827 { #Denided Unsecure RPC machine client
$mcObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '2 - Denied Unsecure NRPC Evt:5827,5828'
$mcObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[0].value
$mcObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[1].value
$mcObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[2].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value $mc582.Properties[3].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value $mc582.Properties[4].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value $mc582.Properties[5].value
}
5829 { #Temp Allowed Unsecure RPC machine client
$mcObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '1 - TempAllowed Unsecure NRPC Evt:5829'
$mcObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[0].value
$mcObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[1].value
$mcObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[2].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value $mc582.Properties[3].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value $mc582.Properties[4].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value $mc582.Properties[5].value
}
5830 { #Allowed Unsecure RPC machine client
$mcObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '3 - Allowed Unsecure NRPC Evt:5830,5831'
$mcObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[0].value
$mcObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[1].value
$mcObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[2].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value $mc582.Properties[3].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value $mc582.Properties[4].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value $mc582.Properties[5].value
}
5828 { #Temp Allowed Unsecure RPC trust
$mcObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '2 - Denied Unsecure NRPC Evt:5827,5828'
$mcObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[1].value
$mcObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[2].value
$mcObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[0].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value 'Domain Trust'
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value 'N/A'
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value 'N/A'
}
5831 { #Allowed Unsecure RPC trust
$mcObject | Add-Member -MemberType NoteProperty -Name NRPCType -force -value '3 - Allowed Unsecure NRPC Evt:5830,5831'
$mcObject | Add-Member -MemberType NoteProperty -Name Name -force -value $mc582.Properties[1].value
$mcObject | Add-Member -MemberType NoteProperty -Name DomainName -force -value $mc582.Properties[2].value
$mcObject | Add-Member -MemberType NoteProperty -Name AccountType -force -value $mc582.Properties[0].value
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOS -force -value 'Domain Trust'
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsBuild -force -value 'N/A'
$mcObject | Add-Member -MemberType NoteProperty -Name MachineOsServicePack -force -value 'N/A'
}
}
$mcObject.Name = $mcObject.Name.Trim()
$mcObject.DomainName = $mcObject.DomainName.Trim()
$mcObject.AccountType = $mcObject.AccountType.Trim()
$mcObject.MachineOS = $mcObject.MachineOS.Trim()
$mcObject.MachineOsBuild = $mcObject.MachineOsBuild.Trim()
$mcObject.MachineOsServicePack = $mcObject.MachineOsServicePack.Trim()
$mc582sOut += $mcObject
}
$mc582sout | ConvertTo-Csv -NoTypeInformation | Out-File $mcOutFile
}
#Job [ # Maxthread code 2/3
} | Out-Null #Job output
Write-Output " Processing: $mcFile"
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 ((($mcfiles.Count-(Get-Job -State 'Running').Count)/$mcfiles.Count)*100)
Start-Sleep -Milliseconds 10
} # wait for last job to complete
#Get-Job | Receive-Job
Get-Job -State Completed | Remove-Job
#Job ]
#---- Import csv to excel-----------------------------------------------------
if ($mcCSVImport -eq $true) { #Debug only, perforem CSV to Excel import
$mcFiles = Get-ChildItem -Path $mcEvtPath | Where {$_.name -clike '582-*.csv'}
If ($mcFiles -ne $null) { #Create a new Excel workbook if there are CSV in directory.
cd $mcEvtPath #for Import-Csv to read all files.
$mcObject = Import-Csv $mcfiles -Delimiter ','
cd $mcScriptPath #Back to script path
#Write-Output 'Import csv to excel.'
$mcExcel = New-Object -ComObject excel.application
$mcWorkbooks = $mcExcel.Workbooks.Add()
$Sheet1 = $mcWorkbooks.worksheets.Item(1)
$mcCurrentRow = $mcFileProcessed = 1
ForEach ($mcFile in $mcFiles) { #Define Excel TXT connector and import/append
Write-Progress -Activity "Importing $mcFile" -PercentComplete (($mcFileProcessed/$mcfiles.Count)*100)
Sleep $mcProgressBarDelay
$mcFileProcessed=$mcFileProcessed+1
if ($mcCurrentRow -le $mcMaxImport){ #Import only up to $mcMaxImport number of records.
$mcConnector = $Sheet1.QueryTables.add(("TEXT;" + $mcEvtPath+'\'+$mcFile),$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: $mcfile"
}
}
#---- Customize XLS, Excel Globals--------------------------------------------------------
#Write-Output 'Customizing XLS.' #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-Output 'Tab 0.5826, 5827, 5828 events imported'
Write-Progress -Activity "Created 0.Raw RPC events Tab" -PercentComplete 30
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)
$mcPF = $Sheet2.PivotTables("PivotTable1").PivotFields("EventTime")
mcSetPivotField($mcPF, $xlDataField, $mcNumberF, $null, $null, $null, "Event Count",1)
mcSetPivotTableFormat($Sheet2, "PivotTable1", 60, 15, $null, $null, $null, $null, $null,"NRPC Type", "1.OS,Domain,Client", $null, $null)
mcSortPivotFields($sheet2,$mcPF,$mcPF0)
mcSetPivotTableHeaderColor($Sheet2, "A")
$Sheet2.Cells.Item(1,1).Formula='Source DC'
#Write-Output ' Tab 1.NRPCType-OS,Domain,Client Done'
Write-Progress -Activity "Created Tab 1.NRPCType-OS,Domain,Client pivot table" -PercentComplete 60
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)
$mcPF = $Sheet3.PivotTables("PivotTable2").PivotFields("EventTime")
mcSetPivotField($mcPF, $xlDataField, $mcNumberF, $null, $null, $null, "Event Count",1)
mcSetPivotTableFormat($Sheet3, "PivotTable2", 60, 15, $null, $null, $null, $null, $null,"NRPC Type", "2.Domain,Client", $null, $null)
mcSortPivotFields($Sheet3,$mcPF,$mcPF0)
mcSetPivotTableHeaderColor($Sheet3, "A")
$Sheet3.Cells.Item(1,1).Formula='Source DC'
#Write-Output ' Tab 2.NRPCType-Domain,Client Done'
Write-Progress -Activity "Created Tab 2.NRPCType-Domain,Client pivot table" -PercentComplete 90
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"
#Set Sort sheet names in reverse
$Sheet1.Name = "0.Raw RPC 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
Sleep $mcProgressBarDelay
if ($mcScriptPrompts) { #SaveAsFile
$mcFileName = Read-Host "Enter a FileName to save extracted event 582 xlsx.`n"
if ($mcFileName) {
Write-Output "Saving file to $mcEvtPath\$mcFileName.xlsx"
$mcWorkbooks.SaveAs($mcEvtPath+'\'+$mcFileName)
}
$mcCleanup = Read-Host "Delete 582-*.CSV? ([Enter]/[Y] to delete, [N] to keep csv)`n"
if ($mcCleanup -ne 'N') {
Get-ChildItem -Path $mcEvtPath | Where {$_.name -clike '582-*.csv'} | foreach ($_) {
Remove-Item $mcEvtPath'\'$_
Write-Output " $_ deleted."
}
}
}
$mcExcel.visible = $true
} else { #end of mcFiles
Write-Output " No 582-*.csv found in specified $mcEvtPath."
}
}
Write-Output 'Script completed.'
