Build a PowerShell logging function for troubleshooting

Editor's note: This article was republished after a technical review and light updates.

If you think ahead and implement proper logging throughout your PowerShell scripts, you will have a much easier time troubleshooting anything that goes wrong.

There are many different ways to think about logging. Within PowerShell, this could mean logging to the host, a file, the Windows event log, a database, syslog on Linux or even a combination of these choices.

It helps to have options and flexibility, but the setup process to log to all the different places can be difficult.

One of the preferred methods is to create a custom PowerShell logging function that directs the output where you want it in your scripts and functions.

In effect, a custom PowerShell logging function lets administrators gather the information that they need from the system or devices that they're interested in, while locating any log output in a location they can easily find. This is particularly handy when troubleshooting a specific system or device. The time-saving benefits for troubleshooting cannot be overstated.

Building the framework

First, decide on the logging options to include. A good starting point is to log to the host, a file and a database.

Next, build the skeleton of the PowerShell logging function.

Function Write-Log {
     [CmdletBinding()]

     Param (
         $Message
     )

     Process {
         Write-Host $Message
     }
 }

At its simplest, we define a single parameter and output it back to the host. Using CmdletBinding will let us take advantage of advanced cmdlet functionality that is explained further along in this tutorial.

Prepping the parameters

Next, add some additional parameters. One of the common functionalities within PowerShell logging is the level of logging such as information, verbose, error or debug.

Add a few parameters into the Param block and some decorative statements to make the PowerShell logging function more comprehensive.

Param (
     [Parameter(
         Mandatory=$true,
         ValueFromPipeline=$true,
         Position=0)]
     [ValidateNotNullorEmpty()]
     [String]$Message,

   [Parameter(Position=1)]
     [ValidateSet("Information","Warning","Error","Debug","Verbose")]
     [String]$Level = 'Information',

     [String]$Path = [IO.Path]::GetTempPath(),
     [String]$Server,
     [String]$Database,
     [String]$Table,

     [Switch]$NoHost,
     [Switch]$SQL,
     [Switch]$File
 )

The functionality of the new parameters in the script does the following:

• Message. This is a string sent to the different destinations in the script. This is mandatory, pipeline enabled and usable without defining a parameter.
• Level. The level corresponds to one of the following: information, verbose, error, debug. The default is information. The script makes this usable without defining a parameter.
• Path. Because the function enables logging to a file, use the default temporary path. The script uses [IO.Path]::GetTempPath() to work cross-platform, defaulting to the temp path in Linux and Windows.
• Server. To support logging to a database -- in this case, SQL Server -- the script provides the server, which relies on Windows authentication.
• Database. Along with the server, the script supplies a database to store the data.
• Table. This section defines the table where we will send the logs.
• NoHost. This portion generates log entries without outputting to the host.
• SQL. The switch defines whether to send logs to SQL Server. This requires setting the Server, Database and Table.
• File. The switch defines whether to send logging output to the file.

Defining the logic

The next portion of our PowerShell logging function centers on the output.

$DateFormat = "%m/%d/%Y %H:%M:%S"

 If (-Not $NoHost) {
   Switch ($Level) {
     "information" {
       Write-Host ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message)
       Break
     }
     "warning" {
       Write-Warning ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message)
       Break
     }
     "error" {
       Write-Error ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message)
       Break
     }
     "debug" {
       Write-Debug ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message) -Debug:$true
       Break
     }
     "verbose" {
       Write-Verbose ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message) -Verbose:$true
       Break
     }
   }
 }

 If ($File) {

Add-Content -Path (Join-Path $Path 'log.txt') -Value ("[{0}] ({1}) {2}" -F (Get-Date -UFormat $DateFormat), $Level, $Message)
 }

 If ($SQL) {
   If (-Not $Server -Or -Not $Database -Or -Not $Table) {
     Write-Error "Missing Parameters"
     Return
   }

   $connection                  = New-Object System.Data.SqlClient.SqlConnection
   $connection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security=SSPI;"

   If (-Not ($connection.State -like "Open")) {
     $connection.Open()
   }

   $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
   $sqlCommand.Connection = $connection

   $sqlCommand.CommandText = "INSERT INTO [$Database].dbo.$table ( DateTime, Level, Message ) VALUES ( @DateTime, @Level, @Message )"

   $sqlCommand.Parameters.Add("@DateTime", [System.Data.SqlDbType]::VarChar, 255) | Out-Null
   $sqlCommand.Parameters.Add("@Level",    [System.Data.SqlDbType]::VarChar, 255) | Out-Null
   $sqlCommand.Parameters.Add("@Message",  [System.Data.SqlDbType]::NText) | Out-Null

   $sqlCommand.Parameters['@DateTime'].Value = ( Get-Date -UFormat $DateFormat )
   $sqlCommand.Parameters['@Level'].Value    = $Level
   $sqlCommand.Parameters['@Message'].Value  = ($message | Out-String)

   Try {
     $sqlCommand.ExecuteNonQuery() | Out-Null
   } Catch {
     Write-Error "Unable to Insert Log Record: $($_.Exception.Message)"
   }

   If ($connection.State -like "Open") {
     $connection.Close()
   }
 }

There are three different sections to the code, which is structured to enable multiple logging variations, such as output to three different sources at the same time.

