16 августа 2012
Применение SQL-запросов для оценки потока событий в базах данных систем безопасности (на примере базы данных системы «ОРИОН ПРО»)
Введение
Технические средства безопасности в своем полном варианте представляют собой симбиоз оборудования и программного обеспечения. Задачей программного обеспечения является выполнение функций управления, а также, отображения и сохранения важной информации. Для хранения информации в таких системах используются базы данных.
При поверхностном взгляде базы данных состоят из таблиц, в которых хранятся данные. Каждая таблица состоит из столбцов, которые в терминологии баз данных называются полями, и строк – называемых записями.
Вышеупомянутая важная информация поступает в базу данных в виде событий, с отражением времени его (события) возникновения. Источниками этих событий являются:
- система контроля доступа;
- система сигнализации;
- система видеонаблюдения;
- система технологической сигнализации;
- действия пользователя и т.д.
Информация о событиях хранится в отдельной таблице базы данных, называемой таблица событий (журнал событий, журнал регистрации, протокол событий). Все события записываются с указанием даты, времени, источника и вида события: проход, тревога, пожар, авторизация пользователя. Иными словами - каждому событию соответствует запись в таблице (журнале) событий. Каждая запись имеет следующие поля (столбцы):
- даты,
- времени,
- источник события,
- вида события*.
* - полный набор полей гораздо шире иу каждого производителя свой.
ПРОБЛЕМА
Возникает вопрос, каково предельное количество событий, которое целесообразно хранить в базе данных. Со временем, что крайне актуально для систем с интенсивным потоком событий, например, системы контроля доступа, размер базы данных увеличивается. Необходимо отметить, что в таких базах данных зачастую хранятся не только данные журнала событий, но и настройки системы, а так же различная сопутствующая информация. Однако, нельзя не признать, что доля этой информации на длительном отрезке времени эксплуатации системы снижается по отношению к журналу событий и, в конечном счете, размер базы данных определяется этим журналом, конечно же, с некоторыми допущениями.
Увеличение размера базы данных приводит к замедлению работы с ней, например, выполнение запроса может занимать длительное время. Поэтому, рекомендуется периодически производить удаление событий из базы данных. Также, нельзя забывать, что бесплатные базы данных, например, MS SQL Server Express, имеют ограничение максимального размера файлов базы данных.
В таблице 1 приведен максимальный объем базы данных для различных выпусков SQL Server.
Таблица 1
|
MSDE 2000 |
SQL Server 2005
Express |
SQL Server 2008 Express |
SQL Server 2008 R2 Express |
Максимальный объем, ГБ |
2 |
4 |
4 |
10 |
Проблема заключается в том, что периодичность проведения процедуры удаления событий выбирается исходя из скорости заполнения базы данных событиями, которая (скорость), в свою очередь зависит от:
- размера занимаемой одной записью;
- количества происходящих на объекте событий.
Если размер записи определить можно, например, для SQL Server по методикам, описанным в http://msdn.microsoft.com/ru-ru/library/ms187445.aspx. То поток событий спрогнозировать трудно, т.к., он зависит от большого числа случайных факторов. Зачастую это удается сделать лишь в ретроспективном анализе. Например, с помощью инструмента «статистический анализ» у «Кодос»’а. Статистический анализ применяется для получения информации о числе событий, зафиксированных в системе в течение заданного периода времени. Однако, имеется возможность реализовать статистический анализ событий «своими руками» с помощью пользовательских SQL-запросов к базе данных.
SQL - компьютерный язык, используемый для работы с данными в реляционных базах данных.
Рассмотрим на примере базы данных из, всем известной, системы «Орион Про». В «Менеджере центрального сервера» (из комплекта ПО «Орион Про») имеется возможность выполнения SQL-запросов. Однако, производителем настоятельно не рекомендуется использовать SQL-запросы, за исключением реализации оператора выбора SELECT. Что ж, нас это вполне устраивает!
РЕАЛИЗАЦИЯ SQL-ЗАПРОСОВ
В системе «Орион Про» журнал событий заносится в таблицу под названием pLogData. А за дату и время возникновения события отвечает столбец TimeVal (тип DateTime). Для реализации запроса достаточно скопировать его текст из статьи в окно ввода SQL-запроса «Менеджера центрального сервера» и нажать кнопку «Выполнить запрос» (процедура подробно описана в инструкции на ПО «Орион Про», которую вы без труда найдете на сайте производителя).
Запрос 1. Общее количество событий
SELECT COUNT(*) AS 'Кол-во событий'
FROM [pLogData]
Результат:
Запрос 2. Период хранения событий
SELECT MIN([TimeVal]) AS 'Начало', MAX([TimeVal]) AS 'Конец'
FROM [pLogData]
Результат:
Запрос 3.Частота встречаемости событий
SELECT e.Contents AS 'Тип события', COUNT(*) AS 'Кол-во'
FROM [pLogData] AS p
JOIN [Events] AS e ON p.Event=e.Event
GROUP BY e.Contents
ORDER BY COUNT(*) DESC
Результат:
Запрос 4. Суммарное количество событий по дням недели
SELECT DATEPART(dw,[TimeVal]) AS [№пп], DATENAME(dw,[TimeVal]) AS [День недели], COUNT(*) AS [Сум. кол-во]
FROM [pLogData]
GROUP BY DATEPART(dw,[TimeVal]), DATENAME(dw,[TimeVal])
ORDER BY DATEPART(dw,[TimeVal])
Результат:
Запрос 5. Среднее по дням недели
SELECT T.WD AS [День недели], AVG(T.Val) AS [Среднее]
FROM (SELECT DATENAME(YY,[TimeVal]) AS [Y], DATENAME(M,[TimeVal]) AS [M], DATENAME(D,[TimeVal]) AS [D], DATENAME(W,[TimeVal]) AS [WD], COUNT(*) AS [Val]
FROM [pLogData] GROUP BY DATENAME(YY,[TimeVal]), DATENAME(M,[TimeVal]), DATENAME(D,[TimeVal]), DATENAME(W,[TimeVal])) AS T
GROUP BY T.WD
ORDER BY AVG(T.Val) DESC
Результат:
Запрос 6. Суммарное количество событий по месяцам
SELECT DATENAME(YEAR,[TimeVal]) AS [Год], DATENAME(M,[TimeVal]) AS [Месяц], COUNT(*) AS [Кол-во событий]
FROM [pLogData]
GROUP BY DATENAME(YEAR,[TimeVal]), DATEPART(M,[TimeVal]), DATENAME(M,[TimeVal])
ORDER BY DATENAME(YEAR,[TimeVal]), DATEPART(M,[TimeVal])
Результат:
Запрос 7. Среднее количество событий по месяцам
SELECT T.M AS [Месяц], AVG(T.EC) AS [Среднее]
FROM (SELECT DATENAME(YY,[TimeVal]) AS [Y], DATENAME(M,[TimeVal]) AS [M], COUNT(*) AS [EC] FROM [pLogData] GROUP BY DATENAME(YY,[TimeVal]), DATENAME(M,[TimeVal])) AS T
GROUP BY T.M
ORDER BY AVG(T.EC) DESC
Результат:
Примечание: в зависимости от версии SQLServer название дней недели и месяцев могут выводиться по-английски.
P.S. В теории представленные SQL-запросы могут быть модифицированы для применения на других базах данных (за исключением запроса №3*), хотя на практике и не ручаюсь за это. Для осуществления этого, необходимо изменить имя таблицы журнала событий (pLogData) и имя столбца времени произошедшего события (TimeVal) на соответствующие в другой базе данных. Данные имена можно получить, либо из документации, либо инспектированием таблиц, содержащихся в базе данных. Также, необходимо отметить, что есть альтернативный способ реализовывать запросы к базам данных MS SQL Server - с помощью средства управления SQL Server Management Studio Express.
*- В запросе № 3 используется данные из таблицы Event, в которой хранятся названия типов событий. Для такой модификации уже надо обладать специальными познаниями в базах данных.
