Fun With KQL – New Pluralsight Course! Kusto Query Language: Scalar Operators

Introduction

My third course in the Pluralsight Kusto Query Language Learning Path has just been published: Kusto Query Language: Scalar Operators. This course is the third in Pluralsight’s Kusto Query Language Learning Path.

The Kusto Query Language has a rich set of scalar operators. Operators that can be used to transform your data, making the output of your queries easier to read.

In this course, we cover some of the most used Kusto Query Language scalar operators, which will enable you to author effective queries right away.

Some of the major topics that are covered include:

  1. Work with dates and times, including datetime math as well as formatting.
  2. Logic branching
  3. Working with strings
  4. Advanced techniques for working with column data

By the end of the course, you’ll have enough information about scalar operators to write queries with more readable output.

Kusto Query Language: Previous Courses

If you have not seen my previous two courses in the series, you should probably watch those first if you are not familiar with KQL.

The first course, Kusto Query Language: Getting Started, covered the basics of KQL, as well as the user interface.

In the second Pluralsight course, Kusto Query Language: Beginning Operators, I cover some of the most useful of the KQL operators. Using this course you can write effective queries in order to do most of your data retrieval.

The newest course, which I mentioned above, shows how to take your output and format it nicely for a wider audience.

No Pluralsight Subscription? No Problem!

Every good IT organization should have a place for it’s staff to continue their education, and stay up to date with the latest IT subjects. Pluralsight is a great choice, it can be done on demand, has a HUGE catalog of courses, is constantly being updated, and the courses are done by leading IT professionals.

Talk to your boss about it, you’d be amazed at how many things you can get in life just by asking (using a persuasive argument of course).

While waiting for your organization to get approval, there is a way to watch for free. Just go to my list of courses on Pluralsight . At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.

Conclusion

Just a side note for those who subscribe to my posts. Normally I publish on Mondays, but this post was a bit late this week due to wrapping up the course. I’ll probably take next week off of my KQL series and resume the week after.

I hope you enjoy the courses! And stay tuned to the blog, as I will be continuing my ongoing series Fun With KQL.

Advertisement

Fun With KQL – New Pluralsight Course! Kusto Query Language: Beginning Operators

Introduction

I’ve just published a new course at Puralsight, Kusto Query Language: Beginning Operators. This course is the second in Pluralsight’s Kusto Query Language Learning Path.

The Kusto Query Language can be used to retrieve and analyze data, not only in your own Azure Data Clusters, but for the health and status of your Azure-hosted infrastructure as well.

In this course, Kusto Query Language: Beginning Operators, you’ll learn the basic set of Kusto operators needed to author effective, useful queries.

First, you’ll learn to search across tables. Next, you’ll discover how to limit the data being output, as well as to limit the columns of data being returned.

Finally, you’ll explore how to add calculated columns to your query results. When you’re finished with this course, you’ll have the skills to write queries to answer a majority of your needs.

If you don’t have a Pluralsight subscription, there is a way to watch for free. Just go to my list of courses on Pluralsight . At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.

Kusto Query Language: Getting Started

If you missed it, I have a previous course in the learning path, Kusto Query Language: Getting Started.

The Kusto Query Language (KQL) is becoming increasingly important in the world of Azure.

In this course, Kusto Query Language: Getting Started, you’ll learn foundational knowledge of the Kusto Query Language.

First, you’ll learn what KQL is and where it is used, exploring the Log Analytics environment. Next, you’ll discover the basic structure, comparing it to other query languages.

Finally, you’ll explore the definitions of terms used with KQL. When you’re finished with this course, you’ll have the necessary skills and knowledge of the Kusto Query Language and the environments it is used in to begin authoring your own Kusto Queries.

You can also see this course for free, if you take advantage of the free trial described in the previous section.

Conclusion

As I write this, I have just completed recording the third course in the series, Kusto Query Language: Scalar Operators. It should be published around the end of January 2023.

I hope you enjoy the courses! And stay tuned to the blog, as I will be continuing my ongoing series Fun With KQL.

Fun With the PowerShell Switch Parameter

Introduction

Over the last few posts I’ve been covering PowerShell functions, both Basic Functions and Advanced Functions. In this post I had originally intended to cover two switches available to all advanced functions, Verbose and Debug.

But then it occurred to me, not everyone may know what a switch parameter is. And to be clear, I’m not talking about the switch statement. I covered that in my post Fun With PowerShell Logic and Branching.

Here, I’m talking about the ability to use what PowerShell calls a switch parameter.

We’ll take a deeper look in a moment, but first let me mention that for all of the examples, we’ll display the code, then under it the result of our code. In this article I’ll be using PowerShell Core, 7.1.3, and VSCode. The examples should work in PowerShell 5.1 in the PowerShell IDE, although they’ve not been tested there.

To run a snippet of code highlight the lines you want to execute, then in VSCode press F8 or in the IDE F5. You can display the contents of any variable by highlighting it and using F8/F5.

A Simple Switch Example

If you’ve been following my series, you know that you can pass in values, in other words parameters, to a function by name. For example:

Get-AValue -one 33 -two 42

-one and -two were the parameter names, and 33 and 42 the values passed in for them.

A switch is similar, you list the name of the switch on call to the function, but unlike a regular parameter you pass in no value. The presence of the switch is enough to tell the function what you want to do.

Let’s look at an example using the common Write-Host cmdlet.

