La auditoría en Dataverse/Dynamics 365 CE es una de las funcionalidades de mayor utilidad, ya que proporciona un histórico completo sobre los cambios realizados en los registros del sistema para las entidades habilitadas. Sin embargo, el almacenamiento de esta información en Dataverse a largo plazo puede crecer de forma exponencial y llegar a suponer un sobrecoste económico a nivel de almacenamiento. Por ello, puede ser conveniente exportar el histórico de auditoría a una solución de almacenamiento externa en la nube (blob storage, FTP, base de datos SQL…) con objeto de liberar espacio de almacenamiento en la base de datos de Dataverse.
En este artículo, se propone una solución para exportar el histórico de auditoría de Dataverse en una cuenta de almacenamiento de tipo Data Lake Gen2 a través de SQL Server Integration Services (SSIS) y los componentes de KingswaySoft para Dynamics 365, y la explotación de los datos de auditoría alojados en DataLake desde PowerBI.
Requisitos
• Visual Studio 2019/2022 (Edición Professional o Enterprise).
• SQL Server Data Tools (SSDT) para Visual Studio.
• KingswaySoft SSIS Productivity Pack.
• KingswaySoft SSIS Integration Toolkit para Microsoft Dynamics 365.
• Cuenta de almacenamiento en Azure de tipo Data Lake Gen2.
Arquitectura
Como punto de partida, se detalla la arquitectura propuesta para la solución de exportación periódica de los datos de auditoría desde Dataverse a Data Lake.
Habitualmente, los registros de auditoría en Dataverse se crean cada poco tiempo debido a la actividad continua de los usuarios en el sistema y los procesos programados, por lo que se plantea recuperar la información de auditoría de manera incremental y recurrente (por ejemplo, una vez al día), por lo que se hace necesario mantener la fecha correspondiente a la última extracción de datos con objeto de recuperar solamente los nuevos registros creados en el sistema durante las próximas ejecuciones del proceso ETL de exportación.
Para ello, se propone almacenar la fecha de última ejecución para cada una de las entidades auditadas en un fichero CSV alojado en el Data Lake, recuperar este valor durante la ejecución del proceso aplicando los filtros correspondientes como fecha de corte, y actualizar el mismo al final del proceso de cara a las ejecuciones futuras.
En referencia a la estructura de directorios de la cuenta de almacenamiento en Data Lake, se propone la creación de un contenedor raíz específico para alojar la información de auditoría, integrado por una carpeta para cada una de las entidades auditadas en Dataverse y distribuida en subcarpetas en los niveles inferiores de acuerdo con la estructura ‘AÑO / MES / DÍA’. No obstante, esta es solo una sugerencia a nivel de organización dentro de Data Lake, cualquier otra estructura es igualmente válida de acuerdo con los requisitos, las necesidades de desarrollo y la recurrencia en la ejecución del proceso (por ejemplo, si la extracción de la auditoría se realiza de forma mensual o trimestral, se podría crear una carpeta por cada mes o trimestre, respectivamente).
En el caso de uso planteado en el artículo, se implementará un proceso para la extracción diaria de la auditoría de la entidad de Contacto en Dataverse, de manera que se propone la siguiente estructura de directorios en Data Lake:
Definida la arquitectura, se puede proceder a la implementación del proceso ETL para la exportación de los datos de auditoría desde Dataverse a la cuenta de almacenamiento de Data Lake.
Implementación del paquete de integración SSIS
Como se ha mencionado anteriormente, como caso de uso de plantea la implementación de un proceso para la exportación diaria de la auditoría correspondiente a la entidad de contacto en Dataverse mediante la implementación de un paquete de SQL Server Integration Services (SSIS) y la utilización de los componentes de KingswaySoft para la extracción de datos desde Dataverse.
A continuación, se detallan los pasos necesarios para la implementación del proceso ETL.
1. El primer paso es la creación en Visual Studio de un nuevo proyecto de tipo ‘Integration Services Project’.
2. Tras la creación del proyecto, lo primero es recuperar la fecha desde la cual se desean recuperar los datos de auditoría. Para ello, se crear un nuevo fichero delimitado CSV (se puede utilizar cualquier otra extensión) con la siguiente estructura, el cual se almacenará en una ubicación específica de la cuenta de almacenamiento de Data Lake para recuperar y actualizar este valor durante cada una de las ejecuciones del proceso.
Se recomienda utilizar el formato de fecha ‘yyyy-MM-dd HH:mm’ para evitar transformaciones innecesarias de formato en el proceso ETL.
De acuerdo con la estructura de ficheros definida en el apartado anterior, este fichero para el control incremental de la fecha se almacena en el directorio ‘auditcrm/contact/’ de Data Lake.
3. El flujo de control principal se integra por 4 fases, detalladas a continuación.
| Fase | Descripción |
| GetLastExecutionDate | Se recupera la fecha de la última extracción desde el fichero de control incremental de fechas almacenado en Data Lake, y se almacena el valor en una variable global de proceso. |
| SetFilePath | Se genera la ruta de destino en Data Lake (en función de la fecha en la que se ejecuta el proceso) donde se creará el fichero CSV con los datos de auditoría. |
| GetAuditData | Se recuperan desde Dataverse los datos de auditoría desde la última fecha de extracción, se genera el fichero CSV y se deposita en el directorio de Data Lake generado en la fase anterior. |
| UpdateExecutionDate | Se actualiza el fichero de control incremental de fechas con la fecha actual en el momento de la ejecución del proceso. |
FASE 1: GetLastExectionDate
Para la implementación de la primera fase, se requiere crear una variable global en el paquete de integración (LastExecutionDate), de tipo ‘String’ y sin valor inicial asignado, la cual se utilizará para almacenar la fecha de la última extracción de la auditoría desde Data Lake.
A continuación, se crea un flujo de datos para recuperar este valor. Para ello, se recomienda utilizar el componente ‘Premium Flat File’ para la lectura desde el fichero de control incremental de fechas, y el componente ‘Premium Derived Colum’ para actualizar la variable ‘LastExecutionDate’ con la fecha recuperada, ambos disponibles en el Productivity Pack de KingswaySoft.
Lo primero, es crear una conexión con la cuenta de almacenamiento de Data Lake a través del ‘Azure Data Lake Storage Connection Manager’ de KingswaySoft, especificando el nombre de la cuenta de almacenamiento y la clave compartida para la configuración de la conexión.
Una vez creada y validada la conexión, para recuperar la fecha de ejecución del fichero se debe especificar la conexión, la ruta en Data Lake, el formato del fichero y los delimitadores de campo y datos del mismo a través del componente ‘Premium Flat File Source’.
Desde la sección de ‘Columnas’, se añade el mapeo de la columna ‘lastExecutionDate’ del fichero.
Para actualizar la variable ‘lastExecutionDate’ del paquete de integración, se utiliza la siguiente función desde el componente ‘Premium Derived Column’ de KingswaySoft:
WriteValueToVariable(@[User::LastExecutionDate],[lastExecutionDate])
Donde @[User::LastExecutionDate] es la variable global y [lastExecutionDate] la columna de salida del componente anterior con el valor de la fecha de ejecución recuperada desde el fichero CSV.
FASE 2: SetFilePath
Obtenida la última fecha de extracción de la auditoría, en la siguiente fase se genera el directorio en Data Lake donde se va a crear el fichero CSV correspondiente a la ejecución actual del proceso. Para almacenar el valor de la ruta generada, se hace necesario crear una variable adicional en el paquete de integración (Path), de tipo String y con valor inicial «» asignado.
En este caso, como se ha mencionado anteriormente en el artículo se mantienen una estructura de directorios «/auditcrm/contact/AÑO/MES/DÍA/contact_HHmmss.csv«, por lo que se puede utilizar la siguiente expresión para generar la ruta y actualizar la variable ‘Path’, a través del componente ‘Premium Expression Task’ de KingswaySoft, directamente sobre el flujo de control principal:
WriteValueToVariable(@[User::Path] ,«/auditcrm/contact/»+YEAR(GETDATE())+«/»+MONTH(GETDATE())+«/»+DAY(GETDATE())+«/contact»+«_»+DATEPART(«Hh», GETDATE())+DATEPART(«Mi», GETDATE())+DATEPART(«Ss», GETDATE())+«.csv»)
FASE 3: GetAuditData
La tercera fase corresponde al flujo de datos principal del proceso ETL, en el cual se extraen los datos de auditoría desde Dataverse a partir de la última fecha de ejecución y se genera el fichero CSV almacenado en Data Lake.
Para la extracción de datos desde Dataverse, se utiliza el componente ‘CDS/CRM Source’ de KingswaySoft (es necesario crear previamente una conexión contra la instancia de Dataverse desde la cual se desea extraer la auditoría a través del administrador de conexiones).
Una vez creada la conexión, se debe marcar la opción ‘AuditLogs’ del componente, y a continuación introducir una consulta en formato FetchXML correspondiente a la entidad auditada (en este caso contacto). Se pueden aplicar en la consulta filtros adicionales si se desea, pero para recuperar únicamente los registros de auditoría generados desde la última ejecución, es necesario añadir al menos la condición marcada en amarillo, donde @[User::LastExecutionDate] corresponde a la variable global del paquete de integración en la cual se ha almacenado la fecha de la última extracción en la fase inicial del proceso.
Cuando se selecciona la opción ‘AuditLogs’, se habilitan diferentes salidas en el componente, en el siguiente enlace de la documentación de KingswaySoft se ofrece más información sobre todas las opciones que se pueden utilizar para trabajar con la auditoría.
Extracting Audit Logs for Multiple CRM Entities (kingswaysoft.com)
Para recuperar los datos correspondientes al historial de modificaciones realizadas sobre los registros en Dataverse, se utilizan las siguientes salidas:
- Primary Output: Contiene los datos principales sobre cada entrada del historial de auditoría, como el registro al que corresponde, el usuario que ha realizado los cambios, la fecha y el tipo de acción realizada sobre el registro.
- Audit Details (Attribute Changes): En esta salida se incluye el registro primario de auditoría relacionado y los valores nuevos y anteriores de los campos modificados.
Cada registro primario de auditoría contiene un identificador único (auditid), el cual se incluye también en todas las salidas secundarias del componente para relacionarlas.
Por tanto, es necesario cruzar la salida primaria con los cambios en los atributos para obtener la traza completa de la auditoría para cada registro modificado en la misma línea. Para ello, se ordenan ambas salidas por el campo ‘auditid’ en sentido ascendente y posteriormente se realiza un cruce de tipo ‘INNER JOIN’ (utilizando el campo ‘auditid’ como clave relacionada) a través del componente ‘Combinación de mezcla’ de SSIS, como se indica a continuación:
Para cada una de las salidas relacionadas, se seleccionan los siguientes atributos:
| Salida | Atributo | Descripción |
| Primary Output | actionname | Nombre de la acción realizada sobre el registro auditado. |
| Primary Output | createdon | Fecha de creación de la entrada en el historial de auditoría. |
| Primary Output | objectid | GUID del registro modificado. |
| Primary Output | objectidname | Campo principal (normalmente nombre) del registro modificado. |
| Primary Output | userid | GUID del usuario que realiza la modificación. |
| Primary Output | useridname | Nombre del usuario que realiza la modificación. |
| Attribute Changes | fieldname | Nombre del campo modificado. |
| Attribute Changes | oldvalue | Valor anterior. |
| Attribute Changes | oldvalue_label | Valor anterior (formateado). |
| Attribute Changes | newvalue | Nuevo valor. |
| Attribute Changes | newvalue_label | Nuevo valor (formateado). |
De este modo, cada una de las líneas obtenidas de detalle de auditoría como salida de la combinación de mezcla estará conformada por las columnas indicadas en la tabla anterior.
Antes de la escritura del fichero CSV con los datos de auditoría recuperados en el Data Lake, se recomienda aplicar un filtro adicional sobre la fecha de creación de las entradas de auditoría con respecto a la fecha de última extracción, con objeto de evitar duplicidades en la información almacenada en Data Lake. Para aplicar el filtro, se utiliza el componente ‘División condicional’ de SSIS, desde el cual se crea una nueva salida sobre los registros a considerar aplicando la siguiente condición (donde @[User::LastExecutionDate] corresponde a la variable en la que se almacena la fecha de última extracción):
createdon > (DT_DBTIMESTAMP)@[User::LastExecutionDate]
Finalmente, se procede a la escritura del fichero CSV para los datos de auditoría recuperados y a su almacenamiento en el directorio correspondiente (generado previamente) de la cuenta de almacenamiento de Data Lake. Esta operación se puede realizar a través del componente ‘Premium Flat File Destination’ de KingswaySoft, especificando la conexión a Data Lake, el directorio de destino (almacenado previamente en la variable @[User::Path] del paquete de integración, el formato del fichero y los delimitadores de campo y datos).
Para establecer el directorio de destino se puede utilizar una expresión sobre la propiedad ‘DestinationFilePath‘, especificando el valor almacenado la variable ‘Path’ durante la segunda fase del proceso.
Por otro lado, es necesario especificar el mapeo de campos correspondiente al fichero CSV a generar desde la sección ‘Columnas’ del componente.
Siguiendo todos estos pasos, queda implementada la lógica principal del proceso ETL para la recuperación de los datos de auditoría desde Dataverse y la exportación de los mismos a Data Lake.
FASE 4: UpdateExecutionDate
En la última fase del proceso, se actualiza la fecha actual en el momento de la ejecución del proceso sobre el fichero de control incremental de fechas, con objeto de garantizar a extracción de datos incremental durante cada ejecución y evitar la duplicidad de datos en Data Lake. Para ello, se crea un flujo de datos adicional integrador por los siguientes componentes.
Se genera una entrada de datos auxiliar mediante la utilización del componente ‘Data Spawner’ de KingswaySoft.
A continuación, se obtiene la fecha actual con formato ‘yyyy-MM-dd HH:MM’ utilizando la siguiente expresión a través del componente ‘Premium Derived Column’ de KingswaySoft:
(DT_WSTR, 4)YEAR(GETDATE()) + «-» + RIGHT(«0» + (DT_WSTR, 2)MONTH(GETDATE()), 2) + «-» + RIGHT(«0″ + (DT_WSTR, 2)DAY(GETDATE()), 2) + » » + RIGHT(«0» + (DT_WSTR, 2)DATEPART(«HOUR», GETDATE()), 2) + «:» + RIGHT(«0» + (DT_WSTR, 2)DATEPART(«MINUTE», GETDATE()), 2)
Por último, se actualiza el fichero CSV de control incremental de fechas almacenado en Data Lake con la fecha actual obtenida a través del componente ‘Premium Flat File Destination’ de KingswaySoft, especificando el directorio de destino donde se encuentra almacenado el fichero de control, el formato, delimitador de campo y de datos.
Desde la sección ‘Columnas’ del componente, se deber especificar el mapeo de la columna de entrada procedente del componente anterior (la cual contiene la fecha actual obtenida) con la única columna del fichero CSV de control incremental de fechas.
4. Tras la implementación de las 4 fases detalladas anteriormente, se podría proceder a la ejecución del proceso completo, el cual debería recuperar durante cada ejecución los datos del historial de auditoría de Dataverse generados desde la última extracción y almacenarlos en Data Lake de acuerdo con la estructura de ficheros y directorios que se haya definido.
Una vez que verifiquemos el correcto funcionamiento del proceso y que los datos de auditoría se exportan correctamente, se podría proceder a eliminar los registros de auditoría antiguos en Dataverse para liberar espacio en la base de datos.
NOTA: En el caso de que se desee desplegar y automatizar la ejecución del paquete de integración desde un servidor SQL, se requiere disponer de una licencia de KingswaySoft en vigor instalada en el mismo.
Reporte de auditoría en PowerBI
Una forma habitual de explotar y analizar los datos almacenados en el Data Lake es a través de un reporte en PowerBI. En esta última sección, vamos a ver cómo realizar la conexión con la cuenta de almacenamiento en Data Lake y elaborar un informe sencillo para consultar la información de auditoría de un entorno de Dataverse de forma externa.
- Desde la herramienta PowerBI Desktop, seleccionar la opción » desde el menú ‘Obtener datos’.
- Introducir la dirección URL (DFS) del contenedor de auditoría en Data Lake. la cual tiene el siguiente formato (donde ‘XXXXX’ es el nombre de la cuenta de almacenamiento y ‘YYYYY’ el nombre del contenedor de auditoría):
https://XXXXX.dfs.core.windows.net/YYYYY
Se solicitarán las credenciales de Azure o la clave compartida de la cuenta de almacenamiento para realizar la autenticación.
- En la siguiente pantalla, pinchar en ‘Transformar datos‘, ya que son necesarios algunos ajustes previos a la carga de datos en el reporte.
- Desde el editor de Power Query, en primer lugar, se de deben excluir los ficheros almacenados en la cuenta de almacenamiento que no contienen los datos de auditoría (por ejemplo, los ficheros de control incremental de fechas), para lo cual se puede aplicar el filtro ‘Comienza por…’ y el nombre de la entidad auditada como valor sobre el campo ‘Name’.
- A continuación, pinchar en el botón ‘Combinar archivos’, ubicado en la parte derecha del encabezado de la columna ‘Content’.
6. En el diálogo ‘Combinar archivos’, pinchar en Aceptar y proceder a la carga de datos en el reporte pinchando en el botón ‘Cerrar y aplicar’ situado al inicio de la cinta de opciones
- Una vez completada la carga de datos, se podría agregar, por ejemplo, una visualización de tipo tabla en el área de trabajo y seleccionar las columnas necesarias, aplicar filtros, añadir segmentaciones, gráficos, etc. de acuerdo con las necesidades del informe.
NOTA: Si se desea publicar el informe en el servicio de PowerBI, es necesario disponer de una licencia de PowerBI y los privilegios necesarios asignados.
Como conclusión final, a lo largo del artículo se ha visto paso a paso como implementar una solución para exportar los datos de las entidades habilitadas para el registro del historial de auditoría de un entorno de Dataverse en una cuenta de almacenamiento de Data Lake mediante SQL Server Integration Services (SSIS) y los componentes de KingswaySoft, y cómo explotar esta información desde un informe de Power BI, lo cual nos permite liberar espacio de en la base de datos de Dataverse y reducir costes en el almacenamiento.
Espero que la solución os haya resultado de utilidad.
About the Author: Alberto Cotelo
