Read-SqlXEvent
Reads SQL Server XEvents from XEL file or live SQL XEvent session.
Syntax
ByFile (Default)
Read-SqlXEvent
[-FileName] <String>
[<CommonParameters>]
ByLiveData
Read-SqlXEvent
-ConnectionString <String>
-SessionName <String>
[<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.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByLiveData
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-FileName
File name of a XEvent file to read.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByFile
| Position: | 0 |
| Mandatory: | True |
| Value from pipeline: | True |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-SessionName
The SQL Server XEvent session name as defined by the CREATE EVENT SESSION Transact-SQL.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByLiveData
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
CommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutBuffer, -OutVariable, -PipelineVariable, -ProgressAction, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.