Write-Host 'Hi Mom' -NoNewline -ForegroundColor Green
Write-Host ' and Dad' -ForegroundColor Yellow

Result:
Hi Mom and Dad Image

Normally, Write-Host displays its text, then automatically moves the cursor to the next line. However, Write-Host has a -NoNewLine switch. Including the switch will keep Write-Host from adding the line feed at the end, and allows us do fun things like having two different colors of text on the same line.

Note that we didn’t have to pass in any value, didn’t have to set it to true or false. Just including the switch was enough to say “hey don’t wrap to a new line”.

Implementing Your Own Switches

Switches wouldn’t be any fun if we couldn’t use them too! And it’s actually quite easy. I started off the whole PowerShell series on my blog with a post Fun With PowerShell Get-Random. In it I described (among other things) how to use the Get-Random cmdlet to return a random value from an array. We’ll borrow on that idea for this function.

function Get-RandomSouthAmericanCountry()
{
[CmdletBinding()]
param(
[switch] $UpperCase
)

$array = (
'Argentina', 'Bolivia', 'Brazil',
'Chile', 'Columbia', 'Ecuador',
'Guyana', 'Paraguay', 'Peru',
'Suriname', 'Uruguay', 'Venezuela'
)

# Get an item from the array and convert from
# a generic object to a string
$retVal = $($array | Get-Random).ToString()

# If user passed in upper case switch,
# upper case return value
if ($UpperCase.IsPresent)
{
$retVal = $retVal.ToUpper()
}

return $retVal

}

I’ll give a shout of sorts to my wonderful geek friends who live on the South American continent with this example, our Get-RandomSouthAmericanCountry function. Of course we start with [CmdletBinding()] to indicate this is an advanced function. Then we have our param block.

param(
[switch] $UpperCase
)

We have one parameter named $UpperCase. But instead of having a traditional data type in front, we have [switch]. This will indicate to PowerShell that $UpperCase is a switch. We’ll see how to use it within our code in a moment, but first let’s take a quick look at the rest of the function.

After the param block we have an array which lists the countries in South America (according to the countries of the world website I found).

I then fall to this line to get the country:

$retVal = $($array | Get-Random).ToString()

First, I use $array | Get-Random to get a random country. This will return an element from the array, but I need it to be a datatype of string rather than a generic object. So I wrap the result of Get-Random in $( ) to make PowerShell evaluate it as an object. Then I can call the .ToString() method of the object to convert it to a string. Finally it gets assigned to my return variable, $retVal.

The next part is where I look to see if my switch was used.

if ($UpperCase.IsPresent)
{
$retVal = $retVal.ToUpper()
}

Here, you use the name of the switch and access its .IsPresent property. This returns $true if the switch was passed in. Thus if it was passed in, the if statement will take affect and call the .ToUpper() method on our $retVal string. This is why we had to convert to a string, string data types have a .ToUpper() method, generic objects don’t.

As the final step we return the value held in $retVal, which sticks to the rule of having one exit point for a function.

After highlighting the function and running it using F8/F5 to get it in memory, we’ll call it. First, we’ll so so using it without the switch.

Get-RandomSouthAmericanCountry

Result:
Venezuela

Great, now let’s call it with our switch.

Get-RandomSouthAmericanCountry -UpperCase

Result:
URUGUAY

In this call, just including the switch caused the output to display in upper case.

Switches Work with Basic Functions Too

In the above example I used an advanced function. Be aware that if you are using a basic function, as I described in a previous article, you can still use a switch.

Here is the previous example rewritten as a basic function. (Note the function declaration should all be on one line, the blog layout will probably wrap it.)

function Get-RandomSouthAmericanCountryBasic([switch] $UpperCase)
{
$array = (
'Argentina', 'Bolivia', 'Brazil',
'Chile', 'Columbia', 'Ecuador',
'Guyana', 'Paraguay', 'Peru',
'Suriname', 'Uruguay', 'Venezuela'
)

# Get an item from the array and convert from
# a generic object to a string
$retVal = $($array | Get-Random).ToString()

# If user passed in upper case switch,
# upper case return value
if ($UpperCase.IsPresent)
{
$retVal = $retVal.ToUpper()
}

return $retVal

}

The only real difference is the removal of the [CmdletBinding()} statement and the param block. I then declared the [switch] $UpperCase inside the parenthesis like you would do with a basic function.

By the way, like most things in PowerShell casing isn’t important. These are all the same to PowerShell:

[switch] $UpperCase
[SWITCH] $UpperCase
[Switch] $UpperCase

When you strongly type your variables, for example using [int] or [string], it’s common to use lower case, so I generally stick to lower for [switch], but it’s not mandatory. Just pick a standard for you and your team, then stick with it.

Naming Switches

When creating your switches, be sure to use clear names. In our example, UpperCase was a good switch name. Especially when using the Get- verb in our function name, as it implied the results should be in upper case. It doesn’t occur to me that I need to pass in some value to make it happen.

Contrast that with a switch name like MaxValue. Here, I wonder if I want to return a maximum value, or if I need to pass in a value that I want to be the maximum value, or something else.

A Note on $switchName -eq $true

You may see examples on the web such as:

if ($UpperCase -eq $true)

to check to see if a switch is present. This is a much older, and very much out of date method. Microsoft recommends you use the .IsPresent method that you saw in these examples, you should stick to it in your code.

Conclusion

