How to find out who is connected to my cube

A few days ago, to prepare a potentially difficult meeting with some internal clients, I needed more insights about the users of one the cube … In other words: who is connected and do they connect daily or weekly (or less frequently).

students group raise hands up in classroom

You need another solution to know who is connected to your cube!

First issue: some people are consuming data from this cube through reports or applications. These reports and applications use impersonation of a generic account to query the cube. Hopefully, these kind of connections were out-of-scope for my study. I just wanted to intercept the connections from Excel or SSMS and they are not impersonated.

You’ve many solutions to intercept the different connections to a cubes, but one of the most elegant (and not well-known) ways is with the Extended Events of SSAS. You’ll need to request Analysis Service to intercept one or more events and redirect them to a target.

The definition of the trace is not so complex and you can easily code it with an XMLA command if you’ve a template. The first point is to give an identification to the trace and specify that this trace must be restarted in case of restart of the SSAS service (attribute AutoRestart set to true).

<Create>
   <ObjectDefinition>
      <Trace>
         <ID>SQLAdmin_Audit</ID>
         <Name>SQLAdmin_Audit</Name>
         <AutoRestart>true</AutoRestart>
         
      </Trace>
   </ObjectDefinition>
</Create>

In order to limit the effect of this trace on server performances and to be able to read the content a few days later, I decided to persist the trace on a file. I configured the trace’s destination on a different disk that my cube (attribute filename). To avoid any disk space issue, I configured a roll-over files strategy and limit them to 3 occurrences (parameter named max_rollover_files) of 1Gb (parameter max_file_size expressed in Mb) in the xml element target.

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

The you’ll need to define which events are interesting. In this case, I decided to intercept the event Audit Login ([/code]code = AuditLogin[/code]). This event is raised for all new connection, such as when a client requests a connection to the server. This server is dedicated to the cube that I needed to monitor so no additional filter will be needed.

 <ddl300_300:XEvent>
   <event_session ...>
      <event package="AS" name="AuditLogin" />
   </event_session>
 </ddl300_300:XEvent>

The full creation script is available here under:

<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</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" />
               <target package="Package0" name="event_file">
                  <parameter name="filename" value="L:\MSOLAP\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>

To be sure that your trace is running you can use the following script

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
   <RequestType>DISCOVER_TRACES</RequestType>
   <Restrictions/>
   <Properties/>
</Discover>

You can also check on your file location (L:\MSOLAP), if a file starting SQLAdmin_Audit and ending by .xel is available. Don’t panic with the size of your file or tha last update timestamp, some events could be delayed and the timestamp is not always updated.

And how can I query this file? With SQL Server database engine! The key function to read this kind of file is named sys.fn_xe_file_target_read. This function takes care of rollover files for you and that’s really cool. First parameter is the filename and others re not needed. If you’ve more events than just the AuditLogin, you can easily filter on the field object_name

select 
   cast(event_data as xml) as event_data
from
   sys.fn_xe_file_target_read_file(
      'L:\MSOLAP\SQLAdmin_Audit_*.xel'
      , null, null, null
   )
where
   object_name='AuditLogin'
)

Oups, this function returns some xml elements. SQL Server has features to query xml and extract specific fragments. For this case, the usage of value will be a good approach. This method requires and XML path followed by and an additional parameter (just let the [1]). The second parameter expected by this function is just the SQL return type (between simple quotes). With the help of this function, I’m able to extract the NTUserName and the StartTime of the connection, just after I’m filtering on distinct users to have my list.

with cte as
(
select
   event_data.value(
      '(event/data[@name="NTUserName"])[1]'
      , 'varchar(255)'
   ) as    NTUserName
   , event_data.value(
      '(event/data[@name="StartTime"])[1]'
      , 'varchar(255)'
   ) as StartTime
   , event_data
from
(
   select 
      cast(event_data as xml) as event_data
   from
      sys.fn_xe_file_target_read_file(
         'L:\MSOLAP\SQLAdmin_Audit_*.xel'
         , null, null, null
      )
   where
      object_name='AuditLogin'
)
) t
select distinct
   NTUserName
from
   cte

With this first step I’m able to identify the users and if they were frequent users or not. In a next blog post, I will explain how to intercept the queries. But before, it’s perhaps useful to remove the trace from your server if it’s not useful at the moment:

<Delete
   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">
   <Object>
      <TraceID>SQLAdmin_Audit</TraceID>
   </Object>
</Delete>
Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s