Load Extended Events via Powershell

Question

Load Extended Events via Powershell

aakash9

SSC Enthusiast

Points: 199
More actions
August 22, 2017 at 12:03 am

#338810

Comments posted to this topic are about the item Load Extended Events via Powershell

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Narodul Say Hey Kid Points: 711 More actions · Answer 1

Thanks Aakash, great article, and it provided the breakthrough I was looking for to migrate our existing performance baseline capture using SQL Trace, wrapped in stored procedures, to using XE. Being able to call the Powershell script from an SP makes it so easy now.
One thing I noticed is that shredding the XE files (using Powershell), and importing the data into a persisted table takes about 3 times longer than importing from SQL Trace files using ::fn_trace_gettable(). I was hoping that using Powershell would be comparable, but, maybe it's not to be... it's still and darn sight easier and quicker that writing T-SQL to do it!
Anyhow, well done, and thanks.

aakash9 SSC Enthusiast Points: 199 More actions · Answer 2

Simon Bailey - Sunday, December 3, 2017 3:19 PM
Thanks Aakash, great article, and it provided the breakthrough I was looking for to migrate our existing performance baseline capture using SQL Trace, wrapped in stored procedures, to using XE. Being able to call the Powershell script from an SP makes it so easy now.
One thing I noticed is that shredding the XE files (using Powershell), and importing the data into a persisted table takes about 3 times longer than importing from SQL Trace files using ::fn_trace_gettable(). I was hoping that using Powershell would be comparable, but, maybe it's not to be... it's still and darn sight easier and quicker that writing T-SQL to do it!
Anyhow, well done, and thanks.

You are welcome Simon.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

SQL_Hunt

SSC-Dedicated

Points: 33518

September 20, 2018 at 8:35 pm

#2006512

Does not work

Thanks.

cmgui Grasshopper Points: 12 More actions · Answer 4

Not working. Thank you.

The file C:\Program Files\Microsoft SQL Server\130\Shared\Microsoft.SqlServer.XEvent.Linq.dll exists but we still get this error message:

Assembly not found. Loading it from C:\Program Files\Microsoft SQL Server\130\Shared\Microsoft.SqlServer.XEvent.Linq.dll

GAC    Version        Location
---    -------        --------
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_64\Microsoft.SqlServer.XEvent.Linq\v4.0_13.0.0.0__89845dcd8080cc91\Microso...
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_64\Microsoft.SqlServer.XEvent.Linq\v4.0_13.0.0.0__89845dcd8080cc91\Microso...
Exception setting "EndResult": "Cannot set Column 'EndResult' to be null. Please use DBNull instead."

aakash9 SSC Enthusiast Points: 199 More actions · Answer 5

cmgui - Monday, March 4, 2019 8:23 PM
Not working.   Thank you.
The file C:\Program Files\Microsoft SQL Server\130\Shared\Microsoft.SqlServer.XEvent.Linq.dll exists but we still get this error message:
Assembly not found. Loading it from C:\Program Files\Microsoft SQL Server\130\Shared\Microsoft.SqlServer.XEvent.Linq.dll
GAC    Version        Location
---    -------        --------
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_64\Microsoft.SqlServer.XEvent.Linq\v4.0_13.0.0.0__89845dcd8080cc91\Microso...
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_64\Microsoft.SqlServer.XEvent.Linq\v4.0_13.0.0.0__89845dcd8080cc91\Microso...
Exception setting "EndResult": "Cannot set Column 'EndResult' to be null. Please use DBNull instead."

Real error is "Exception setting "EndResult": "Cannot set Column 'EndResult' to be null. Please use DBNull instead." . Check the data types in your destination table.

cmgui Grasshopper Points: 12 More actions · Answer 6

Thank you aakash9!

Now, I'm getting this error:
Exception calling "NewRow" with "0" argument(s): "Invalid storage type: DBNull."

It is referring to :
$events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($filewithPath)
$events | % {
$currentEvent = $_
$row = $dt.NewRow()
$audittime = Get-Date

Thank you very much once again.

aakash9 SSC Enthusiast Points: 199 More actions · Answer 7

cmgui - Tuesday, March 5, 2019 9:05 AM
Thank you aakash9!
Now, I'm getting this error:
Exception calling "NewRow" with "0" argument(s): "Invalid storage type: DBNull."
It is referring to :
$events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($filewithPath)
$events | % {
$currentEvent = $_
$row = $dt.NewRow()
$audittime = Get-Date
Thank you very much once again.

check if your extended event file has any data/rows. I am not so familiar with above error but it seems you are trying to load an empty file.

cmgui Grasshopper Points: 12 More actions · Answer 8

Thank you aakash9 once again.

The problem is still "Invalid storage type: DBNull. Please use DBNull instead.""
As per your suggestion, I changed the datatype of $EndREsult from Int to DBNull
$EndResult = New-Object system.Data.DataColumn 'EndResult',([DBNull])

After I commented out this line $EndResult = New-Object system.Data.DataColumn 'EndResult',([DBNull]), I now get a different error:
Exception calling "Add" with "1" argument(s): "'column' argument cannot be null.
Parameter name: column"

So the problem is still with $EndResult = New-Object system.Data.DataColumn 'EndResult',([int])
It cannot be [int] because that would result in the earlier error: Real error is "Exception setting "EndResult": "Cannot set Column 'EndResult' to be null.
But if we change it to from [int] to [DBNull], we will get this new error: Exception calling "NewRow" with "0" argument(s): "Invalid storage type: DBNull."

Also tried these but still getting the Exception setting "EndResult": "Cannot set Column 'EndResult' to be null. Please use DBNull instead." error.

$EndResult = New-Object system.Data.DataColumn 'EndResult',([int])
$EndResult.AllowDBNull = $true
$database_id = New-Object system.Data.DataColumn 'database_id',([int])
$database_id.AllowDBNull = $true

[void]$dt.Columns.Add($EndResult)
$dt.EndResult.Nullable
[void]$dt.Columns.Add($database_id)
$dt.database_id.Nullable

Thank you once again.

P.S. The file loaded wasn't empty. Tested the command $events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($filewithPath) in Powershell command line and the $events wasn't empty.

cmgui Grasshopper Points: 12 More actions · Answer 9

Finally got it to work:

If (!$currentEvent.Fields["result"].Value.Key) {
$row.EndResult = [System.DBNull]::Value
}
else
{
$row.EndResult = $currentEvent.Fields["result"].Value.Key
}

If (!$currentEvent.Actions["database_id"]) {
Write-Host "here"
$row.database_id = [System.DBNull]::Value
}
else
{
$row.database_id = $currentEvent.Actions["database_id"].Value
}

tomerw Grasshopper Points: 12 More actions · Answer 10

There is now very easy way to read and parse Extended Events via PowerShell: https://www.powershellgallery.com/packages/SqlServer.XEvent

This was just published by Microsoft couple of days ago.

Thanks, Tomer (MSFT).

aakash9 SSC Enthusiast Points: 199 More actions · Answer 11

Thanks Tomer(/MSFT) for providing enhanced tool for reading extended event data.

I tried using Read-SqlXEvent which worked fine for reading from .XEL files but it's throwing error when reading from live Session.

Read-SqlXEvent : Could not load file or assembly 'System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot

find the file specified.

Any guidance will be appreciated.

Thanks,

Aakash

tomerw Grasshopper Points: 12 More actions · Answer 12

Hi Aakash,

While i'm not sure why you are facing this problem, we did move this new cmldet to its final destination - SQLServer module. Can you try to install the latest version from: https://www.powershellgallery.com/packages/SqlServer ?

Thanks, Tomer.