In this post we covered the useful switch parameter. It’s very easy to use, and can add a lot of flexibility to your functions. This also gives a good foundation for discussion of the Verbose and Debug switches built into all Advanced Functions in our next blog post.

The demos in this series of blog posts came from my Pluralsight course PowerShell 7 Quick Start for Developers on Linux, macOS and Windows, one of many PowerShell courses I have on Pluralsight. All of my courses are linked on my About Me page.

If you don’t have a Pluralsight subscription, just go to my list of courses on Pluralsight. At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.

Fun With PowerShell Pipelined Functions

Introduction

In my previous post, I covered the use of PowerShell Advanced Functions. I highly suggest you read it if you haven’t, it provides some foundational knowledge that will be important to understand for this post.

In this post, we’ll see how to pipeline enable your functions. Just like a cmdlet, you’ll be able to take input from the pipeline, work with it, then send it out your function back into the pipeline.

For all of the examples, we’ll display the code, then under it the result of our code. In this article I’ll be using PowerShell Core, 7.1.3, and VSCode. The examples should work in PowerShell 5.1 in the PowerShell IDE, although they’ve not been tested there.

To run a snippet of code highlight the lines you want to execute, then in VSCode press F8 or in the IDE F5. You can display the contents of any variable by highlighting it and using F8/F5.

Pipelining Your Advanced Functions

Pipelining is what gives PowerShell it’s real power. The ability to have small, focused cmdlets (or functions) that you can chain together to produce a useful output. Let’s see this simple, common example:

Get-ChildItem | Sort-Object -Property Length

Result:
Mode  LastWriteTime       Length Name
----  -------------       ------ ----
la--- 11/24/2020  3:37 PM   1115 ReadMe.md
la--- 11/24/2020  3:42 PM   1819 02 - Providers.ps1
la--- 11/24/2020  3:42 PM   3125 09 - Examples.ps1
la--- 11/24/2020  3:42 PM   3732 01 - Cmdlets.ps1
la---  7/15/2021  4:48 PM   4315 06 - Logic Branching and Looping.ps1
la--- 11/24/2020  3:42 PM   4717 03 - Variables.ps1
la--- 11/24/2020  3:42 PM   6210 08 - Classes.ps1
la---  7/20/2021  4:35 PM   6843 07 - Functions.ps1
la---  7/18/2021  8:18 PM   7970 04 - Strings.ps1
la---  7/9/2021   8:00 PM   8486 05 - Arrays and Hashtables.ps1
la--- 11/20/2020 12:58 AM   9694 Company.csv
la--- 11/20/2020 12:58 AM  19479 Company.json

As you can see, Get-ChildItem‘s output was piped into the Sort-Object cmdlet using the pipe symbol, the vertical bar | . Sort-Object took a parameter of a property name, in this case Length, and sorted its output based on that property name.

So how fun would it be to write your own functions that could work with the pipeline? Well as it turns out it’s not only fun but easy too. Here’s an example of a simple pipeline enabled function.

function Get-PSFiles ()
{
  [CmdletBinding()]
  param ( [Parameter (ValueFromPipeline) ]
          $file
        )

  begin  { }

  process
  {
    if ($file.Name -like "*.ps1")
    {
      $retval = "  PowerShell file is $($file.Name)"
      # This is the equivalent of: return $retval
      $retval
    }
  }

  end { }
}

The purpose of Get-PSFiles is to examine each file being passed in from the pipeline. If it finds the file name ends in .ps1, it will pass it onto the next item in the pipeline. Otherwise it gets ignored.

First off is the [CmdletBinding()], which you know by now is needed to let Powershell know this is an advanced function.

Now we have our param block. Note that the parameter block has an attribute of (ValueFromPipeline). When an object comes in from the pipeline it will get copied into the variable with the (ValueFromPipeline) attribute, in this case $file.

The next block in the function is the begin { } block. The begin block executes once, and only once, when the function is first called. Here you could do things like set variables or perform a calculation that will be used later in the function.

In my experience the begin block isn’t used a lot, and if yours is empty you can actually omit it completely from your function.

The next section is the process block. This is where all the fun happens! Here we can work with the objects as they are passed in, one by one, from the pipeline.

In this simple script, I look at the Name property of the item being passed in, which is stored in the variable $file. If it ends in .ps1, I set the return value ($retVal) to a string which has some text including the file name. If it doesn’t end in .ps1, I just don’t do anything with it.

I then have a simple line of $retVal. Since it’s not consumed by any other code PowerShell pushes it out into the next item in the pipeline. If there is no next item, it is displayed to the screen.

Once all the incoming items from the pipeline have been processed, the function continues to the end { } block. This is where you would do any cleanup. Similar to the begin block, end does not get used a lot, and you can omit it entirely if it is empty.

To use our Get-PSFiles function, we first need to highlight it and use F5/F8 to load it into memory. Once done we’ll be ready to call it. In this case when we call it we’ll save the output into a variable then display the contents of the variable.

$output = Get-ChildItem | Get-PSFiles
$output

Result:
  PowerShell file is 01 - Cmdlets.ps1
  PowerShell file is 02 - Providers.ps1
  PowerShell file is 03 - Variables.ps1
  PowerShell file is 04 - Strings.ps1
  PowerShell file is 05 - Arrays and Hashtables.ps1
  PowerShell file is 06 - Logic Branching and Looping.ps1
  PowerShell file is 07 - Functions.ps1
  PowerShell file is 08 - Classes.ps1
  PowerShell file is 09 - Examples.ps1

