MBSA Powershell and Excel

I am working with a group in a very large organization that handles patching for several thousand servers. Part of their mission is to check all their servers monthly with MBSA scan to see which patches have NOT been installed. MBSA creates a report in XML format, one report per server. I was asked to consolidate the reports into one Excel spreadsheet that shows which servers were scanned and which Patches were not installed. They devide their servers up into seven groups, one for each day of the week and the reports are filed by month and day.

They also wanted me to exclude about 90 patches that they have decided not to include in their report for various reasons.

Here is the Powershell script I came up with ( with lots of help ) :

# ==============================================================================================
Function Pivot {
#
# Macro2 Macro
#
$xlPivotTableVersion12     = 3
$xlPivotTableVersion10     = 1
$xlCount                 = -4112
$xlDescending             = 2
$xlDatabase                = 1
$xlHidden                  = 0
$xlRowField                = 1
$xlColumnField             = 2
$xlPageField               = 3
$xlDataField               = 4   

$PivotTable = $wb.PivotCaches().Create($xlDatabase,"Report!R1C1:R65536C5",$xlPivotTableVersion10)
$PivotTable.CreatePivotTable("Pivot!R1C1") | Out-Null

[void]$ws3.Select()
$ws3.Cells.Item(1,1).Select()
$wb.ShowPivotTableFieldList = $true

$PivotFields = $ws3.PivotTables("PivotTable1").PivotFields("Server")
$PivotFields.Orientation = $xlRowField
$PivotFields.Position = 1

$PivotFields = $ws3.PivotTables("PivotTable1").PivotFields("KBID")
$PivotFields.Orientation = $xlColumnField
$PivotFields.Position = 1

$PivotFields = $ws3.PivotTables("PivotTable1").PivotFields("KBID")
$PivotFields.Orientation=$xlDataField

$mainRng = $ws3.UsedRange.Cells
$RowCount = $mainRng.Rows.Count 
$R = $RowCount
$R = $R - 1   

$mainRng.Select()
$objSearch = $mainRng.Find("Grand Total")
$objSearch.Select()
$C = $objSearch.Column
Write-Host $C $R

$xlSummaryAbove = 0
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2

$range1 = $ws3.UsedRange
$range2 = $ws3.Cells.Item(3, $C)

# one-column sort --> works
[void]$range2.sort($range2, $xlDescending)
}

Function Show-Msgbox {
  Param([string]$message=$(Throw "You must specify a message"),
      [string]$button="okonly",
      [string]$icon="Question",
      [string]$title="Message Box"
     )
# Buttons: OkOnly, OkCancel, AbortRetryIgnore, YesNoCancel, YesNo, RetryCancel
# Icons: Critical, Question, Exclamation, Information
  [reflection.assembly]::loadwithpartialname("microsoft.visualbasic") | Out-Null
  [microsoft.visualbasic.interaction]::Msgbox($message,"$button,$icon",$title)
}

Function Show-Inputbox {
Param([string]$message=$(Throw "You must enter a prompt message"),
       [string]$title="Input",
       [string]$default
       )
[reflection.assembly]::loadwithpartialname("microsoft.visualbasic") | Out-Null
[microsoft.visualbasic.interaction]::InputBox($message,$title,$default)

}

$c=Show-Inputbox -message "Enter A Month" `
-title "Scan Month" -default "April"

$d=Show-Inputbox -message "Enter A WeekDay" `
-title "Scan Day" -default "Monday"

Write-Host $c $d

#delete old CSV files
Remove-Item D:\Temp\*.csv -force

$xlPasteValues = -4163          # Values only, not formulas
$xlFillCopy = 1
$row = 2
$xRow = 2
$zRow = 2
$curdate = Get-Date
$day = (get-date).day
$Rf = "Z:\MBSA_ReportDailyReport\$c$d" + $c + "-" + $d + "-" + $day + ".xlsx"
dir Z:\MBSA_ReportScanData\$c$d*.mbsa |
ForEach-Object {
$FileName = $_.name
$N = $FileName.tostring()
$E = $N.split()
$F = $E[2]
Write-Host $F".csv"
$xml=[xml](Get-Content Z:\MBSA_ReportScanData\$c$d$N)
$a = $xml.SelectNodes("//*[@Name='Windows Security Updates']/Detail/UpdateData")
$a | select BulletinID,KBID,IsInstalled,Title | Where-Object { $_.IsInstalled -match "FALSE"}|
Export-Csv D:temp$f".csv" -NoTypeInformation
}

#Open Excel thread
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$xlCellTypeLastCell = 11
#Create spreadsheet
$wb = $xl.Workbooks.Add()
#Create Worksheets
$ws = $wb.Worksheets.Item(1)
# rename the Worksheet. 30 charactor limit   
$ws1 = $wb.worksheets | where {$_.name -eq "sheet1"} #<------- activate()s sheet 1
$ws2 = $wb.worksheets | where {$_.name -eq "sheet2"} #<------- activate()s sheet 2
$ws3 = $wb.worksheets | where {$_.name -eq "sheet3"} #<------- activate()s sheet 3
# Activate sheet 1
$ws1.activate()
$ws1.name = "xml_source"

