Extended Events (XE) is a great diagnostics tool introduced in SQL Server 2008. But loading Extended Event data into SQL Server is not as trivial as it was for Profiler trace data. This article describes how you can load XE data using a PowerShell script, especially if you have to do it repetitively.

I assume that you already know to how to setup an extended event session but are struggling to make the collected data more useful. I was in the same place a few months ago, and then I found this blog post which gave me some hope, http://byobi.com/blog/2015/12/3-methods-for-shredding-analysis-services-extended-events/#!prettyPhoto. Thank you, Bill, for this helpful Article.

The article linked above describes three different methods for loading XE data into SQL Server. I liked the last/fastest (obviously) method, but it was C# , so I thought to covert the code into PowerShell.

Let me give you a brief overview of the code. Anybody familiar with the object oriented concepts of programming can easily understand this code.  First of all, we will load an assembly, Microsoft.SqlServer.XEvent.Linq, which contains the QueryableXEventData Class. This class gives us the ability to fetch event data row by row from the .xel file. The second major part of the script is a data table. The data table is created to hold the data temporarily streamed from the .xel file. At the end, the script opens a database connection and bulk copies the data contained in a data table to a permanent table in our database.

While converting the C# code into PowerShell, I added a few lines of additional code as I am using a computed column in the destination table.  Whenever you use a data table with BCP, you have to define the mappings of the data table columns.  Finally, I am disposing the object instantiated from the “QueryableXEventData”Class after reading each XE file. Otherwise, the object holds the lock on the file. This took me a while to figure out.

You will have to add/remove the columns of a data table created in the code below depending on what fields and events you are capturing through extended events. Below script was developed in context of extended events “rpc_completed” and “sql_batch_completed”. Make sure to update the connection string & destination table as well, obviously it won’t work otherwise.

Below is the PowerShell cmdlet I came-up with, it works fine on PS 3.0.  I have parameterized the cmdlet to make it bit flexible such that it can be used to iterate over multiple extended event files. So, just open the ISE and get things rolling J

Hope this article will make your life easier when dealing with Extended Events and help XE to get some love.

