協助監視與 CVE 相關的 Netlogon 安全通道連線中的變更相關的事件識別碼的腳本-2020-1472

摘要

本文中提供的腳本是如何管理 Netlogon 安全通道連線中與 CVE-2020-1472 相關變更的相關資訊。 這是以原樣提供。

腳本會處理從事件檢視器匯出的 .EVTX 檔案,並建立一份包含樞紐分析表的 Microsoft Excel 試算表,其中包含各種問題和您環境中觸發事件的裝置。 腳本需要 PowerShell 和 Microsoft Excel 2013 或更新版本(適用于大型資料集的64位版建議)。

使用量

您必須 EVTX 從您要監視的網網域控制站(dc)匯出檔案,以尋找KB4557222中所述的問題。 若要這麼做,請選取 [開始],輸入事件檢視器並選取 [啟動]。 活動檢視器開啟之後,展開 [ Windows 記錄],用滑鼠右鍵按一下 [系統] 或 [冗長],然後選取 [將所有事件儲存為 ... ],並將檔案儲存在某個位置以允許處理檔案。

您也可以視需要處理多個檔案 EVTX 。 您可能需要花很長的時間才能完成大型資料集。 我們也建議您在執行 EVTX 腳本的同一部裝置上使用本機檔案,以協助您加快檔案處理速度。

您必須在 PowerShell 命令提示字元中執行腳本。 不需要以系統管理員身分執行。 腳本會透過您所需的提示來執行提示,除非您將設定 $mcScriptPrompts$false 。 如果檔案 EVTX 與腳本位於同一個資料夾中,您不需要提供檔案的路徑。

自訂腳本

變數

描述

$mcScriptPrompts

鍵值 $true,$false

產品介紹 預設值為 $true ,且需要來自使用者的輸入/交互。 如果檔案與 $falseEVTX 腳本位於同一個資料夾中,則可以設定為,且所有其他設定會以預設的方式執行。

$mcMaxExport

預設值: 50000

產品介紹 使用預設設定,在將 EVTX 50000 事件匯出至檔案之後,腳本就會停止處理每個檔案 CSV 。 如果您收到的是 Microsoft Excel 記憶體所產生的錯誤,您必須降低這個數位,或調整正在處理的資料,例如變更 $mcStartTime

$mcMaxImport

預設值: 50000

產品介紹 使用預設設定時,腳本會在處理的目前 CSV 檔案達到最大事件50000時停止處理活動。 這表示當其達到最大活動數量時,它會完成已啟動的目前檔案, CSV 但不會開始處理新檔案 CSV

$mcStartTime

預設值: '2020/05/26 11:06'

產品介紹 這個變數能讓您設定開始時間,而腳本只會處理該日期和時間之後的事件。 如果您已將多個裝置定址到某個特定點,您就可以使用此功能僅針對您尚未解決的事件。

$mcMaxThreads

預設值: 4

產品介紹 這個變數可讓您增加或減少從檔案匯入到檔案時所使用的執行緒數 EVXTCSV

 

文字

您可以在下方找到您自訂至您的環境之後可以使用的 PowerShell 腳本。 您需要將此腳本複製並貼到 CVE-2020-1472EventReader.ps1 您用來處理事件之裝置上的檔案中。

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

 

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×