[CmdletBinding()]
Param($min,$max)
Begin{
$MyPath = Split-Path $MyInvocation.MyCommand.path -Parent
$webclient = New-Object System.Net.WebClient
Function Get-KBData{
Param(
$Site
)
try{
$dl = $webclient.DownloadString($site)
#Get if hotfix is downloadable
$FilterDownloadable = 'Hotfix Download Available'
[Int]$Downloadable = $dl -match $FilterDownloadable
#Get KBID
$KBID = $Site.Split('/')[-2]
#Get Short Description
$filterShortText = 'id="mt_title".*<\/h1>'
$dl -match $filterShortText | Out-null
$shortTextRaw = $Matches[0]
$ShortText = ($shortTextRaw -split '>')[1].Replace(''
$dl -match $filterAppliesTo | Out-null
$AppliesToRaw = $Matches[0]
[Array]$arrAppliesto = ($AppliesToRaw -split '
')[1..99] | ForEach-Object{$_ -replace '(<.*)','' }
$AppliesTo = $arrAppliesto -join ','
$return = @{"HotfixDownload" = $Downloadable;"KBID" = $KBID;"ShortText" = $ShortText;"LastReview" = $LastReview;"AppliesTo" = $AppliesTo;"Exists" = 1;$error = 'N/A'}
}
Catch{
$KBID = $Site.Split('/')[-2]
$return = @{"HotfixDownload" = 0;"KBID" = $KBID;"ShortText" = 'N/A';"LastReview" = $null;"AppliesTo" = '';"Exists" = 0;$error = $($_.Exception.Message)}
}
$Return
} # End Get-KBData
Function Get-SQLQuery{
Param(
[Parameter(Mandatory=$true)]
[String]$SQLQuery,
[Parameter(Mandatory=$false)]
[String]$ConnectionString = 'Server=LocalHost\SQL;Database=Hotfixes;Trusted_Connection=True;'
)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "$ConnectionString"
$connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
Try{
$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount
$DataTable = New-Object system.Data.DataTable
for($i = 0; $i -lt $counter; $i++){
$Column = New-Object system.Data.DataColumn $Reader.GetName($i),([string])
$DataTable.Columns.Add($Column)
}
while ($Reader.Read()) {
$strData = @()
for ($i = 0; $i -lt $Counter; $i++) {
$strData += [String]$Reader.GetValue($i)
}
$DataTable.Rows.Add($strData)
}
$Reader.Close()
$return = $Datatable
}
Catch{
Write-Error $_
$return = $_.Exception
}
Finally{
$Connection.Close()
}
Return $return
}
Function Process-SQLCmd {
Param(
[Parameter(Mandatory=$true)]
[String]$Command,
[Parameter(Mandatory=$false)]
[String]$ConnectionString = 'Server=LocalHost\SQL;Database=Hotfixes;Trusted_Connection=True;'
)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $ConnectionString
$connection.Open()
$cmd = New-Object System.Data.SQLClient.SQLCommand
$cmd.Connection = $connection
$cmd.CommandText = $Command
Try{$cmd.ExecuteNonQuery()}
Catch{
If(!($_.Exception -match 'Violation of PRIMARY KEY constraint')){
$_.Exception | Out-file "$Mypath\error.log" -append
$command | Out-file "$Mypath\error.log" -append
}
}
$connection.Close()
}
Function Process-stuff($kb){
$template = 'http://support.microsoft.com/kb/{0}/en-us'
$Site = $template -f $kb
$data = Get-KBData $Site
[string]$KBID = $kb
[string]$Downloadable = $data.HotfixDownload
[string]$Exists = $data.Exists
[string]$AppliesTo = $data.AppliesTo
[string]$ShortText = $data.ShortText
[string]$LastReviewed = $Data.LastReview
$Columns = "KBID","Exist","ShortText","Download","AppliesTo","LastReviewed","Link"
$Values = "`'$KB`'","`'$Exists`'","`'$ShortText`'","`'$Downloadable`'","`'$AppliesTo`'","`'$LastReviewed`'","`'$Site`'"
$InsertCMD = "INSERT INTO hotfix_primary ($($Columns -join ',')) VALUES ($($Values -join ','));"
$UpdateCMD = "UPDATE hotfix_primary SET $($c = $Columns.count;(1..($c-1) | ForEach-Object{"$($Columns[$_]) = $($Values[$_])"}) -Join ',') WHERE KBID = `'$KB`';"
Try{Process-SQLCmd $InsertCMD #| out-null
}
Catch{
#If($data.Error -notmatch '404'){
Process-SQLCmd $UpdateCMD #| out-null
#}
}
}
}
Process{
$min = 1
$max = 4000000
#$articles = (4000000..1)
$articles = ($max..$min)
$articles | ForEach-Object{
Process-stuff $_
}
}
End{
}