TSQL-Tasks: августа 2015

В 2012 году в Analysis Services появилась новая возможность отслеживать разнообразные события сервера аналитики с помощью сессий расширенных событий. Рассмотрим как можно в автоматическом режиме настраивать и загружать в реляционные таблицы логи событий сервера Analysis Services. Для начала понадобится связанный сервер, который позволит запускать код XML/A на стороне Database Engine. Создадим linked-сервер следующим кодом:

use master

exec master.dbo.sp_addlinkedserver @server = N'ANALYTICS', @srvproduct = N'MSOLAP', @provider = N'MSOLAP', @datasrc = N'1-ПК'

exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ANALYTICS', @useself = N'True', @locallogin = null, @rmtuser = null, @rmtpassword = null

exec master.dbo.sp_serveroption @server = N'ANALYTICS', @optname = N'collation compatible', @optvalue = N'true'

exec master.dbo.sp_serveroption @server = N'ANALYTICS', @optname=N'data access', @optvalue = N'true'

exec master.dbo.sp_serveroption @server = N'ANALYTICS', @optname = N'rpc', @optvalue = N'true'

exec master.dbo.sp_serveroption @server = N'ANALYTICS', @optname = N'rpc out', @optvalue = N'true'

go

При создании сессий расширенных событий требуется создавать файл с расширением xel. Сервер при этом добавляет к имени файла суффикс. Для определения его точного имени создадим табличную clr-функцию, которая возвращает список всех файлов каталога. Также понадобится функция, проверяющая существование файла, и процедура, удаляющая файл:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Collections;

using System.Collections.Generic;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlBoolean IsFileExist(SqlString vcFile)

{

return File.Exists(vcFile.ToString());

}

[Microsoft.SqlServer.Server.SqlProcedure]

public static void FileDelete(SqlString vcFile)

{

File.Delete(vcFile.ToString());

}

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "DirDataSend", TableDefinition = "vcFile nvarchar ( 400 )")]

public static IEnumerable DirScan(SqlString vcDir)

{

List<string> Arr = new List<string>();

foreach (string it in Directory.GetFiles(vcDir.ToString()))

{

Arr.Add(it);

}

return Arr;

}

public static void DirDataSend(object Item, out string vcFile)

{

vcFile = (string)Item;

}

};

С помощью программы sn.exe создадим асимметричный ключ, запустив от имени администратора следующую команду:

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\sn.exe" -k "C:\Users\key.snk"

Этим ключом подпишем сборку. Это требуется для того чтобы сборку, которая не является безопасной, можно было развернуть в базе данных, у которой отключено свойство trustworthy.
Далее требуется импортировать этот ключ на сервер и создать на его основе логин с необходимыми правами:

use master

create asymmetric key ExternalOperations

from file = 'C:\Users\key.snk'

encryption by password = N'p@ssw0rd'

create login ExtrenalLogin from asymmetric key ExternalOperations

grant external access assembly to ExtrenalLogin

go

Теперь развернем сборку и новые объекты в базе данных:

create assembly Files

from 'C:\Users\SqlServerProject1\bin\Debug\SqlServerProject1.dll'

with permission_set = external_access

create function dbo.IsFileExist ( @vcFile nvarchar ( 200 ) )

returns bit

as external name Files.UserDefinedFunctions.IsFileExist

create function dbo.DirScan ( @vcDir nvarchar ( 200 ) )

returns table ( vcFile nvarchar ( 300 ) )

as external name Files.UserDefinedFunctions.DirScan

create proc dbo.FileDelete ( @vcFile nvarchar ( 300 ) )

as external name FilesNew.UserDefinedFunctions.FileDelete

go

Теперь можно создать инфраструктуру для отслеживания событий Analysis Services. Для того чтобы создать сессию расширенных событий требуется выполнить на сервере аналитики код на языке XML/A.

<Create

xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"

xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"

xmlns:ddl100_10="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"

xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"

xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">

<Trace>

<ID>SessId</ID>

<Name>SessId</Name>

<ddl300_300:XEvent>

<event_session

name="xeas"

dispatchLatency="2"

maxEventSize="8"

maxMemory="8"

memoryPartitionMode="none"

eventRetentionMоde="allowSingleEventLoss"

trackCausality="true">

</target>

</event_session>

</ddl300_300:XEvent>

</Trace>

</ObjectDefinition>

</Create>

