Words: 646
Time to read: ~ 3 minutes
Date of writing: 2017-10-19
Logs!
Everybody has dealt with them in one form or another; Log Files, Captain’s Logs, Yule Logs, the list goes on!
In my case, we have logs on SQL Server Agent jobs; whether they’ve completed successfully or not, as well as any output created through the running of whatever is in the jobs.
Since these logs are mainly to do with Database Maintenance, we use them to identify indexes, statistics, backups, or DBCC CHECKDBs that are taking a particularly long time to maintain so we can hopefully do something about them.
We send out notification emails if jobs fail as well but hey, we’re DBAs, we like a backup!
Since this used to be done manually, and since I like PowerShell, I created separate functions that can take individual log files and parse out the needed data in a nice, easy format.
My problem, and the reason for this post, was figuring out, based on the name of the file in a directory, which function to call…
MCVE:
Let us create some strings so everyone can play along at home.
The files were split out into different .txt files bases on what was run, and I needed to invoke a particular function depending on what was in the file name.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#Random Strings | |
0..4 | | |
ForEach-Object –Begin { | |
$values = 'Index', 'Stats', 'Backups', 'Restores', 'Checkdbs' | |
} –Process { | |
(New-Guid).ToString().Insert((Get-Random –Maximum ((New-Guid).ToString().Length)), $values[$_]) | |
}; |

I’ve thrown that into a variable called
$fileNames
just to call them easier e.g.$filenames = 0..4 | ForEach-Object....
Since I don’t always know the exact string, I can’t use -eq
, I have to rely on checking for parts of the string.
Simple Option
Now I could have just gone with the simple choice and used what I know. This would have been using multiple if / elseif
statements with the -match
operator.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$fileNames | ForEach-Object –Process { | |
if ($_ -match 'Index') { | |
"Use index on: $_" | |
} elseif ($_ -match 'Stats') { | |
"Use stats on: $_" | |
} elseif ($_ -match 'Backups') { | |
"Use backup on: $_" | |
} elseif ($_ -match 'Restores') { | |
"Use restores on: $_" | |
} elseif ($_ -match 'Checkdbs') { | |
"Use checkdbs on: $_" | |
} | |
}; |
This works…

…but it’s not brief and I’ve been learning about the switch
operator so I want to use that.
What’s the point in learning about stuff if you’re not going to implement it, right?
That was the start of my problem…
When intuition proves me an idiot…
So I change the code away from the if / elseif
code to a switch
statement, added in a final clause in case I feck things up, and let it run…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$fileNames | ForEach-Object -Process { | |
switch ($_) { | |
($_ -match 'Index') { "Use index on: $_" } | |
($_ -match 'Stats') { "Use stats on: $_" } | |
($_ -match 'Backups') { "Use backups on: $_" } | |
($_ -match 'Restores') { "Use restores on: $_" } | |
($_ -match 'Checkdbs') { "Use checkdbs on: $_" } | |
Default { "didn't match anything…" } | |
}; | |
}; |

Okay, that failed miserably. Let’s quickly glance at the help on the switch
operator and see what we’re doing wrong i.e. Get-Help about_Switch -Full;

If we throw a -regex
before the value, we can check if it matches our string! Brilliant, let’s do that!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$fileNames | ForEach-Object –Process { | |
switch –regex ($_) { | |
'Index' { "Use index on: $_" } | |
'Stats' { "Use stats on: $_" } | |
'Backups' { "Use backups on: $_" } | |
'Restores' { "Use restores on: $_" } | |
'Checkdbs' { "Use checkdbs on: $_" } | |
Default { "didn't match anything…" } | |
}; | |
}; |
And it works! Brilliant!

That’s not all folks!
Why I like blogging is because I learn stuff, arguably more stuff than just by working through the problem.
Such as I need to read the help files more rather than just skimming through them…
If you think about the above examples, something is wrong. It seems that you can do a lot more with if
than you can do with switch
.
This can’t be the case, otherwise why would Microsoft promote it when you have multiple if statements?
I must be missing something…
So I go back and read through the help files again, all of it…

In my haste and false intuition, I assumed that switch
couldn’t take an expression…all because my intuition said I had to wrap my expression in brackets.
It should have been curly brackets!!!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$fileNames | ForEach-Object –Process { | |
switch ($_) { | |
{$_ -match 'Index'} { "Use index on: $_" } | |
{$_ -match 'Stats'} { "Use stats on: $_" } | |
{$_ -match 'Backups'} { "Use backups on: $_" } | |
{$_ -match 'Restores'} { "Use restores on: $_" } | |
{$_ -match 'Checkdbs'} { "Use checkdbs on: $_" } | |
Default { "didn't match anything…" } | |
}; | |
}; |
Which works!

Final Round
Now in this case, I am happy with the switch -regex
as it’s shorter and more compact but, if the time came where I need to check on multiple conditions or need to use an expression in the switch
, it’s nice to know that I don’t have to revert to a whole heap of if / elseif/ else
statements.
Fancy. Do you know how to work with “-or” here? So, if string matches A or B then do X ; if string matches C OR D then do Y.?
Hi Greg, you can use scriptblocks for the first part of the switch statement.
This should also work
switch ($fileNames) {
{$_ -match ‘Index|Stats’} { “It’s maintenance”; $_ }
{$_ -match ‘Backups|Restores|Checkdb’} { “It’s backup & checkdb”; $_ }
else {“Oh no”; $_}
}
I’m using the pipe character (|) as a regex or but you can also do {$_ -match ‘Index’ -or $_ -match ‘Stats’} if it’s more complex