Powershell Primer

I have added these workshops to my Skydrive. These are include in Workshops.zip:
 
* Windows PowerShell – EN.zip
Includes the English Workshop book for the first Windows PowerShell workshop:
“Introduction to Windows PowerShell” (in PDF and XPS format)
* Windows PowerShell – EN-2.zip
Includes the English Workshop book for the second Windows PowerShell workshop:
“Administrative tasks with Windows PowerShell” (in PDF and XPS format)
* Workshop1.zip
Includes the exercise files for workshop 1 as well as:
– a TXT file with all script source codes
* Workshop2.zip
Includes the exercise files for workshop 2 as well as:
– a TXT file with all script source codes
– a TXT file with all links for additional downloads you will need in the exerices
 
You find all necessary instructions in the workbooks for both workshops.
It’s recommended to do the exercises in the correct order.
 
Questions or comments? Please contact
frankoch@microsoft.com   or   http://frankoch.com
Advertisements

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."}
}