Building a GitHub Dashboard using PowerShell, AzureStorageTable, AzureFunction, and PowerBI

Last week, I published a PowerShell Community Dashboard and today, I’m going to share the code and cover some of the learnings.The code is published as a module on the PowerShell Gallery.
Make sure you get v1.1 as I found an issue where if you’re not a member of the PowerShell Org on GitHub, you won’t have permission to query the members so I changed the code to accommodate that.You can install the module using:

install-module PSGitHubStats

(and it works on PowerShell Core 6.0 including Linux! I only tested it with alpha.15, though…)

Once installed, you can just run it manually:

PS C:\> Get-PSDownloadStats -publishedSinceDate 1-1-2017 -accessToken $accesstoken

Tag             Name                                                 OS      Distro    Count Published
---             ----                                                 --      ------    ----- ---------
v6.0.0-alpha.15 powershell-6.0.0-alpha.15.pkg                        MacOS   MacOS     1504  1/25/2017 7:25:52 PM
v6.0.0-alpha.15 powershell-6.0.0_alpha.15-1.el7.centos.x86_64.rpm    Linux   CentOS    436   1/25/2017 7:25:52 PM
v6.0.0-alpha.15 powershell_6.0.0-alpha.15-1ubuntu1.14.04.1_amd64.deb Linux   Ubuntu14  368   1/25/2017 7:25:52 PM
v6.0.0-alpha.15 powershell_6.0.0-alpha.15-1ubuntu1.16.04.1_amd64.deb Linux   Ubuntu16  951   1/25/2017 7:25:52 PM
v6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win10-win2k16-x64.msi      Windows Windows10 349   1/25/2017 7:25:52 PM
v6.0.0-alpha.15      Windows Windows10 70    1/25/2017 7:25:52 PM
v6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win7-win2k8r2-x64.msi      Windows Windows7  119   1/25/2017 7:25:52 PM
v6.0.0-alpha.15      Windows Windows7  34    1/25/2017 7:25:52 PM
v6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win7-x86.msi               Windows Windows7  192   1/25/2017 7:25:52 PM
v6.0.0-alpha.15               Windows Windows7  17    1/25/2017 7:25:52 PM
v6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win81-win2k12r2-x64.msi    Windows Windows8  74    1/25/2017 7:25:52 PM
v6.0.0-alpha.15    Windows Windows8  21    1/25/2017 7:25:52 PM

PS C:\> $contributors = Get-PSGitHubReport -startDate 1-1-2017 -repos powershell/powershell -accessToken $accesstoken
PS C:\> $contributors | ? {$_.Org -eq "Community"} | sort -Property Total -Top 10 -Descending

   Org: Community

Name             PRs               Issues           PR Comments      Issue Comments   Total            End Date
----             ---               ------           -----------      --------------   -----            --------
iSazonov         8                 4                44               46               102              2017-02-03 10...
vors             5                 4                12               6                27               2017-02-03 10...
thezim           0                 2                0                9                11               2017-02-03 10...
juneb            0                 4                0                4                8                2017-02-03 10...
Jaykul           0                 3                0                5                8                2017-02-03 10...
pcgeek86         0                 3                0                5                8                2017-02-03 10...
jeffbi           0                 0                0                6                6                2017-02-03 10...
MaximoTrinidad   0                 2                0                3                5                2017-02-03 10...
g8tguy           0                 0                0                5                5                2017-02-03 10...
mwallner         0                 1                0                3                4                2017-02-03 10...

The $accesstoken is something you would generate and needed because the number of queries I have to do against the GitHub API will likely exceed the unauthenticated rate limit.
I ran over the rate limit many times while generating my report, even as an authenticated user. I solved this by adding a sleep command to the report generation.

One thing you may notice with the module vs the dashboard is that you get the raw numbers rather than just the rankings.
On the dashboard, we decided to only show the rankings so that people don’t focus specifically on the numbers.

Get-PSDownloadStats should be pretty straight forward.
There is some specialized logic in that function to determine the target operating system for the release package which unfortunately depends on the filename.

Publishing to AzureTable is fairly simple once you figure out the magic sauce to provide in the headers to make sure you’re calling the appropriate version of the REST API:

