Get-SqlErrorLog

Syntax

ByPath (Default)

Get-SqlErrorLog
    [[-Path] <String[]>]
    [-Timespan <TimeSpan>]
    [-Before <DateTime>]
    [-After <DateTime>]
    [-Since <SinceType>]
    [-Ascending]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [<CommonParameters>]

ByName

Get-SqlErrorLog
    [[-ServerInstance] <String[]>]
    [-Timespan <TimeSpan>]
    [-Before <DateTime>]
    [-After <DateTime>]
    [-Since <SinceType>]
    [-Ascending]
    [-Credential <PSCredential>]
    [-ConnectionTimeout <Int32>]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [<CommonParameters>]

ByObject

Get-SqlErrorLog
    [-InputObject] <Server[]>
    [-Timespan <TimeSpan>]
    [-Before <DateTime>]
    [-After <DateTime>]
    [-Since <SinceType>]
    [-Ascending]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [<CommonParameters>]

Description

Examples

Example 1: Get all logs generated in a specific time frame that have a search word

PS C:\> CD SQLSERVER:\SQL\MyServer\MyInstance
PS SQLSERVER:\SQL\MyServer\MyInstance> Get-SqlErrorLog -Since Yesterday  | Where-Object { $_.Text -match 'Error' } | Format-Table

Date                 Source Text                                  ArchiveNo ServerInstance
----                 ------ ----                                  --------- --------------
6/16/2016 6:04:20 PM Logon  Error: 17828, Severity: 20, State: 4.         0 MyServer\MyInstance

Example 2: Get all logs generated in a specific time frame

PS C:\> CD SQLSERVER:\SQL\MyServer\MyInstance
PS SQLSERVER:\SQL\MyServer\MyInstance> Get-SqlErrorLog -Timespan '05:30:00' | Format-Table

Date                  Source  Text
----                  ------  ----
6/17/2016 12:00:00 AM spid26s This instance of SQL Server has been using a process ID of 21520 since 6/10/2016 3:56:...
6/16/2016 6:04:20 PM  Logon   The prelogin packet used to open the connection is structurally invalid; the connectio...
6/16/2016 6:04:20 PM  Logon   Error: 17828, Severity: 20, State: 4.

Example 3: Get all logs generated in a specific time frame sorted ascending and grouped

PS C:\> CD SQLSERVER:\SQL\MyServer
PS SQLSERVER:\SQL\MyServer> ls | Get-SqlErrorLog -After '2016-05-10' -Before '2016-06-18' -Ascending | ? { $_.Text -match 'Login failed' } | Group-Object -Property ServerInstance

Count Name                      Group
----- ----                      -----
    1 MyServer                  {{ Date = 6/17/2016 2:00:04 AM, Source = Logon, Text = Login failed for user ...
    2 MyServer\INST1            {{ Date = 6/10/2016 3:58:46 PM, Source = Logon, Text = Login failed for user

Parameters

-AccessToken

The access token used to authenticate to SQL Server, as an alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity.

The parameter to use can be either a string representing the token or a PSAccessToken object as returned by running Get-AzAccessToken -ResourceUrl https://database.windows.net.

This parameter is new in v22 of the module.

Parameter properties

Parameter sets

-After

Specifies that this cmdlet only gets error logs generated after the given time.

If you specify the Before parameter, then this cmdlet gets all the error logs generated before the specified.

Do not specify this parameter if you intend to use the Since or Timespan parameters.

The format is defined according to the rules of .Net System.DataTime.Parse().

Parameter properties

Parameter sets

-Ascending

Indicates that the cmdlet sorts the collection of error logs by the log date in ascending order. If you do not specify this parameter, the cmdlet sorts the error logs in descending order.

When this cmdlet gets error logs multiple sources, the sorting is applied to all the error logs from the same source. The logs this cmdlet get are grouped by source first and then sorted by log date.

Parameter properties

Parameter sets

-Before

Specifies that this cmdlet only gets error logs generated before the given time.

If the After parameter is specified, the cmdlet defaults to now, meaning that the cmdlet gets all the error logs generated after what you specified for this parameter until the present time.

Do not specify a value for this parameter if you intend to use the Since or Timespan parameters. The format is defined according to the rules of .Net System.Datatime.Parse().

Parameter properties

Parameter sets

-ConnectionTimeout

Specifies the number of seconds to wait for a server connection before a time-out failure. The time-out value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.

Parameter properties

Parameter sets

-Credential

Specifies a PSCredential object used to specify the credentials for a SQL Server login that has permission to perform this operation.

Parameter properties

Parameter sets

-Encrypt

The encryption type to use when connecting to SQL Server.

This value maps to the Encrypt property SqlConnectionEncryptOption on the SqlConnection object of the Microsoft.Data.SqlClient driver.

In v22 of the module, the default is Optional (for compatibility with v21). In v23+ of the module, the default value will be 'Mandatory', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Parameter properties

Parameter sets

-HostNameInCertificate

The host name to be used in validating the SQL Server TLS/SSL certificate. You must pass this parameter if your SQL Server instance is enabled for Force Encryption and you want to connect to an instance using hostname/shortname. If this parameter is omitted then passing the Fully Qualified Domain Name (FQDN) to -ServerInstance is necessary to connect to a SQL Server instance enabled for Force Encryption.

This parameter is new in v22 of the module.

Parameter properties

Parameter sets

-InputObject

Specifies the server object, as an array, of the target instance that this cmdlet get the logs from.

Parameter properties

Parameter sets

-Path

Specifies the path, as an array, to the instance of SQL Server on which this cmdlet runs the operation. If you do not specify a value for this parameter, the cmdlet uses the current working ___location.

Parameter properties

Parameter sets

-ServerInstance

Specifies the name of an instance of SQL Server, as an array. For default instances, only specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName.

Parameter properties

Parameter sets

-Since

Specifies an abbreviation for the Timespan parameter.

Do not specify this parameter if you intend to use the After or Before parameter.

The acceptable values for this parameter are:

• Midnight (gets all the logs generated after midnight)
• Yesterday (gets all the logs generated in the last 24 hours).
• LastWeek (gets all the logs generated in the last week)
• LastMonth (gets all the logs generated in the last month)

Parameter properties

Parameter sets

-Timespan

Specifies a TimeSpan object that this cmdlet filters out of the error logs that do are outside of the time span.

The format of this parameter is d.HH:mm:ss.

This parameter is ignored if you use the Since, After, or Before parameters.

Parameter properties

Parameter sets

-TrustServerCertificate

Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

In v22 of the module, the default is $true (for compatibility with v21). In v23+ of the module, the default value will be '$false', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Parameter properties

Parameter sets

CommonParameters

Inputs

System.String

Microsoft.SqlServer.Management.Smo.Server

Related Links

• Set-SqlErrorLog