В элементах с именем event можно указать список событий, за которыми будет следить сессия. Полный перечень событий можно посмотреть в одном из системных представлений сервера Analysis Services. В атрибутах элемента event_session прописываются параметры сессии, например, максимальный объем оперативной памяти, для буферизации событий, перед их записью в файл. Также можно указать время задержки перед переносом информации из оперативной памяти в файл, узел оперативной памяти, в котором будет находиться буфер, максимальный размер события, который может протоколироваться, реакция на переполнение буфера. В элементе target указываются параметры файла, в котором будут храниться события.

Для начала создадим таблицу с перечнем сессий для возможности создания многих сессий, отслеживающих разнообразные события.

if object_id ( N'dbo.TrackSessions', N'U' ) is null

begin

create table dbo.TrackSessions

(

iSessId smallint identity ( 1, 1 ) not null,

vcName varchar ( 200 ) not null,

iCurOffSet bigint not null,

constraint PK_TrackSessions_iSessId primary key clustered ( iSessId asc ) on [PRIMARY],

constraint PK_TrackSessions_vcName unique nonclustered ( vcName asc ) on [PRIMARY]

)
end

При считывании лога расширенного события потребуется читать xel-файл с определенной периодичностью. Чтобы всякий раз считывать только новые события, требуется запоминать последний оффсет. Для этой цели используется столбец iCurOffset. Следующий код показывает как создать табличный тип данных, с помощью которого можно передавать перечень событий как параметр процедуры.

if type_id ( 'dbo.EventsList' ) is null

begin

create type dbo.EventsList as table

(

vcEvent varchar ( 200 ) not null,

primary key clustered ( vcEvent asc )

)

end

Теперь можно написать процедуру для отслеживания событий.

if object_id ( N'dbo.MakeSession', N'P' ) is null

exec ( N'create proc dbo.MakeSession as return' )

alter proc dbo.MakeSession

(

@vcSessName varchar ( 200 ),

@EventsList dbo.EventsList readonly

)

begin

set nocount, xact_abort on

declare @vcErr nvarchar ( max ), @vcEventList varchar ( max ), @vcDir varchar ( 300 ) = 'C:\Users\', @vcFile varchar ( 300 )

begin try

if exists

(

select *

from dbo.TrackSessions

where vcName = @vcSessName

) or exists

(

select *

from openquery ( analytics, N'select * from $system.discover_traces' )

where cast ( TraceName as varchar ( max ) ) = @vcSessName

)

begin

; throw 60000, N'Сессия с указанным именем уже зарегистрирована', 1

end

set @vcFile = @vcDir + @vcSessName + '.xel'

set @vcEventList = replace ( replace ( replace ( (

select '<event package="AS" name="' + vcEvent + '" />' + nchar ( 13 ) as [text()]

from @EventsList

for xml path ( '' )

), '<', '<' ), '>', '>' ), '', nchar ( 13 ) )

declare @xSessDef varchar ( max ) =

<Create

xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"

xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"

xmlns:ddl100_10="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"

xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"

xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">

<Trace>

<ID>' + @vcSessName + '</ID>

<Name>' + @vcSessName + '</Name>

<ddl300_300:XEvent>

<event_session

name="xeas"

dispatchLatency="2"

maxEventSize="8"

maxMemory="8"

memoryPartitionMode="none"

eventRetentionMode="allowSingleEventLoss"

trackCausality="true">

' + @vcEventList + '

</target>

</event_session>

</ddl300_300:XEvent>

</Trace>

</ObjectDefinition>

</Create>

exec ( @xSessDef ) at analytics

set @vcFile = (

select top 1 vcFile

from dbo.DirScan ( @vcDir )

where vcFile like @vcDir + @vcSessName + '%'

)

insert into dbo.TrackSessions ( vcName, vcFilePath, iCurOffSet )

values ( @vcSessName, @vcFile, 0 )

end try

begin catch

set @vcErr = concat ( 'Ошибка: ', error_message (), ' Строка: ', error_line (), '. Номер: ', error_number (), '. Процедура: ', error_procedure () )

; throw 60000, @vcErr, 1

end catch

end

go

Можно, например, так создать сессию для отслеживания подключений, запросов и ошибок:

declare @Events dbo.EventsList

insert into @Events ( vcEvent )

select vcEvent

from ( values ( 'AuditLogin' ), ( 'QueryEnd' ), ( 'Error' ) ) EventList ( vcEvent )

exec dbo.MakeSession 'BaseTrack', @Events

go

Информацию о всех типах событий, которые можно отслеживать, можно получить из следующих запросов:

select try_convert ( xml, Data ), *

