SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Load Extended Events via Powershell

By Aakash Patel,

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
 
Total article views: 737 | Views in the last 30 days: 10
 
Related Articles
FORUM

Invalid object

Invalid object error

FORUM

object name from objid

object name from objid

FORUM

object privileges

sql server 2000 - object privileges

FORUM

SSIS Object Model

SSIS 2008 Object Model

FORUM

ASCMD to process objects

ASCMD to process objects

Tags
extended events (xe)    
powershell    
xe    
 
Contribute