Read-SqlXEvent

Reads SQL Server XEvents from XEL file or live SQL XEvent session.

Syntax

Read-SqlXEvent
    [-FileName] <String>
    [-ProgressAction <ActionPreference>]
    [<CommonParameters>]
Read-SqlXEvent
    -ConnectionString <String>
    -SessionName <String>
    [-ProgressAction <ActionPreference>]
    [<CommonParameters>]

Description

The Read-SqlXEvent reads SQL Server XEvents from a file or live SQL XEvent session. XEvent sessions are created by the CREATE EVENT SESSION Transact-SQL API. SQL XEvent files usually have the .XEL or .sqlaudit extensions and contain a stream of XEvents. The structure of the XEvents is defined in the session configuration. SQL live streams are available via the SQL TDS protocol and require authetication against the server that produced them.

New in v22: if you are using the -ConnectionString parameter, you may get an error like "The certificate chain was issued by an authority that is not trusted." This is because the new SQL driver changed its default to something more secure. Either make sure you configure your server properly, or install/trust the certificate; alternatively (not recommended), you can pass Encrypt=Optional or TrustServerCertificate=true in the connection string to go back to the behavior that existed in v21 of the module.

Examples

Example 1: Read and parse the sqlaudit file. Each object returned will contain all the information from a single audit record.

Read-SqlXEvent -FileName C:\audits\Audit-20190529-132659.sqlaudit

# The output would look like this:
#
# Name      : audit_event
# UUID      : c6479a6f-f1bd-4759-9881-fcb493821aff
# Timestamp : 6/13/2019 7:49:42 AM +00:00
# Fields    : {[audit_schema_version, 1], [event_time, 6/13/2019 7:49:42 AM +00:00], [sequence_number, 1], [action_id,
#             1129534785]...}
# Actions   : {}
#
# Name      : audit_event
# UUID      : c6479a6f-f1bd-4759-9881-fcb493821aff
# Timestamp : 6/13/2019 12:42:28 PM +00:00
# Fields    : {[audit_schema_version, 1], [event_time, 6/13/2019 12:42:28 PM +00:00], [sequence_number, 1], [action_id,
#             1129534785]...}
# Actions   : {}
# ...

Example 2: Read and parse the live stream of XEvents from the SQL Server running on the same machine connected with Windows authentication. Each object returned will contain all the information from a single XEvent record.

Read-SQLXEvent -ConnectionString "Server=.;Database=master;Trusted_Connection=True" -SessionName telemetry_xevents

# The output would look like this:
#
# Name      : auto_stats
# UUID      : 9237e17a-a73d-4832-8936-f319e19e219b
# Timestamp : 11/14/2022 5:31:12 AM +00:00
# Fields    : {[database_id, 1], [object_id, 74], [index_id, 1], [job_id, 0]…}
# Actions   : {}
# 
# Name      : auto_stats
# UUID      : 9237e17a-a73d-4832-8936-f319e19e219b
# Timestamp : 11/14/2022 5:31:12 AM +00:00
# Fields    : {[database_id, 1], [object_id, 74], [index_id, 17], [job_id, 0]…}
# Actions   : {}
# ...

Parameters

-ConnectionString

SQL Server connection string.

Type:String
Position:Named
Default value:None
Required:True
Accept pipeline input:False
Accept wildcard characters:False

-FileName

File name of a XEvent file to read.

Type:String
Position:0
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-ProgressAction

Determines how PowerShell responds to progress updates generated by a script, cmdlet, or provider, such as the progress bars generated by the Write-Progress cmdlet. The Write-Progress cmdlet creates progress bars that show a command's status.

Type:ActionPreference
Aliases:proga
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-SessionName

The SQL Server XEvent session name as defined by the CREATE EVENT SESSION Transact-SQL.

Type:String
Position:Named
Default value:None
Required:True
Accept pipeline input:False
Accept wildcard characters:False

Inputs

System.String

Outputs

Microsoft.SqlServer.XEvent.XELite.IXEvent