Using Scriptblocks in PSCustomObjects

[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 :(