小贝子编程

将扩展事件结果导入到 SQL Server 表



我正在实施一个新过程，以利用扩展事件功能审核成功和失败的 SQL Server 登录。 我已经设置了扩展事件来监视成功和失败的登录，并将其结果发送到 .xel 文件。 但是，查询这些 .xel 文件以查看数据似乎非常麻烦。 最好将 .xel 文件数据导入到 SQL Server 表中。 我找到了一个可用于导入数据的 PowerShell 脚本 (http://www.sqlservercentral.com/articles/PowerShell/160582/(，但我在使其工作时遇到问题。 我设置了一个测试扩展事件来监视rpc_completed和sql_batch_completed事件，我相信这将显示已运行的 t-sql 语句以及运行它们的用户。 下面的脚本是专门为这些事件编写的，旨在导入我所解释的数据，但我无法让它工作。

有谁知道为什么我的PowerShell脚本无法加载Microsoft.SqlServer.XE.Core.dll程序集？

这是我的剧本——

########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 FilesMicrosoft SQL 
Server120SharedMicrosoft.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)
[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=(servername);Integrated Security=SSPI;Initial Catalog=AuditTest");
$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 = "C:Extended Event Audit LogsRPC_competed and sql_batch_completedRPC_competed and sql_batch_completed.xel"  ## file location
$server = "(servername)"   ## server name, you are collecting XE data
$XEFile = "RPC_competed and sql_batch_completed.xel"  ## XE File Name
Shred-XElogs -filewithPath $XEFilePath -servername $server -fileName $XEFile

这是我收到的错误消息 -

"未找到程序集。 从 C：\Program Files\Microsoft SQL Server\120\Shared\Microsoft.SQLServer.XEvent.Linq.dll" 加载它

"异常调用".ctor"，参数为"1"："无法加载文件或程序集'Microsoft.SQLServer.XE.Core，版本=12.0.0.0，Culter=neutral，PublicKeyToken=89845dcd8080cc91'或其依赖项之一。 系统找不到指定的文件。

我确保 Microsoft.Sqlserver.XE.Core.dll 文件存储在 C：\Program Files\Microsoft SQL Server\120\Shared 文件夹中。

谁能帮我？

谢谢

如果你想使用C#，你不需要powershell。 这可以通过使用fn_xe_file_target_read_file查询 .xel 文件，然后将结果存储在表中来完成。 以下示例基于与旋转锁相关的扩展事件，这将删除结果表(如果存在(，然后创建它。 当然，您可能希望将其修改为不通过删除DROP/SELECT INTO来丢弃过去的结果，或者仅TRUNCATE保存结果的表中。

string sqlQuery = @" if (OBJECT_ID(N'YourDatabase.dbo.XE_Output') is not null)
begin
drop table YourDatabase.dbo.XE_Output
end
select rf.object_name, 
cast(rf.event_data as xml).value('(//data/text)[1]', 'nvarchar(100)') as SpinlockType,
cast(rf.event_data as xml).value('(//data)[1]', 'nvarchar(250)') as SpinlockAddress,
cast(rf.event_data as xml).value('(//data)[2]', 'nvarchar(250)') as Worker,
cast(rf.event_data as xml).value('(//data/value)[3]', 'nvarchar(100)') as BackoffCount,
cast(rf.event_data as xml).value('(//data/value)[4]', 'nvarchar(100)') as Duration,
xp.name as Package, xp.description as PackageDescription, rf.file_name,
cast(rf.event_data as xml) as EventInfo
into dbo.XE_Output
from sys.fn_xe_file_target_read_file('C:TestYourOutputFile.xel', null, null, null) rf
left join sys.dm_xe_packages xp on rf.package_guid = xp.guid";

string connString = "Data Source=localhost; Initial Catalog=YourDatabase; Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand sqlCmd = new SqlCommand(sqlQuery, conn);
sqlCmd.Connection.Open();
sqlCmd.ExecuteNonQuery();
}

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium