Importing Extended Event files

06 Jun 2022 35226 views 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.

		
			
T-SQL
			
Transact-SQL
			

			
Function ImportFile($ExEvFile)<br />{<br /><br />####################################<br /># Read the XEL file into $rawXELdata<br />####################################<br /><br />$loggingstring = "Reading XEL file...$ExEvFile";<br />$loggingstring <br /><br />$rawXELdata = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("$exevfile")<br /><br />#########################################################<br /># Create new virtual table to put formatted XEL data into<br />#########################################################<br /><br />$virtualtable = New-Object -TypeName System.Data.DataTable<br />$virtualtable.TableName = 'TableExtendedEvents'<br /><br />#####################################<br /># Define columns in the virtual table<br />#####################################<br /><br />[Void]$virtualtable.Columns.Add("collect_system_time" ,[DateTime]) <br />[Void]$virtualtable.Columns.Add("database_name" ,[String])<br />[Void]$virtualtable.Columns.Add("object_name" ,[String]) <br />[Void]$virtualtable.Columns.Add("client_hostname" ,[String])<br />[Void]$virtualtable.Columns.Add("client_app_name" ,[String])<br />[Void]$virtualtable.Columns.Add("server_name" ,[String])<br />[Void]$virtualtable.Columns.Add("row_count" ,[Int64])<br />[Void]$virtualtable.Columns.Add("Duration" ,[Int64])<br />[Void]$virtualtable.Columns.Add("server_principal_name" ,[String])<br /><br />#######################################################<br /># In a loop parse the rawXELdata into the virtual table<br />#######################################################<br /><br />$loggingstring = "Parsing raw XEL data into virtual table...";<br />$loggingstring <br /><br />try<br />{<br />$rawXELdata | %{<br /><br />###############################################<br /># Data comes from Actions and Fields structures<br />###############################################<br /><br />$collect_system_time = $_.Timestamp[0].Datetime.ToString();<br /><br />$database_name = $_.Actions['database_name']<br />$object_name = $_.Fields['object_name'] <br />$client_hostname = $_.Actions['client_hostname']<br />$client_app_name = $_.Actions['client_app_name']<br />$server_principal_name = $_.Actions['server_principal_name']<br />$row_count = $_.Fields['row_count']<br />$duration = $_.Fields['duration']<br />$server = $ExEvFile.Split("\")[5]<br /><br />$row=@(<br />@{$true = $collect_system_time; $false = $null }[$collect_system_time -ne $null] ,<br />@{$true = $database_name.Value; $false = $null }[$database_name -ne $null] ,<br />@{$true = $object_name.Value; $false = $null }[$object_name -ne $null], <br />@{$true = $client_hostname.Value; $false = $null }[$client_hostname -ne $null],<br />@{$true = $client_app_name.Value; $false = $null }[$client_app_name -ne $null], <br />$server, <br />@{$true = [int64]$row_count.Value; $false = $null }[$row_count -ne $null],<br />@{$true = $duration.Value; $false = $null }[$duration -ne $null];<br />@{$true = $server_principal_name.Value; $false = $null }[$server_principal_name -ne $null]<br />);<br />$virtualtable.Rows.Add($row);<br />} |out-null<br />}<br />catch{<br />$loggingstring = "Processing virtual table:`t$_.Exception.Message`tReverting importfile";<br />$loggingstring >>$logfile<br />$_ = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', -1" <br />Write-Host $loggingstring <br />}<br /><br /><br />##############################################<br /># Bulk Insert into $dbTable from virtual table<br />##############################################<br /><br />$loggingstring = "$($ExEvFile) - records in extended file:`t$($virtualtable.rows.count)`t";<br />$loggingstring >>$logfile<br />Write-Host $loggingstring<br /><br />Try<br />{<br />$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$servername;user id=$userid;password=$pwd;Initial catalog=$dbname")<br />$cn.Open()<br /><br />$bcp = New-Object -TypeName System.Data.SqlClient.SqlBulkCopy $cn<br />$bcp.DestinationTableName = $dbTable<br />$bcp.Batchsize = 10000<br />$bcp.BulkCopyTimeout = 0<br />$bcp.ColumnMappings.add('collect_system_time','collect_system_time') | Out-Null<br />$bcp.ColumnMappings.add('database_name','database_name') | Out-Null<br />$bcp.ColumnMappings.add('object_name','object_name')| Out-Null<br />$bcp.ColumnMappings.add('client_hostname','client_hostname')| Out-Null<br />$bcp.ColumnMappings.add('client_app_name','client_app_name')| Out-Null<br />$bcp.ColumnMappings.add('server_name','server_name')| Out-Null<br />$bcp.ColumnMappings.add('row_count','row_count')| Out-Null<br />$bcp.ColumnMappings.add('Duration','Duration')| Out-Null<br />$bcp.ColumnMappings.add('server_principal_name','username')| Out-Null<br /><br />$loggingstring = "Starting bcp...";<br />Write-Host $loggingstring<br /><br />$bcp.WriteToServer($virtualtable)<br />$bcp.Close()<br />$cn.close()<br />$count = $virtualtable.rows.count<br />$_ = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', $count " <br />$virtualtable.clear() <br />}<br />catch{<br />$loggingstring = "Running BCP:`t$_.Exception.Message`t";<br />$loggingstring >>$logfile<br />$_ = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', -1" <br />Write-Host $loggingstring <br />}<br /><br />}<br /><br />################################NOTES#######################<br /># Notes: bcp Extended Events files into to destination table<br />############################################################<br />#$ExEvFile='\\xxxxx-srv-xxxxxx\DBA\DatabaseObjectUsage\filename.xel'<br />Function Main{<br />param(<br />#[Parameter(Mandatory=$True)]<br />[string]$ExEvFile #New File name to move<br />)<br /><br />$error.Clear();<br /><br />$servername='xxxxx-srv-xxxxx'<br />$dbName='ObjectUsage' <br />$dbTable='dbo.ObjectUsage'<br />$UserID="login_extendedevents"<br />$Pwd ='password_extendedevents_user'<br />$connectionString ="Data Source=$ServerName;Database=$dbName;User Id=$UserID;Password=$Pwd;"<br /><br />$LogFile='\\xxxx-srv-xxxx\dba\DatabaseObjectUsage\ImportXEL.log';<br /><br />#############################################<br /># Output text to console and write log header<br />#############################################<br /><br />$StartTime = Get-Date -Format "yyyy-MM-dd HH:mm:ss"<br />$loggingstring = "$StartTime`t$ServerName`tFile`t$ExEvFile`t"<br />$loggingstring >>$logfile<br />$loggingstring<br /><br />################################################################################################<br /># Add-Type when running this script on MS SQL Server<br /># use this line of code on win2012 server, need only Microsoft.SqlServer.XEvent.Linq.dll library<br /># Add the path where this dll is.<br />################################################################################################<br /><br />Add-Type -Path 'Microsoft.SqlServer.XEvent.Linq.dll' | out-null<br /><br />if ($error.count -ne 0) {<br />$loggingstring = "Error:`t$error`t";<br />$loggingstring >>$logfile<br />$loggingstring <br />$error.clear();<br />}<br /><br />if( $ExEvFile -eq "" )<br />{<br />$filelist = Get-ChildItem -Path "\\xxxxx-srv-xxxxx\DBA\DatabaseObjectUsage" -Filter "*.XEL" -Recurse -ErrorAction SilentlyContinue | Select-Object FullName, Length<br /><br />foreach( $file in $filelist )<br />{<br />if( $file.length -ge 0 )<br />{<br />$ExEvFile = $file.fullname<br /><br />$imported = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', NULL, $($file.length)"<br /><br />if ($imported.ItemArray[0] -eq "0" )<br />{<br />ImportFile( $ExEvFile );<br /><br />}<br />}<br />}<br />} <br />else<br />{<br />ImportFile( $ExEvFile );<br />} <br />#######################################<br /># End processing Write log file trailer <br />#######################################<br />$EndDate = Get-Date<br /><br />if ($error.count -ne 0) {<br />$loggingstring += "Error:`t$error`t";<br />$error.clear();<br />}<br />else {<br />$loggingstring = "Finished successfully:`t$EndDate`n";<br />$loggingstring >>$logfile<br />$loggingstring <br />}<br />}<br />################## END OF SCRIPT FILE ######################<br />#### Call Main<br /><br />Main
			

				

					

				

					
1
				
						
Function ImportFile($ExEvFile)<br />{<br /><br />####################################<br /># Read the XEL file into $rawXELdata<br />####################################<br /><br />$loggingstring = "Reading XEL file...$ExEvFile";<br />$loggingstring <br /><br />$rawXELdata = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("$exevfile")<br /><br />#########################################################<br /># Create new virtual table to put formatted XEL data into<br />#########################################################<br /><br />$virtualtable = New-Object -TypeName System.Data.DataTable<br />$virtualtable.TableName = 'TableExtendedEvents'<br /><br />#####################################<br /># Define columns in the virtual table<br />#####################################<br /><br />[Void]$virtualtable.Columns.Add("collect_system_time" ,[DateTime]) <br />[Void]$virtualtable.Columns.Add("database_name" ,[String])<br />[Void]$virtualtable.Columns.Add("object_name" ,[String]) <br />[Void]$virtualtable.Columns.Add("client_hostname" ,[String])<br />[Void]$virtualtable.Columns.Add("client_app_name" ,[String])<br />[Void]$virtualtable.Columns.Add("server_name" ,[String])<br />[Void]$virtualtable.Columns.Add("row_count" ,[Int64])<br />[Void]$virtualtable.Columns.Add("Duration" ,[Int64])<br />[Void]$virtualtable.Columns.Add("server_principal_name" ,[String])<br /><br />#######################################################<br /># In a loop parse the rawXELdata into the virtual table<br />#######################################################<br /><br />$loggingstring = "Parsing raw XEL data into virtual table...";<br />$loggingstring <br /><br />try<br />{<br />$rawXELdata | %{<br /><br />###############################################<br /># Data comes from Actions and Fields structures<br />###############################################<br /><br />$collect_system_time = $_.Timestamp[0].Datetime.ToString();<br /><br />$database_name = $_.Actions['database_name']<br />$object_name = $_.Fields['object_name'] <br />$client_hostname = $_.Actions['client_hostname']<br />$client_app_name = $_.Actions['client_app_name']<br />$server_principal_name = $_.Actions['server_principal_name']<br />$row_count = $_.Fields['row_count']<br />$duration = $_.Fields['duration']<br />$server = $ExEvFile.Split("\")[5]<br /><br />$row=@(<br />@{$true = $collect_system_time; $false = $null }[$collect_system_time -ne $null] ,<br />@{$true = $database_name.Value; $false = $null }[$database_name -ne $null] ,<br />@{$true = $object_name.Value; $false = $null }[$object_name -ne $null], <br />@{$true = $client_hostname.Value; $false = $null }[$client_hostname -ne $null],<br />@{$true = $client_app_name.Value; $false = $null }[$client_app_name -ne $null], <br />$server, <br />@{$true = [int64]$row_count.Value; $false = $null }[$row_count -ne $null],<br />@{$true = $duration.Value; $false = $null }[$duration -ne $null];<br />@{$true = $server_principal_name.Value; $false = $null }[$server_principal_name -ne $null]<br />);<br />$virtualtable.Rows.Add($row);<br />} |out-null<br />}<br />catch{<br />$loggingstring = "Processing virtual table:`t$_.Exception.Message`tReverting importfile";<br />$loggingstring >>$logfile<br />$_ = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', -1" <br />Write-Host $loggingstring <br />}<br /><br /><br />##############################################<br /># Bulk Insert into $dbTable from virtual table<br />##############################################<br /><br />$loggingstring = "$($ExEvFile) - records in extended file:`t$($virtualtable.rows.count)`t";<br />$loggingstring >>$logfile<br />Write-Host $loggingstring<br /><br />Try<br />{<br />$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$servername;user id=$userid;password=$pwd;Initial catalog=$dbname")<br />$cn.Open()<br /><br />$bcp = New-Object -TypeName System.Data.SqlClient.SqlBulkCopy $cn<br />$bcp.DestinationTableName = $dbTable<br />$bcp.Batchsize = 10000<br />$bcp.BulkCopyTimeout = 0<br />$bcp.ColumnMappings.add('collect_system_time','collect_system_time') | Out-Null<br />$bcp.ColumnMappings.add('database_name','database_name') | Out-Null<br />$bcp.ColumnMappings.add('object_name','object_name')| Out-Null<br />$bcp.ColumnMappings.add('client_hostname','client_hostname')| Out-Null<br />$bcp.ColumnMappings.add('client_app_name','client_app_name')| Out-Null<br />$bcp.ColumnMappings.add('server_name','server_name')| Out-Null<br />$bcp.ColumnMappings.add('row_count','row_count')| Out-Null<br />$bcp.ColumnMappings.add('Duration','Duration')| Out-Null<br />$bcp.ColumnMappings.add('server_principal_name','username')| Out-Null<br /><br />$loggingstring = "Starting bcp...";<br />Write-Host $loggingstring<br /><br />$bcp.WriteToServer($virtualtable)<br />$bcp.Close()<br />$cn.close()<br />$count = $virtualtable.rows.count<br />$_ = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', $count " <br />$virtualtable.clear() <br />}<br />catch{<br />$loggingstring = "Running BCP:`t$_.Exception.Message`t";<br />$loggingstring >>$logfile<br />$_ = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', -1" <br />Write-Host $loggingstring <br />}<br /><br />}<br /><br />################################NOTES#######################<br /># Notes: bcp Extended Events files into to destination table<br />############################################################<br />#$ExEvFile='\\xxxxx-srv-xxxxxx\DBA\DatabaseObjectUsage\filename.xel'<br />Function Main{<br />param(<br />#[Parameter(Mandatory=$True)]<br />[string]$ExEvFile #New File name to move<br />)<br /><br />$error.Clear();<br /><br />$servername='xxxxx-srv-xxxxx'<br />$dbName='ObjectUsage' <br />$dbTable='dbo.ObjectUsage'<br />$UserID="login_extendedevents"<br />$Pwd ='password_extendedevents_user'<br />$connectionString ="Data Source=$ServerName;Database=$dbName;User Id=$UserID;Password=$Pwd;"<br /><br />$LogFile='\\xxxx-srv-xxxx\dba\DatabaseObjectUsage\ImportXEL.log';<br /><br />#############################################<br /># Output text to console and write log header<br />#############################################<br /><br />$StartTime = Get-Date -Format "yyyy-MM-dd HH:mm:ss"<br />$loggingstring = "$StartTime`t$ServerName`tFile`t$ExEvFile`t"<br />$loggingstring >>$logfile<br />$loggingstring<br /><br />################################################################################################<br /># Add-Type when running this script on MS SQL Server<br /># use this line of code on win2012 server, need only Microsoft.SqlServer.XEvent.Linq.dll library<br /># Add the path where this dll is.<br />################################################################################################<br /><br />Add-Type -Path 'Microsoft.SqlServer.XEvent.Linq.dll' | out-null<br /><br />if ($error.count -ne 0) {<br />$loggingstring = "Error:`t$error`t";<br />$loggingstring >>$logfile<br />$loggingstring <br />$error.clear();<br />}<br /><br />if( $ExEvFile -eq "" )<br />{<br />$filelist = Get-ChildItem -Path "\\xxxxx-srv-xxxxx\DBA\DatabaseObjectUsage" -Filter "*.XEL" -Recurse -ErrorAction SilentlyContinue | Select-Object FullName, Length<br /><br />foreach( $file in $filelist )<br />{<br />if( $file.length -ge 0 )<br />{<br />$ExEvFile = $file.fullname<br /><br />$imported = Invoke-Sqlcmd -ServerInstance $servername -Username $userid -password $pwd -Query "exec ObjectUsage.dbo.usp_objectusage_import '$ExEvFile', NULL, $($file.length)"<br /><br />if ($imported.ItemArray[0] -eq "0" )<br />{<br />ImportFile( $ExEvFile );<br /><br />}<br />}<br />}<br />} <br />else<br />{<br />ImportFile( $ExEvFile );<br />} <br />#######################################<br /># End processing Write log file trailer <br />#######################################<br />$EndDate = Get-Date<br /><br />if ($error.count -ne 0) {<br />$loggingstring += "Error:`t$error`t";<br />$error.clear();<br />}<br />else {<br />$loggingstring = "Finished successfully:`t$EndDate`n";<br />$loggingstring >>$logfile<br />$loggingstring <br />}<br />}<br />################## END OF SCRIPT FILE ######################<br />#### Call Main<br /><br />Main