Use Jupyter Notebooks to Help People on StackOverFlow

I am sat in the PowerShell Saturday in Hamburg. You can see me on the right of this picture writing my previous blog post!

I was talking with my friend Mathias Jessen @IISResetMe on Twitter about notebooks and he said that another great use case was to use them on Stack OverFlow

Now Mathias is an active answerer on Stack OverFlow

and he puts a lot of effort into writing his answers, formatting them, including code and results. Basically exactly the same as a Notebook. However, with a Notebook, you can enable people to run the code as well on their own machines.

Mathias says he will use notebooks to help people when he answers their PowerShell questions on Stack OverFlow. If you are a Stack OverFlow Answerer then you can too.

.NET PowerShell Notebooks – Using Pester

My last post had a lot of information about the new .NET PowerShell notebooks including installation instructions.

.NET Notebooks are Jupyter Notebooks that use .NET core to enable C#, F# and PowerShell kernels.

Use Cases

One of the main benefits that I see for Jupyter Notebooks for Ops folk is that the results of the query are saved with the notebook. This makes them fantastic for Incident resolution.

If you have an incident at 3am and you know that you will need that information in the wash up meeting the next day instead of copying and pasting results into a OneNote document or a text file, you can simply run the queries in a notebook and save it.

In the meeting, you can simply open the notebook and the results will be available for everyone to see.

Even better, if you have a template notebook for those scenarios and you can then compare them to previous occurrences.

Using Pester

Using Pester to validate that an environment is as you expect it is a good resource for incident resolution, potentially enabling you to quickly establish an area to concentrate on for the issue. However, if you try to run Pester in a .NET Notebook you will receive an error

