Voice of the DBA

Tag: powershell

Parsing EXE Output in PowerShell
I saw a post internally that asked this question: Anyone have a handy powershell script testing if the installed flyway version matches a specific string?

That seemed simple, but getting program output from PoSh wasn’t something I’ve tried. So I tackled the challenge and this is what happened.

Getting the Output

The first thing I wanted to do was actually figure out what the output of checking the version was from the CLI. I looked at the help and noticed a version verb. When I run that, I see a bunch of lines of output.

A lot of output. I need to parse a bunch of strings, and then find a line.

My first experiment was to run this to get a file with this output.
```
flyway version > fwversion.txt
```
Now, let’s parse this.

Parsing Content

It’s been awhile since I read stuff from a file, but I know Get-Content works to read the file. What about finding a line. I saw this post with an answer that noted Select-String can be used, so I decided to try that.

Here’s a first cut of code:

That didn’t work. However, with some experiments, I tried this code:
```
Get-content fwversion.txt | select-string 'Edition'
```
That worked.

Now, I’ll assign that to a variable with this code:

$a=Get-content fwversion.txt | select-string ‘Edition’

Next, I’ll split this string by spaces into a new variable with this:

$b = $a -split(‘ ‘)

Then I can evaluate the various element of $b. You can see below the first and third elements are what I’m interested in. Really the third. Remember, PoSh is zero-based.

That let’s me parse the output, but I don’t want to save a file. Now on to the next step.

Capturing the Output from a Program

One of the things I know you can do in a PoSh ptompt is run a program. The redirection operator allows you to move output. When I tried it, I couldn’t quite get the output I wanted, but I did find this post that helped. With that, I ran this code:
```
$a = & "flyway" --version  2>&1 | select-string 'Edition'
```
This runs Flyway, captures the output in a stream and then uses the code above to find the right line. I assign this to a variable.

Almost there.

Adding a Parameter and a Test

Since I want to call this from the CLI and pass i a parameter, I added a param() clause to my script and then a test that compares the version output from the flyway.exe to the parameter. That gest me this code:
```
param(
    [string]$versionToCheck=""
)
$a = & "flyway" --version  2>&1 | select-string 'Edition'
$b = $a -split(' ')
if ($b[3] -eq $versionToCheck)
{ Write-Output("$($b) installed")}
else {
```
Write-Output(“wrong version – $($b) installed”)
}
Now I can call this from the CLI and check things. It works well. At least for now.

I am certainly not a PoSh expert, but this short script took me about 15 minutes to write with a little research. Then a little testing and I sent it off to the requester. Haven’t heard any complaints, so I’m hoping this actually works for them.
September 18, 2024
Updating dbatools–Fixing the Certificate Failure
I was trying to update my dbatools install to test something and go this error.

I fixed it with a little help.

The Fix

The short answer from Chrissy LeMaire is to run this:
```
install-module dbatools  -Force –SkipPublisherCheck
```
That worked.

Why?

Chrissy explained this, but essentially it’s a certificate approval thing. Certificates work by verifying each other in a chain. One cert checks another, which checks another, etc.

In this case, Microsoft changed some things and while the dbatools goes through some of these things, the update-module is doing extra checks. Supposedly MS will fix this.

In any case, if you need an update, you need to bypass things for now.
July 24, 2024
Executing One Line in VSCode–#SQLNewBlogger

I wrote about arrays in PowerShell last week, but I realized one of the things I did while experimenting was look up how to run code a line at a time. I’m sure you can Google for that, but I decided to write a post to help me remember this in the future.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Running One Line

Years ago I saw someone demonstrating either PoSh or Python in Visual Studio and they were walking through code a line at a time. I thought that was cool, like a debugger, but not being in the debugger.

To do this in VS Code, we use F8. Here’s how it works. Create a new PoSh file in VS Code. You should see something like this:

Now put the cursor on line 1, anywhere. I’ll put it after the = sign. If I press F8, I see this:

Pretty cool. I just ran that line.

One Hassle

