Отчеты
21
Сен
13

Отчет по аудиту

CRM 2011 позволяет смотреть логи аудита через два интерфейса – связанное Представление для какого-либо объекта и Сводное представление аудита. Ни то ни другое не позволяет распечатать эти логи, что печалит некоторых начальников 🙂 Чтобы компенсировать этот недостаток нарисуем отчет, который выводит почти все то же самое что и обозначенных выше два инструмента.

Проблем начинаются, когда смотришь на таблицу аудита в БД CRM – данные в ней храниться в ненормализованном виде. Таблица аудита содержит в двух полях коды полей и измененные значения, перечисленные через запятую. Поэтому нам нужно:

  1. «Разбить» одну строку на столько, сколько полей в ней перечислено. И вывести каждое поле отдельной строкой;
  2. Для каждого поля в «распарсенной» таблицы найти, его более новое значение. Т.е. то значение, на которое оно был изменено;
  3. И последнее: в таблице аудита хранятся только исторически данные, поэтому нам нужно будет для последних строк аудит по каждому полю подтянуть текущие данные.

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

  • Процедура AuditCRM:
    USE [superfirma_MSCRM]
    GO
    /****** Object:  StoredProcedure [dbo].[AuditCRM]    Script Date: 09/20/2012 23:03:43 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    /*
    	Создаем процедуру, которая будет выдавать данные по аудиту.
    	Принимает четыре параметра:
    	 - GUID объекта
    	 - GUID пользователя
    	 - Временной интервал между которыми необходимо искать данные по аудиту
    */
    ALTER PROCEDURE [dbo].[AuditCRM]
    	@obj NVARCHAR(50),
    	@usr NVARCHAR(50),
    	@start datetime,
    	@end datetime
    AS
    BEGIN
    	
    	IF @usr = 'vse' SET @usr = '%'
    	
    	-- Временная таблица для хранения итоговых данных по аудиту	
    	CREATE TABLE #tlog ( 
    		Fields NVARCHAR(100),
    		oldValue NVARCHAR(500),
    		newValue NVARCHAR(500),
    		RecordId NVARCHAR(100),
    		ObjectName NVARCHAR(100),
    		UserIdName NVARCHAR(100),
    		UserId NVARCHAR(100),
    		DomainName NVARCHAR(100),
    		CreatedOn NVARCHAR(100)
    	)
    
    	-- Курсор для выбора исходных данных по аудиту
    	DECLARE log_cursor CURSOR
    	FOR
    		SELECT
    			SUBSTRING(A.AttributeMask,2,LEN(A.AttributeMask)-2) Fields,	-- Обрезаем лишние запятые по краям
    			A.ChangeData,
    			A.ObjectId RecordId,
    			E.Name ObjectName,
    			E.ObjectTypeCode ObjectCode,
    			A.UserIdName,
    			A.UserId,
    			U.DomainName,
    			A.CreatedOn
    		FROM
    			Audit A
    				INNER JOIN EntityView E 
    					ON A.ObjectTypeCode = E.ObjectTypeCode
    				INNER JOIN SystemUser U 
    					ON A.UserId = U.SystemUserId
    		WHERE
    			1=1
    			AND A.Action = '2' -- Изменения записей
    			AND A.ObjectTypeCode = @obj
    			AND A.UserId LIKE @Usr
    			AND (A.CreatedOn between @start AND @end)
    		ORDER BY A.CreatedOn DESC
    
    	-- Открываем курсор
    	OPEN log_cursor
    		DECLARE
    			@Fields VARCHAR(100),
    			@oldValue VARCHAR(500),
    			@RecordId VARCHAR(100),
    			@ObjectName VARCHAR(100),
    			@ObjectCode VARCHAR(100),
    			@UserIdName VARCHAR(100),
    			@UserId VARCHAR(100),
    			@DomainName VARCHAR(100),
    			@CreatedOn DATETIME,
    			@IP VARCHAR(100)
    
    		FETCH NEXT FROM log_cursor INTO @Fields, @oldValue, @RecordId, @ObjectName, @ObjectCode, @UserIdName, @UserId, @DomainName, @CreatedOn
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    			-- Добавляем во временную таблицу "распарсенные" строки аудита
    			INSERT INTO
    				#tlog
    			SELECT
    				sitem Field,
    				yitem oldValue,
    				[superfirma_MSCRM].[dbo].[fnNewValue](@RecordId, @ObjectCode, @CreatedOn, zitem) newValue, -- Функция для каждого поля вытаскивает значение, на котороео оно было обнолвено
    				@RecordId RecordId,
    				@ObjectName ObjectName,
    				@UserIdName UserIdName,
    				@UserId UserId,
    				@DomainName UserDomainName,
    				@CreatedOn CreatedOn
    			FROM
    				dbo.fnSplit(@Fields, @oldValue, @ObjectCode)-- Функция рабивает строку на количество полей в ней
    
    		FETCH NEXT FROM log_cursor INTO @Fields, @oldValue, @RecordId, @ObjectName, @ObjectCode, @UserIdName, @UserId, @DomainName, @CreatedOn
    	END
    	
    	-- Закрываем курсор
    	CLOSE log_cursor
    	DEALLOCATE log_cursor
    	
    	-- Курсор для вставки в таблицу текущего значения
    	DECLARE setCurrentValue CURSOR
    	FOR
    		SELECT
    			Fields,
    			newValue,		
    			RecordId,
    			ObjectName,
    			CreatedOn
    		FROM
    			#tlog
    	
    	OPEN setCurrentValue
    		DECLARE
    			@Field NVARCHAR(100),
    			@new NVARCHAR(500),
    			@Id NVARCHAR(100),
    			@Object NVARCHAR(100),
    			@Date NVARCHAR(100)
    
    		FETCH NEXT FROM setCurrentValue INTO @Field, @new, @Id, @Object, @Date
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    		
    			DECLARE @curValue NVARCHAR(500)
    			CREATE TABLE #tcurValue (curValue NVARCHAR(500))
    			
    			IF @new is NULL
    			BEGIN
    				INSERT #tcurValue EXEC curValue @Object, @Field, @Id
    				SELECT TOP 1 @curValue = curValue FROM #tcurValue
    				
    				UPDATE #tlog
    				SET newValue = @curValue
    				WHERE
    					1=1
    					AND RecordId = @Id
    					AND Fields = @Field
    					AND CreatedOn = @Date
    			END
    			
    			DROP TABLE #tcurValue
    
    		FETCH NEXT FROM setCurrentValue INTO @Field, @new, @Id, @Object, @Date
    	END
    		
    	CLOSE setCurrentValue
    	DEALLOCATE setCurrentValue
    
    	-- Выбираем все данные из временной таблицы
    	SELECT * FROM #tlog
    
    	-- Удаление временной таблицы
    	DROP TABLE #tlog
    
    END
    

    Это основная процедура, из которой мы будем брать данные. Она состоит из:

    • Временной таблицы #tlog, которая хранит данные пока мы с ними производим манипуляции;
    • Курсора log_cursor, который производит начальный отбор данных из таблицы аудита, а также подтягивает мена пользователей и объектов. При просмотре каждой записи в этом курсе, происходит распарсивание (с помщью функции fnSplit) строк аудита на количество полей, задействованных в этой строке. Для каждой распарсенной строки с помощью функции newValue подтягивается, то значение на которое оно было заменено;
    • o Курсор setCurrentValue, возвращает и подставляет последнее (текущее) значение во временную таблицу.

    Процедура принимает четыре параметра:

    • o GUID объекта, аудит которого нас интересует;
    • o GUID пользователя, внесшего изменения, или строку «vse», что означает, что будут отобраны изменения всех пользователей;
    • o Временной интервал, в промежутке которого следует искать изменения.
  • Функция fnSplit:
    USE [superfirma_MSCRM]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER FUNCTION [dbo].[fnSplit](
    	@sInputList NVARCHAR(4000),
    	@yInputList NVARCHAR(4000),
    	@Object NVARCHAR(5)
    	) RETURNS @List	TABLE (sItem NVARCHAR(4000), yItem NVARCHAR(4000), zItem NVARCHAR(100))
    BEGIN
    	DECLARE @sItem NVARCHAR(4000)
    	DECLARE @yItem NVARCHAR(4000)
    	DECLARE @zItem VARCHAR(100)
    	
    	WHILE CHARINDEX(',',@sInputList,0) <> 0
    	BEGIN
    		SELECT
    			@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(',',@sInputList,0)-1))),
    			@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(',',@sInputList,0)+1,LEN(@sInputList)))),
    			@yItem=RTRIM(LTRIM(SUBSTRING(@yInputList,1,CHARINDEX('~',@yInputList,0)-1))),
    			@yInputList=RTRIM(LTRIM(SUBSTRING(@yInputList,CHARINDEX('~',@yInputList,0)+1,LEN(@yInputList))))
    		
    		IF LEN(@sItem) > 0
    		BEGIN
    	
    			SET @zItem = (			
    				SELECT
    					A.Name
    				FROM
    					AttributeView A
    						INNER JOIN dbo.EntityView E 
    							ON A.EntityID = E.EntityID 
    				WHERE
    					1=1
    					AND A.ColumnNumber = @sItem
    					AND E.ObjectTypeCode = @Object
    			)
    			INSERT INTO @List SELECT @zItem, @yItem, @sItem
    		END			
    	END
    	
    	IF LEN(@sInputList) > 0
    	BEGIN
    		SET @zItem = (			
    			SELECT
    				A.Name
    			FROM
    				AttributeView A
    					INNER JOIN dbo.EntityView E 
    						ON A.EntityID = E.EntityID 
    			WHERE
    				1=1
    				AND A.ColumnNumber = @sInputList
    				AND E.ObjectTypeCode = @Object
    		)
    		INSERT INTO @List SELECT @zItem, @yInputList, @sInputList
    	END
    	
    	RETURN
    END
    

    Функция напрямую не вызывается. Используется для разбиение строк аудита по полям;

  • Функция fnNewValue:
    USE [superfirma_MSCRM]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER FUNCTION [dbo].[fnNewValue]
    (
    	@RecordId VARCHAR(50),
    	@ObjectTypeCode VARCHAR(50),
    	@StartDate DATETIME,
    	@Field VARCHAR(10)
    )
    RETURNS VARCHAR(500)
    AS
    BEGIN
    	DECLARE @NewValue NVARCHAR(500)
    	DECLARE @AttributeMask NVARCHAR(4000)
    	DECLARE @ChangeData NVARCHAR(4000)
    
    	SELECT TOP 1
    		@AttributeMask = SUBSTRING(a.AttributeMask,2,LEN(a.AttributeMask)-2),
    		@ChangeData = a.ChangeData
    	FROM
    		Audit a
    	WHERE
    		1=1
    		AND a.AttributeMask LIKE '%' + @Field + '%'
    		AND a.Action = '2'
    		AND a.ObjectTypeCode = @ObjectTypeCode
    		AND a.CreatedOn > @StartDate
    		AND a.ObjectId = @RecordId
    	
    	SELECT @NewValue = (
    		SELECT
    			yitem
    		FROM
    			[superfirma_MSCRM].[dbo].[fnSplit] (@AttributeMask, @ChangeData, @ObjectTypeCode)
    		WHERE
    			zitem = @Field
    	)
    
    	RETURN @NewValue
    
    END
    

    Функция для каждой строки аудита с отдельным полем, подыскивает его более новое значение в этой же таблице аудита;

  • Процедура curValue:
    USE [superfirma_MSCRM]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[curValue]
    	@Table NVARCHAR(100),
    	@Field NVARCHAR(100),
    	@RecordId NVARCHAR(100)
    AS
    BEGIN
    	DECLARE @req NVARCHAR(4000) = 'USE superfirma_MSCRM; SELECT ' + @Field + ' FROM ' + @Table + ' WHERE ' + @Table + 'id = ''' + @RecordId + ''''
    	EXEC (@req)
    END
    

    Используется для отбора текущего значения для каждого поля.



Бэкграунд готов. Перейдем к созданию отчета:

  • Создайте новый проект отчета в VS 2008;
  • Добавьте в проект два DataSet’а (оба датасета будут использоваться для заполнения параметров):
    • Объекты:
      SELECT 
      	e.ObjectTypeCode,
      	l.Label
      FROM
      	EntityAsIfPublishedView e
      		LEFT JOIN LocalizedLabelLogicalView l
      			on l.ObjectId = e.EntityId
      WHERE
      	1=1
      	AND l.ObjectColumnName = 'LocalizedName'
      ORDER BY
      	l.Label
      

      Запрос отбирает коды и названия объектов;

    • Пользователи:
      SELECT
      	'ВСЕ' FullName,
      	'vse' SystemUserId
      UNION ALL
      SELECT
      	FullName,
      	CAST(SystemUserId AS NVARCHAR(36))
      FROM
      	SystemUser
      

      Отбирает имена всех пользователей и их GUID’ы, а также ключевое слово «vse», которое используется, чтобы отобрать изменения внесенные всеми пользователями;


  • Добавьте в отчет четыре параметра:
    Имя Приглашение Тип Дополнительно
    obj Объект Text Доступные значения из запроса:

    • DataSet – Объекты;
    • Value – ObjectTypeCode;
    • Label – Label.
    usr Пользователь Text
    • DataSet – Пользователи;
    • Value – SystemUserId;
    • Label – FullName.

    Значение по умолчанию: «vse»

    start Начало Date/Time Значение по умолчанию (начало месяца):

    =DateAdd("d",-Day(Now())+1,Now())
    end Окончание Date/Time Значение по умолчанию (конец месяца):

    =DateAdd("m",1,DateAdd("d",-Day(Now()),Now()))





  • Добавьте в отчет еще один DataSet (с именем Аудит), который отбирает данные из процедуры AuditCRM. На вкладке Parameters дата сета, сопоставьте параметры отчета и входные параметры процедуры:
    • @obj = [@obj]
    • @usr = [@usr]
    • @start = [@start]
    • @end = [@end]
  • Добавьте в отчет таблицу и разбередите в ней следующие поля из Dataset’а Аудит:
    • Поле – [Fields]
    • Новое значение – [newValue]
    • Старое значение – [oldValue]
    • Объект – [ObjectName]
    • Пользователь – [UserIdName]
    • Дата – [CreatedOn]


  • Для полей Объект и Пользователь добавьте ссылки, для быстрого перехода к соответствующим записям. Для этого щелкните по ним правой кнопкой мыши – Text Box Properties – Actions – в поле Select URL задайте функцию (тока измените название организации на свое):
    • Объект:
      ="http://crm2011:5555/superfirma/main.aspx?etn=" & LCase(First(Fields!ObjectName.Value, "Аудит")) & "&pagetype=entityrecord&id={" & First(Fields!RecordId.Value, "Аудит") & "}"
    • Пользователь:
      ="http://crm2011:5555/superfirma/main.aspx?etn=systemuser&pagetype=entityrecord&id={" & First(Fields!UserId.Value, "Аудит") & "}"



На этом все. Грузим отчет в CRM.


З.Ы. Вам также, скорее всего, потребуется выдать права на созданные SQL процедуры и функции учетке, под которой работает пул приложений CRM.

Комментарии (13)
  • Алексей 21.09.2012

    1. Большое Вам спасибо!

    2. А можно как-то сделать, что бы вместо имени сущности в колонке «Объект», было значение поля «Name»? Кажется не оч. тривиальной задачей, т.к. они раскиданы по разным таблицам..

  • slivka_83 21.09.2012

    1. Пжлст. 🙂
    2. А как же Вы поймете в какой сущности произвели изменения?

  • Алексей 21.09.2012

    Если я правильно понял вопрос, то эта информация у нас есть в табличке аудита:

    Например берем оттуда

    ObjectId
    061F54AE-D70E-E211-874F-7071BC326C77

    ObjectTypeCode
    2

    и открываем измененную запись контакта:

    http://crm2011:5555/superfirma/main.aspx?etc=2&pagetype=entityrecord&id={061F54AE-D70E-E211-874F-7071BC326C77}

    а вот как в отчете для каждую запись в табличке аудита «джойнить» с _соответствующей_ табличкой исходя из идентификатора и кода сущности… возможно ли такое?

  • slivka_83 21.09.2012

    Если Вам все также нужен основной атрибут сущности. Я бы нарисовал отдельную функцию, которая бы его возвращала. На входе получала бы код объекта и его GUID. Выясняла бы имя объекта и название его основного атрибута и динамически формировала соответствующий Select.

  • Алексей 21.09.2012

    Ага, я именно это имел в виду, спасибо!

  • Анатолий 21.09.2012

    Есть большое желание почистить таблицу аудита.
    Сейчас 41 млн записей. Написал скрипты, ниже. Но как-то стремно))! Может crm подписан на события добавления в аудит и делает что-то хитрое.

    —первое, копируем всю таблюицу аудит
    select * FROM dbo.AuditBase
    into audit_archive as aa

    —второе чистим таблицу аудит по условию
    delete from dbo.AuditBase
    where
    ObjectTypeCode =2
    and CreatedOn < '20121021'

    —Если нужно вернуть данные пишем
    INSERT INTO dbo.AuditBase
    (CallingUserId,
    UserId,
    CreatedOn,
    TransactionId,
    ChangeData,
    Action,
    Operation,
    ObjectId,
    AuditId,
    AttributeMask,
    ObjectTypeCode,
    ObjectIdName)
    select
    CallingUserId,
    UserId,
    CreatedOn,
    TransactionId,
    ChangeData,
    Action,
    Operation,
    ObjectId,
    AuditId,
    AttributeMask,
    ObjectTypeCode,
    ObjectIdName
    FROM
    audit_archive as aa
    where
    aa.ObjectTypeCode =2
    and aa.CreatedOn < '20121021'

    —если нужно посмотреть ObjectTypeCode отдельные сущности ниже
    SELECT e.Name, e.ObjectTypeCode from MetadataSchema.Entity as E
    order by Name

  • slivka_83 21.09.2012

    А зачем Вам скрипты? Данные аудита можно через интерфейс удалять пачками 🙂

  • Анатолий 21.09.2012

    эээ батенька, а если заказчик хочет иметь возможность все же посмотреть что было ранее.
    Пусть с запросом к нам, но посмотреть..
    Если начисто вычистить, то уже точно не посмотришь

  • slivka_83 21.09.2012

    Можно попробовать, только я не знаю к каким результатам приведет прямое удаление записей из БД. Также можете посмотреть профайлером, какой SQL-запрос выполняется при вызове удаления пакета аудита из интерфейса.

  • Ярослав 21.09.2012

    Хороший отчет. Но возникла ошибка, когда загружаю его в CRM, а затем пытаюсь выполнить, при этом в Prewiew и если загрузить на Repor Server, или загружать по ссылке с Repor Server отчет работает нормально.

    «Ошибка при создании отчета
    Не удается отобразить отчет. (rsProcessingAborted)»

    Подскажите пожалуйста, как включить подробное логироване и выявить причину ошибки?

    И В процедуру AuditCRM пришлось внести изменения:
    1) Увеличить длину полей временной таблицы, например oldValue NVARCHAR(500), => oldValue NVARCHAR(2000), иначе значения не помещались и выдавали ошибку.
    2) Добавить условие A.AttributeMask », иначе сыпала ошибки SUBSTRING(A.AttributeMask,2,LEN(A.AttributeMask)-2)

  • Ярослав 21.09.2012

    Немного съелось движком блога:
    A.AttributeMask не равно пустой строке.

    А еще «права учетке, под которой работает пул»,
    В IIS пул работает под Network Service, права выданы.
    А а Report Services под учеткой админа SQL.

  • slivka_83 21.09.2012

    вот так включить ошибки в отчетах: http://msdn.microsoft.com/en-us/library/aa337165.aspx

  • Ярослав 21.09.2012

    Ага, спасибо, через пару часов тоже самое откопал
    http://technet.microsoft.com/en-us/library/aa337165.aspx

    Поймал, была ошибка по правам, могу сказать что нудно дать DataReader к Org_MSCRM для учетки на которой работает Repot Services, у меня это domain\SQLadmin. Так же это решает и проблему с другими некоторыми отчетами. Учетка пула Network Service не пределах.

    По этому отчету, еще исправил ошибки в SQL:
    Исправил запрос по действиям, Task, Appointment, PhoneCall… В звонках кстати, еще и проблема с полями from, to. Правим процедуру curValue.
    Убрал дублированные строки от базовой валюты. Правим функцию fnSplit.
    Перевел поля на русский, написал еще одну функцию.

    В Репорт Билдере:
    Еще замечено, ссылка на Объект НЕ работает, надо убрать один First, а я вообще заменил ссылки на …CRMReports/viewer/drillopen…, вроде так феншуйнее.
    Еще что бы время привести к божескому виду использовал CDATE() и заданный формат в настройках ячейки.

    Еще замечена особенность, picklist и lookup вылазят в отчет как коды. Пока оставил так.
    А вообще отчет хорош, очень мощная штука.

*

code