The directory I’m running from has other files in it besides PowerShell scripts, but the function we wrote filtered those out and only returns my PowerShell script files.

You may wonder, what datatype gets returned? We can find out by checking the type of the variable using the GetType() method built into all PowerShell variables.

$output.GetType()

Result:
IsPublic IsSerial Name     BaseType
-------- -------- ----     --------
True     True     Object[] System.Array

As you can see, it returns an array of objects.

So this function is useful, but limited in one way. It can only work with file type objects such as ones retrieved using Get-ChildItem. What if instead we wanted a function that only worked on a single property?

Line Continuation

Before we get into the next section, I want to bring up the topic of line continuation. Sometimes a line of PowerShell code will get very long. It can be useful to break it up across multiple lines for readability.

PowerShell uses the backtick character, the ` as its line continuation character. On US keyboard this is typically to the left of the number 1. If you are outside the US you’ll have to hunt around to find it, I’ll have to claim ignorance of keyboard layouts for other areas of our globe.

The line continuation character simply tells PowerShell “hey, this set of commands continues on the next line, treat it all as one line of code until you no longer find another backtick character”.

Here’s a simple example:

Write-Host 'Here are the names!' `
  -ForegroundColor Green

As you can see I have a simple Write-Host which displays information to the screen. At the very end of the line I have the backtick character. Then the Write-Host cmdlet continues, passing in the parameter of ForegroundColor.

I encourage you to use line continuation characters whenever you have a long line of code as it will make it much easier to read. It’s also useful to folks like myself who write blog posts or give presentations where my display area is limited. If you go look at some examples on my GitHub site, you’ll see I use them quite a bit.

On a large monitor such as the one I normally use it’s not as needed. But when I write a blog post like this one, or go present at a user group, the amount of space I have is much more limited.

It’s also worth mentioning that the pipe symbol | has line continuation built right into it. I could have taken my earlier example and entered into my editor like so:

Get-ChildItem |
  Sort-Object -Property Length

The pipe symbol has to be the very last character on the line. There cannot be any spaces, comments, or other code after it. On the next line I chose to indent the Sort-Object command by two spaces, this is strictly to make the code easier to read and is not required, but is a good habit.

OK that little detour complete, let’s continue our fun by using just a single property from the pipeline in our function.

Using a Single Property in your Pipeline Function

One way we can make our functions more reusable is to work with a single property. For example, almost all cmdlets return a set of objects that have a Name property. We could create a function that works with just that property, and thus use it with all cmdlets that return objects with a Name property.

This next example is a bit contrived, to show you the concepts, and isn’t something I’d normally code in “real life” but it will serve to show you how this concept works.

