Scrape All Microsoft Connect Items marked Resolved or Closed
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # IE has to be used because of a refresh that Invoke-WebRequest can't handle (think Clicks) | |
| # Add required types | |
| Add-Type -Path C:\Scripts\Scheduled\ClosedAsFixed\HtmlAgilityPack.dll | |
| Add-Type -AssemblyName System.Web | |
| Function Get-Total ($closedorresolved, $bugorsuggestion){ | |
| switch ($closedorresolved) { | |
| "Closed" { $status = '3' } | |
| "Resolved" { $status = '2' } | |
| } | |
| switch ($bugorsuggestion) { | |
| "Bug" { $tabbiew = '0' } | |
| "Suggestion" { $tabbiew = '1' } | |
| } | |
| $url = "https://connect.microsoft.com/SQLServer/SearchResults.aspx?FeedbackType=0&Status=$status&Scope=0&SortOrder=40&TabView=$tabbiew" | |
| $html = Invoke-WebRequest -UseBasicParsing -Uri $url | |
| $null = $doc.LoadHtml($html.Content) | |
| if ($bugorsuggestion -eq "Bug") { | |
| $total = ($doc.DocumentNode.SelectNodes('//*[@id="ctl00_MasterBody_BugPagingControlTop_Summary"]').InnerText -Split "`n")[4].Trim() | |
| } else { | |
| $total = ($doc.DocumentNode.SelectNodes('//*[@id="ctl00_MasterBody_SuggestionPagingControlTop_Summary"]').InnerText -Split "`n")[4].Trim() | |
| } | |
| return $total | |
| } | |
| Function Parse-ConnectPages ($closedorresolved, $bugorsuggestion) { | |
| switch ($closedorresolved) { | |
| "Closed" { $status = '3' } | |
| "Resolved" { $status = '2' } | |
| } | |
| switch ($bugorsuggestion) { | |
| "Bug" { $tabbiew = '0' } | |
| "Suggestion" { $tabbiew = '1' } | |
| } | |
| $total = Get-Total $closedorresolved $bugorsuggestion | |
| $totalTimes = ($total / 10) | |
| $url = "https://connect.microsoft.com/SQLServer/SearchResults.aspx?FeedbackType=0&Status=$status&Scope=0&SortOrder=40&TabView=$tabbiew#&&PageIndex=" | |
| Write-Output "Doing this a total of $totaltimes times for $total items in $closedorresolved $bugorsuggestion" | |
| 1..$totalTimes | ForEach-Object { | |
| $currentpage = $_ | |
| $currenturl = "$url" + "$currentpage" | |
| $ie.navigate($currenturl) | |
| While ($ie.Busy) { Start-Sleep 1 } | |
| Start-Sleep 4 | |
| $body = $ie.Document.documentElement.outerHTML | |
| $null = $doc.LoadHtml($body) | |
| switch ($bugorsuggestion) { | |
| "Bug" { Parse-BugPage $doc $closedorresolved } | |
| "Suggestion" { Parse-SuggestionPage $doc $closedorresolved } | |
| } | |
| } | |
| } | |
| Function Parse-BugPage ($doc, $status){ | |
| $items =@() | |
| $count = $doc.DocumentNode.SelectNodes('//*[@id="ctl00_MasterBody_BugPagingControlTop_NumberOfItems"]').InnerText | |
| Write-Output "On item number: $count" | |
| 1..$count | ForEach-Object { | |
| if ($_ -lt 10) { $num = "0$_" } else { $num = "$_" } | |
| $basepath = "//*[@id='ctl00_MasterBody_BugsSearchResultsView_ctl$($num)_BugsResultModule" | |
| $xpath = "$($basepath)_FeedbackStatus_ResolutionLabel']" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $resolution = $node.InnerText | |
| if ($resolution -eq 'as Fixed') { | |
| # feedbackid | |
| $xpath = "$($basepath)_FeedbackItemDetailsUpdatePanel']/div/span[5]" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $feedbackid = $node.InnerText.TrimStart("feedback id: ") | |
| $link = "http://connect.microsoft.com/SQLServer/feedback/details/$feedbackid/" | |
| # Title | |
| $xpath = "$($basepath)_FeedbackLink']" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $title = $node.InnerText | |
| $title = [System.Web.HttpUtility]::HtmlDecode($title) | |
| # Author | |
| $xpath = "$($basepath)_NewFeedbackAuthor']" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $author = $node.InnerText | |
| # Created and a bunch of other metadata | |
| $xpath = "$($basepath)_FeedbackItemDetailsUpdatePanel']/div" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| # created | |
| $xpath = "$($basepath)_FeedbackItemDetailsUpdatePanel']/div/span[1]" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $created = (($node.FirstChild.InnerText -Split " ")[0]).TrimStart("Created on ") | |
| # closed | |
| try { | |
| $closed = $node.FirstChild.InnerText | |
| $closed = $closed -Split '\;\(' | |
| $closed = $closed[1] | |
| $closed = $closed.Replace("ago)","") | |
| $closed = $closed.Replace("updated ","") | |
| if ($closed -match "weeks") { | |
| $daysago = ($closed.Replace(" weeks","")) | |
| $daysago = ($daysago.Replace(" week","")) | |
| $daysago = [int]$daysago * 7 | |
| } elseif ($closed -match "days") { | |
| $daysago = $closed.Replace(" days","") | |
| $daysago = $daysago.Replace(" day","") | |
| $daysago = [int]$daysago | |
| } else { | |
| $daysago = 0 | |
| } | |
| $closed = $(Get-Date).AddDays(-$daysago).ToString('MM/dd/yyyy') | |
| } catch { $closed = $node.FirstChild.InnerText } | |
| # Votes | |
| $xpath = "$($basepath)_FeedbackItemVotingControlForVoting_SimpleVote_ctl02_text']" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $votes = $node.InnerText | |
| # Create tweet | |
| $tweet = "$title - $author" | |
| if ($tweet.length -gt 119) { | |
| $tweet = $tweet.Substring(0,113) + "..." | |
| } | |
| $tweet = "$tweet $link" | |
| $tweet = $tweet.Replace("`&`#39;","") | |
| $exists = $items | Where-Object { $_.id -eq $feedbackid } | |
| if ($exists.count -eq 0) { | |
| # add item to collection | |
| $items += [PSCustomObject]@{ | |
| Link = $link | |
| Title = $title | |
| ID = $feedbackid | |
| Author = $author | |
| Created = $created | |
| Closed = $closed | |
| Votes = $votes | |
| ItemType = "Bug" | |
| Status = $status | |
| } | |
| } | |
| } | |
| } | |
| $items | Select-Object Link, Title, ID, Author, Created, Closed, Votes, ItemType, Status | Export-Csv -Path C:\Scripts\Scheduled\ClosedAsFixed\bug-$status.csv -Append -NoTypeInformation | |
| } | |
| Function Parse-SuggestionPage ($doc, $status) { | |
| $items = @() | |
| $count = $doc.DocumentNode.SelectNodes('//*[@id="ctl00_MasterBody_SuggestionPagingControlTop_NumberOfItems"]').InnerText | |
| Write-Output "On item number: $count" | |
| 1..$count | ForEach-Object { | |
| if ($_ -lt 10) { $num = "0$_" } else { $num = "$_" } | |
| $basepath = "//*[@id='ctl00_MasterBody_SuggestionsSearchResultsView_ctl$($num)_SuggestionsResultModule" | |
| $xpath = "$($basepath)_FeedbackStatus_ResolutionLabel']" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $resolution = $node.InnerText | |
| if ($resolution -eq 'as Fixed') { | |
| # feedbackid | |
| $xpath = "$($basepath)_FeedbackItemDetailsUpdatePanel']/div/span[4]" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $feedbackid = $node.InnerText.TrimStart("feedback id: ") | |
| $link = "http://connect.microsoft.com/SQLServer/feedback/details/$feedbackid/" #$link = $node.Attributes[1].Value | |
| # Title | |
| $xpath = "$($basepath)_FeedbackLink']" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $title = $node.InnerText | |
| $title = [System.Web.HttpUtility]::HtmlDecode($title) | |
| # Author | |
| $xpath = "$($basepath)_NewFeedbackAuthor']" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $author = $node.InnerText | |
| switch ($author) { | |
| "AaronBertrand" { $author = "@AaronBertrand" } | |
| "Chrissy LeMaire" { $author = "@cl" } | |
| "SQLvariant" { $author = "@SQLvariant" } | |
| } | |
| # Created and a bunch of other metadata | |
| $xpath = "$($basepath)_FeedbackItemDetailsUpdatePanel']/div" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| # created | |
| $xpath = "$($basepath)_FeedbackItemDetailsUpdatePanel']/div/span[1]" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $created = (($node.FirstChild.InnerText -Split " ")[0]).TrimStart("Created on ") | |
| # closed | |
| try { | |
| $closed = $node.FirstChild.InnerText | |
| $closed = $closed -Split '\;\(' | |
| $closed = $closed[1] | |
| $closed = $closed.Replace("ago)","") | |
| $closed = $closed.Replace("updated ","") | |
| if ($closed -match "weeks") { | |
| $daysago = ($closed.Replace(" weeks","")) | |
| $daysago = ($daysago.Replace(" week","")) | |
| $daysago = [int]$daysago * 7 | |
| } elseif ($closed -match "days") { | |
| $daysago = $closed.Replace(" days","") | |
| $daysago = $daysago.Replace(" day","") | |
| $daysago = [int]$daysago | |
| } else { | |
| $daysago = 0 | |
| } | |
| $closed = $(Get-Date).AddDays(-$daysago).ToString('MM/dd/yyyy') | |
| } catch { $closed = $node.FirstChild.InnerText } | |
| # Votes | |
| $xpath = "$($basepath)_FeedbackItemVotingControlForVoting_SimpleVote_ctl02_text']" | |
| $node = $doc.DocumentNode.SelectNodes($xpath) | |
| $votes = $node.InnerText | |
| $exists = $items | Where-Object { $_.id -eq $feedbackid } | |
| if ($exists.count -eq 0) { | |
| # Create tweet | |
| $tweet = "$title - $author" | |
| if ($tweet.length -gt 119) { | |
| $tweet = $tweet.Substring(0,113) + "..." | |
| } | |
| $tweet = "$tweet $link" | |
| $tweet = $tweet.Replace("`&`#39;","") | |
| # add item to collection | |
| $items += [PSCustomObject]@{ | |
| Link = $link | |
| Title = $title | |
| ID = $feedbackid | |
| Author = $author | |
| Created = $created | |
| Closed = $closed | |
| Votes = $votes | |
| ItemType = "Suggestion" | |
| Status = $status | |
| } | |
| } | |
| } | |
| } | |
| $items | Select-Object Link, Title, ID, Author, Created, Closed, Votes, ItemType, Status | Export-Csv -Path C:\Scripts\Scheduled\ClosedAsFixed\suggestion-$status.csv -Append -NoTypeInformation | |
| } | |
| # Start Parsers | |
| $doc = New-Object HtmlAgilityPack.HtmlDocument | |
| $ie = New-Object -Com "InternetExplorer.Application" | |
| $ie.visible = $true | |
| # Prep CSV | |
| Remove-Item *.csv -Force -ErrorAction Ignore | |
| # Do it | |
| Parse-ConnectPages "Resolved" "Suggestion" | |
| Parse-ConnectPages "Resolved" "Bug" | |
| Parse-ConnectPages "Closed" "Suggestion" | |
| Parse-ConnectPages "Closed" "Bug" | |
| # Clean up | |
| Get-Process iexplore | Stop-Process -Force |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment