Загрузка лога kerio в MSSQL

Столкнулся с проблемой анализа наиболее посещаемых сайтов в компании. Топ 10 сайтов за прошлый месяц. 

А также с задачей составить отчет по деятельности пользователя, чтобы все хранилось в одном месте.

В результате придумал такую вещь. Все грузим в таблицу MSSQl, далее в 1С подключаем эту таблицу как внешний источник данных и строим различные отчеты

1. Разбираем файл лога Kerio

 Формат файла следующий:

[11/Nov/2014 14:17:28] 192.168.31.9 — «Яндекс» http://ya.ru/

Дата

IP адрес компьютера

Заголовок браузера

Интернет ссылка

Файл довольно объемный у меня весит около 300 метров. Для чтения из 1С использую объект «ЧтениеТекста». Перебираю строки и парсю их на составляющие. Иногда попадаются недопустимые символы. С помощью функции 1С «НайтиНедопустимыеСимволыXML» избавляюсь от них.

2. Создаем хранимую процедуру на MSSQL

 

INSERT INTO KerioLog.dbo.Log

    ([id] ,[IP],[Text],[DateEvent],[Ost])

    VALUES (@id, @IP,@text,@DataEvent,@Ost)

3. Создаем внешний источник данных в 1С

Подключаюсь к MSSQL через стандартный драйвер.

Строка подключения:

DRIVER={SQL Server};SERVER=server1C;UID=sa;PWD=password;DATABASE=KerioLog

 Добавляю таблицу и функцию.

На закладке дополнительно указываю права отдельно на таблицу и отдельно на функцию.

Более подробно подключение к внешним источникам описано здесь: //infostart.ru/public/90666/

 При первом запуске в режиме Предприятия будет выводиться оконо с запросом аутентификации к MSSQL серверу.

4. Запускаем обработку загрузки данных в таблицу

ВнешниеИсточникиДанных.LogKerio.dbo_insert_log(ТекИтерация,ИП, СтрокаURL,ДатаСоб,Остаток);

В поле «Остаток» я записывал домен из СтрокиURL. Далее в отчете я компоновал по этому полю и получал количество посещений одного сайта.

5. Строим отчет по таблице через компоновку данных

 ВЫБРАТЬ
dbo_Log.IP КАК IP,
dbo_Log.Text КАК URL,
dbo_Log.DateEvent КАК ДатаСобытия,
dbo_Log.Ost КАК Домен,
dbo_Log.Text КАК КоличествоUrl,
НАЧАЛОПЕРИОДА(dbo_Log.DateEvent, ДЕНЬ) КАК НачалоДня
ИЗ
ВнешнийИсточникДанных.LogKerio.Таблица.dbo_Log КАК dbo_Log
ГДЕ
dbo_Log.DateEvent МЕЖДУ &НачалоПериода И &КонецПериода
УПОРЯДОЧИТЬ ПО
ДатаСобытия

 В настройках отчета создал 2 варианта:

Самые посещаемые сайты 

Посещаемые сайты по дням

Оформил все в виде отдельной конфигурации. 

Обработка загрузки данных из Kerio и отчет к внешней таблице.

Конфигурация будет полезна для быстрого понимания, как работать с внешними источниками данных.