Администрирование
09
Май
9

Отчет об использовании MS CRM

Частый вопрос, который задают клиенты (в частности админы и руководители) – можно ли узнать, какие пользователи и когда используют CRM. Платформа CRM обеспечивает возможность собирать детализированную информацию об использовании CRM при помощи плагина. Но есть более простой и более доступный способ – заключается в использовании механизма логгирования Internet Information Services (IIS).
Вот основные шаги, которые нужно выполнить, чтобы использовать полезную информацию из логов IIS:

  1. Конфигурация логгирования IIS так, чтобы получить простой способ доступа к данным лога;
  2. Фильтрация необработанных данные лога, чтобы отсеять не нужные данные;
  3. Задание временных интервалов, чтобы помочь сгруппировать обращение пользователей к CRM системе;
  4. Представление статистической информации в графическом виде (через отчеты).

А теперь подробнее…

Настройка логгирования IIS

Логи IIS позволяют получать информации о каждом web-запросе, отправленному серверу, и это настраивается на уровне веб-сайта. По умолчанию лог пишется в текстовый файл (txt), но IIS может также записывать эту информацию непосредственно в базу данных, что упрощает ее анализ. Для этого необходимо настроить IIS, так, чтобы он писал лог в источник данных SQL Server ODBC.

База данных лога

  1. Откройте Management Studio, щелкните правой кнопкой на Database — New Database…
  2. Задайте имя новой базы (я использовал «IIS»). Напротив поля Owner щелкните на кнопке «…», далее Browse и выберите в качестве владельца базы данных NT AUTHORITYNETWORK SERVICE (т.к. именно под этой учеткой будет коннектится к скулю ODBC источник, который мы создадим позже…);
  3. Теперь нам нужна табличка, в которую будет писаться лог. Товарищи из мелкософта учли это и подготовили для нас соответствующий SQL скрипт. Откройте файл C:\WINDOWS\system32\inetsrv\logtemp.sql в Management Studio и выполните его (не забудьте только подключиться к нашей новоиспеченной базе).



Источник данных ODBC

  1. Перейдите Start — Programs — Administrative Tools — Data Sorces (ODBC);
  2. Вкладка System DNS, кнопель Add;
  3. Сначала надо указать к чему будем коннектится – SQL Server;
  4. Задайте имя нового источника (у мну HTTPLOG) и выберите Ваш SQL сервер (у меня SQL и CRM сервера установлены на одной машине, поэтому я выбрал local);
  5. Установите виндовую аутентификацию (Windows NT authentication);
  6. Ставьте галку Change the default database to и укажите созданную для нужд лога базу;
  7. В следующем окне ничего менять не нужно, жмите Finish и ОК (можете еще и потестировать соединение с базой). Все 🙂 источник данных готов.




Настройка Internet Information Services

  1. Откройте IIS Manager (Start — Programms — Administrative Tools — Internet Information Services (IIS) Manager), раскройте дерево сайтов, правой кнопкой мыши по црмному сайту (по умолчанию Microsoft Dynamics CRM, но у Вас может называться иначе) и Properties;
  2. В ниспадающем списке Active log format выберите ODBC Logging, далее Properties;
  3. Введите имя источника данных ODBC, который создали ранее и имя таблицы (если Вы его не изменили при создании, то inetlog). Логин и пароль задавать не надо, т.к. они будут проигнорированы – у нас установлена виндовая аутентификация. Жмите ОК.


Логгирование настроено! Можете открыть таблицу inetlog в Management Studio и полюбоваться на упорядоченный и систематизированный лог 🙂

Фильтрация данных

Лог IIS содержит записи о каждом запросе переданном веб-сайту. Что включает запросы изображений, таблиц стилей (css) и других поддерживаемых файлов, что может привести к избыточному количеству информации. Например, открытие объекта в CRM приводит до 30 записям в логе. Данные лога также включают записи о запросах асинхронных процессов (таких как CRM Asynchronous Service) к платформе CRM.

Вьюха

Эти посторонние данные могут быть отфильтрованы с помощью представления (вьюхи) SQL. Мы будем использовать следующее представление:

CREATE view [dbo].[IISLogFiltered]
as
SELECT
	ClientHost,
	u.fullname as username,
	LogTime,
	processingtime,
	bytesrecvd,
	bytessent,
	servicestatus,
	operation, target, parameters
FROM
	dbo.inetlog, 
    superfirma_mscrm.dbo.systemuserbase u
WHERE
	(username NOT IN ('-', 'CRMSERVER\CRMTEST$')) AND 
    (parameters <> '-') AND
	(parameters NOT LIKE 'lcid=%') AND
	u.domainname = username collate Cyrillic_General_CI_AI

Не забудьте заменить CRMSERVER\CRMTEST$ учетной записью, используемой Вашим CRM services’ом, а также название основной базы данных CRM в Вашем развертовании.

Этот SQL скрипт берет данные из таблицы лога inetlog (измените название, если оно у Вас отличается) и отфильтровывает:

  • Анонимные запросы;
  • Запросы от служб CRM;
  • Запросы без параметров в строке запроса (или где единственный параметр – lcid), что исключает запросы различных файлов.

Вы можете исследовать данные лога более подробно, и откорректировать фильтр как Вам нужно.


Временные интервалы

Даже с представлением SQL, описанным выше, Вы все еще далеко от получения полезной информации о том, когда пользователи обращаются к CRM. Исходные данные хранят дату и время каждого запроса в одном поле, что поможет преобразовать эти данные в периоды. Мы используем для этих целей отдельную таблицу SQL, содержащую заранее определенные периоды времени, и функцию SQL, которая ставит соответствие между зарегистрированным временем и определенным периодом. Таблица SQL создается следующим скриптом:

CREATE TABLE [dbo].[TimePeriod](
	[PeriodText] [nvarchar](20) NULL,
	[HourStart] [int] NULL,
	[MinuteStart] [int] NULL,
	[TotalMinuteStart] AS ((60)*[HourStart]+[MinuteStart]),
	[TotalMinuteEnd] [int] NULL
)

Заполните ее данными периодов времени, которые Вам необходимы. Например:

PeriodText HourStart MinuteStart TotalMinuteStart TotalMinuteEnd
00:00 — 08:00 0 0 0 480
08:00 — 09:00 8 0 480 540
09:00 — 10:00 9 0 540 600
10:00 — 11:00 10 0 600 660
11:00 — 12:00 11 0 660 720
12:00 — 13:00 12 0 720 780
13:00 — 14:00 13 0 780 840
14:00 — 15:00 14 0 840 900
15:00 — 16:00 15 0 900 960
16:00 — 17:00 16 0 960 1020
17:00 — 18:00 17 0 1020 1080
18:00 — 00:00 18 0 1080 1440

А вот и соответствующий скрипт для ее заполнения:

INSERT INTO TimePeriod VALUES ('00:00 - 08:00',0,0,480)
INSERT INTO TimePeriod VALUES ('08:00 - 09:00',8,0,540)
INSERT INTO TimePeriod VALUES ('09:00 - 10:00',9,0,600)
INSERT INTO TimePeriod VALUES ('10:00 - 11:00',10,0,660)
INSERT INTO TimePeriod VALUES ('11:00 - 12:00',11,0,720)
INSERT INTO TimePeriod VALUES ('12:00 - 13:00',12,0,780)
INSERT INTO TimePeriod VALUES ('13:00 - 14:00',13,0,840)
INSERT INTO TimePeriod VALUES ('14:00 - 15:00',14,0,900)
INSERT INTO TimePeriod VALUES ('15:00 - 16:00',15,0,960)
INSERT INTO TimePeriod VALUES ('16:00 - 17:00',16,0,1020)
INSERT INTO TimePeriod VALUES ('17:00 - 18:00',17,0,1080)
INSERT INTO TimePeriod VALUES ('18:00 - 00:00',18,0,1440)

Мы разобьем день на часовые периоды между 8:00 и 18:00, и оставим один период до 8 часов и один период после 18 часов.


Эта таблица может использоваться для поиска периода вхождения (PeriodText) для зарегистрированного времени (из поля datetime), с помощью следующей функции:

CREATE function GetTimePeriodText (@DateTime datetime) returns nvarchar(20)
as
begin
	declare @tm int, @ret nvarchar(20)
	set @tm = 60 * datepart(hh, @DateTime) + datepart(mi, @DateTime)
	select 
    	@ret = max(PeriodText) 
    from 
    	TimePeriod 
    where 
    	@tm >= TotalMinuteStart and 
    	@tm < TotalMinuteEnd
return @ret
end

Следующее представление SQL применяет эту функцию к вышеупомянутому представлению SQL, используемому для фильтрации. Оно также извлекает название организации CRM из URL.

Create view IISLogFilteredWithPeriods
as
select 
	ClientHost, 
	username, 
	LogTime, 
	processingtime, 
	bytesrecvd, 
	bytessent, 
	servicestatus, 
	operation, 
	target, 
	parameters, 
	dbo.GetTimePeriodText(LogTime) as TimePeriod, 
	convert(nchar(10), LogTime, 103) as DateText, 
	convert(nchar(8), LogTime, 112) as DateYYYYMMDD, 
	case 
		when 
        	charindex('/', target, 2) > 2
		then
    		substring(target, 2, charindex('/', target, 2) - 2) 
    	else 
	    	''
	end as Organization
