Importing SQL Server Extended Events XEL with BCP

20 Aug 2019 0 minutes to read Contributors

Extended Events

SQL Server has the ability to catch all kind of 'events' into an Extended Event File (XEL). These files are readable with C# / Powershell / SQL functions.

Reading XEL files can be a lengthy task especially when you want to do analysis on the data.

SQL Read XEL Function

SQL Server provides  a function to read XEL files: sys.fn_xe_file_target_read_file ( path, mdpath, initial_file_name, initial_offset )

That function reads files that are created by the Extended Events asynchronous file target. One event, in XML format, is returned per row.

More info here:

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-xe-file-target-read-file-transact-sql

C# / Powershell

The function is implemented in Microsoft SQL Server libraries that are invokable by C# or Powershell:

Microsoft.SqlServer.XEvent.Linq.QueryableXEventData

Solution

We have created a powershell solution that uses this library to reads XEL files and import them via BCP into a database table.

Function ImportFile($ExEvFile)
{

####################################
# Read the XEL file into $rawXELdata
####################################

$loggingstring = "Reading XEL file...$ExEvFile";
$loggingstring

$rawXELdata = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("$exevfile")

#########################################################
# Create new virtual table to put formatted XEL data into
#########################################################

$virtualtable = New-Object -TypeName System.Data.DataTable
$virtualtable.TableName = 'TableExtendedEvents'

#####################################
# Define columns in the virtual table
#####################################

[Void]$virtualtable.Columns.Add("collect_system_time" ,[DateTime])
[Void]$virtualtable.Columns.Add("database_name" ,[String])
[Void]$virtualtable.Columns.Add("object_name" ,[String])
[Void]$virtualtable.Columns.Add("client_hostname" ,[String])
[Void]$virtualtable.Columns.Add("client_app_name" ,[String])
[Void]$virtualtable.Columns.Add("server_name" ,[String])
[Void]$virtualtable.Columns.Add("row_count" ,[Int64])
[Void]$virtualtable.Columns.Add("Duration" ,[Int64])
[Void]$virtualtable.Columns.Add("server_principal_name" ,[String])

#######################################################
# In a loop parse the rawXELdata into the virtual table
#######################################################

$loggingstring = "Parsing raw XEL data into virtual table...";
$loggingstring

try
{
$rawXELdata | %{

###############################################
# Data comes from Actions and Fields structures
###############################################

$collect_system_time = $_.Timestamp[0].Datetime.ToString();

$database_name = $_.Actions['database_name']
$object_name = $_.Fields['object_name']
$client_hostname = $_.Actions['client_hostname']
$client_app_name = $_.Actions['client_app_name']
$server_principal_name = $_.Actions['server_principal_name']
$row_count = $_.Fields['row_count']
$duration = $_.Fields['duration']
$server = $ExEvFile.Split("\")[5]

$row=@(
@{$true = $collect_system_time; $false = $null }[$collect_system_time -ne $null] ,
@{$true = $database_name.Value; $false = $null }[$database_name -ne $null] ,
@{$true = $object_name.Value; $false = $null }[$object_name -ne $null],
@{$true = $client_hostname.Value; $false = $null }[$client_hostname -ne $null],
@{$true = $client_app_name.Value; $false = $null }[$client_app_name -ne $null],
$server,
@{$true = [int64]$row_count.Value; $false = $null }[$row_count -ne $null],
@{$true = $duration.Value; $false = $null }[$duration -ne $null];
@{$true = $server_principal_name.Value; $false = $null }[$server_principal_name -ne $null]
);
$virtualtable.Rows.Add($row);
} |out-null
}
catch{
$loggingstring = "Processing virtual table:`t$_.Exception.Message`tReverting importfile";
$loggingstring >>$logfile
$_ = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', -1"
Write-Host $loggingstring
}


##############################################
# Bulk Insert into $dbTable from virtual table
##############################################

$loggingstring = "$($ExEvFile) - records in extended file:`t$($virtualtable.rows.count)`t";
$loggingstring >>$logfile
Write-Host $loggingstring

Try
{
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$servername;user id=$userid;password=$pwd;Initial catalog=$dbname")
$cn.Open()

$bcp = New-Object -TypeName System.Data.SqlClient.SqlBulkCopy $cn
$bcp.DestinationTableName = $dbTable
$bcp.Batchsize = 10000
$bcp.BulkCopyTimeout = 0
$bcp.ColumnMappings.add('collect_system_time','collect_system_time') | Out-Null
$bcp.ColumnMappings.add('database_name','database_name') | Out-Null
$bcp.ColumnMappings.add('object_name','object_name')| Out-Null
$bcp.ColumnMappings.add('client_hostname','client_hostname')| Out-Null
$bcp.ColumnMappings.add('client_app_name','client_app_name')| Out-Null
$bcp.ColumnMappings.add('server_name','server_name')| Out-Null
$bcp.ColumnMappings.add('row_count','row_count')| Out-Null
$bcp.ColumnMappings.add('Duration','Duration')| Out-Null
$bcp.ColumnMappings.add('server_principal_name','username')| Out-Null

$loggingstring = "Starting bcp...";
Write-Host $loggingstring

$bcp.WriteToServer($virtualtable)
$bcp.Close()
$cn.close()
$count = $virtualtable.rows.count
$_ = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', $count "
$virtualtable.clear()
}
catch{
$loggingstring = "Running BCP:`t$_.Exception.Message`t";
$loggingstring >>$logfile
$_ = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', -1"
Write-Host $loggingstring
}

}

################################NOTES#######################
# Notes: bcp Extended Events files into to destination table
############################################################
#$ExEvFile='\\xxxxx-srv-xxxxxx\DBA\DatabaseObjectUsage\filename.xel'
Function Main{
param(
#[Parameter(Mandatory=$True)]
[string]$ExEvFile #New File name to move
)

$error.Clear();

$servername='xxxxx-srv-xxxxx'
$dbName='ObjectUsage'
$dbTable='dbo.ObjectUsage'
$UserID="login_extendedevents"
$Pwd ='password_extendedevents_user'
$connectionString ="Data Source=$ServerName;Database=$dbName;User Id=$UserID;Password=$Pwd;"

$LogFile='\\xxxx-srv-xxxx\dba\DatabaseObjectUsage\ImportXEL.log';

#############################################
# Output text to console and write log header
#############################################

$StartTime = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$loggingstring = "$StartTime`t$ServerName`tFile`t$ExEvFile`t"
$loggingstring >>$logfile
$loggingstring

################################################################################################
# Add-Type when running this script on MS SQL Server
# use this line of code on win2012 server, need only Microsoft.SqlServer.XEvent.Linq.dll library
# Add the path where this dll is.
################################################################################################

Add-Type -Path 'Microsoft.SqlServer.XEvent.Linq.dll' | out-null

if ($error.count -ne 0) {
$loggingstring = "Error:`t$error`t";
$loggingstring >>$logfile
$loggingstring
$error.clear();
}

if( $ExEvFile -eq "" )
{
$filelist = Get-ChildItem -Path "\\xxxxx-srv-xxxxx\DBA\DatabaseObjectUsage" -Filter "*.XEL" -Recurse -ErrorAction SilentlyContinue | Select-Object FullName, Length

foreach( $file in $filelist )
{
if( $file.length -ge 0 )
{
$ExEvFile = $file.fullname

$imported = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', NULL, $($file.length)"

if ($imported.ItemArray[0] -eq "0" )
{
ImportFile( $ExEvFile );

}
}
}
}
else
{
ImportFile( $ExEvFile );
}
#######################################
# End processing Write log file trailer
#######################################
$EndDate = Get-Date

if ($error.count -ne 0) {
$loggingstring += "Error:`t$error`t";
$error.clear();
}
else {
$loggingstring = "Finished successfully:`t$EndDate`n";
$loggingstring >>$logfile
$loggingstring
}
}
################## END OF SCRIPT FILE ######################
#### Call Main

Main

In this article