Capturing Write-Verbose/Error/Warning in Custom Cmdlets and saving in SQL

PowerShell Help
1

Hi

I’m exploring Don Jones’ PS best practices about output from scripts using CmdletBinding() and the various Write-Verbose, Write-Debug etc.
I want to combine this with the use of Custom Modules, and i want to capture all output as it is being produced (not getting it afterwards from a piped txt) and saving it in a SQL Server table.

This appears to be no trivial task :slight_smile:

Google shows a lot examples with main.ps1 9>&1> mylog.txt, which doesn’t cut if for me.

I’ve read these blogposts, which resolve the issues of passive down environment scope, to make Cmdlets understand -Debug parameters on the main script.
https://powershell.org/2014/01/13/getting-your-script-module-functions-to-inherit-preference-variables-from-the-caller
https://powershell.org/2014/01/20/revisited-script-modules-and-variable-scopes/

I dont think is a great solution, but it has by far been the best i could find.
So how to i use Write-Output, Verbose, Error,etc… through multiple layers of custom Cmdlets and get all the output logged to SQL server, and displayed on Console as its being produced.

My own suggestion, is to send all log messages through a Log function, which then again can call a Console Write function, and a Sql Wrinte function, thereby getting logmessages both places.

-Drawbacks:

  • Write-Error no longer shows which line it was call from, it will always appear to originate from the Log function
  • If we implement this in all our tools, it becomes challenging to run scripts by hand without having to include log classes etc.

– Script example:

Main.ps1

[CmdLetBinding()]
Param ()

Import-Module .\Import3rdPartyModules.psm1 -Force
Import-Module .\Logging-Module.psm1 -Force
Import-Module .\WindowsServicesModule.psm1 -Force

if($PSBoundParameters[‘Verbose’] -eq $true) { $VerbosePreference = “Continue” }
if($PSBoundParameters[‘Debug’] -eq $true) { $DebugPreference = “Inquire” }

Stop-WindowsService -Computername “kDesktop01” -ServiceName “MyWindowsService”

WindowsServicesModule.psm1

Function Stop-WindowsService
{
[CmdLetBinding()]
Param
(
[string]$ComputerName,
[string]$ServiceName
)

# Used to pass down environment scope for external verbose/debug paramters. E.g. Main.ps1 -Debug
Get-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

Log -Message "Stopping $ServiceName on $ComputerName"
# Faking stop Windows service call

Log -Message "Problems stopping $ServiceName on $ComputerName" -IsWarning

Log -Message "There was a problem stopping $ServiceName on $ComputerName" -IsError

Log -Message "$ServiceName on $ComputerName could not stop, as it does not exist :)" -IsDebug

}

#LoggingModule.psm1
Function Log
{
[CmdLetBinding()]
Param
(
[Parameter(Mandatory=$True)]
[string]$Message,
[switch]$IsDebug,
[switch]$IsVerbose,
[switch]$IsWarning,
[switch]$IsError
)

# Used to pass down environment scope for external verbose/debug paramters. E.g. Main.ps1 -Debug
Get-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

WriteToConsole -Message $Message -IsDebug $IsDebug -IsVerbose $IsVerbose -IsWarning $IsWarning -IsError $IsError
#WriteToDatabase -Message $Message -IsDebug $IsDebug -IsVerbose $IsVerbose -IsWarning $IsWarning -IsError $IsError

}

Function WriteToConsole
{
[CmdLetBinding()]
Param
(
[Parameter(Mandatory=$True)]
[string]$Message,

    [boolean]$IsDebug = $false,
    [boolean]$IsVerbose = $false,
    [boolean]$IsWarning = $false,
    [boolean]$IsError = $false
)

# Used to pass down environment scope for external verbose/debug paramters. E.g. Main.ps1 -Debug
Get-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

if ( $IsDebug -eq $True )
{
    Write-Debug -Message $Message
}
elseif ( $IsVerbose -eq $True )
{
    Write-Verbose -Message $Message
}
elseif ( $IsWarning -eq $True )
{
    Write-Warning -Message $Message
}
elseif ( $IsError -eq $True )
{
    Write-Error -Message $Message
}
else
{
    Write-Output $Message
}

}

#Import3rdPartyModules.psm1

Load Custom modules

function Get-ScriptDirectory { Split-Path $MyInvocation.ScriptName }
$ScriptDirectory = Get-ScriptDirectory

################################################

– Get-CallerPreference.ps1 –

Added by: kbrandenburg 12062014

Last updated by: kbrandenburg 12062014

Source URL: http://gallery.technet.microsoft.com/Inherit-Preference-82343b9d

Description: Fixes passing variables between script-scopes (Cmdlets does not get main script scope passed down)

Problem/Solution link: https://powershell.org/2014/01/20/revisited-script-modules-and-variable-scopes/

Problem/Solution link: https://powershell.org/2014/01/13/getting-your-script-module-functions-to-inherit-preference-variables-from-the-caller/comment-page-1/

################################################
. $ScriptDirectory’\3rdPartyModules\Get-CallerPreference.ps1’

2

The only thing I can think of which meets the requirements you’ve listed would be to create a new instance of System.Management.Automation.PowerShell and run the scripts inside that. Then you can set up event handlers to respond to the various streams as they write data, and push them into a queue to be added to SQL (which should preserve their order, since PowerShell is basically single-threaded.) In order to do this properly without screwing up the order, you’d probably have to implement parts of it in C# using a .NET event handler directly, instead of writing it in PowerShell and using Register-ObjectEvent.