Last active
February 24, 2021 15:22
-
-
Save potatoqualitee/eb3a96ad18a83eacd45e4c6154625fcb to your computer and use it in GitHub Desktop.
Scrape All Microsoft Connect Items marked Resolved or Closed
This file contains hidden or 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