function Show-BName ()
{
  [CmdletBinding()]
  param ( [Parameter (ValueFromPipelineByPropertyName)]
          $Name
        )

  begin
  {
    Write-Host 'Here are the names!' `
      -ForegroundColor Green
    $names = @()
  }

  process
  {
    $names += $name
    "  Here is the name: $name"
  }

  end
  {
    Write-Host "Those were the names you passed in:" `
      -ForegroundColor Green
    foreach ($n in $names)
    {
       Write-Host "  You passed in " `
         -ForegroundColor White -NoNewline
      Write-Host $n -ForegroundColor Yellow
    }
  }
}

In the parameter block, we use a slightly different attribute, ValueFromPipelineByPropertyName. This tells PowerShell to take each object being passed in, and copy it’s Name property into our variable, in this case $Name.

Note that the name of the parameter must match the name of the property you want to work with. Thus the Name property is copied into the $Name parameter. The rest of the object will be discarded, and not available to you in the function.

The function falls into our begin block, in which I’ve included a Write-Host statement to display a message our function is beginning. Let me stress, you normally do not include Write-Host messages in functions. Typically the script that calls the function will take the output, and display the results. I’m doing this here only to demonstrate how each piece of the advanced function works.

I then do something you might do, I create a variable called $names. I initialize it as an empty array.

Now the function moves into the process block. In it, I take the name that came in from the pipeline and add it to the array. I then embed the name in a string. Since I don’t do anything with the string, it is now passed out to return to the pipeline.

Finally we hit the end block. Here, I’m using some Write-Host statements to show we’re done and what names were processed. In a real world situation you might use this for logging or some other reason that fits your needs.

So let’s see this in action. As before, highlight the function and run it using F8/F5 to get it into memory. Then we can call it.

Get-ChildItem |
  Show-BName

Result:
Here are the names!
  Here is the name: 01 - Cmdlets.ps1
  Here is the name: 02 - Providers.ps1
  Here is the name: 03 - Variables.ps1
  Here is the name: 04 - Strings.ps1
  Here is the name: 05 - Arrays and Hashtables.ps1
  Here is the name: 06 - Logic Branching and Looping.ps1
  Here is the name: 07 - Functions.ps1
  Here is the name: 08 - Classes.ps1
  Here is the name: 09 - Examples.ps1
  Here is the name: Company.csv
  Here is the name: Company.json
  Here is the name: ReadMe.md
Those were the names you passed in:
  You passed in 01 - Cmdlets.ps1
  You passed in 02 - Providers.ps1
  You passed in 03 - Variables.ps1
  You passed in 04 - Strings.ps1
  You passed in 05 - Arrays and Hashtables.ps1
  You passed in 06 - Logic Branching and Looping.ps1
  You passed in 07 - Functions.ps1
  You passed in 08 - Classes.ps1
  You passed in 09 - Examples.ps1
  You passed in Company.csv
  You passed in Company.json
  You passed in ReadMe.md

The first set of “Here is the names…” is what came out of the function into the pipeline. Since we didn’t do anything with them, they were displayed on the screen. The second set, with “You passed in…” is from the end block.

We can have even more fun, let’s take the output of our function and pipe it into another cmdlet. We’ll use Sort-Object to display our results in descending order.

Get-ChildItem |
  Show-BName |
  Sort-Object -Descending

Result:
Here are the names!
Those were the names you passed in:
  You passed in 01 - Cmdlets.ps1
  You passed in 02 - Providers.ps1
  You passed in 03 - Variables.ps1
  You passed in 04 - Strings.ps1
  You passed in 05 - Arrays and Hashtables.ps1
  You passed in 06 - Logic Branching and Looping.ps1
  You passed in 07 - Functions.ps1
  You passed in 08 - Classes.ps1
  You passed in 09 - Examples.ps1
  You passed in Company.csv
  You passed in Company.json
  You passed in ReadMe.md
  Here is the name: ReadMe.md
  Here is the name: Company.json
  Here is the name: Company.csv
  Here is the name: 09 - Examples.ps1
  Here is the name: 08 - Classes.ps1
  Here is the name: 07 - Functions.ps1
  Here is the name: 06 - Logic Branching and Looping.ps1
  Here is the name: 05 - Arrays and Hashtables.ps1
  Here is the name: 04 - Strings.ps1
  Here is the name: 03 - Variables.ps1
  Here is the name: 02 - Providers.ps1
  Here is the name: 01 - Cmdlets.ps1

There are a couple of things to notice with this version. First, the result of the end block, the “You passed in…” comes first. That’s because the function ended, but the data it produced is now being consumed by Sort-Object.

Second, you can see Sort-Object took the output and sorted in reverse (descending) order.

As they say on TV, but wait, there’s more!

Because our function only acts on the Name property, it could care less what type of object is coming in as long as it has a Name property. Thus we could use it with any cmdlet as long as that cmdlet produces objects with a Name property!

Get-Process returns objects with a Name property, so let’s use it with our new function.

Get-Process | Show-BName

Result:
  Here is the name: 5KPlayer
  Here is the name: aesm_service
  Here is the name: Airplay
  Here is the name: AppleMobileDeviceProcess
  Here is the name: ApplicationFrameHost
  Here is the name: CepstralLicSrv
  Here is the name: Code
  (many more here, truncated for brevity)
Those were the names you passed in:
  You passed in 5KPlayer
  You passed in aesm_service
  You passed in Airplay
  You passed in AppleMobileDeviceProcess
  You passed in ApplicationFrameHost
  You passed in CepstralLicSrv
  You passed in Code
  (many more here too, again truncated for brevity)

Because we were able to keep the needs of our function tightly scoped to just the Name property, we were able to create a function that was highly flexible and reusable.

Conclusion

In this post we saw how to create a function that would work in the pipeline. We did it two ways, first by passing in entire objects, then by passing in a specific property of an object.

But there’s still more fun to be had! In a future post we’ll see how to implement two switches common to all cmdlets and advanced functions, Verbose and Debug.

The demos in this course came from my Pluralsight course PowerShell 7 Quick Start for Developers on Linux, macOS and Windows, one of many PowerShell courses I have on Pluralsight. All of my courses are linked on my About Me page.

If you don’t have a Pluralsight subscription, just go to my list of courses on Pluralsight. At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.

Fun With PowerShell Advanced Functions

Introduction

In my previous post, I covered the use of PowerShell Basic Functions. In this post we’ll cover Advanced Functions.

Advanced Functions provide several abilities over basic ones. First, you can create a detailed parameter list, including the ability to include optional parameters.

Second, you can pipeline enable your functions. Just like a cmdlet, you’ll be able to take input from the pipeline, work with it, then send it out your function back into the pipeline. This will be the subject of our next post.

Finally, you can use features such as Verbose and Debug, which will be the subject of an upcoming blog post.

For all of the examples, we’ll display the code, then under it the result of our code. In this article I’ll be using PowerShell Core, 7.1.3, and VSCode. The examples should work in PowerShell 5.1 in the PowerShell IDE, although they’ve not been tested there.

To run a snippet of code highlight the lines you want to execute, then in VSCode press F8 or in the IDE F5. You can display the contents of any variable by highlighting it and using F8/F5.

A Simple Advanced Function

In my Basic Functions post, I created a simple function Get-AValue that multiplied two values together and returned the result. Let’s take a look at the advanced version, Get-BValue.

function Get-BValue()
{
  [CmdletBinding()]
  param (
          [Parameter( Mandatory = $true
                    , HelpMessage = 'Enter value one.'
                    )
          ]
          [int] $one
        , [Parameter( Mandatory = $false
                    , HelpMessage = 'Enter value two.'
                    )
          ]
          [int] $two = 42
        )

  return $one * $two

}

We start with the function keyword, followed by the name we’ve chosen for the function, in this case Get-BValue. Note the parenthesis are empty, unlike a basic function advanced ones declare their parameters in a different spot.

The first line you’ll see in the function is [CmdletBinding()]. This is the indicator to PowerShell that this is an advanced function. It must be included in the first line for PowerShell to treat this as advanced.

Next up are the parameters, inside a param () block. Within we list each parameter, separated by commas.

A useful feature is the ability to add parameter attributes. For the first parameter, $one, we have the following block:

[Parameter( Mandatory = $true
          , HelpMessage = 'Enter value one.'
          )
]

The first attribute is Mandatory. This indicates that this is a required parameter, and can be set to either $true or $false. When $true, if the user does not enter it they will be prompted to supply a value.

The HelpMessage is text that will be displayed when the user gets help on your function. We won’t do much with it in this demo, but I’ve included it to show there are a variety of attributes you can use with parameters.

Next up is the actual parameter declaration, [int] $one. In this demo we’ve strongly typed our variable. Only integer values will be allowed, any string our decimal value (such as 3.14) will be rejected by PowerShell.

Note that for readability I’ve chosen to split the code onto separate lines, you could have condensed all this into a single line if you’d wanted.

Strong typing has its pros and cons. It can be very helpful in cases where the data must be of a specific type. On the other hand, it can prevent other uses. In this demo for example, we could just have easily multiplied a decimal value.

If you want a flexible variable, then you can leave off the type declaration. In this example we’ll leave it in, so you can see how it works.

Next up is the second variable declaration.

, [Parameter( Mandatory = $false
             , HelpMessage = 'Enter value two.'
             )
  ]
  [int] $two = 42

Here, we’ve set Mandatory to $false. This means if the user does not supply a value for $two, it will use the default value. Where does the default value come from?

If you look you’ll see [int] $two = 42. The 42 is the default value, and is used when the user does not supply a value.

After the parameter block ends we have the actual code for the function, in this case a simple return $one * $two.

So let’s see some examples of how this works. In your development environment highlight the function and press F8 (for VSCode) or F5 (for the PowerShell IDE). Now that it’s in memory you can call it.

Get-BValue -one 33 -two 42

Result:
1386

Here I called the function, passing in the parameters by name. I could have also left off the names and called it by position, as we did in the tutorial on basic functions.

Now lets call it only passing in a value for -one.

Get-BValue -one 33

Result:
1386

In this case, it took our input for -one, but since no value for -two was passed in, the function used the default value of 42.

So what happens if we call it and pass in no values?

Get-BValue

Result:
cmdlet Get-BValue at command pipeline position 1
Supply values for the following parameters:
one: 33
1386

As you can see in the Result: output area, we were prompted for a value for one. I entered 33 and hit enter, then got the expected result of 1386.

To illustrate how the strong typing works, let’s call passing in a string for the -one parameter.

Get-BValue -one "x"

Result:
Get-BValue: Cannot process argument transformation on parameter 'one'. Cannot convert value "x" to type "System.Int32". Error: "Input string was not in a correct format."

As you can see, PowerShell rejects our input with the resulting error message.

Conclusion

In this post we got a start in PowerShell Advanced Functions. We covered a simple advanced function, including parameters and how to use attributes with them.

But there’s still more fun to be had! In the next post we’ll see how to pipeline enable your advanced functions. Then in a future post we’ll implement two switches common to all cmdlets and advanced functions, Verbose and Debug.

The demos in this course came from my Pluralsight course PowerShell 7 Quick Start for Developers on Linux, macOS and Windows, one of many PowerShell courses I have on Pluralsight. All of my courses are linked on my About Me page.

If you don’t have a Pluralsight subscription, just go to my list of courses on Pluralsight. At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.

Introduction to the SQL Server Mobile Report Publisher – Now Live on Pluralsight

My latest Pluralsight course, Introduction to the SQL Server Mobile Report Publisher, just went live!

The Mobile Report Publisher is a powerful, under-appreciated tool that lets you quickly and easily create reports for not just the web, but for a variety of platforms such as Apple’s iOS and Google Android.

In this course, Introduction to the SQL Server Mobile Report Publisher, you’ll learn to quickly and easily create dashboards for the web, as well as mobile devices such as phones and tablets.

First, you’ll explore how to use the Report Publisher designer to create the layout of the dashboard.

Next, you’ll see how to create datasets and bind them to the dashboard.

Finally, you’ll learn advanced features such as filters to limit the data on the dashboard, as well as drillthroughs to launch a detailed report or web page.

When you’re finished with this course, you’ll have the skills and knowledge of the Mobile Report Publisher needed to create dashboards on multiple platforms.

What’s that you say? You don’t have a subscription but want to watch my course? Hey, no problem. Pluralsight has a 10 day free trial. Just go to https://www.pluralsight.com/pricing/free-trial and sign up!

Azure PowerShell PlaybooK: Azure SQL–Now on Pluralsight!

My latest course is now available on Pluralsight! It’s the Azure PowerShell Playbook: Azure SQL. If you aren’t familiar with Pluralsight’s Playbook format, they are fast past courses that are almost 100% demo driven. They are meant to be consumed quickly, my course is just one hour and four minutes long. Great lunchtime viewing!

This course shows you how to use PowerShell to manage and migrate your on premises database up to Azure SQL. In brief, you’ll learn how to:

  • Create resource groups
  • Create and work with Storage Accounts
  • Create a SQL Server in Azure SQL
  • Package up your local database into a bacpac file
  • Import your bacpac file into a new Azure SQL database
  • Execute commands against your new Azure SQL database
  • Cleanup! I even show how to remove everything you’ve created, individually or as a whole

And all of this with PowerShell!

Additionally, I’ve included functions for just about everything listed, so (assuming your subscription gives you access to the samples) you’ll have a great starting point for your own library of cmdlets. (All the code for the functions appears on screen, so if you have to you could always pause and type it in.)

You can find my new course at:

https://www.pluralsight.com/courses/azure-powershell-sql-playbook

I also wrote an article for RedGate’s SimpleTalk website that aligns well with this course. I dive deeper into the restartability aspect of the way the functions were coded, something I couldn’t get deep into with the video course due to time constraints.

https://www.red-gate.com/simple-talk/sysadmin/powershell/powershell-functions-reusability-restartability-azure/

What’s that? Yes you in the back row, you say you don’t have a Pluralsight subscription? Well no worries, just email me, free@arcanetc.com and I’ll be glad to send you a code that will be good for 30 days at Pluralsight. During that time you can watch my courses, indeed you can watch any course at Pluralsight.

Introduction to DW/BI–My Newest Old Course at Pluralsight

Way back in November of 2010, I published my second course with Pluralsight, Introduction to Data Warehousing and Business Intelligence. Over that time a lot has transpired. While the basic fundamentals haven’t changed, a lot of technology has. In addition, Microsoft’s WideWorldImporters sample provides a great platform for demonstrating many of the concepts.

Additionally, I have to admit that after eight years the slides and accompanying graphics have started to look a bit dated. As a result, Pluralsight asked me to update the course.

I’m happy to announce the new and improved version of Introduction to Data Warehousing and Business Intelligence is now live at Pluralsight!

In case the title didn’t give it away, in this course I cover all the basics of data warehousing and business intelligence. What are facts and dimensions, and what do they actually look like in the database? How should you design a data warehouse?

All new to this course, you’ll see the various tools  from Microsoft to do DW/BI, using the all new Wide World Importers sample database as our platform. You’ll see how to design a database using SSDT, and see how the concepts of DW/BI were implemented.

You’ll also see how SSIS was used to achieve ETL, as well as SSAS to create an analytic cube to do BI. Multiple tools are then explored for reporting; SSRS, Mobile Report Publisher, Excel, and PowerBI.

So, who is this course for?

As a DBA, you’ll be asked to implement new data warehouse projects. The design of a data warehouse is very different from a traditional database, and it’s important to understand these differences. In addition, you’ll be asked to install and configure the data warehouse tools. We’ll talk about the tools included with SQL Server, such as SQL Server Integration Services, Analysis Services, and Reporting Services.

If you are a database designer or developer, you’ll be asked to design these data warehouses. As we said, the design is VERY different from a traditional database. You need to understand these differences in order to implement a data warehouse.

Software developers are interacting with data warehouses on an increasing basis, in order to get additional data for their programs. In the past developers had to create custom interfaces to other systems in order to get additional information needed for their applications. In today’s world developers are being told to get that required data from the central repository of the data warehouse.

Finally, this course is valuable for project managers and business users who seek to understand the systems they can pull data from in order to do self service reporting. By the end of the course users will understand what terms like facts and dimensions mean, and how to effectively use them in your reporting.

If you aren’t already a Pluralsight customer, but are interested, just shoot me an email: free @ arcanetc.com. I can hook you up with a code which will give you 30 days of access to their site, during which you can watch my courses, or any of the courses on Pluralsight. S

IT ops and news talk–Episode 3 Secure that Jump Server

I owe everyone an apology, I missed blogging about this at the time it occurred. Last December I was a guest on Don Box’s podcast, “IT ops and news talk”. I appeared on Episode 3, Secure that Jump Server.

In the podcast we discuss the testing of PowerShell code with Pester. After that we got into an interesting discussion on the current state of DevOps. Give it a listen I think you’ll enjoy, it’s about half an hour in length. Don is a great interviewer it was a lot of fun.

As a follow on to the discussion, you might want to learn more about Pester. I have a complete course on the subject in my Pluralsight course Testing PowerShell with Pester. If you don’t know anything about PowerShell, but want to learn, then I’d suggest my Beginning PowerShell Scripting for Developers. Note that even though it says “…for Developers” we don’t mean programmers (although it could), instead it refers to people who wish to develop scripts in PowerShell.

What? What was that? You in the back row waving your hand? You say you don’t have a Pluralsight subscription? Hey, no problem. Just email me, free <at> arcanetc.com and I can send you a code good for 30 days of free access to Pluralsight, with which you can watch not just my courses, but any course from Pluralsight’s library of over 5,000 courses.

What’s New in SQL Server 2016 Reporting Services–Now live at Pluralsight

So you want to see what’s new in SQL Server 2016 Reporting Services? Well I’ve got just the thing for you. My 12th course just went live on Pluralsight, named appropriately “What’s New in SQL Server 2016 Reporting Services”.

This course is designed specifically for those who are already familiar with SQL Server Reporting Services, and just need to come up to speed with the new features in the 2016 release. Even better you can do it during a long lunch (or two short ones), the course is just under an hour and a half so you can learn quickly.

The course starts with a look at the brand new Report Portal, the replacement for Report Manager. You’ll see how to create KPIs right in the portal, improvements to subscriptions, and how to custom brand the Report Portal so it can integrate right into your companies websites. After that you’ll learn about the new HTML 5 compatibility, something that makes SSRS integrate even better into your web apps and sites.

Some time is then spent on improvements to what Microsoft calls “Paginated Reports”, essentially the same reports you’ve grown to know and love. You’ll see improvements for embedded reports, as well as the two new chart types, sunburst and treemap. Finally, the long awaited ability to arrange parameters is covered.

Last, but certainly not least, the course covers what I consider to be the most exciting piece of the 2016 release, the Mobile Report Publisher. You’ll see how to use it to generate reports, and how they can be used on the web as well as mobile devices such as phones and tablets.

What’s that you say? You want to see it, but don’t have a Pluralsight subscription? Hey, no problem. Just shoot me an email, free at arcanetc.com. I can send you a code good for 30 days during which you can watch this and any of the over 5,000 (yes, FIVE THOUSAND) courses.

Testing PowerShell with Pester

My newest Pluralsight course is now live, Testing PowerShell with Pester! In this 4 hour course I walk through a complete introduction to Pester, to showing you how to both modify existing code as well as create a brand new module using Pester to guide development using the Test Driven Development methodology. You’ll find all the details at:

https://www.pluralsight.com/courses/powershell-testing-pester

Not as familiar as PowerShell as you’d like to be? I have several other courses at Pluralsight that may help, especially my Beginning PowerShell Scripting for Developers course. You can see my full catalog of courses at:

https://www.pluralsight.com/authors/robert-cain

What? You don’t have a Pluralsight subscription? No problem, just shoot me an email to free<at>arcanetc.com and I’ll send you a code good for a 30 day trial to Pluralsight, with which you can watch my courses, or any of the great courses by my fellow Pluralsight authors.

Arcane Training and Consulting, LLC

Well this has been an exciting last few months. I’m finally able to share what’s been happening with me.

As some of you know, I’ve left Pragmatic Works. They went through a reorganization of sorts, and several of us either had our positions eliminated, or as people departed were not replaced. Let me be clear, I have no ill will toward them. This was purely a business decision.

I have to be grateful to them, as through them I gained many new opportunities for speaking, authoring, and the like. In addition I worked with many top level people, who constantly challenged me. I wish them the best of luck in their new business model.

As my good friend Alan Stevens likes to say, “…sometimes getting fired is the best thing that can happen to you”. In my case, it has finally given me the impetus to start my own company, a dream I’ve had for a while.

As Arcane Training and Consulting, LLC, I plan a twofold approach. First, I will continue my passion for training primarily through continuing my videos on Pluralsight. Here I have a suite of ten videos already, with another shortly to follow. If you don’t have a Pluralsight subscription, email me free@arcanetc.com and I will be happy to set you up with a 30 day trial through which you can watch my videos, or those of any of the other awesome authors at Pluralsight.

In addition I will also be setting up a YouTube channel, I’ll post more on that as it comes about. Finally I will be available to do live training, both customized as well as public courses. I’m already scheduled to do a one day precon for an event here in Birmingham Alabama to be held in August, and hope to do more for SQL Saturdays, code camps, and other such events.

On the consulting side, I’m very excited to announce I’ve now become a team member at Linchpin People! I have long admired the company, especially the outstanding quality of the people who work with them. I can’t tell you how thrilled I am to be part of their team now.

If you aren’t familiar with their model, they are basically a confederation of independent consultants with a top notch sales team. This gives them a pool of outstanding talent to draw from. In addition it gives me, as a consultant, a great team to back me up, people to draw on when I need additional resources for a project. Likewise when they have projects they can pull me in very easily. I see this as a great partnership between Arcane Training and Consulting and Linchpin People.

Finally, I am in the process of wrapping up a new book deal. I can’t say much about it right now, but look for a new tome from the old Arcane Coder to come out sometime around the end of 2016 or early 2017.

If you have any inquires about my training or consulting services, please email me at info@arcanetc.com. With the rush of getting things setup I still haven’t had time to establish a real website, but for now I’ve just updated my company domain of http://arcanetc.com points to my company Facebook page http://www.facebook.com/arcanetc . I’ll revise it once I get a website created.

I’ve also setup a company twitter account of @ArcaneTC. I’ll use this for company announcements like upcoming training, releases of new Pluralisght courses, and the like. Of course you can still follow me at @ArcaneCode.

Thanks for following along, and hope to do business with you in the future!

PowerPivot with Pluralsight

For the last few months I’ve been cooped up here in the Arcane dungeon, working on a new project. And now that’s it’s released I can finally share all the details.

While I’m still happily employed during the days as a BI Architect for Comframe, in my spare time I’ve been acting as a technical advisor to a training company called Pluralsight.

I’ve developed a series of four training modules on PowerPivot for Excel 2010. The first module is an overview, that gives you the basic steps on creating a pivot table using PowerPivot.

Module 2 goes in-depth on working with data behind the scenes. How to add calculations, formatting data, filtering data and more.

In the third module I cover the creation and formatting of Pivot Tables, formatting them for users, using DAX (Data Analysis eXpressions), and how to use Sparklines with PowerPivot data.

In the last module we explore the charting capabilities of PowerPivot, covering various types of charts and ways to format charts. We then use the knowledge we’ve gained over the last four modules to create a realistic dashboard.

If you are already a subscriber to Pluralsight, take a look, the courses are now live. If you aren’t, take a look, Pluralsight has the best training I’ve seen on the net and it’s growing daily.