{"id":12905,"date":"2017-02-09T09:16:04","date_gmt":"2017-02-09T17:16:04","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/powershell\/?p=12905"},"modified":"2019-03-05T13:25:57","modified_gmt":"2019-03-05T21:25:57","slug":"building-a-github-dashboard-using-powershell-azurestoragetable-azurefunction-and-powerbi","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/powershell\/building-a-github-dashboard-using-powershell-azurestoragetable-azurefunction-and-powerbi\/","title":{"rendered":"Building a GitHub Dashboard using PowerShell, AzureStorageTable, AzureFunction, and PowerBI"},"content":{"rendered":"<div class=\"markdown-body\">Last week, I published a <a href=\"http:\/\/aka.ms\/psgithubbi\">PowerShell Community Dashboard<\/a> and today, I&#8217;m going to share the code and cover some of the learnings.The code is published as a <a href=\"https:\/\/www.powershellgallery.com\/packages\/PSGitHubStats\/\">module<\/a> on the PowerShell Gallery.\nMake sure you get v1.1 as I found an issue where if you&#8217;re not a member of the PowerShell Org on GitHub, you won&#8217;t have permission to query the members so I changed the code to accommodate that.You can install the module using:<\/p>\n<div class=\"highlight highlight-source-powershell\">\n<pre class=\"lang:default decode:true \">install-module PSGitHubStats<\/pre>\n<p>&nbsp;<\/p>\n<\/div>\n<p>(and it works on <a href=\"https:\/\/github.com\/powershell\/powershell\">PowerShell Core 6.0<\/a> including <a href=\"https:\/\/blogs.msdn.microsoft.com\/powershell\/2017\/02\/01\/installing-latest-powershell-core-6-0-release-on-linux-just-got-easier\/\">Linux<\/a>! I only tested it with alpha.15, though&#8230;)<\/p>\n<p>Once installed, you can just run it manually:<\/p>\n<div class=\"highlight highlight-source-powershell\">\n<pre class=\"lang:default decode:true\">PS C:\\&gt; Get-PSDownloadStats -publishedSinceDate 1-1-2017 -accessToken $accesstoken\r\n\r\nTag             Name                                                 OS      Distro    Count Published\r\n---             ----                                                 --      ------    ----- ---------\r\nv6.0.0-alpha.15 powershell-6.0.0-alpha.15.pkg                        MacOS   MacOS     1504  1\/25\/2017 7:25:52 PM\r\nv6.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\r\nv6.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\r\nv6.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\r\nv6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win10-win2k16-x64.msi      Windows Windows10 349   1\/25\/2017 7:25:52 PM\r\nv6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win10-win2k16-x64.zip      Windows Windows10 70    1\/25\/2017 7:25:52 PM\r\nv6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win7-win2k8r2-x64.msi      Windows Windows7  119   1\/25\/2017 7:25:52 PM\r\nv6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win7-win2k8r2-x64.zip      Windows Windows7  34    1\/25\/2017 7:25:52 PM\r\nv6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win7-x86.msi               Windows Windows7  192   1\/25\/2017 7:25:52 PM\r\nv6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win7-x86.zip               Windows Windows7  17    1\/25\/2017 7:25:52 PM\r\nv6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win81-win2k12r2-x64.msi    Windows Windows8  74    1\/25\/2017 7:25:52 PM\r\nv6.0.0-alpha.15 PowerShell_6.0.0-alpha.15-win81-win2k12r2-x64.zip    Windows Windows8  21    1\/25\/2017 7:25:52 PM\r\n\r\nPS C:\\&gt; $contributors = Get-PSGitHubReport -startDate 1-1-2017 -repos powershell\/powershell -accessToken $accesstoken\r\nPS C:\\&gt; $contributors | ? {$_.Org -eq \"Community\"} | sort -Property Total -Top 10 -Descending\r\n\r\n   Org: Community\r\n\r\nName             PRs               Issues           PR Comments      Issue Comments   Total            End Date\r\n----             ---               ------           -----------      --------------   -----            --------\r\niSazonov         8                 4                44               46               102              2017-02-03 10...\r\nvors             5                 4                12               6                27               2017-02-03 10...\r\nthezim           0                 2                0                9                11               2017-02-03 10...\r\njuneb            0                 4                0                4                8                2017-02-03 10...\r\nJaykul           0                 3                0                5                8                2017-02-03 10...\r\npcgeek86         0                 3                0                5                8                2017-02-03 10...\r\njeffbi           0                 0                0                6                6                2017-02-03 10...\r\nMaximoTrinidad   0                 2                0                3                5                2017-02-03 10...\r\ng8tguy           0                 0                0                5                5                2017-02-03 10...\r\nmwallner         0                 1                0                3                4                2017-02-03 10...\r\n<\/pre>\n<\/div>\n<p>The <code>$accesstoken<\/code> is something you would <a href=\"https:\/\/github.com\/settings\/tokens\">generate<\/a> and needed because the number of queries I have to do against the <a href=\"https:\/\/developer.github.com\/\">GitHub API<\/a> will likely exceed the unauthenticated <a href=\"https:\/\/developer.github.com\/v3\/rate_limit\/\">rate limit<\/a>.\nI ran over the rate limit many times while generating my report, even as an authenticated user. I solved this by adding a <code>sleep<\/code> command to the report generation.<\/p>\n<p>One thing you may notice with the module vs the dashboard is that you get the raw numbers rather than just the rankings.\nOn the dashboard, we decided to only show the rankings so that people don&#8217;t focus specifically on the numbers.<\/p>\n<p><code>Get-PSDownloadStats<\/code> should be pretty straight forward.\nThere is some specialized logic in that function to determine the target operating system for the release package which unfortunately depends on the filename.<\/p>\n<p>Publishing to AzureTable is fairly simple once you figure out the magic sauce to provide in the headers to make sure you&#8217;re calling the appropriate version of the REST API:<\/p>\n<div class=\"highlight highlight-source-powershell\">\n<pre class=\"lang:default decode:true \">if ($publishToAzure)\r\n{\r\n    $json = $pkg | ConvertTo-Json -Compress\r\n    $date = [datetime]::UtcNow.ToString(\"R\", [System.Globalization.CultureInfo]::InvariantCulture)\r\n    [string] $canonicalizedResource = \"\/$storageAccount\/$storageTable\"\r\n    $contentType = \"application\/json\"\r\n    [string] $stringToSign = \"POST`n`n$contentType`n$date`n$canonicalizedResource\"\r\n    $headers = @{\"Prefer\"=\"return-no-content\";\"Authorization\"=(CreateAuthHeader -canonicalizedString $stringToSign -storageAccount $storageAccount -storageKey $storageKey);\r\n        \"DataServiceVersion\"=\"3.0;NetFx\";\"MaxDataServiceVersion\"=\"3.0;NetFx\";\"Accept\"=\"application\/json;odata=nometadata\";\r\n        \"Accept-Charset\"=\"UTF-8\";\"x-ms-version\"=\"2013-08-15\";\"x-ms-date\"=$date}\r\n    $null = Invoke-RestMethod -Uri $storageUrl -Headers $headers -Body $json -Method Post -ContentType $contentType\r\n}<\/pre>\n<p>&nbsp;<\/p>\n<\/div>\n<p>I deliberately chose to use an AzureTable for a few reasons:<\/p>\n<ul>\n<li>Power BI supports reading from AzureTable natively (although I think you can only do it from the <a href=\"https:\/\/powerbi.microsoft.com\/en-us\/desktop\/\">Power BI desktop app<\/a>) as I couldn&#8217;t find the option in the web interface<\/li>\n<li>I didn&#8217;t need the relational capabilities nor the additional cost of AzureSQL for my purposes<\/li>\n<li>I can import JSON directly into AzureTable<\/li>\n<\/ul>\n<p>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.\nThe string is defined in the previous code section as <code>$stringToSign<\/code> while this bit of code hashes it and converts the result to Base64:<\/p>\n<div class=\"highlight highlight-source-powershell\">\n<pre class=\"lang:default decode:true \">Function CreateAuthHeader([string]$canonicalizedString,[string]$storageAccount,[string]$storageKey)\r\n{\r\n    [string]$signature = [string]::Empty\r\n    [byte[]]$bytes = [System.Convert]::FromBase64String($storageKey)\r\n    [System.Security.Cryptography.HMACSHA256] $SHA256 = New-Object System.Security.Cryptography.HMACSHA256(,$bytes)\r\n    [byte[]] $dataToSha256 = [System.Text.Encoding]::UTF8.GetBytes($canonicalizedString)\r\n    $signature = [System.Convert]::ToBase64String($SHA256.ComputeHash($dataToSha256))\r\n    \"SharedKey $($storageAccount):$signature\"\r\n}<\/pre>\n<p>&nbsp;<\/p>\n<\/div>\n<p><a href=\"https:\/\/github.com\/iSazonov\">Ilya<\/a>&#8216;s <a href=\"https:\/\/github.com\/powershell\/\">RFC on Get-StringHash<\/a> should help make this simpler and more readable eliminating several lines of code from that function.<\/p>\n<p>Once I had the module working in the command line, I validated it was correctly uploaded to Azure using <a href=\"http:\/\/storageexplorer.com\/\">Microsoft Azure Storage Explorer<\/a>. Now I needed to have the script run regularly. I considered both <a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/automation\/\">Azure Automation<\/a> and <a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/functions\">Azure Functions<\/a> 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 <a href=\"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2015\/08\/31\/introduction-to-powershell-5-classes\/\">PowerShell classes<\/a> for my internal types and thus changed it all to <a href=\"https:\/\/powershell.org\/2013\/04\/24\/pscustomobject-save-puppies-and-avoid-dead-ends\/\">PSCustomObjects<\/a>.\nI&#8217;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.<\/p>\n<p>With Azure Functions, you really only have the ability to run a PowerShell script. This means that unless you install the <a href=\"https:\/\/www.powershellgallery.com\/packages\/Azure\/\">Azure PowerShell module<\/a> at runtime (and <a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/dn835097.aspx\">PowerShellGet<\/a> isn&#8217;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.<\/p>\n<p>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&#8217;ll see I pass <code>-UseBasicParsing<\/code> to all my <code>Invoke-WebRequest<\/code> calls as the <code>ParsedHtml<\/code> property in the output relies on <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/aa741317(v=vs.85).aspx\">MSHTML<\/a> which relies on Internet Explorer.\nIE is not available in the Azure Function container your script is running in. I should also mention that I use both <code>Invoke-WebRequest<\/code> and <code>Invoke-RestMethod<\/code> 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:<\/p>\n<div class=\"highlight highlight-source-powershell\">\n<pre class=\"lang:default decode:true \">if ($null -ne $output.Headers.Link) {\r\n    $links = $output.Headers.Link.Split(\",\").Trim()\r\n    foreach ($link in $links) {\r\n        if ($link -match \"&lt;(?&lt;url&gt;.*?)&gt;;\\srel=`\"(?&lt;rel&gt;.*?)`\"\") {\r\n            if ($matches.rel -eq 'next') {\r\n                $query = $matches.url\r\n            }\r\n        }\r\n    }\r\n}<\/pre>\n<p>&nbsp;<\/p>\n<\/div>\n<p>I&#8217;ll be working to <a href=\"https:\/\/github.com\/PowerShell\/PowerShell\/issues\/3041\">add this capability<\/a> into <code>Invoke-RestMethod<\/code> so this bit of code can be removed.<\/p>\n<p>Building the Power BI visualization is a whole other topic and <a href=\"https:\/\/twitter.com\/MSFTzachal\">@MSFTzachal<\/a> 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.<\/p>\n<p>Steve Lee\nPrincipal Software Engineer Manager\nPowerShell Core<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Last week, I published a PowerShell Community Dashboard and today, I&#8217;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&#8217;re not a member of the PowerShell Org on GitHub, you [&hellip;]<\/p>\n","protected":false},"author":685,"featured_media":13641,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-12905","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-powershell"],"acf":[],"blog_post_summary":"<p>Last week, I published a PowerShell Community Dashboard and today, I&#8217;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&#8217;re not a member of the PowerShell Org on GitHub, you [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts\/12905","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/users\/685"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/comments?post=12905"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts\/12905\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/media\/13641"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/media?parent=12905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/categories?post=12905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/tags?post=12905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}