Describe: 
Line |
   3 | Describe "Checking Problem ...... by $($ENV:USERDOMAIN) $($ENV:UserName)" {

     | ^ The 'Describe' command was found in the module 'Pester', but the module could not be loaded. For more information, run 'Import-Module Pester'.

Fixing it

When you try to Import-Module Pester you get the following error

Get-Command: C:\Users\mrrob\Documents\PowerShell\Modules\Pester\4.9.0\Pester.psm1
Line |
  94 |     $script:SafeCommands['Get-CimInstance'] = Get-Command -Name Get-CimInstance -Module CimCmdlets @safeCommandLookupParameters

     |                                               ^ The term 'Get-CimInstance' is not recognized as the name of a
     | cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included,
     | verify that the path is correct and try again.
 Import-Module: The module to process 'Pester.psm1', listed in field 'ModuleToProcess/RootModule' of module manifest 'C:\Users\mrrob\Documents\PowerShell\Modules\Pester\4.9.0\Pester.psd1' was not processed because no valid module was found in any module directory. 

Thats odd, why is it failing there? Dongbo Wang from the PowerShell team explains in the issue that I raised

Yes, it was the CimCmdlets module from the system32 module path that got imported (via the WinCompat feature added in PS7). This is because currently the PS kernel don’t ship all the built-in modules along with it …
The built-in modules are not published anywhere and are platform specific, it’s hard for an application that host powershell to ship them along. We have the issue PowerShell/PowerShell#11783 to track this work.

The way to resolve this is to Import the CimCmdlets Module from your local PowerShell 7 installation until the issue is resolved

Import-Module 'C:\program files\powershell\7-preview\Modules\CimCmdlets\CimCmdlets.psd1'

Then you can run your Pester

You can see all of this including all the results in this notebook that I have created and shared on Github and also below as a gist to embed in this blogpost

Sharing Code AND Results 🙂


Notebooks – A brilliant way of sharing what you did and the results that you got enabling others to follow along. You can do this with this Notebook. Download it and open it in your Jupyter Lab and you will be able to run it and see all of the errors and the fix on your machine.

New .NET Notebooks are here – PowerShell 7 notebooks are here.

Data Science folk used Notebooks for documentation and to show re-runnable research. Azure Data Studio included this notebook functionality and added SQL kernel where with a little bit of faffing you could run PowerShell and then a Python kernel that enabled PowerShell. It seems that notebooks are so cool that everyone is creating them these days! I was browsing twitter when I saw this tweet.

PowerShell 7 Notebooks 🙂

A notebook experience for PowerShell 7 that sounds amazing. This will enable a true cross-platform PowerShell Notebook experience which is lacking from the Python version as it uses Windows PowerShell on Windows and PowerShell Core on other OS’s

The first thing I asked was – Will this come to Azure Data Studio. I got an immediate response from Sydney Smith PowerShell Program Manager saying it is on the roadmap

Install dependencies

To be able to run the notebook, you need to install some dependencies. First install the .NET CORE SDK which you can download from https://dotnet.microsoft.com/download This needs admin permissions to install.

You also need a Python installation – You can use Anaconda, which you can download from here https://www.anaconda.com/distribution/ This does not need admin to install

Add Anaconda to Windows Terminal

I have added the Anaconda prompt to Windows Terminal so that I have one entry point for all my CLIs. Open the settings file and add the code below. (It will also give you an icon and background.

        {
            // Make changes here to the Anaconda.exe profile
            "guid": "{0caa0dad-35be-5f56-a7ff-afceeeaa6101}",
            "name": "Anaconda",
            "commandline": "cmd.exe /K C:\\Users\\mrrob\\Anaconda3\\Scripts\\activate.bat",
            "hidden": false,
            "backgroundImage": "C:\\Users\\mrrob\\Anaconda3\\Menu\\anaconda-navigator.ico",
            "icon": "C:\\Users\\mrrob\\Anaconda3\\Menu\\anaconda-navigator.ico",
            "backgroundImageAlignment": "topRight",
            "backgroundImageStretchMode": "uniform",
            "backgroundImageOpacity": 0.1
        }

and it appears in the drop down

With Anaconda installed, check that that the kernel is available on your path. If like me you have Azure Data Studio installed, you will have additional kernels but the important one line here is

python3 C:\Users\USERNAME\Anaconda3\share\jupyter\kernels\python3

In Windows Terminal move to a PowerShell 7 prompt and install the dotnet interactive tool

dotnet tool install --global Microsoft.dotnet-interactive

Then you can install the .NET kernel in your Anaconda prompt using this command

dotnet interactive jupyter install

Sometimes new things have errors

I had an error when I tried this first time

Could not execute because the specified command or file was not found.
Possible reasons for this include:
* You misspelled a built-in dotnet command.
* You intended to execute a .NET Core program, but dotnet-interactive does not exist.
* You intended to run a global tool, but a dotnet-prefixed executable with this name could not be found on the PATH.

This is easily fixed by adding %USERPROFILE%\.dotnet\tools to my path with set PATH=%PATH%;%USERPROFILE%\.dotnet\tools

Running jupyter kernelspec list shows that the .NET kernel is installed for C Sharp, F Sharp and .NET PowerShell

Lets open a Notebook

Now you want to play with it!
You can run the lab environment using `jupyter lab`

This opens a browser

You can open existing Azure Data Studio PowerShell notebooks (but not SQL ones)

Sometimes new things have errors Part 2

Unfortunately, I get errors when trying to import Pester which means I can not use my dbachecks notebooks in this blog post. I have raised an issue on the repo here.

Create a New Notebook

But it is easy to create a new Notebook

In the launcher page click the .NET PowerShell button


Which will open a new Notebook in the directory that you launched the lab from. You can then add Code or Markdown as I have described before here.

Then you can add code, markdown and images to create your notebook.

Once you have finished using the notebook lab, you can shut it down in the Anaconda prompt with CTRL + C

Here is a video of running a notebook which anyone can use to create a couple of Docker containers running SQL 2019 and query them with dbatools. You can find the notebook further down this post.

Sharing Notebooks

You can create notebooks to run common tasks. Even better, from the lab you can convert the notebook including the results to a variety of formats to share with other none-technical people. I used this functionality this week to export Azure Data Studio Notebooks to HTML and PDF for a Project manager 🙂

You can find the Export Notebook command in the File menu

Exporting to HTML did not export the images but it does include the results

You can share notebooks via GitHub – Either in a gist like this

or by providing a straight link to the notebook in GitHub https://github.com/SQLDBAWithABeard/Notebooks/blob/master/notebooks/Exploring%20dbatools.ipynb

You can also use Binder https://mybinder.org/

This uses Docker to create an interactive Notebook. Create a Github repo like https://github.com/SQLDBAWithABeard/Notebooks (or just clone it) Copy your notebooks into the notebooks folder and push the changes to Github and then go to https://mybinder.org/ and add your URL to the repository.

You can see what it looks like by clicking the button below which Binder creates for you

Unfortunately the kernel only supports Python for the moment but you can see the possibilities 🙂

Fixing the ‘Failed to generate the compressed file for module ‘C:\Program Files\dotnet\dotnet.exe’ error when deploying to the PowerShell Gallery using Azure DevOps

The PowerShell module for validating your SQL Server estate dbachecks is deployed via Azure DevOps, you can see how it is working (or not) via this link

Grrr Automation for the Lose!

Until recently, this had worked successfully. In the last few weeks I have been receiving errors

 Exception : Microsoft.PowerShell.Commands.WriteErrorException: Failed to generate the compressed file for module 'C:\Program Files\dotnet\dotnet.exe failed to pack: error 
 C:\Program Files\dotnet\sdk\3.0.100\Sdks\NuGet.Build.Tasks.Pack\build\NuGet.Build.Tasks.Pack.targets(198,5): error : 
2 Index was outside the bounds of the array. 
 [C:\Users\VssAdministrator\AppData\Local\Temp\cbc14ba6-5832-46fd-be89-04bb552a83ac\Temp.csproj]
'.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\2.2.1\PSModule.psm1:10944 char:17
20       Publish-PSArtifactUtility @PublishPSArtifactUtility_Param ...
              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [Write-Error], WriteErrorException
2019-11-25T22:44:46.8459493Z     + FullyQualifiedErrorId : FailedToCreateCompressedModule,Publish-PSArtifactUtility

You can see these errors in the release pipeline logs here

Confusion

This was very frustrating as it was stopping the continuous delivery to the PowerShell Gallery. It was even more confusing as I was successfully deploying the ADSNotebook module to the gallery using the same method as you can see here.

Raise an Issue on GitHub

I went and looked at the PowerShellGet GitHub repository and opened an issue I also found another issue regarding Required Modules

But this doesnt help to get dbachecks released.

Just Try to Make it Work

I asked the wonderful folk in the PowerShell Slack channel – Through the magic of automation, you can also interact with them via the powershellhelp channel in the SQL Server Slack as well but there were no answers that could assist.

So I had to go searching for an answer. PowerShellGet uses nuget for package management. I found that if I downloaded an earlier version and placed it in my user profile (in the right location) I could publish the module.

I found this out by removing the nuget.exe from anywhere useful on the machine and trying to publish the module. The error message says

NuGet.exe upgrade is required to continue
This version of PowerShellGet requires minimum version '4.1.0' of NuGet.exe to publish an item to the NuGet-based repositories. NuGet.exe must be available in 
'C:\ProgramData\Microsoft\Windows\PowerShell\PowerShellGet\' or 'C:\Users\BeardyMcBeardFace\AppData\Local\Microsoft\Windows\PowerShell\PowerShellGet\', or under 
one of the paths specified in PATH environment variable value. NuGet.exe can be downloaded from https://aka.ms/psget-nugetexe. For more information, see 
https://aka.ms/installing-powershellget . Do you want PowerShellGet to upgrade to the latest version of NuGet.exe now?

If I said yes then I got the latest version and the error continued.

However, on my laptop I can go to the nuget downloads page and download an earlier version and place it in one of those paths then I could publish the module.

Can I Automate it?

I would rather not have to deploy manually though, and as I use hosted agents my access to the operating system is limited so I wondered if I could place the nuget.exe in the user profile and it would get used or if it would look for the the latest one. Turns out it uses the one in the user profile 🙂

So now I have this code as a step in my Azure DevOps Release pipeline before calling Publish-Module and we have automated the releases again.

and now deployments to the PowerShell Gallery are just triggered by the build and the pipeline is green again 🙂

Dynamically Creating Azure Data Studio Notebooks with PowerShell for an Incident Response Index Notebook

Now that Azure Data Studio has PowerShell Notebooks and there is a PowerShell Module for creating notebooks. I have been asked, more than once, what is the point? What is the use case? How does this help. I hope that this post will spark some ideas of one particular use-case.

I showed my silly example PowerShell code to create a PowerShell Notebook that created a PowerShell Notebook to my good friend Nick.

Nick is a fantastic, clever DBA who isn’t active on social media, which is a great shame as if he had time to share some of his fantastic work we would all benefit. He looked at that code and less than an hour later, came back to me with this code and idea which I have replicated here with his permission.

Thanks Nick.

The Use Case

The use case that Nick has is that he is converting some troubleshooting runbooks from their original locations (you know the sort of places – Sharepoint Docs, OneNote Notebooks, Shared Folders, the desktop of the Bastion Host) into a single repository of Azure Data Studio SQL or PowerShell Notebooks.

The idea is to have a single entry point into the troubleshooting steps and for the on-call DBA to create a Notebook from a template for the issue at hand which could be attached to an incident in the incident management solution. I suppose you could call it an Index Notebook.

Work Flow

When the DBA (or another team) opens this Notebook, they can choose the task that they are going to perform and click the link which will

  • copy the Notebook to the local machine
  • Rename the Notebook with the username and date
  • Open it ready for the work.

Once the work has been completed, the DBA can then attach the Notebook to the task or incident that has been created or use it in the Wash-Up/ Post Incident meeting.

This ensures that the original template notebook stays intact and unchanged and it is easy (which is always good when you are called out at 3am!) to create a uniquely named notebook .

Azure DevOps

Nick has placed this code into the deploy step in Azure DevOps which will deploy the template Notebooks from source control into the common folder and then this code will dynamically create the index Notebook each time there is a release.

Whilst the initial use case is incident response, this could easily be adapted for Notebooks used for Common Tasks or Run Books.

Notebooks

There are a number of Notebooks for different issue stored in directories. For this post, I have used the Notebooks from Microsoft that explain SQL 2019 features and troubleshooting which you can find in their GitHub repositories by following this link

The Azure DevOps deploys the Notebooks to a directory which then looks something like this

Some directories of Notebooks in a directory

Create an Index Notebook

Here is the code to create an index Notebook

This creates a Notebook in the root of the folder. It also uses the new -Collapse parameter in New-AdsNoteBookCell that creates the code blocks with the code collapsed so that it looks neater. The index Notebook looks like this in the root of the folder

Three O’Clock in the Morning

It’s 3am and I have been called out. I can open up the Index Notebook, find the set of queries I want to run and click the run button.

A new workbook opens up, named with my name and the time and I can get to work 🙂 I think it’s neat.

Here’s a video

Thanks Nick.

Maybe you can find him at SQL Bits next year. Did you know that SQL Bits 2020 was announced?

Check out https://sqlbits.com for more details

Create a PowerShell Notebook for Azure Data Studio with PowerShell

The latest update to the ADSNotebook PowerShell module I blogged about here now enables the creation of PowerShell notebooks with PowerShell.

You can install the module with

Install-Module ADSNotebook

or if you have already installed it you can use

Update-Module ADSNotebook

In the latest release, there is an extra parameter for New-AdsWorkBook of -Type which will accept either SQL or PowerShell

Create a PowerShell Notebook with PowerShell Rob

OK!

Here is some code to create a PowerShell Notebook. First we will create some cells using New-AdsWorkBookCell including all the markdown to add images and links. You can find my notebooks which explain how to write the markdown for your notebooks in my GitHub Presentations Repository

$introCelltext = "# Welcome to my Auto Generated PowerShell Notebook

## dbatools
![image](https://user-images.githubusercontent.com/6729780/68845538-7afcd200-06c3-11ea-952e-e4fe72a68fc8.png)  

dbatools is an open-source PowerShell Module for administering SQL Servers.
You can read more about dbatools and find the documentation at [dbatools.io](dbatools.io)
"
$SecondCelltext = "### Installation
You can install dbatools from the PowerShell Gallery using `Install-Module dbatools`
"

$thirdcelltext = "Install-Module dbatools"

$fourthCelltext = "### Getting Help
You should always use `Get-Help` to fins out how to use dbatools (and any PowerShell) commands"

$fifthcelltext = "Get-Help Get-DbaDatabase"
$sixthCelltext = "Try a command now. get the name, owner and collation of the user databases on the local instance"
$seventhCellText = "Get-DbaDatabase -SqlInstance localhost -ExcludeSystem | Select Name, Owner, Collation"

$Intro = New-ADSWorkBookCell -Type Text -Text $introCelltext
$second = New-ADSWorkBookCell -Type Text -Text $SecondCelltext
$third = New-ADSWorkBookCell -Type Code -Text $thirdcelltext
$fourth = New-ADSWorkBookCell -Type Text -Text $fourthCelltext
$fifth = New-ADSWorkBookCell -Type Code -Text $fifthcelltext
$sixth = New-ADSWorkBookCell -Type Text -Text $sixthCelltext
$seventh = New-ADSWorkBookCell -Type Code -Text $seventhCellText

Then we will create a new workbook using those cells

$path = 'C:\temp\dbatools.ipynb'
New-ADSWorkBook -Path $path -cells $Intro,$second,$third,$fourth,$fifth,$sixth,$Seventh -Type PowerShell

Then, when that code is run we can open the Notebook and ta-da

And it is super quick to run as well

UPDATE – Tyler Leonhardt t from the PowerShell team asked

Challenge accepted, with extra meta, here is the PowerShell to create a PowerShell Notebook which will create a PowerShell Notebook!!

Create Azure Data Studio SQL Notebooks with PowerShell

At PASS Summit today I gave a presentation about SQL Notebooks in Azure Data Studio for the DBA. I demo’d the PowerShell module ADSSQLNotebook.

which you can also find on GitHub (where I will be glad to take PR’s to improve it 🙂 )

This module has 3 functions

This module contains only 3 commands at present

  • Convert-ADSPowerShellForMarkdown

This will create the markdown link for embedding PowerShell code in a Text Cell for a SQL Notebook as described in this blog post

  • New-ADSWorkBookCell

This command will create a workbook text cell or a code cell for adding to the New-ADSWorkBook command

  • New-ADSWorkBook

This will create a new SQL Notebook using the cell objects created by New-ADSWorkBookCell

Usage

Convert-ADSPowerShellForMarkdown

Convert-ADSPowerShellForMarkdown -InputText "Get-ChildItem" -LinkText 'This will list the files' -ToClipBoard

Converts the PowerShell so that it works with MarkDown and sets it to the clipboard for pasting into a workbook cell

New-ADSWorkBookCell

$introCelltext = "# Welcome to my Auto Generated Notebook

## Automation
Using this we can automate the creation of notebooks for our use
"
$Intro = New-ADSWorkBookCell -Type Text -Text $introCelltext

Creates an Azure Data Studio Text cell and sets it to a variable for passing to  New-AdsWorkBook

New-ADSWorkBook

$introCelltext = "# Welcome to my Auto     Generated Notebook

## Automation
Using this we can automate the creation of notebooks for our use
"
$SecondCelltext = "## Running code
The next cell will have some code in it for running

## Server Principals
Below is the code to run against your     instance to find the server principals that are enabled"

$thirdcelltext = "SELECT Name
FROM sys.server_principals
WHERE is_disabled = 0"
$Intro = New-ADSWorkBookCell -Type Text -Text $introCelltext
$second = New-ADSWorkBookCell -Type Text  -Text $SecondCelltext
$third = New-ADSWorkBookCell -Type Code -Text $thirdcelltext

$path = 'C:\temp\AutoGenerated.ipynb'
New-ADSWorkBook -Path $path -cells $Intro,$second,$third

Creates 3 cells with New-AdsWorkBookCells to add to the workbook,
two text ones and a code one, then creates a SQL Notebook with
those cells and saves it as     C:\temp\AutoGenerated.ipynb

Installation

You can install this Module from the PowerShell Gallery using

Install-Module ADSNotebook

Compatability

This module has been tested on Windows PowerShell 5.1, PowerShell Core 6 and PowerShell 7 on Windows 10 and Ubuntu

Demo

My current VS Code Extensions and using a workspace file

I have been asked a couple of times recently what my Visual Studio Code extensions are at the moment so I thought I would write a quick post and also look at workspaces and how you can enable and disable extensions within them

Listing Extensions

From the command line you can list your extensions using

code --list-extensions
code-insiders --list-extensions

My list looks like this

You can also see them in the view on the left of default Visual Studio Code and open them with CTRL + SHIFT + X (unless like me you have Snaggit installed and it has taken that shortcut

Installing Extensions

You can install extensions by opening the Extensions view in Visual Studio Code and searching for the extension. The list I have below has the precise names for each extension which you can use to search

You can also install extensions from the command-line with

code --install-extension <extensionid>
code-insiders --install-extension <extensionid>

My Extensions

I am going to list these in alphabetical order by display name for ease (my ease that is!)

Because Chrissy LeMaire and I are writing dbatools in a Month of Lunches using AsciiDoc, it makes sense to have an extension enabling previewing and syntax, you can find it here

For interacting with Azure I use the Azure Account Extension – ms-vscode.azure-account

I use Azure CLI so I make use of the functionality of the Azure CLI Tools extension ms-vscode.azurecli

For interacting with Azure Repos I use the ms-vsts.team extension

When creating ARM templates, this extension is very useful msazurermtools.azurerm-vscode-tools

I have a few theme extensions, this one is for fun in demos 😉 beardedbear.beardedtheme

The blackboard theme is my default one gerane.theme-blackboard

Chasing closing brackets is much easier with the Bracket Pair Colorixer, I use the beta version coenraads.bracket-pair-colorizer-2

I am rubbish at spelling and typing so I use this to help point out the issues! streetsidesoftware.code-spell-checker

Using the Docker extension adds another view to Visual Studio Code to ease working with containers ms-azuretools.vscode-docker

As an open-source project maintainer it is good to be able to work with GitHub pull requests without leaving Visual Studio Code github.vscode-pull-request-githubPreview

GitLens is absolutely invaluable when working with source control. It has so many features. This is an absolute must eamodio.gitlens

Working with Kubernetes? This extension adds another view for interacting with your cluster ms-kubernetes-tools.vscode-kubernetes-tools

Visual Studio Live Share enables you to collaborate in real-time in Visual Studio Code with your colleagues or friends. I blogged about this here ms-vsliveshare.vsliveshare

I love writing markdown and this linter assists me to ensure that my markdown is correct davidanson.vscode-markdownlint

The Material Icon Theme ensures that there are pretty icons in my editor! pkief.material-icon-theme

I have both the PowerShell extension ms-vscode.powershell and the PowerShell preview extension ms-vscode.powershell-preview installed but only one can be enabled at a time

This suite of extensions enables easy remote development so that you can develop your PowerShell scripts, for example, inside a ubuntu container running PowerShell 7 or inside Windows Subsystem for LInux ms-vscode-remote.vscode-remote-extensionpackPreview

Writing for cross-platform means looking out for line endings and this extension will display them and any whitespace in your editor medo64.render-crlf

An absolutely essential extension which enables me to backup all of my Visual Studio Code settings, shortcuts, extensions into a GitHub gist and keep all of my machines feeling the same. shan.code-settings-sync

For working with SQL Server within Visual Studio Code and having a view for my instances as well as a linter and intellisense I use ms-mssql.mssql

Yaml files and spaces! I no longer get so confused with this extension to help me 🙂 redhat.vscode-yaml

Workspaces

Now that is a lot of extensions and I dont need all of them everytime. I use workspaces to help with this. I will create a workspace file for the project I am working on.

I open or create the folders I will be working on and then click File and Save Workspace As and save the file in the root of the folder.

Now, the next time I want to open the workspace, I can open the workspace file or if I open the folder Visual Studio Code will helpfully prompt me

Now I can have all of my settings retained for that workspace

For this folder, I am ensuring that the PowerShell extension uses the PSScriptAnalyzer Settings file that I have created so that it will show if the code is compatible with the versions of PowerShell I have chosen. I can define settings for a workspace in the settings file, which you can open using CTRL and ,

But I can also enable or disable extensions for a workspace

So everytime I open this workspace I am only loading the extensions I want

PowerShell Notebooks in Azure Data Studio

The latest release of the insiders edition of Azure Data Studio brings the first edition of PowerShell Notebooks!

You can download the latest insiders edition from the link above, it can be installed alongside the stable release.

To access many of the commands available use F1 to open the command palette (like many of my tips this also works in Visual Studio Code). You can then start typing to get the command that you want.

You can then hit enter with the command that you want highlighted, use the mouse or use the shortcut which is displayed to the right.

In a new notebook, you can click the drop down next to kernel and now you can see that PowerShell is available

When you choose the PowerShell kernel, you will get a prompt asking you to configure the Python installation

If you have Python already installed you can browse to the location that it is installed or you can install Python. In the bottom pane you will be able to see the progress of the installation.

When it has completed, you will see

You may also get a prompt asking if you would like to upgrade some packages

Again this will be displayed in the tasks pane

Adding PowerShell


To add PowerShell Code to the notebook click the Code button at the top of the file

or the one you can find by highlighting above or below a block

I did not have intellisense, but you can easily write your code in Azure Data Studio or Visual Studio Code and paste it in the block.

Interestingly Shawn Melton ( t ) did

This was because he had the PowerShell extension installed and I did not (I know !!)
If you find you dont have intellisense then install the PowerShell extension!

Clicking the play button (which is only visible when you hover the mouse over it) will run the code

You can clear the results from every code block using the clear results button at the top

Otherwise, you can save the results with the Notebook by saving it. This is the part that is missing from running PowerShell in the Markdown blocks in a SQL Notebook as I described here

I am looking forward to how this develops. You can find my sample PowerShell notebook (with the code results) here

PowerShell in SQL Notebooks in Azure Data Studio

I have done a lot of writing in the last few months but you see no blog posts! My wonderful friend Chrissy and I are writing “dbatools in a Month of Lunches” to be published by Manning. That has taken up a lot of my writing mojo. We have hit a little break whilst we have some reviews done ready for the MEAP (For everyone who asks, the answer is the unfulfilling ‘soon’) so it’s time for a blog post!

SQL Notebooks are cool

I have had a lot of fun with SQL Notebooks recently. I have presented a session about them at a couple of events this month DataGrillen and SQL Saturday Cork. Here is a little snippet

Yes, you can run PowerShell in a SQL Notebook in Azure Data Studio just by clicking a link in the markdown cell. This opens up a lot of excellent possibilities.

I have had several discussions about how SQL Notebooks can be used by SQL DBAs within their normal everyday roles. (Mainly because I don’t really understand what the sorcerers of data science do with notebooks!). I have helped clients to look at some of their processes and use SQL Notebooks to help with them. Creating Disaster Recovery or Change Run-books or Incident Response Templates or using them for product demonstrations. Of course, I needed to use PowerShell in that 🙂

I have really enjoyed working out how to run PowerShell in the markdown in a SQL Notebook in Azure Data Studio and I think Anthony the kubernetes magician did too!

OK enough magic puns lets talk about PowerShell in SQL Notebooks. You can read about how to create a SQL Notebook and run T-SQL queries here, (you no longer need the Insider Edition by the way)

PowerShell in Markdown!

First, before I go any further, I must say this. I was at the European PowerShell Conference when I was working this out and creating my sessions and I said the words

“Cool, I can click a link and run PowerShell, this is neat”

A Beardy fellow in Hannover

This stopped some red team friends of mine in their tracks and they said “Show me”. One of them was rubbing their hands with glee! You can imagine the sort of wicked, devious things that they were immediately considering doing.

Yes, it’s funny but also it carries a serious warning. Without understanding what it is doing, please don’t enable PowerShell to be run in a SQL Notebook that someone sent you in an email or you find on a GitHub. In the same way as you don’t open the word document attachment which will get a thousand million trillion pounddollars into your bank account or run code you copy from the internet on production without understanding what it does, this could be a very dangerous thing to do.

With that warning out of the way, there are loads of really useful and fantastic use cases for this. SQL Notebooks make great run-books or incident response recorders and PowerShell is an obvious tool for this. (If only we could save the PowerShell output in a SQL Notebook, this would be even better)

How on earth did you work this out?

Someone asked me how I worked it out. I didn’t! It began with Vicky Harp PM lead for the SQL Tools team at Microsoft

I then went and looked at Kevin Cunnane‘s notebook. Kevin is a member of the tools team working on Azure Data Studio. With SQL Notebooks, you can double click the markdown cell and see the code that is behind it. To understand how it is working, lets deviate a little.

Keyboard Shortcuts

IF you click the cog at the bottom left of Azure Data Studio and choose Keyboard Shortcuts

you can make Azure Data Studio (and Visual Studio Code) work exactly how you want it to. Typing in the top box will find a command and you can then set the shortcuts that you want to use to save yourself time.

This also enables you to see the command that is called when you use a keyboard shortcut. For example, you can see that for the focus terminal command it says workbench.action.terminal.focus.

It turns out that you can call this as a link in a Markdown document using HTML with <a href=""> and adding command: prior to the command text. When the link is clicked the command will run. Cool 🙂

For this to be able to work (you read the warning above?) you need to set the Notebook to be trusted by clicking this button.

This will allow any command to be run. Of course, people with beards will helpfully advise when this is required for a SQL Notebook. (Safe to say people attempting nefarious actions will try the same with your users)

Now that we know how to run an Azure Data Studio command using a link in a markdown cell the next step is to run a PowerShell command. I headed to the Visual Studio Code documentation and found

Send text from a keybinding
The workbench.action.terminal.sendSequence command can be used to send a specific sequence of text to the terminal, including escape sequence

That’s the command we need, however, we still need to craft the command so that it will work as a link. It needs to be converted into a URL.

I started by using this website https://www.url-encode-decode.com/ to do this. This is how you can check the code in other peoples notebook, use the decode capability.

Encoding Set-Location C:\dbachecks gives Set-Location+C%3A%5Cdbacheck`

So I can just put that code into the href link and bingo!

If only it was that easy!!

Some Replacing is required

The + needs to be replaced with a space or %20

You also need to double the \ and replace the %3A with a :
The " needs to be replaced with \u022, the ' with \u027, the curly braces won’t work unless you remove the %0D%0A. Got all that? Good!

Once you have written your PowerShell, encoded it, performed the replacements, you add \u000D at the end of the code to pass an enter to run the code and then place all of that into a link like this

<a href="command:workbench.action.terminal.sendSequence?%7B%22text%22%3A%22 PLACE THE ENCODED CODE HERE %22%7D">Link Text</a>

This means that if you want to add the PowerShell code to set a location and then list the files and folders in that location to a Markdown cell using PowerShell like this

Set-Location C:\dbachecks
Get-ChildItem

You would end up with a link like this

<a href="command:workbench.action.terminal.sendSequence?%7B%22text%22%3A%22 Set-Location C:%5C%5Cdbachecks \u000D Get-ChildItem \u000D %22%7D">Set Location and list files</a>

Doing something more than once?

I don’t want to remember that all of the time so I wrote a PowerShell function. You can find it on GitHub https://github.com/SQLDBAWithABeard/Functions/blob/master/Convert-ADSPowerShellForMarkdown.ps1

This will take a PowerShell command and turn it into a link that will work in an Azure Data Studio markdown. It’s not magic, it’s PowerShell. There is a –ToClipboard parameter which will copy the code to the clipboard ready for you to paste into the cell (On Windows machines only)

Giants

There are many uses for this but here’s one I think is cool.

The link below will go to a notebook, which will show how you the giants upon whose shoulders I stand

Glenn Berry,
Chrissy LeMaire,
André Kamman,
Gianluca Sartori

have enabled me to create a SQL Notebook with a link which will run some PowerShell to create a SQL Notebook which will have all of the Diagnostic Queries in it.

You could possibly use something like it for your incident response SQL Notebook.

It’s also cool that GitHub renders the notebook in a browser (You can’t run PowerShell or T-SQL from there though, you need Azure Data Studio!)

https://github.com/SQLDBAWithABeard/Presentations/blob/master/2019/Berlin%20SQL%20User%20Group/04%20-%20Glenn%20Berry%20Notebook.ipynb