Get started Bring yourself up to speed with our introductory content.

Build a PowerShell logging function for troubleshooting

A basic administrative skill is checking over logs to find out why something broke. Learn how to build a proper logging mechanism to catch errors for diagnostic purposes.

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 ways is to create a custom PowerShell logging function that directs the output where you want it in your scripts and functions.

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, we 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 do 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: Since the function allows 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 does logging without outputting to the host.
  • SQL: The switch defines whether or not 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 allow for 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.

PowerShell logging function output
Figure 1. Example of output from the PowerShell logging function.

Additional logging functionality to consider

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

Dig Deeper on Windows administration tools

Join the conversation

2 comments

Send me notifications when other members comment.

Please create a username to comment.

What are some techniques you use to handle logging in your environment?
Cancel
i created a module with some functions and one of them i named Show-Errorlog. It connects to Azure Analytics and send a KQL query, read the response and show the wanted information (using powershell to filter the "known" errors. I have made the function so that it reads a file with the specific KQL query. If i want to run another query i just call it. I also added a parameter to use for a timeframe.
this gives me a flexible way to quickly check the analytics log. 
Cancel

SearchServerVirtualization

SearchCloudComputing

SearchSQLServer

SearchEnterpriseDesktop

SearchVirtualDesktop

Close