(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
