In this article I show how to write a wrapper script to swiftly call a selection of dbatools commands on the computerlevel.

Case

There are a lot of commands out there now. Sometimes it's handy to have a subselection bundled to give an overview. It's also nice if you can call those commands repeatedly, or alternate between them without having to look them up again and again.

Solution

We will make a comprehensible list of related commands we often call subsequently on one or more computers.
All commands we choose for now are functions from the dbatools module. Find out about this wonderful tool at dbatools.
Then we will create a simple menu to call the functions with a number, and return to the menu until the user wants to quit.

Solution

Decide on the list

As theme we choose the dbatools functions that work on the computer level. This was the complete selection in february 2017:

• Get-DbaPrivilege
• Get-DbaSqlService
• Get-DbaclientProtocol
• Get-DbaDiskSpace
• Get-DbaPageFileSetting
• Get-DbaMemoryUsage
• Get-DbaMsDtc
• Get-DbaSPN

Menu choices

Basically, there are 3 ranges of responses we need to handle:

• 9 The user wants to quit
• 1..8 The user wants to execute a command
• anything else The use needs a warning he made an invalid choice

Write-Host !!!

Yes, again we're only displaying directly in the console and using Write-Host for that. This way we can put a well formed menu on a blank screen, and use some colour.
Disadvantage is we can't redirect output, but that would be hard anyway, since the function calls are out of reach for the user anyway.
We'll write another script to collect output and save it somewhere.

Steps

Show me the menu

First we clear the screen, then we show the menu. We create a so-called Here-string, that keeps the lay out of a pece of text we declare in a variable:

Clear-Host
$menu = @"

1    Get Privileges
2    Get SQL Services
3    Get Client Protocols
4    Get Disk Space
5    Get Page File Settings
6    Get SQL Memory Usage
7    Get MS DTC settings
8    Get SPN
9    Quit
"@

  Write-Host $menu -ForegroundColor Yellow

Ask for input

Then we add a Read-Host to ask input from the user (usually, that's us :-) )

  [int]$choice = Read-Host "Make your Choice"

That will result in this:

Is this valid input?

We have a simple check to see if the input is a number between 1 and 9. We use the range operator .. to define that array and if it doens't contain the value the user entered, we tell him so and pause ( see below ).
As soon as the user hits <Enter>, we start our script from the beginning:

  if ( (1..9) -notcontains $choice )
  {
    Write-Warning "$choice is not a valid choice"
    pause
    &$MyInvocation.MyCommand
  }

Do you want to stop?

Choosing to stop is just another menu item. On receiving number 9, we wish ourselves a nice day and stop the script:

    if ( $choice -eq 9 )
  {
    Write-Host "Have a nice day" -ForegroundColor Green
    Return
  }

When we enter <Enter>, we get our regular prompt back.

Execute a function

So, if we receive a number from 1 to 8, we execute the corresponding function. For that, we first ask for the computername to target, and then 'translate' the number into a function call with the Switch statement.

There's no error handling or input validation here. We assume that we are people of good will and know our servers. :-)

  elseif ( (1..8) -contains $choice )
  {
    $Computername = Read-Host "Enter a computername or press Enter to use the localhost"
    if ( $Computername -notmatch "\w+" ) { $computername = $env:COMPUTERNAME }
  }

  Switch ($choice) {
    1 { Get-DbaPrivilege -computername $Computername -Verbose}
    2 { Get-DbaSqlService -computername $Computername }
    3 { Get-DbaclientProtocol -ComputerName $Computername }
    4 { Get-DbaDiskSpace -ComputerName $Computername }
    5 { Get-DbaPageFileSetting -ComputerName $Computername }
    6 { Get-DbaMemoryUsage -ComputerName $Computername }
    7 { Get-DbaMsDtc -ComputerName $Computername }
    8 { Get-DbaSPN -ComputerName $Computername }
  }

Wait for it ...

We show the output in the console, and when the user is ready, we clear the screen again and ask for new input. We do that with one simple word:

pause

This will write 'Press Enter to continue...:' in the console, and continue to execute our script once <Enter> is pressed.

Back to start

If we reach the end of the script, which should happen as long as the user didn't hit [9], and we didn't cause a terminating error, we start the whole script again:

&$MyInvocation.MyCommand

The complete script

<#
  .SYNOPSIS
   Basic demo script showing use of dbatools at computer level.

  .DESCRIPTION
   Shows a menu to let the user choose a function to execute against a Computer.
   Included are dbatools functions that act on the computer level.
   Needs dbatools module installed.
   User has to be local admin on the chosen target computer.

  .OUTPUTS
   Objects depending on the choice made.

  .NOTES
    Name    :   Get-DbaComputerInfo
    Author  :   Klaas Vandenberghe ( @PowerDBAKlaas )
    Date    :   2017-02-22
#>
Clear-Host
$menu = @"

1    Get Privileges
2    Get SQL Services
3    Get Client Protocols
4    Get Disk Space
5    Get Page File Settings
6    Get SQL Memory Usage
7    Get MS DTC settings
8    Get SPN
9    Quit
"@

  Write-Host $menu -ForegroundColor Yellow
  $choice = Read-Host "Make your Choice"
  if ( (1..9) -notcontains $choice )
  {
    Write-Warning "$choice is not a valid choice"
    pause
    &$MyInvocation.MyCommand
  }
  if ( $choice -eq 9 )
  {
    Write-Host "Have a nice day" -ForegroundColor Green
    Return
  }

  elseif ( (1..8) -contains $choice )
  {
    $Computername = Read-Host "Enter a computername or press Enter to use the localhost"
    if ( $Computername -notmatch "\w+" ) { $computername = $env:COMPUTERNAME }
  }

  Switch ($choice) {
    1 { Get-DbaPrivilege -computername $Computername -Verbose}
    2 { Get-DbaSqlService -computername $Computername }
    3 { Get-DbaclientProtocol -ComputerName $Computername }
    4 { Get-DbaDiskSpace -ComputerName $Computername }
    5 { Get-DbaPageFileSetting -ComputerName $Computername }
    6 { Get-DbaMemoryUsage -ComputerName $Computername }
    7 { Get-DbaMsDtc -ComputerName $Computername }
    8 { Get-DbaSPN -ComputerName $Computername }
  } #switch

  pause

  #re-run this script
  &$MyInvocation.MyCommand

To be continued

Now you have one example of how you can write a short script to easily call several functions.
This is not a proper function, but a simple and fast creation for private use.
It's only for interactive use with a regular console host or ISE, which can be a useful tool if you want a quick look at a server.
We can improve this in many ways:

• turn it into a function
• make it accept parameters for the function and the computer or sql instance
• add Verbose and Warning messages
• ...

But I leave that up to you.

Previous Post Next Post