[Powershell] Grabbing and parsing the RSS feed for new SQL Server Releases

(Updated 2019/04/18 - MS changed the site, moved to TLS 1.2)

My boss asked me to scrape a web site for release/security info for SQL Server.  Okay.

First attempt required a stop at stackoverflow, where I was reminded about hashtables (Thanks again to Grainer!)  And after several hours, I had found the correct Microsoft blog for security updates and had this:

cls
$posts= @() # this basically resets the posts for multiple runs - broke my ISE and good before

[xml]$rss = Invoke-WebRequest http://technet.microsoft.com/en-us/security/rss/comprehensive

$rss.SelectNodes('//item') | % {
    $posts += New-Object psobject -Property @{
        Title = $_.Title.Trim()
        Thedate = [datetime]$_.date.Trim()
        Guid = $_.Guid.Trim()
        Content = $_.Encoded."#cdata-section".Trim()
    }
}

$posts | select-object # | where-object {$_.Title -like '*sql*' -or $_.Guid -like '*sql*'}
#and then insert into a table

Awesome!  I feel good - mission accomplished.  Worked on it at home (yes, nerd here, big shock).
Then the boss looks and says "no, I didn't want that, I wanted the patches and CUs".

Crap.  So let's go find a different blog.  Oh yeah, I have one specifically for SP/CUs!  (In google reader, naturally...RIP.  OT: go to Newsblur and CommaFeed!)

Fortunately, this blog is much easier to parse, so I don't need the hashtable.  Well, not a massive hashtable.  The second one below is specifically to rename the "Description" field so that I don't have a field that's a semi-reserved word. The first one below is to reformat it as a datetime.  Use Chad Miller's awesome scripts to turn it into a datatable and write to disk, and voila!  (Alternatively, you should be able to use my Extensible Powershell Repository, elsewhere on this blog, and just have a 2-line PS script that is invoked via the EPR.

Now, I just have to use that with a MERGE statement to add only the new records, and email me when a new record comes in. 

# CREATE TABLE SQL_Server_Releases_from_RSS
#(
# title varchar(200),
# link varchar(500),
# pubdate smalldatetime,
# descript varchar(MAX) --I really don't want a field named "description"
#)
# Scripts from http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx
. C:\sql_tools\out-datatable.ps1  #Chad Miller rocks!
. C:\sql_tools\write-datatable.ps1
. C:\sql_tools\add-sqltable.ps1


#the next line, fixing the TLS ("could not create SSL/TLS secure channel") fixed by Chandan Rai on https://stackoverflow.com/questions/41618766/powershell-invoke-webrequest-fails-with-ssl-tls-secure-channel

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

#web page changed by MS, yet again, around 2019/04/15. Also moved to TLS 1.2.

[xml]$rss = Invoke-WebRequest https://techcommunity.microsoft.com/gxcuf89792/rss/board?board.id=SQLServer -UseBasicParsing  #was http://blogs.msdn.com/sqlreleaseservices/rss.xml, changed by mdb on 2018/03/19

$datatable = $rss.rss.channel.item | select title, link, @{Name="pubdate";Expression={get-date ($_.date) -format "yyyy/MM/dd hh:mm"}}, @{Name="descript";Expression={$_."Description"}} |out-datatable

write-datatable -ServerInstance "sql_repository" -Database myrepository -TableName SQL_Server_Releases_from_RSS -data $datatable