# Activate sheet 2
$ws2.activate()
$ws2.name = "Report"
# Make Row 1 bold
$range = $ws2.range("1:1")
$range.font.bold = "true"
# Make Col A bold
$range = $ws2.range("A:A")
$range.font.bold = "true"

$ws1.activate()
$ws1.Cells.Item(1, 2) = $curdate
dir d:temp*.csv |
ForEach-Object {
    $FileName = $_.name
    $N = $FileName.tostring()
    $E = $N.split(".")
    $F = $E[0]
    #Write-Host $N
    $wb2 = $xl.Workbooks.Open("D:\temp\$F.csv")
    [void]$wb2.Activate()
    $ws5 = $wb2.Worksheets.Item(1)
    $range = $ws5.UsedRange
    #Write-Host $F
    $range.Copy()
    [void]$wb.Activate()
    $ws1.Cells.Item($zRow, 1)= $F
    [void]$ws1.Cells.Item($zRow, 2).PasteSpecial(-4163)

$mainRng = $ws1.UsedRange.Cells
$RowCount = $mainRng.Rows.Count 
$R = $RowCount
$R = $R + 1   
    For ($i = $zRow; $i -lt $R; $i++) {
    $ws1.Cells.Item($i, 1) = $F
    }
    $xrange = $ws1.UsedRange
    $lastCell = $xrange.SpecialCells($xlCellTypeLastCell)
    $newRow = $lastCell.row
    $zRow = $newRow + 1
      [void]$wb2.Close() 
}    
[void]$wb.Activate()
$mainRng = $ws1.UsedRange.Cells
$RowCount = $mainRng.Rows.Count 
$R = $RowCount
$R = $R + 1
$zRow = 2

$xlSummaryAbove = 0
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2

$range1 = $ws1.UsedRange
$range2 = $ws1.range("D2")

# one-column sort --> works
[void]$range2.sort($range2, $xlAscending)

$range = $ws2.range("A1:E1")
$range.font.bold = "true"
$range = $ws2.range("A:A")
$range.font.bold = "true"
$ws2.Cells.Item(1, 1) = "Server"
$ws2.Cells.Item(1, 2) = "BulletinID"
$ws2.Cells.Item(1, 3) = "KBID"   
$ws2.Cells.Item(1, 4) = "IsInstalled"
$ws2.Cells.Item(1, 5) = "Title"

$ws1.activate()
$objRange = $ws1.Range("D1").EntireColumn
$i = 1
$row = 2
Do {
    $objSearch = $objRange.Find("False")
    $e = $objSearch.row
    If ($objSearch -eq $null) {
        Write-Host "Not found. " + "False"
    }
    Else {
       $range4=$ws1.range("A${e}:E$R")
       $range4.select()
        $range4.Copy()
        [void]$ws2.Activate()
        [void]$ws2.Cells.Item($row,1).PasteSpecial(-4163)
        Break
    }

    $i++
}

While ($ws1.Cells.Item($i,4).Value() -ne "True")

#Build Array of Exceptions
$arrKB = (
"110806","832671","867460","887616","887618","887619","887620","887622","890830",`
"902848","905649","905758","920816","921596","923618","923620","928365","928366",`
"928957","929300","933399","933854","934062","934737","936509","936514","936646",`
"936960","936982","937060","937961","943973","944036","944423","946983","946985",`
"947318","947319","947320","947742","947746","947748","949426","950114","950129",`
"951338","951550","951847","951944","953195","953297","953298","953300","953404",`
"954326","954478","955440","955466","955468","955706","956329","956358","956464",`
"956828","957646","957781","957789","959897","959988","959997","960003","960082",`
"960083","960089","969559","969604","969613","969693","970892","970895","971090",`
"971108","971110","971117","972222","972363","972580","972581","973143","973444",`
"973484","973702","973709","973923","973924","974234","974331","974417","974470",`
"974554","974556","975008","976325","977896","978471","979202","979306","980470")

[void]$ws2.Activate()
$mainRng = $ws2.UsedRange.Cells
$RowCount = $mainRng.Rows.Count 
$R = $RowCount
$R = $R + 1   
For ($i = 1; $i -lt $R; $i++) {
        $kb = $ws2.Cells.Item($i,3).Text
        $b = $arrKB -contains $kb

        If ($b) {
        Write-Host $kb $i
           $range4 = $ws2.range("${i}:$i")
        $range4.select()
        $range4.Delete()
           $i = $i - 1
           $R = $R - 1
           }
   }     

[void]$ws3.Activate()
$ws3.name = "Pivot"
Pivot

$wb.SaveAs($Rf)

$rc=Show-Msgbox -message "Do you want to view the Spredsheet?" `
-icon "exclamation" -button "YesNoCancel" -title "Hey $env:username!!"
Switch ($rc) {
"Yes" {$xl.Visible = $True }
# close and release resources
"No" {$wb.close($false)
  $xl.quit()
  spps -n excel } #<---- This will shut down every instance of Excell (for sure!)
"cancel" {"When in doubt, punt."}
}
Advertisements

One thought on “MBSA Powershell and Excel

  1. I don’t understand the pathing of where your grabbing and where your putting stuff.
    also doesn’t create tabs in excel 2013

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s