from openquery ( analytics, 'select * from $system.DISCOVER_TRACE_EVENT_CATEGORIES' )

select *

from openquery ( analytics, 'select * from $system.DISCOVER_XEVENT_TRACE_DEFINITION' )

На всякий случай создадим процедуру, которая корректно удаляет сессию.

if object_id ( N'dbo.DeleteSession', N'P' ) is null

exec ( N'create proc dbo.DeleteSession as return' )

alter proc dbo.DeleteSession

(

@vcSessName varchar ( 200 )

)

begin

set nocount, xact_abort on

declare @vcSessDel varchar ( max ) = '

<TraceID>' + @vcSessName + '</TraceID>

</Object>

</Delete>

', @vcFile varchar ( 300 )

if exists

(

select *

from openquery ( analytics, N'select * from $system.discover_traces' )

where cast ( TraceName as varchar ( max ) ) = @vcSessName

)

begin

exec ( @vcSessDel ) at analytics

end

select @vcFile = vcFilePath

from dbo.TrackSessions

where vcName = @vcSessName

if dbo.IsFileExist ( @vcFile ) = 1

begin

exec dbo.FileDelete @vcFile

end

delete dbo.TrackSessions

where vcName = @vcSessName

end

go

Приступим к сбору данных. Необходимо создать таблицу со всеми логами. Также понадобится таблица для хранения атрибутов: логин, домен, сетевой адрес, приложение, база данных.

if object_id ( N'dbo.AttrTypes', N'U' ) is null

begin

create table dbo.AttrTypes

(

iAttrTypeId smallint identity ( 1, 1 ) not null,

vcName varchar ( 200 ) not null,

constraint PK_AttrTypes_iAttrId primary key clustered ( iAttrType asc ) on [PRIMARY],

constraint PK_AttrTypes_vcName unique nonclustered ( vcName asc ) on [PRIMARY]

)

end

insert into dbo.AttrTypes ( vcName )

select vcName

from ( values ( 'Домен' ), ( 'Логин' ), ( 'Сетевой адрес' ), ( 'База данных' ), ( 'Приложение' ) ) AttrTypes ( vcName )

where vcName not in ( select vcName from dbo.AttrTypes )

if object_id ( N'dbo.Attrs', N'U' ) is null

begin

create table dbo.Attrs

(

iAttrId smallint identity ( 1, 1 ) not null,

iAttrTypeId smallint not null,

vcName varchar ( 200 ) not null,

constraint PK_Attrs_iAttrId primary key clustered ( iAttrId asc ) on [PRIMARY],

constraint PK_Attrs_vcName unique nonclustered ( vcName asc, iAttrTypeId asc ) on [PRIMARY],

constraint FK_Attrs_iAttrTypeId foreign key ( iAttrTypeId ) references dbo.AttrTypes ( iAttrTypeId ) on update cascade on delete no action

)

end

go

Мы создали таблицу с типами атрибутов, что позволяет использовать для всех текстовых атрибутов одну таблицу: dbo.Attrs. Код ниже создает таблицу с перечнем событий и главную таблицу с логами:

if object_id ( N'dbo.SsasEvents', N'U' ) is null

begin

create table dbo.SsasEvents

(

iEventId smallint identity ( 1, 1 ) not null,

vcCode varchar ( 100 ) not null,

vcDescr varchar ( max ) not null,

constraint PK_SsasEvents_iEventId primary key clustered ( iEventId asc ) on [PRIMARY],

constraint AK_SsasEvents_vcCode unique nonclustered ( vcCode asc ) on [PRIMARY]

) on [PRIMARY]

end

insert into dbo.SsasEvents ( vcCode, vcDescr )

select vcCode, vcDescr

from

( values

( 'AuditLogin', 'Подключение к сервеву' ),

( 'QueryEnd', 'Завершение работы запроса' ),

( 'Error', 'Ошибка' )

) SsasEvents ( vcCode, vcDescr )

where vcCode not in ( select vcCode from dbo.SsasEvents )

if object_id ( N'dbo.SsasLogs', N'U' ) is null

begin

create table dbo.SsasLogs

(

iRowId int identity ( 1, 1 ) not null,

iEventId smallint not null,

iLoginId smallint not null,

iDomainId smallint not null,

iAppId smallint not null,

iDbId smallint not null,

iHostId smallint not null,

iConnectId int not null,

vcTxt varchar ( max ) not null,

dtStart datetime not null,

iError int not null,

iDuration int not null,

iCpu int not null,

constraint PK_SsasLog_iRowId primary key clustered ( iRowId asc ) on [PRIMARY],

constraint FK_SsasLog_iRowId_iLoginId foreign key ( iLoginId ) references dbo.Attrs ( iAttrId ) on update no action on delete no action,

constraint FK_SsasLog_iRowId_iDomainId foreign key ( iDomainId ) references dbo.Attrs ( iAttrId ) on update no action on delete no action,

constraint FK_SsasLog_iRowId_iAppId foreign key ( iAppId ) references dbo.Attrs ( iAttrId ) on update no action on delete no action,

constraint FK_SsasLog_iRowId_iDbId foreign key ( iDbId ) references dbo.Attrs ( iAttrId ) on update no action on delete no action,

constraint FK_SsasLog_iRowId_iHostId foreign key ( iHostId ) references dbo.Attrs ( iAttrId ) on update no action on delete no action,

constraint FK_SsasLog_iRowId_iEventId foreign key ( iEventId ) references dbo.SsasEvents ( iEventId ) on update no action on delete no action

) on [PRIMARY]

end

Таблица является полностью нормализованной, что снижает требования к дисковому пространству. В качестве типов данных идентификаторов всех справочников используется smallint, так как он занимает в 2 раза меньше места чем int. Приступим к разработке процедуры, которая будет периодически запускаться агентом и считывать вновь появившиеся записи в xel-файле, сохраняя их в таблице SsasLogs. Для обращения к файлу будет использоваться функция sys.fn_xe_file_target_read_file. Эта функция имеет параметр для оффсета файла, что позволит всегда читать только новые события.

if object_id ( N'dbo.LogSsas', N'P' ) is null

exec ( N'create proc dbo.LogSsas as return' )

alter proc dbo.LogSsas

(

@vcSsasName varchar ( 200 )

)

begin

set nocount, xact_abort on

declare @vcFile varchar ( 300 ), @vcIniFile varchar ( 300 ), @iOffset int

select @vcFile = vcFilePath, @iOffset = iCurOffset

from dbo.TrackSessions

where vcName = @vcSsasName

set @iOffset = iif ( @iOffset = 0, null, @iOffset )

set @vcIniFile = iif ( @iOffset is null, null, @vcFile )

if object_id ( N'tempdb..#Events', N'U' ) is not null

drop table #Events

select xEvent = cast ( event_data as xml ), iOffset = file_offset

into #Events

from sys.fn_xe_file_target_read_file( @vcFile, null, @vcIniFile, @iOffset )

select @iOffset = max ( iOffset )

from #Events

if object_id ( N'tempdb..#AtttrNorm', N'U' ) is not null

drop table #AtttrNorm

select

vcEvent = evn.xEvent.value ( '(/event/@name)[1]', 'varchar ( 200 )' ),

dtTime = evn.xEvent.value ( '(/event/@timestamp)[1]', 'datetime' ),

vcAttr = curevn.evndet.query ( '.' ).value ( '(/data/@name)[1]', 'varchar ( 200 )' ),

vcVal = curevn.evndet.query ('.').value ( '(/data/value)[1]', 'varchar ( max )' )

into #AtttrNorm

from

#Events evn

cross apply

evn.xEvent.nodes ( '/event/data' ) curevn ( evndet )

if object_id ( N'tempdb..#FlatAttr', N'U' ) is not null

drop table #FlatAttr

select

vcEvent,

vcTxt = isnull ( TextData, '' ),

iConnectId = isnull ( ConnectionId, 0 ),