• Host: You can generate output based on the level required, such as verbose, warning or error. If we have defined the nohost switch, then we will skip this. Otherwise, the default of our function is to output to the host. The script forces the Write-Debug and Write-Verbose functions with -Debug:$true and -Verbose:$true to send output to the host.
• File. Outputting to a file is the simplest of the functions here. The script adds content to a file for each new log line.
• SQL. The most complicated function is the SQL functionality. Most of this is boilerplate code that will set up the connection and prepare the insert command. The script uses SQL parameterization to make the insert code safer.

The following is an example of a Logs table that contains the fields defined in the insert query to set up the database to insert the data.

CREATE TABLE [dbo].[Logs](
   [DateTime] [varchar](255) NOT NULL,
   [Level] [varchar](255) NOT NULL,
   [Message] [ntext] NOT NULL,
 );

For the sake of convenience, here's the full code.

Function Write-Log {
  [CmdletBinding()]

     Param (
         [Parameter(
             Mandatory=$true,
             ValueFromPipeline=$true,
             Position=0)]
         [ValidateNotNullorEmpty()]
         [String]$Message,

       [Parameter(Position=1)]
         [ValidateSet("Information","Warning","Error","Debug","Verbose")]
         [String]$Level = 'Information',

         [String]$Path = [IO.Path]::GetTempPath(),
         [String]$Server,
         [String]$Database,
         [String]$Table,

         [Switch]$NoHost,
         [Switch]$SQL,
         [Switch]$File
     )

     Process {
         $DateFormat = "%m/%d/%Y %H:%M:%S"

         If (-Not $NoHost) {
           Switch ($Level) {
             "information" {
               Write-Host ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message)
               Break
             }
             "warning" {
               Write-Warning ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message)
               Break
             }
             "error" {
               Write-Error ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message)
               Break
             }
             "debug" {
               Write-Debug ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message) -Debug:$true
               Break
             }
             "verbose" {
               Write-Verbose ("[{0}] {1}" -F (Get-Date -UFormat $DateFormat), $Message) -Verbose:$true
               Break
             }
           }
         }

         If ($File) {
           Add-Content -Path (Join-Path $Path 'log.txt') -Value ("[{0}] ({1}) {2}" -F (Get-Date -UFormat $DateFormat), $Level, $Message)

         If ($SQL) {
           If (-Not $Server -Or -Not $Database -Or -Not $Table) {
             Write-Error "Missing Parameters"
             Return
           }

           $connection                  = New-Object System.Data.SqlClient.SqlConnection
           $connection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security=SSPI;"

           If (-Not ($connection.State -like "Open")) {
             $connection.Open()
           }

           $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
           $sqlCommand.Connection = $connection

           $sqlCommand.CommandText = "INSERT INTO [$Database].dbo.$table ( DateTime, Level, Message ) VALUES ( @DateTime, @Level, @Message )"

           $sqlCommand.Parameters.Add("@DateTime", [System.Data.SqlDbType]::VarChar, 255) | Out-Null
           $sqlCommand.Parameters.Add("@Level",    [System.Data.SqlDbType]::VarChar, 255) | Out-Null
           $sqlCommand.Parameters.Add("@Message",  [System.Data.SqlDbType]::NText) | Out-Null

           $sqlCommand.Parameters['@DateTime'].Value = ( Get-Date -UFormat $DateFormat )
           $sqlCommand.Parameters['@Level'].Value    = $Level
           $sqlCommand.Parameters['@Message'].Value  = ($message | Out-String)

           Try {
             $sqlCommand.ExecuteNonQuery() | Out-Null
           } Catch {
             Write-Error "Unable to Insert Log Record: $($_.Exception.Message)"
           }

           If ($connection.State -like "Open") {
             $connection.Close()
           }
         }
     }
 }

Using the logging function

Now that we have our function, what's the best way to use it? Is there something else we should be logging? What makes the most sense for troubleshooting in the future?

The following examples show a few ways to use the new logging function.

Write-Log -Message "First Message"
 Write-Log "Second Message" -Level "Warning"
 Write-Log "Third Message" -NoHost -File
 Write-Log "Fourth Message" -SQL -Server "SQLServer" -Database "Logging" -Table "Logs"
 Write-Log "Fifth Message" -Level "Error" -File

What if we log to SQL but don't want to define the same parameters repeatedly? PowerShell gives the option to use default parameters that the functions will use unless there is an override on a per-function call basis.

$PSDefaultParameterValues = @{
     'Write-Log:SQL'      = $true
     'Write-Log:Server'   = 'SQLServer'
     'Write-Log:Database' = 'Logging'
     'Write-Log:Table'      = 'Logs'
 }

By defining some default parameters, the script can use the following.

# Full Function with all Parameters
 Write-Log "Logging Message" -SQL -Server "SQLServer" -Database "Logging" -Table "Logs"
 # Same Function Call with Default Parameters
 Write-Log "Logging Message"

This makes it much easier to control the logging behavior and provides more flexibility.

The end result is you get a PowerShell logging function that builds output, similar to Figure 1.

Additional logging functionality to consider

While the PowerShell logging function in this article is useful, there is much more advanced functionality you can add. That includes output to syslog in Linux and even internal logging for the function itself to diagnose when the script does not perform as expected.