functions/New-ADSWorkBook.ps1
|
<#
.SYNOPSIS Creates a New Azure Data Studio WorkBook .DESCRIPTION This will create a new Azure Data Studio Notebook .PARAMETER Path The full path where you want the notebook saved - must end with .ipynb .PARAMETER cells An array of New-AdsWorkBookCells to add to the WorkBook .PARAMETER Type The type of WorkBook to create - SQL or PowerShell or DotNetPowerShell .EXAMPLE $introCelltext = "# Welcome to my Auto Generated SQL 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 -Type SQL 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 .EXAMPLE $introCelltext = "# Welcome to my Auto Generated PowerShell Notebook ## dbatools  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 $path = 'C:\temp\dbatools.ipynb' New-ADSWorkBook -Path $path -cells $Intro,$second,$third,$fourth,$fifth,$sixth,$Seventh -Type PowerShell Creates 7 cells with New-AdsWorkBookCells to add to the workbook, four text ones and three code ones, then creates a PowerShell Notebook with those cells and saves it as C:\temp\dbatools.ipynb .NOTES Rob Sewell 10/10/2019 - Initial Rob Sewell 14/11/2019 - Added PowerShell Notebooks type Rob Sewell 23/07/2020 - Added DotNetPowerShell Notebook Rob Sewell @SQLDbaWithBeard blog.robsewell.com #> function New-ADSWorkBook { [cmdletbinding(SupportsShouldProcess)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidTrailingWhitespace", "", Justification = "the markdown requires a trailing space to work correctly")] Param( # The full path to the file [Parameter(Mandatory)] [ValidateScript({ if($_ -match '^*.ipynb'){ $true } else{ Throw [System.Management.Automation.ValidationMetadataException] "The path $($_) does not have the correct extension. It needs to be .ipynb" } })] [string] $Path, # The cells (in order) created by New-ADSWorkBookCell to build the notebook [Parameter(Mandatory)] [pscustomobject[]] $cells, # The type of notebook [Parameter(Mandatory)] [ValidateSet('SQL','PowerShell','DotNetPowerShell')] [string] $Type ) $PSCmdlet.WriteVerbose('Lets create a Notebook of type $type') $PSCmdlet.WriteVerbose('Creating the base object') switch ($type) { 'DotNetPowerShell' { $Base = [PSCustomObject]@{ metadata = [PSCustomObject]@{ kernelspec = [PSCustomObject]@{ name = '.net-powershell' language = "PowerShell" display_name = '.NET (PowerShell)' } } language_info = [PSCustomObject]@{ name = 'PowerShell' version = '7.0' pygments_lexer = 'powershell' mimetype = 'text/x-powershell' file_extension = '.ps1' } nbformat_minor = 4 nbformat = 4 cells = @() } } 'PowerShell' { $Base = [PSCustomObject]@{ metadata = [PSCustomObject]@{ kernelspec = [PSCustomObject]@{ name = 'powershell' display_name = 'PowerShell' } } language_info = [PSCustomObject]@{ name = 'powershell' version = '' codemirror_mode = 'shell' mimetype = 'text/x-sh' file_extension = '.ps1' } nbformat_minor = 2 nbformat = 4 cells = @() } } 'SQL' { $Base = [PSCustomObject]@{ metadata = [PSCustomObject]@{ kernelspec = [PSCustomObject]@{ name = 'SQL' display_name = 'SQL' language = 'sql' } } language_info = [PSCustomObject]@{ name = 'sql' version = '' } nbformat_minor = 2 nbformat = 4 cells = @() } } } $PSCmdlet.WriteVerbose('Adding the array of cells to the base object') foreach ($cell in $cells) { $base.cells += $cell } $PSCmdlet.WriteVerbose('Finished adding the array of cells to the base object') $PSCmdlet.WriteVerbose('Creating the json and replacing the back slashes and double quotes') try { if($IsCoreCLR){ $base = ($Base | ConvertTo-Json -Depth 4 ).Replace('\\r ', '\r').Replace('\\n ', '\n').Replace('"\', '').Replace('\""','"') } else{ # Grr PowwerShell $base = ($Base | ConvertTo-Json -Depth 4 ).Replace('\\r ', '\r').Replace('\\n ', '\n').Replace('\"\u003e','\">').Replace('"\"\u003c','"<').Replace('"\"', '"').Replace('\""','"').Replace('\u003c','<').Replace('\u003e','>') } } catch { $PSCmdlet.WriteWarning('Failed to create the json for some reason. Run `$error[0] | fl -force to find out why') Return } $PSCmdlet.WriteVerbose('json created') if ($PSCmdlet.ShouldProcess("$path", "Creating File")) { $Base | Set-Content -Path $path } $PSCmdlet.WriteVerbose('Created json file at' + $path + ' - Go and open it in Azure Data Studio') } |