Централизованный аудит печати — Загрузка в базу данных


Цикл статей:

1.       Описание задачи и решения

2.       Сбор событий аудита

3.       Экспорт событий аудита в файл

4.       Загрузка в базу данных

5.       Создание отчёта

6.       Размещение отчёта на Sharepoint

7.       Дополнение 1: Powershell – системные журналы и особенности фильтров по времени

8.       Дополнение 2: Powershell – производительность работы с системными журналами

На этом этапе каждые сутки мы имеем один файл в формате CSV, который нужно загрузить в базу данных. Сделать это можно средствами самого SQL (SSIS или утилитой bcp) или скриптом Powershell. Решение с SSIS мощное, гибкое, но всё же это вещь в себе. Утилита bcp тоже не проста и, как минимум, требует установки на компьютер, где запускается скрипт. Поэтому используем Powershell.

И опять возникает вопрос производительности. При малых объемах печати нужно загружать в базу сотни записей аудита печати, и это можно сделать любым способом не опасаясь проблем с производительностью. Если же ориентироваться на среднее предприятие (это наш случай), то объемы печати могут достигать нескольких десятков тысяч заданий печати в сутки. В этом случае добавление по одной записи в базу будет занимать много времени. Также отпадают всякие ODBC и подобные варианты.

Интересно: Speeding Up SSIS Bulk Inserts into SQL Server

Наибольшей производительностью обладает пакетная загрузка в SQL Bulk. И есть несколько вариантов её использования. Они описаны в статье Four Easy Ways to Import CSV Files to SQL Server with PowerShell.

Первый вариант с T-SQL BULK INSERT command отпадает, т.к. работает только локально, а нам нужна удаленная загрузка.

Второй и третий варианты с LogParser очень привлекательны, т.к. LogParser обладает огромной производительностью и гибкостью. Но LogParser нужно устанавливать. Поэтому этот вариант тоже пропускаем.

А вот вариант с использованием класса SqlBulkCopy из .Net сочетает в себе быстроту Bulk, возможность удаленной загрузки, гибкость, и к тому же он предустановлен вместе с .Net Framework.

«Всё уже написано до нас» и, действительно, поиск навел на уже готовый скрипт Write-DataTable из галереи скриптов, но у этого варианта есть один недостаток: он чувствителен к набору и порядку столбцов: если вы решите не импортировать в базу, например, имена заданий печати (docname), то скрипт выдаст ошибку.

Но есть переработанная версия этого скрипта Write-DataTable by bholliger, которая использует простейший маппинг столбцов SqlBulkCopy.ColumnMappings Property, поэтому можно не опасаясь добавлять, удалять или переставлять столбцы в исходном файле и целевой таблице базы данных. Дополнительно нам нужен скрипт для импорта CSV файла и преобразования его в формат DataTable. Такой скрипт также есть в галерее скриптов Out-DataTable.

Ниже пример команды создания базы и таблицы для событий аудита печати:

 

USE [master]
GO

/****** Object:  Database [PrintAudit]    Script Date: 9/4/2014 4:31:07 PM ******/
CREATE DATABASE [PrintAudit]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N'PrintAudit', FILENAME = N'G:\Data\PrintAudit\PrintAudit.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
LOG ON
( NAME = N'PrintAudit_log', FILENAME = N'G:\Data\PrintAudit\PrintAudit_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [PrintAudit] SET COMPATIBILITY_LEVEL = 110
GO

ALTER DATABASE [PrintAudit] SET RECOVERY BULK_LOGGED
GO

USE [PrintAudit]
GO

/****** Object:  Table [dbo].[PrintAuditEvents]    Script Date: 9/4/2014 4:30:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PrintAuditEvents](
[PrinterPort] [nvarchar](256) NULL,
[docName] [nvarchar](512) NULL,
[PrintSize] [nvarchar](256) NULL,
[Username] [nvarchar](256) NULL,
[PrinterName] [nvarchar](256) NULL,
[time] [datetime] NULL,
[Computer] [nvarchar](256) NULL,
[Company] [nvarchar](256) NULL,
[Department] [nvarchar](256) NULL,
[PrintPages] [nvarchar](256) NULL,
[DisplayName] [nvarchar](256) NULL,
[FileDate] [nvarchar](256) NULL
) ON [PRIMARY]

GO

В силу специфики нашей задачи модель восстановления для базы установлена в BulkLoggedещё описание)

Поле FileDate, как ранее было описано, предназначено для защиты от повторной загрузки файла. Но для начала мы просто будем переименовывать исходный файл, меняя расширение с CSV на BAK. Это проще и это исключит повторную загрузку файла, если кто-то запустит скрипт повторно. Позднее можно будет усложнить скрипт и перед загрузкой файла проверять по базе данных его временную метку (в базе нужно будет создать индекс по полю FileDate).

Пример скрипта:


#
#
#
#

Add-Type -AssemblyName System.Web
$a3 =  Measure-Command -Expression {

$workpath = "c:\install\printaudit\work"
$ServerInstance = "Sql11"
$Database = "PrintAudit"
$TableName = "PrintAuditEvents"
$logname = "Microsoft-Windows-PrintService/Operational"
$logname1 = [System.Web.HttpUtility]::UrlEncode($logname)

$filedate = [System.DateTime]::Now.ToString("yyyyMMdd")
$out_csv_file = "$workpath\$logname1-$filedate*.csv"

dir $out_csv_file | ForEach-Object {

$dt = $null
$dt = Import-Csv $_ -Encoding Unicode | Out-DataTable

Write-DataTable -ServerInstance $ServerInstance -Database $Database -TableName $TableName -Data $dt

Rename-Item $_ -Newname "$($_.BaseName).bak"
}
}

$a3

В чём особенность этого скрипта. Во-первых, в его начало надо добавить (или импортировать) Write-DataTable и Out-DataTable . Во-вторых, он загружает всю информацию в память и потом несколько раз перекодирует, поэтому загрузка файла в 100 Мб может потребовать до 500 Мб памяти. Это вполне по силам обычному компьютеру. Т.к. ожидаемый размер загружаемого суточного файла в разы меньше, то проблем со скриптом быть не должно. Проблема может появится, если вы будете загружать около миллиона и более записей за раз, но это масштабы большой корпорации, либо если вы будете обрабатывать события, которые генерируются в бОльших объёмах, например, события аудита безопасности. Но и в этом случае можно не заниматься оптимизацией скрипта, а просто выгружать информацию несколько раз в сутки, что уменьшит размер файла CSV и, соответственно, потребности в ресурсах.

 Продолжение следует…

Реклама

комментариев 6

  1. Илья, решение очень интересное, особенно хорошо тем что в схеме нет принт-сервера. Есть только один минус — кол-во копий распечатанного документа в отчет не попадает.. это если говорить о Server 2012R2. В 307 событии нет данных о копиях… Microsoft почему-то решил данные о копиях перенести в 805 событие.

    • По моим наблюдениям в событии 307, параметр 8, пишится именно количество распечатанных страниц. А вот в событии 805 количество копий «задания». Это верно для Windows 7.

      • На Server 2012 R2, у меня в 307 событии пишется только кол-во страниц документа. Если я печатаю 2 копии двухстраничного документа, то в параметре 8 всегда значится 2 страницы. А вот в 805 уже отображается кол-во распечатанных копий этого документа. Для правдивой статистики необходимо параметр 8 события 307 множить на параметр Copies события 805. Причем еще и куча багов.. например при печати из Word 2013 кол-во копий в 805 событии всегда 1, хотя по факту может быть хоть 10 :)

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: