birmaga.ru
добавить свой файл

  1 ... 14 15 16 17 18

Использование служб SSIS для переноса журналов в таблицу базы данных (LoadLogsPackage.dtsx)

Как говорилось в предыдущем разделе и было показано на иллюстрации Error: Reference source not found, для преобразования и передачи данных можно воспользоваться службами SQL Server Integration Services (SSIS). На иллюстрации Error: Reference source not found показаны задачи из проекта SSIS для перемещения данных из файла аудита SQL Server в таблицу базы данных. Первая задача создает таблицу аудита, если она не существует. Вторая задача выполняет передачу данных.

Рис. . Две задачи служб SSIS для сбора журналов аудита

На иллюстрации Error: Reference source not found подробно показана задача для передачи данных. Необработанные данные журнала считываются из файла подсистемы аудита SQL Server. Из таблицы действий производится получение названия действия. Затем поток данных объединяется с таблицей класса, чтобы получить описание типа класса. Полученные в результате объединения данные помещаются в таблицу аудита.



Рис. . Задачи для объединения данных аудита и их сохранения в таблице

Службы SSIS сохраняют выходные данные в пакете, который можно загрузить в шаг задания с помощью агента SQL Server, как показано на иллюстрации Error: Reference source not found. Это позволяет автоматизировать передачу данных. Перемещение данных из файла журнала в локальную базу данных ограничивает круг лиц, имеющих к ним доступ, до администраторов. Этот вопрос необходимо обсудить с аудиторами, поскольку некоторые из них озабочены тем, что в журналы легко внести изменения. Файл CreateAuditJob.sql создает задание аудита, которое запускается каждые пять минут из файла пакета LoadLogsPackage.dtsx, который должен находиться в каталоге C:\.



Рис. . Пакет служб SSIS, загруженный в агент SQL Server

Доступ к событиям подсистемы аудита SQL Server с помощью Excel (AuditReport.xlsx)


После того как данные сохранены в таблице базы данных, можно импортировать их в книгу Excel. То же относится и к журналам подсистемы аудита SQL Server. Excel реализует привычные механизмы анализа данных, представленных в виде таблиц. На иллюстрации Error: Reference source not found показано, как выглядит таблица подсистемы аудита SQL Server, описанная в предыдущем разделе, после импорта в Excel через ленту «Данные». Чтобы ограничить результат по датам, действиям, объектам и пользователям, можно воспользоваться фильтрами.

Рис. . Журналы подсистемы аудита SQL Server после загрузки в Excel

На основе данных в Excel можно создать динамическое представление PivotTable® с помощью ленты Вставка. На иллюстрации Error: Reference source not found показан пример представления PivotTable, созданного на основе табличных данных аудита. В PivotTable включаются следующие поля: действие, тип класса и имя участника-службы сервера. Созданная для PivotTable линейчатая диаграмма показывает по типам количество действий, выполненных для определенных объектов пользователем с указанным идентификатором. Список полей в PivotTable определяет поля, включаемые в диаграмму, и способ их использования.



Рис. . Представление PivotTable, сформированное на основе журналов подсистемы аудита SQL Server


Получение IP-адреса из журнала аудита

При просмотре журналов аудита поле server_principal_name может служить для определения идентификатора пользователя, выполнившего зарегистрированную операцию, там где это возможно. Однако если для соединения с базой данных используется учетная запись SQL Server, а не учетная запись Windows, то поле server_principal_name не содержит полезной информации, которая позволила бы просто определить лицо, выполнявшее операцию. Однако в журнале имеются данные, которые могут помочь в определении идентификатора пользователя. Каждая запись журнала содержит поле session_id. Это поле поставлено в соответствие идентификатору, назначенному пользователю при входе в систему. В самой первой записи журнала (с идентификатором действия LGIS), имеющей этот идентификатор ID, поле additional_information будет содержать IP-адрес компьютера, который подключается к базе данных. По журналам на этом компьютере можно выяснить, кто работал на нем в это время. Ниже приведен сценарий для получения IP-адреса из поля server_principal_name.

SELECT event_time, statement,

CAST(additional_information AS XML).value('declare namespace z="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data";

(//z:address)[1]', 'nvarchar(300)')

FROM sys.fn_get_audit_file('C:\logs\*',Null, Null)

WHERE action_id = 'LGIS'

ORDER BY event_time DESC

Проект аудита


Этот проект является совместной работой Айяда Шаммута (Ayad Shammout) (Caregroup Healthcare), Энди Робертса (Andy Roberts) (Microsoft Consulting Services) и Денни Ли (Denny Lee) (SQL Customer Advisory Team). Целью этого проекта было создание работающего шаблона для комплексного проекта аудита, способного получать журналы, преобразовывать и загружать данные, выполнять их статистическую обработку и строить отчеты. Дополнительные сведения см. в разделе Технический обзор проекта аудита (готовится к публикации).
Рис. . Рабочий поток для решения с проектом аудита


Загрузите решение

Распакуйте файл SQLAudit.zip. Он должен содержать следующие файлы:



  • SQLAuditRepositoryDatabase.sql — SQL-файл, который создает базу данных SQLAudit




  • LoadLogsPackage.dtsx — пакет служб SSIS, который выполняет задачу по получению, извлечению, преобразованию и загрузке данных аудита, а затем архивирует файл журнала




  • SQLAuditReports — решение служб SQL Server Reporting Services (SSRS), которое можно развернуть на своем сервере для просмотра отчетов аудита



Создайте базу данных SQLAudit

Эта база данных будет служить хранилищем данных аудита.



  1. Откройте файл SQLAuditRepositoryDatabase.sql в среде SQL Server Management Studio и включите режим SQLCMD (в меню Запрос выберите режим SQLCMD). На то, что включен режим SQLCMD, указывают закрашенные серым строки :setvar.




  1. Определите следующие переменные.

Переменная

Описание


Комментарии

DataDirectory

Папка данных базы данных SQL Server (например, H:\sqldata\)

Обязательно завершайте путь обратной косой чертой «\».

LogDirectory

Папка журналов базы данных SQL Server (например, H:\sqllog\)

Обязательно завершайте путь обратной косой чертой «\».

DatabaseName

Имя базы данных (например, SQLAudit)

Нет.




  1. Выполните сценарий. Создается база данных.



Установите пакет LoadLogsPackageSSIS

Будут созданы файлы конфигурации, необходимые для выполнения пакета служб SSIS.



  1. Дважды щелкните SQLAuditLoader.SSISDeploymentManifest. Если с этим файлом не связана никакая программа, запустите файл C:\program files\Microsoft SQL Server\100\DTS\Binn\dtsinstall.exe, чтобы открыть мастер установки пакета. Дополнительные сведения об установке пакетов см. в разделе http://msdn.microsoft.com/ru-ru/library/ms365321(SQL.100).aspx.




  1. Нажмите кнопку Далее, щелкните Установить в файловую систему и снова нажмите Далее. Выберите папку установки, например C:\Program Files\Microsoft SQL Server\100\DTS\Packages\SQLAuditLoader, нажмите кнопку Далее, а затем снова кнопку Далее.




  1. Настройте следующие свойства пакета.


Параметр


Пример значения

Регистратор служб SSIS для SQL Server

SqlAuditLogRepository


SqlAuditLogRepository

Укажите имя базы данных SQLAudit (например, SQLAudit)



Data Source=.;Initial Catalog=$DBName$;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package-{21C9032A-E45A-41F2-BA67-9EF35FCD18C3}SqlAuditLogRepository;

User:auditLogArchivePath

Местоположение, в которое будет производиться архивирование после обработки



D:\audit\logs\archive


User:LogFilePath

Расположение журналов аудита



D:\Audit\logs

Эти значения будут сохранены в файле LoadLogsPackageConfig.dtsConfig. Если их необходимо изменить после установки пакета служб SSIS, то это можно сделать вручную в этом файле.

Примечание. Эта версия проекта аудита подразумевает, что каталог, из которого производится получение файлов аудита, является локальным.

  1. Нажмите кнопку Далее, а затем кнопку Завершить.

Выполните пакет служб SSIS

При выполнении пакета подразумевается, что в папке D:\audit\logs находятся файлы аудита (как указано выше) и после обработки они будут перемещены в папку D:\audit\logs\archive.




  1. В командной строке перейдите в каталог SQLAuditLoader (например, C:\Program Files\Microsoft SQL Server\100\DTS\Packages\SQLAuditLoader).




  1. Выполните команду:

dtexec /ConfigFile LoadLogsPackageConfig.dtsConfig /File LoadLogsPackage.dtsx


Примечание. Убедитесь в том, что используется версия DTExec из поставки SQL Server 2008. На сервере, где установлено несколько экземпляров SQL Server 2005 и SQL Server 2008, может запуститься версия DTExec для SQL Server 2005 (9.00.xxxx), а не для SQL Server 2008 (10.00.xxxx). Чтобы избежать этого, нужно указать полный путь к программе DTExec (например, C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe).

Поскольку файлы журналов аудита формируются постоянно, рекомендуется запускать этот пакет служб SSIS периодически (например, раз в 15 минут), чтобы обеспечить своевременную загрузку данных.



Формирование статистических отчетов

Пакет служб SSIS обрабатывает журналы аудита и загружает их в таблицы aud.AuditLog_[тип события] в базе данных SQLAudit. Но поскольку в них содержится множество событий, удобнее создавать статистические (т. е. сводные) отчеты, которые содержат обобщенную картину по аудиту.




  • Для этого в среде SQL Server Management Studio подключитесь к базе данных SQLAudit и выполните следующие команды.

exec aud.rspAggServerActions @EventDate = '08/22/2008'

exec aud.rspAggDatabaseActions @EventDate = '08/22/2008'

exec aud.rspAggDMLActions @EventDate = '08/22/2008'

exec aud.rspAggDDLActions @EventDate = '08/22/2008'

В результате этого в таблицы aud.rptAgg[событие аудита]Actions будет загружена сводная статистика.


  • Рекомендуется вызывать эти хранимые процедуры (например, с помощью агента SQL Server) по ночам, чтобы обеспечить актуальность дневной информации. Пример сценария для запуска задания по ночам выглядит следующим образом.

-- Объявить переменную @LastDay, в которой будет храниться последняя дата,

-- задание будет запущено после полуночи для статистической обработки данных за последний день

Declare @LastDay char(11)

select @LastDay = Convert(char(11), getdate()-1 , 1)

Select @LastDay

Exec aud.rspAggServerActions @LastDay

Exec aud.rspAggDatabaseActions @LastDay

Exec aud.rspAggDDLActions @LastDay

Exec aud.rspAggDMLActions @LastDay


Как секционированы ваши данные?

SQL-сценарий для создания базы данных аудита (SQLAudit) автоматически создаст 12 файловых групп по одной на каждый месяц, начиная от месяца, в котором выполняется сценарий. Кроме того, схема и функции секционирования обеспечат секционирование по месяцам таблиц aud.AuditLog_% в соответствии с этими файловыми группами. Следующий сценарий покажет, в какие секции таблицы попали те или иные строки данных.

select partition_id, OBJECT_NAME(object_id), object_id, index_id, partition_number, partition_id, rows as [RowCount], x.value

from sys.partitions

left outer join (

select boundary_id, value

from sys.partition_range_values

where function_id = (

select function_id

from sys.partition_functions

where [name] = 'monthly_partition_function'

)

) x



on x.boundary_id = partition_number - 1

where OBJECT_NAME(object_id) like 'AuditLog%' and index_id = 1

order by OBJECT_NAME(object_id), partition_number

Просмотр отчетов

Как отмечалось в разделе «Аудит конфиденциальных операций», существует ряд отчетов, которые можно сформировать на основе журналов аудита. Для просмотра этих отчетов необходимо выполнить развертывание решения SQLAuditReports служб Reporting Services.




  1. Откройте решение SQLAuditReports служб Reporting Services в среде разработки Microsoft Visual Studio®.




  1. Укажите в качестве TargetServerURL свой сервер, т. е. замените http://campschurmann/ на имя своего сервера.


Рис. . Страница свойств SQL AuditingReports


  1. Убедитесь в том, что общий источник данных SQLAudit.rds указывает на вашу базу данных SQLAudit (а не на версию базы данных SQLAudit на другом сервере).





  1. Выполните развертывание отчетов (Построить > Развернуть). После этого можно просматривать отчеты по адресу: http://[имя сервера] /Reports/Pages/Folder.aspx?ItemPath=%2fSQL+Auditing+Reports&ViewMode=List

Известные проблемы

  • Бывают случаи, когда в журналы аудита не попадает имя экземпляра сервера, т. е. поле имени экземпляра сервера остается пустым. Чтобы обойти эту проблему, пакет служб SSIS проходит по всему журналу и выясняет имя экземпляра сервера, а затем присваивает его всем записям журнала (поскольку все они поступили с одного сервера). Но в некоторых случаях такой подход не работает, поскольку во всем журнале имя экземпляра пусто. Чтобы устранить эту проблему, файлы журналов должны называться в соответствии с соглашением об именах:

SQLAudit$%Server$InstanceName%_%GUID%.sqlaudit

[aud].[fn_GetServerInstanceName] — таким образом, экземпляр можно будет извлечь из имени файла аудита (%Server$InstanceName%) и использовать в том случае, если оно отсутствует в журнале. Чтобы называть файлы журналов в соответствии с соглашением об именах при их создании на сервере, измените имя аудита, указав его в формате SQLAudit$Server$InstanceName.



<< предыдущая страница   следующая страница >>