vcLogin = coalesce ( NTCanonicalUserName, NTDomainName + '\' + NTUserName, '' ),

vcApp = isnull ( ApplicationName, '' ),

dtStart = isnull ( StartTime, '1900-01-01' ),

iDuration = isnull ( Duration, 0 ),

vcDb = isnull ( DataBaseName, '' ),

iError = isnull ( Error, 0 ),

iCpu = isnull ( CPUTime, 0 ),

vcDomain = isnull ( NTDomainName, '' ),

vcHost = iif

(

charindex ( ':', isnull ( ClientHostName, '' ), 4 ) <> 0,

left ( isnull ( ClientHostName, '' ), charindex ( ':', isnull ( ClientHostName, '' ), 4 ) - 1 ),

isnull ( ClientHostName, '' )

)

into #FlatAttr

from

(

select vcEvent, dtTime, vcVal, vcAttr

from #AtttrNorm

) as src

pivot

(

max ( vcVal )

for vcAttr in

(

TextData,

ConnectionId,

NTUserName,

ApplicationName,

StartTime,

Duration,

DataBaseName,

Error,

CPUTime,

NTDomainName,

ClientHostName,

NTCanonicalUserName

)

) as trg

insert into dbo.Attrs ( iAttrTypeId, vcName )

select attrtp.iAttrTypeId, data.vcAttr

from

(

select distinct vcAttrType, vcAttr

from

(

select vcAttrType = 'Логин', vcAttr = vcLogin from #FlatAttr

union all

select 'Приложение', vcApp from #FlatAttr

union all

select 'База данных', vcDb from #FlatAttr

union all

select 'Домен', vcDomain from #FlatAttr

union all

select 'Сетевой адрес', vcHost from #FlatAttr

) data

inner join

dbo.AttrTypes attrtp on data.vcAttrType = attrtp.vcName

left outer join

dbo.Attrs attr on

attr.iAttrTypeId = attrtp.iAttrTypeId and

attr.vcName = data.vcAttr

where attr.iAttrId is null

insert into dbo.SsasLogs

(

dtStart,

iEventId,

iConnectId,

iDomainId,

iLoginId,

iAppId,

iDbId,

iHostId,

iError,

iDuration,

iCpu,

vcTxt

)

select

flatdata.dtStart,

evn.iEventId,

flatdata.iConnectId,

domain.iAttrId,

logg.iAttrId,

app.iAttrId,

db.iAttrId,

net.iAttrId,

flatdata.iError,

flatdata.iDuration,

flatdata.iCpu,

flatdata.vcTxt

from

#FlatAttr flatdata

inner join

dbo.Attrs domain on

flatdata.vcDomain = domain.vcName and

domain.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'Домен' )

inner join

dbo.Attrs logg on

flatdata.vcLogin = logg.vcName and

logg.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'Логин' )

inner join

dbo.Attrs app on

flatdata.vcApp = app.vcName and

app.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'Приложение' )

inner join

dbo.Attrs db on

flatdata.vcDb = db.vcName and

db.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'База данных' )

inner join

dbo.Attrs net on

flatdata.vcHost = net.vcName and

net.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'Сетевой адрес' )

inner join

dbo.SsasEvents evn on flatdata.vcEvent = evn.vcCode

if @iOffset is not null

begin

update dbo.TrackSessions

set iCurOffSet = @iOffset

where vcName = @vcSsasName

end

go

В процедуре используются методы типа данных xml для получения данных о событиях. Также выполняется наполнение справочников на случай появления новых атрибутов. Все готово. Надо только подготовить джоб, который периодически будет запускать процедуру.
Если для оптимизации потребуется создать индексы на таблице #FlatAttr, то следует учитывать, что сейчас все столбцы с атрибутами имеют тип varchar ( max ) и перед созданием индекса, следует в инструкции select ... into #FlatAttr использовать конвертацию для преобразования нужных для индекса атрибутов в типы данных с меньшей длиной.
Для удобного просмотра логов можно написать такое представление:

if object_id ( N'dbo.SsasLogGet', N'V' ) is null

exec ( N'create view dbo.SsasLogGet as select 1 as i' )

alter view dbo.SsasLogGet

select

vcEvent = evn.vcCode,

logs.dtStart,

logs.iConnectId,

vcDomain = domain.vcName,

vcLogin = logg.vcName,

vcApp = app.vcName,

vcDb = db.vcName,

vcNet = net.vcName,

logs.iError,

logs.iDuration,

logs.iCpu,

logs.vcTxt

from

dbo.SsasLogs logs

inner join

dbo.SsasEvents evn on logs.iEventId = evn.iEventId

inner join

dbo.Attrs domain on

logs.iDomainId = domain.iAttrId and

domain.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'Домен' )

inner join

dbo.Attrs logg on

logs.iLoginId = logg.iAttrId and

logg.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'Логин' )

inner join

dbo.Attrs app on

logs.iAppId = app.iAttrId and

app.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'Приложение' )

inner join

dbo.Attrs db on

logs.iDbId = db.iAttrId and

db.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'База данных' )

inner join

dbo.Attrs net on

logs.iHostId = net.iAttrId and

net.iAttrTypeId = ( select iAttrTypeId from dbo.AttrTypes where vcName = 'Сетевой адрес' )

TSQL-Tasks

Страницы

Страницы

суббота, 29 августа 2015 г.

Отслеживание событий Analysis Services

Страницы

Страницы

суббота, 29 августа 2015 г.

Отслеживание событий Analysis Services

суббота, 29 августа 2015 г.