if ($publishToAzure)
    $json = $pkg | ConvertTo-Json -Compress
    $date = [datetime]::UtcNow.ToString("R", [System.Globalization.CultureInfo]::InvariantCulture)
    [string] $canonicalizedResource = "/$storageAccount/$storageTable"
    $contentType = "application/json"
    [string] $stringToSign = "POST`n`n$contentType`n$date`n$canonicalizedResource"
    $headers = @{"Prefer"="return-no-content";"Authorization"=(CreateAuthHeader -canonicalizedString $stringToSign -storageAccount $storageAccount -storageKey $storageKey);
    $null = Invoke-RestMethod -Uri $storageUrl -Headers $headers -Body $json -Method Post -ContentType $contentType

I deliberately chose to use an AzureTable for a few reasons:

  • Power BI supports reading from AzureTable natively (although I think you can only do it from the Power BI desktop app) as I couldn’t find the option in the web interface
  • I didn’t need the relational capabilities nor the additional cost of AzureSQL for my purposes
  • I can import JSON directly into AzureTable

The most complicated part of working with AzureTable is correctly crafting the authentication header built from a canonicalized string AzureTable expects to protect against replay attacks.
The string is defined in the previous code section as $stringToSign while this bit of code hashes it and converts the result to Base64:

Function CreateAuthHeader([string]$canonicalizedString,[string]$storageAccount,[string]$storageKey)
    [string]$signature = [string]::Empty
    [byte[]]$bytes = [System.Convert]::FromBase64String($storageKey)
    [System.Security.Cryptography.HMACSHA256] $SHA256 = New-Object System.Security.Cryptography.HMACSHA256(,$bytes)
    [byte[]] $dataToSha256 = [System.Text.Encoding]::UTF8.GetBytes($canonicalizedString)
    $signature = [System.Convert]::ToBase64String($SHA256.ComputeHash($dataToSha256))
    "SharedKey $($storageAccount):$signature"

Ilya‘s RFC on Get-StringHash should help make this simpler and more readable eliminating several lines of code from that function.

Once I had the module working in the command line, I validated it was correctly uploaded to Azure using Microsoft Azure Storage Explorer. Now I needed to have the script run regularly. I considered both Azure Automation and Azure Functions and decided to use the latter as it was newer, which gave me an opportunity to learn it. One immediate problem I had is that Azure Functions today only supports PowerShell v4. I originally used PowerShell classes for my internal types and thus changed it all to PSCustomObjects.
I’ve since contacted the Azure Functions team and asked them to support both Windows PowerShell v5.x and PowerShell Core 6.0 in the future.

With Azure Functions, you really only have the ability to run a PowerShell script. This means that unless you install the Azure PowerShell module at runtime (and PowerShellGet isn’t part of PowerShell v4), you really can only use what is available with PowerShell. Azure Automation would be better suited if you want to use modules.

I just cut and pasted the code out of my module into the web interface and added a call to my functions at the end supplying all the necessary parameters to make sure I was getting the right data from GitHub, and uploading the data correctly into AzureTable. One thing to note is that you’ll see I pass -UseBasicParsing to all my Invoke-WebRequest calls as the ParsedHtml property in the output relies on MSHTML which relies on Internet Explorer.
IE is not available in the Azure Function container your script is running in. I should also mention that I use both Invoke-WebRequest and Invoke-RestMethod where the former is needed when I need to access the response headers specifically for pagination of the GitHub API response which is handled by this bit of code:

if ($null -ne $output.Headers.Link) {
    $links = $output.Headers.Link.Split(",").Trim()
    foreach ($link in $links) {
        if ($link -match "<(?<url>.*?)>;\srel=`"(?<rel>.*?)`"") {
            if ($matches.rel -eq 'next') {
                $query = $matches.url

I’ll be working to add this capability into Invoke-RestMethod so this bit of code can be removed.

Building the Power BI visualization is a whole other topic and @MSFTzachal really did most of the work, but my recommendation is to use the Power BI desktop app which I found easier and more powerful than the current web interface.

Steve Lee
Principal Software Engineer Manager
PowerShell Core