from
	IISLogFiltered

Заметьте, что лог IIS хранит данные в формате Universal Time (UTC). Вы можете либо изменить данные в таблице TmePeriod, либо логику функции GetTimePeriodText, чтобы конвертировать информацию о временной зоне. Другой, хотя официально неподдерживаемый подход, заключается в использовании функции fn_UTCToLocalTime хранящейся в базе данных MSCRM.


Создание отчета из данных лога

Теперь у нас есть вся необходима инфраструктура, чтобы состряпать отчетик об использовании CRM системы в SQL Server Reporting Services. Мы разместим данные в матричной таблице, с периодами в качестве столбцов. Числа в отчете – это количество запросов, а цвета под цифрами варьируются в зависимости от интенсивность использования CRM.

Запрос SQL, на основе которого строится этот отчет:

select 
	username, 
	TimePeriod, 
	DateText, 
	DateYYYYMMDD, 
	sum(processingtime) as ProcessingTime, 
	count(*) as Requests
from 
	IISLogFilteredWithPeriods
where 
	logtime > dateadd(d, -7, getdate())
group by 
	username, 
	TimePeriod, 
	DateText, 
	DateYYYYMMDD

Отчет можете составить сами или можете скачать готовый отчет прямо тут – usingcrm.zip. Только не забудьте изменить источник данных для набора данных на Ваш SQL Server.


Мысли вслух – улучшаем производительность…

Есть некоторые побочные эффекты, связанный со снижением производительности, при использовании ODBC – интенсивность использования процессора выше, нежели запись лога в файл в неформатированном виде.

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

Вы должны также предусмотреть архивирование и/или удаление старых данных. Один (не в меру 🙂 ) активный пользователь CRM может легко генерировать более, чем мегабайт данных в день, таким образом количество данных может возрасти быстро.

Если уж пишете лог непосредственно в SQL сервер, подумайте об использовании индексов SQL. Добавление некластерного индекса на столбце параметров может помочь с производительностью анализа, но неблагоприятно затронуло бы производительность при записи лога. Кластерный индекс на столбец logtime рекомендуется в любом случаи.

Мысли вслух – улучшаем анализ…

Отчет, описанный здесь, показывает не всю информацию, которую можно вытащить из лога. Дальнейшие идеи по его развитию:

  • Используя параметр processingtime для исследования загрузки IIS’а. Этот параметр показывает время прошедшего с момента поступления запроса и начала его обработки до ответа на него, что напрямую зависит от нагрузки на IIS сервер. Это может быть полезно для идентификации, когда и какие запросы занимали много времени на выполнение.
  • Из параметров запроса можно узнать, какой объект запрашивается — и составить матрицу наиболее используемых объектов.
  • IIS лог не позволяет понять тип операции над данными (создание, обновление, удаление, назначение и т.д.), которая была выполнена. Если Вы нуждаетесь в этой информации, попробуйте воспользоваться механизмом плагинов.
Комментарии (9)
  • Иван 09.05.2009

    Супер. Очень информативная статья. Сильно помогла 🙂 Спасибо.

  • slivka_83 09.05.2009

    Пожалуйста 🙂

  • Азат 09.05.2009

    — Базу данных лога создал
    — Источник данных ODBC создал
    остановился на этапе «Настройка Internet Information Services»

    Ну нету «properties» при нажатии правой кнопки на срмном сайте 🙁 (windows server 2008R2, IIS7)

    При просмотре сайта в режиме просмотра возможностей есть функция «Ведение журнала», также тыкался в другие функции — но как подключить ODBC — хз

    Плиз хелп!

  • Азат 09.05.2009
  • slivka_83 09.05.2009

    Да, Вы правы… настройка ODBC логинга в IIS 7 стала через Ж 🙂

  • Азат 09.05.2009

    За неделю база с логами IIS выросла с 0 до 270 МБ.
    Оказывается, такие темпы роста БД для нас — непозволительная роскошь (особенно принимая во внимание, что в ближайшее время кол-во юзеров увеличится с нынешних 4-5 до 10 человек).
    Помогите сделать следующее:
    1. Настроить IIS на логирование запросов только с определенных IP-адресов (по маске: начинающихся/не начинающихся с определенных цифр)
    2. Настроить в SQL Server 2008 R2 автоматическое удаление из БД логов IIS данных старше 7 дней

  • Азат 09.05.2009

    Срочно нужна помощь (см. пункты 1 и 2 предыдущего комментария)

  • Евгения 09.05.2009

    При записи лога в базу (IIS 7.5) поля processingtime, bytesrecvd, bytessent не заполняются (всегда нулевые).
    А именно эта информация была нужна.
    Кто-нибудь сталкивался с такой проблемой и может подсказать решение?

*

code