Load Extended Events via Powershell

  • aakash9

    SSC Enthusiast

    Points: 189

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

  • Simon Bailey

    Say Hey Kid

    Points: 711

    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: 189

    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: 33283

    Does not work

    Thanks.

  • cmgui

    Grasshopper

    Points: 12

    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: 189

    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

    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: 189

    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

    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

    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

    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: 189

    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

    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.

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

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