The one hassle is the focus is now in the terminal. However CTRL+Tab gets me back to the editor, though it would go to the Welcome tab here. However, supposedly CTRL+1 or 2 would work.

My problem is those are mapped to ZoomIt, which is always running

I hit CTRL+Tab twice, however, and I was back in my editor. I could go down one line and hit F8 to run the second code.

Try it for yourself.

If you have a better method, let me know.

SQL New Blogger

After the last post, I spent less than 10 minutes setting up a new file and then capturing some code. A couple google searches helped me figure out the terminal/editor switching.

This was fun, but it also showcased some learning and something that will help me in my work. You could easily do the same thing, with any tool you use at work. Show how you experiment and learn.

December 11, 2023
Removing a PowerShell Array Element–#SQLNewBlogger
I saw an article on this and realized I had no idea how to do this, so I decided to practice a bit. I don’t work with PoSh arrays a lot, but with more and more DevOps work needing complex scripting, PoSh is a better environment for me than bash. At least for now.

This post looks at the basics of creating an array and then removing elements.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Creating a PoSh Array

Creating an array in PowerShell is relatively easy. I use a variable and the @ sign, enclosing the elements inside parenthesis with comma separation. In code, that looks like this:
```
$servers = @("server1", "server2", "server3")
```
or this:
```
$numbers = @(1,2,3,4,5,6,7,8,9,10)
```
If I were to reference an array, I’d get results like this:
```
PS E:\Documents\git\sqlsatwebsite> $servers
server1
server2
server3
```
PS E:\Documents\git\sqlsatwebsite> $numbers[2]
3
As you can see, I can get all elements or a single element with an index inside brackets. Note that the array is zero indexed as the index of 2 returns the third element.

In code, I might use other variables to represent the index or maybe to store the results of an array. If I were trying to brute force the removal of an element, I might decide to copy items one at a time (iterating through the index and skipping the copy if I reached an item to skip.

Removing an Element – Kind of

The way to remove this is deceptively simple. The article referenced above shows the easy way, which is to set an element to $null. I’d have never thought of that, as I’d assume this would leave the element, but change the key.

If I want to remove 5, I’d set $numbers[4]=$Null. Crazy, but let’s see this work. First, I’ll iterate the array, printing each index and the value with this code:
```
for ($i = 0; $i -lt $numbers.Length; $i++) {
```
Write-Host $i : $numbers[$i]
}

When I run this, I see these results:
```
0 : 1
1 : 2
2 : 3
3 : 4
4 : 5
5 : 6
6 : 7
7 : 8
8 : 9
9 : 10
```
Now, I’ll run this:

$numbers[4]=$null

Now we re-run the iteration and see these results:

0 : 1
1 : 2
2 : 3
3 : 4
4 :
5 : 6
6 : 7
7 : 8
8 : 9
9 : 10

Hmmm, the element is gone and isn’t. Nothing has moved, and in fact, the value of 6 remains in index 5. If I try to get element at index 4, I get this:

It’s not there, but the spot is held. That’s somewhat what I’d expect.

Removing an Item – The Better Way

PowerShell has a lot of flexibility in how variables work and how queries work. I found another blog that helped me understand a few things and experiment. Look at this code:

$numbers = $numbers | Where-Object { $_ -ne 5}

If I run my iterator on $numbers now I see:

0 : 1
1 : 2
2 : 3
3 : 4
4 : 6
5 : 7
6 : 8
7 : 9
8 : 10

That works much better, since I create a new array.

Arrays in PowerShell are funny things, and the docs show some of the methods available don’t change the size of an array, they just operate on it. There isn’t a remove method, which is interesting. There is a Remove-Item cmdlet, but even the docs say assigning $null is faster. However, it doesn’t get the indexing right.

If you have a better method, let me know.

SQL New Blogger

I spent about 10-15 minutes experimenting in PowerShell on various code elements trying to understand how different methods change the array. I read some docs, and ended up taking another 15 minutes to write this post and copy over some code.

This was fun, but it also showcased some learning and methodology. You could easily do the same thing, with PowerShell, T-SQL, or any other coding or scripting you do at work. Show how you experiment and learn.
December 4, 2023