--------------------Destination Table Definition ---------------
  CREATE TABLE [dbo].[xe_audit_collection](
         [xe_id] [bigint] IDENTITY(1,1) NOT NULL,
         [server_name] [sysname] NOT NULL,
         [xe_load_date] [datetime2](7) NULL,
         [end_time] [datetime2](7) NULL,
         [text_data] [varchar](max) NULL,
         [duration] [bigint] NULL,
         [logicalreads] [bigint] NULL,
         [physicalreads] [bigint] NULL,
         [EndResult] [int] NULL,
         [RowCount] [bigint] NULL,
         [ObjectName] [varchar](250) NULL,
         [writes] [bigint] NULL,
         [CPU] [bigint] NULL,
         [event_name] [varchar](100) NULL,
         [database_id] [int] NULL,
         [hostname] [sysname] NOT NULL,
         [application_name] [sysname] NULL,
         [login_name] [sysname] NULL,
         [spid] [int] NULL,
         [xe_log_file] [varchar](250) NULL,
         [start_time]  AS (dateadd(millisecond, -CONVERT([int],[duration]/(1000)),[end_time])) PERSISTED
  )
  GO
  ------------------ Powershell cmdlet to load XE ---------------
  Function Shred-XElogs{
  param(
  [Parameter(Position=0, Mandatory=$true)] [string] $filewithPath,
  [Parameter(Position=1, Mandatory=$true)] [string] $servername,
  [Parameter(Position=2, Mandatory=$true)] [string] $fileName
  )
  Try
  {
  #Load the required assemblies
  $dllpath = "C:\Program Files\Microsoft SQL Server\110\Shared\Microsoft.SqlServer.XEvent.Linq.dll"
  if(([appdomain]::currentdomain.getassemblies() | Where {$_.Location -match "Microsoft.SqlServer.XEvent.Linq.dll"}) -eq $null)
       {
         Write-Host "Assembly not found. Loading it from $dllpath" `r`n
         [System.Reflection.Assembly]::LoadFrom($dllpath)
       }
  else
       {
        write-host "Assembly is already loaded." `r`n
       }
  [System.Reflection.Assembly]::LoadFrom($dllpath)
  #create data table
  $dt = New-Object System.Data.Datatable
  #Define Columns
  $server_name = New-Object system.Data.DataColumn 'server_name',([string])
  $xe_load_date = New-Object system.Data.DataColumn 'xe_load_date',([DateTime])
  $start_date = New-Object system.Data.DataColumn 'start_date',([DateTime])
  $end_time = New-Object system.Data.DataColumn 'end_time',([datetime])
  $text_data = New-Object system.Data.DataColumn 'text_data',([string])
  $duration = New-Object system.Data.DataColumn 'duration',([int64])
  $logicalreads = New-Object system.Data.DataColumn 'logicalreads',([int64])
  $physicalreads = New-Object system.Data.DataColumn 'physicalreads',([int])
  $EndResult = New-Object system.Data.DataColumn 'EndResult',([int])
  $RowCount = New-Object system.Data.DataColumn 'RowCount',([int])
  $ObjectName = New-Object system.Data.DataColumn 'ObjectName',([string])
  $writes = New-Object system.Data.DataColumn 'writes',([int])
  $CPU = New-Object system.Data.DataColumn 'CPU',([int64])
  $event_name = New-Object system.Data.DataColumn 'event_name',([string])
  $database_id = New-Object system.Data.DataColumn 'database_id',([int])
  $hostname = New-Object system.Data.DataColumn 'hostname',([string])
  $application_name = New-Object system.Data.DataColumn 'application_name',([string])
  $login_name = New-Object system.Data.DataColumn 'login_name',([string])
  $hostname = New-Object system.Data.DataColumn 'hostname',([string])
  $spid = New-Object system.Data.DataColumn 'spid',([int])
  $xe_log_file = New-Object system.Data.DataColumn 'xe_log_file',([string])
  # create columns
  [void]$dt.Columns.Add($server_name)
  [void]$dt.Columns.Add($xe_load_date)
  [void]$dt.Columns.Add($start_date)
  [void]$dt.Columns.Add($end_time)
  [void]$dt.Columns.Add($text_data)
  [void]$dt.Columns.Add($duration)
  [void]$dt.Columns.Add($logicalreads)
  [void]$dt.Columns.Add($physicalreads)
  [void]$dt.Columns.Add($EndResult)
  [void]$dt.Columns.Add($RowCount)
  [void]$dt.Columns.Add($ObjectName)
  [void]$dt.Columns.Add($writes)
  [void]$dt.Columns.Add($CPU)
  [void]$dt.Columns.Add($event_name)
  [void]$dt.Columns.Add($database_id)
  [void]$dt.Columns.Add($hostname)
  [void]$dt.Columns.Add($application_name)b
  [void]$dt.Columns.Add($login_name)
  [void]$dt.Columns.Add($spid)
  [void]$dt.Columns.Add($xe_log_file)
  $events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($filewithPath)
  $events | % {
      $currentEvent = $_
  $row = $dt.NewRow()
  $audittime = Get-Date
  $row.server_name = $servername
  $row.xe_load_date = [DateTime] $audittime
  $row.end_time = $currentEvent.Timestamp.LocalDateTime
  $row.duration = $currentEvent.Fields["duration"].Value
  $row.logicalreads = $currentEvent.Fields["logical_reads"].Value
  $row.physicalreads = $currentEvent.Fields["physical_reads"].Value
  $row.EndResult = $currentEvent.Fields["result"].Value.Key
  $row.RowCount = $currentEvent.Fields["row_count"].Value
  $row.ObjectName = $currentEvent.Fields["object_name"].Value
  $row.writes = $currentEvent.Fields["writes"].Value
  $row.CPU = $currentEvent.Fields["cpu_time"].Value
  $row.event_name = $currentEvent.name
  $row.database_id = $currentEvent.Actions["database_id"].Value
  $row.hostname = $currentEvent.Actions["client_hostname"].Value
  $row.application_name = $currentEvent.Actions["client_app_name"].Value
  $row.login_name = $currentEvent.Actions["server_principal_name"].Value
  $row.spid = $currentEvent.Actions["session_id"].Value
  $row.xe_log_file = $fileName
  if($currentEvent.name -eq 'sql_batch_completed') {$row.text_data = $currentEvent.Fields["batch_text"].Value}
  else {$row.text_data = $currentEvent.Fields["statement"].Value}
  $dt.Rows.Add($row)
  }
  $cn = new-object System.Data.SqlClient.SqlConnection("Data Source=MyDevServer.domainname;Integrated Security=SSPI;Initial Catalog=AdventureWorks");
  $cn.Open()
  $bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
  $bc.BulkCopyTimeout = 1200 #you can increase if required
  $bc.DestinationTableName = "dbo.xe_audit_collection"
  [void]$bc.ColumnMappings.Add("server_name",$dt.Columns.ColumnName[0])
  [void]$bc.ColumnMappings.Add("xe_load_date",$dt.Columns.ColumnName[1])
  [void]$bc.ColumnMappings.Add("end_time",$dt.Columns.ColumnName[3])
  [void]$bc.ColumnMappings.Add("text_data",$dt.Columns.ColumnName[4])
  [void]$bc.ColumnMappings.Add("duration", $dt.Columns.ColumnName[5])
  [void]$bc.ColumnMappings.Add("logicalreads",$dt.Columns.ColumnName[6])
  [void]$bc.ColumnMappings.Add("physicalreads",$dt.Columns.ColumnName[7])
  [void]$bc.ColumnMappings.Add("EndResult",$dt.Columns.ColumnName[8])
  [void]$bc.ColumnMappings.Add("RowCount",$dt.Columns.ColumnName[9])
  [void]$bc.ColumnMappings.Add("ObjectName",$dt.Columns.ColumnName[10] )
  [void]$bc.ColumnMappings.Add("writes",$dt.Columns.ColumnName[11])
  [void]$bc.ColumnMappings.Add("CPU",$dt.Columns.ColumnName[12])
  [void]$bc.ColumnMappings.Add("event_name",$dt.Columns.ColumnName[13] )
  [void]$bc.ColumnMappings.Add("database_id",$dt.Columns.ColumnName[14])
  [void]$bc.ColumnMappings.Add("hostname",$dt.Columns.ColumnName[15] )
  [void]$bc.ColumnMappings.Add("application_name", $dt.Columns.ColumnName[16])
  [void]$bc.ColumnMappings.Add("login_name",$dt.Columns.ColumnName[17])
  [void]$bc.ColumnMappings.Add("spid",$dt.Columns.ColumnName[18)
  [void]$bc.ColumnMappings.Add("xe_log_file",$dt.Columns.ColumnName[19] )
  $bc.WriteToServer($dt)
  write-host " $($dt.rows.count) Rows have been transferred to SQL Server destination"`r`n
  $cn.Close()
  $events.Dispose() 
  $result = "`n Loading of file $XEFilePath complete! `n"
  }
  Catch
  {
      $result = $_.Exception
      $FailedItem = $_.Exception.ItemName
    
  }
  return $result
  }
  #### Load your First File #####
  $XEFilePath = "Z:\xe_log\OLTPServer\xe_troubleshoot_log_01.xel"  ## file location
  $server = "OLTPServer"   ## server name, you are collecting XE data
  $XEFile = "xe_troubleshoot_log_01.xel"  ## XE File Name
  Shred-XElogs -filewithPath $XEFilePath -servername $server -fileName $XEFile