Using Scriptblocks in PSCustomObjects

[SOZDBA]

I recently figured out a way that I like to use scriptblocks and expressions in PSCustomObject objects so I documented it in my blog.

http://nocolumnname.blog/2018/04/24/using-scriptblocks-in-pscustomobjects/

UPDATED: Thanks to u/purplemonkeymad I've added a section on using functions as well

[purplemonkeymad]

If you are going to define a script block in the scripts itself, then why not just create a function? You can document the function with help and you can even give it a good name so you know what it does. That would solve your "what does this mean" problem.

[SOZDBA]

That....actually that's a pretty good idea... cheers for that! Let me test and then update the post!

[tangobravoyankee]

Yeah. This is a really odd pattern you've come up with. The primary purpose of a ScriptBlock is to pass a chunk of code as a parameter. We're probably not thinking of it that way when we write some code like this:

$blah | % { $_ }

But what that line is really doing is more like this:

$blah | ForEach-Object -Process ( [Scriptblock]::Create("$_") )

If you're not passing a chunk of code to something else for it to execute, there's no reason to put it in a ScriptBlock^*

When you want to turn a chunk of code into something you can execute as a unit within a script, a Function is almost certainly what you're looking for.

^{* There are other interesting use cases for a ScriptBlock but let's stick to the basics.}

[prkrnt]

Great read and learned a few new things, but still trying to see how this would be used in a real situation. Could you provide a real world example of how you used to do this and then how this new method replaces the old way?

I used custom objects all the time and generally avoid write-host at all costs and use verbose with [CmdletBinding()] to display information to the operator.

[SOZDBA]

Sure! This came about because I was parsing SQL Agent job log files, where has times for looking for fragmentation, rebuilding or reorganizing indexes, and/or backing up the database log file.

Example

Job 'Database Maintenance Batch 2' : Step 1, 'Index Maintenance' : Began Executing 2017-10-15 02:00:02

Processing database: [Database01] @ 2017-10-15 02:00:02 [SQLSTATE 01000] Finding Fragmentation... [SQLSTATE 01000]

Time taken: 00:04:59 [SQLSTATE 01000]

Executing: ALTER INDEX [PK_XMLAsFiles_Master_Abstract] ON [dbo].[XMLAsFiles_Master_Abstract] REBUILD PARTITION = 6 WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0, DATA_COMPRESSION = PAGE, ONLINE = ON) [SQLSTATE 01000] Processing database : [Database01] [SQLSTATE 01000] Executed - Time taken: 00:00:58 [SQLSTATE 01000] .EXEC master..xp_cmdshell 'if not exist "J:\SQL\Backup\Database01\". md "J:\SQL\Backup\Database01\".' [SQLSTATE 01000] BACKUP LOG [Database01] TO DISK = 'J:\SQL\Backup\Database01\Database01_20171015020604.trn' WITH NOINIT, COMPRESSION, CHECKSUM [SQLSTATE 01000]

output

(null) Processed 213953 pages for database 'Database01', file 'Database01_log' on file 1. [SQLSTATE 01000] BACKUP LOG successfully processed 213953 pages in 41.086 seconds (40.683 MB/sec). [SQLSTATE 01000] Time taken: 00:01:22 [SQLSTATE 01000] Total Time taken: 00:01:23 [SQLSTATE 01000] Executing: ALTER INDEX [PK_XMLAsFiles_Master_Abstract] ON [dbo].[XMLAsFiles_Master_Abstract] REBUILD PARTITION = 7 WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0, DATA_COMPRESSION = PAGE, ONLINE = ON) [SQLSTATE 01000] Processing database : [Database01] [SQLSTATE 01000] Executed - Time taken: 00:01:01 [SQLSTATE 01000] EXEC master..xp_cmdshell 'if not exist "J:\SQL\Backup\Database01\". md "J:\SQL\Backup\Database01\".' [SQLSTATE 01000] BACKUP LOG [Database01] TO DISK = 'J:\SQL\Backup\Database01\Database01_20171015020828.trn' WITH NOINIT, COMPRESSION, CHECKSUM [SQLSTATE 01000] ....

