Audit Log for Analysis Services

The SQL Server Database Engine has the feature Login Auditing, that can create a simple audit log. This log contains among other things service start and stop and logins.
I was approached with a similar requirement on SQL Server Analysis Services (SSAS), but this does not have something like Login Auditing.
SSAS does have tracing features, and can be monitored with Extended Events. The documentation is mostly written for the Database Engine, but with some trial-and-error it can be implemented on SSAS with a XMLA statement.

The documentation from Microsoft is not good, but I found a great blogpost with a working example (http://byobi.com/blog/2013/06/extended-events-for-analysis-services/). To this example I have added some features to meet requirements.

Auto Restart

Auto Restart is required to ensure that the trace is running even after a restart of the SSAS service or the server. This is implemented by adding a AutoRestart element to the XMLA statement.

Limited number of tracefiles

To ensure that the tracing does not run wild, we decided to limit the size of the logfiles and the number of logfiles. This is by the configuration of the Event File Target.
The size of each logfile is defined with the option "max_file_size" with a value i MBs (MiBs).
The number of logfiles is defined by the option "max_rollover_files".

Implementation

The XMLA statement to implement the Audit Log:
<Create
  xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
  xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
  xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
  xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
  xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
  <ObjectDefinition>
    <Trace>
      <ID>SQLAdmin_Audit</ID>
      <Name>SQLAdmin_Audit_2014-W02</Name>
      <AutoRestart>true</AutoRestart>

      <ddl300_300:XEvent>
        <event_session name="xeas"
          dispatchLatency="1"
          maxEventSize="4"
          maxMemory="4"
          memoryPartitionMode="none"
          eventRetentionMode="allowSingleEventLoss"
          trackCausality="true">

        <event package="AS" name="AuditLogin" />
        <event package="AS" name="AuditLogout" />

        <event package="AS" name="ExistingConnection" />
        <event package="AS" name="ExistingSession" />
        <event package="AS" name="SessionInitialize" />

        <target package="Package0" name="event_file">
          <parameter name="filename" value="L:\MSSQL_AS_Log\SQLAdmin_Audit.xel" />
          <parameter name="max_file_size" value="1024" />
          <parameter name="max_rollover_files" value="3" />
        </target>
      </event_session>
    </ddl300_300:XEvent>
    </Trace>
  </ObjectDefinition>
</Create>


Operation

This DMX statement shows what traces that are running:
SELECT *
FROM [$System].[DISCOVER_TRACES];

This XMLA statement deletes the trace. The logfiles (.xel) will not be deleted.
<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <TraceID>SQLAdmin_Audit</TraceID>
  </Object>
</Delete>


A logfile can be browsed with SQL Server Management Studio. The file can be accessed on a UNC path.
The original blogentry mentions that the logfile can be read with the T-SQL function sys.fn_xe_file_target_read_file, but this is a Database Engine function.
To use this function to get the contents of the logfile – in XML – the file must be moved or copied to a location where a Database Engine can acces the file.