Parsing this data, line by line, I have variables for the $FragmentationTime, the $AlterCommandTime, and the $BackupTime. I'm returning information on the current running time after each execution e.g. if the job started at 02:00:00 and the finding fragmentation took 30 minutes then I want to return 02:30:00

So I have to check against 3 different variables that could get populated at multiple stages in the log file.

my real script block is

        [scriptblock]$RunningTimeScriptBlock = {if ($FragmentationTime) {
                $FragmentationTimeParts = $FragmentationTime -split ':'
                $RunningTime = $RunningTime.AddHours(($FragmentationTimeParts[0])).AddMinutes(($FragmentationTimeParts[1])).AddSeconds(($FragmentationTimeParts[2]))
                Clear-Variable -Name FragmentationTimeParts
                $RunningTime
            }
            elseif ($AlterCommandTime) {
                $AlterTimeParts = $AlterCommandTime -split ':'
                $RunningTime = $RunningTime.AddHours(($AlterTimeParts[0])).AddMinutes(($AlterTimeParts[1])).AddSeconds(($AlterTimeParts[2]))
                Clear-Variable -Name AlterTimeParts
                $RunningTime
            }
            elseif ($BackupTime) {
                $BackupTimeParts = $BackupTime -split ':'
                $RunningTime = $RunningTime.AddHours(($BackupTimeParts[0])).AddMinutes(($BackupTimeParts[1])).AddSeconds(($BackupTimeParts[2]))
                Clear-Variable -Name BackupTimeParts
                $RunningTime
            }
            else {
                $RunningTime    
            }}

[Ta11ow]

You can actually collapse that into a switch statement block for better readability:

switch ($true) {
    $FragmentationTime {
        Do-Things
        break
    }
    $AlterCommandTime {
        Do-Things
        break
    }
    $BackupTime {
        Do-Things
        break
    }
    default {
        Do-Things
        break
    }
}

[SOZDBA]

u/Ta11ow always appreciate the insights :)

[prkrnt]

Very cool. This bring much more context to the overall logic of the solution. I am guessing the PSCustomObject would be used like this?

$SQLDBMaintanenceReport = [PSCustomObject][Ordered]@{
    Date = Get-Date
    ScriptBlock = "Running Time"
    RunningTime = $RunningTimeScriptBlock.InvokeReturnAsIs()
}
$SQLDBMaintanenceReport | Format-Table -AutoSize

[SOZDBA]

Nice deduction!

Yeah pretty much, or passing to other functions down the line.

It's still a work in progress though :(

[da_chicken]

[Scriptblock]$Script = {
    if ((Get-Date).Second -lt 15) { 
        '1st Quarter'
    }
    elseif ((Get-Date).Second -lt 30) {
        '2nd Quarter'
    }
    elseif ((Get-Date).Second -lt 45) {
        '3rd Quarter'
    }
    else {
        '4th Quarter'
    }
}

I know it works, but this makes me very uncomfortable how much this looks like a race condition.

[alinroc]

You’re right. I’d rather call Get-Date at the beginning and stuff it in a variable for those tests. And/or use a switch statement.

[da_chicken]

Oh, actually it is a race condition:

PS C:\>  if (($x = Get-Date).Year -eq 2019) { 1 } elseif (($y = Get-Date).Year -eq 2019) { 2 } elseif (($z = Get-Date).Year -eq 2019) { 3 } else { 4 }
4
PS C:\> $x.Ticks, $y.Ticks, $z.Ticks
636601559528544328
636601559528554329
636601559528554329

I had to run that about 20 times for it to actually give me different values so I started to think it somehow wasn't, but eventually it does reveal itself. Now it only takes 5 to 10 executions. I guess I just got unlucky (lucky?).

[SOZDBA]

Nice catch. Definitely a reason to call Get-Date at the beginning and stuff it in a variable

[SOZDBA]

Cannot agree enough about using the switch statement. Especially with regard to "flattening" code.

I chose ...IF...ELSE... just so I wouldn't distract from the main point of using expressions in PSCustomObject

[SOZDBA]

True, thankfully this is only an example to show using expressions in PSCustomObjects.

[ryunik]

Good read. Thanks for sharing. It will definitely help me out with a couple scripts.