User Activity Logging

Supplement: Analyzer Usage Analysis Report (https://cs.strategycompanion.com/hc/en-us/articles/218009488-Analyzer-Usage-Analysis-Report)

As an Analyzer administrator, you may want to know the users’ actions, for example: the number of times users that are accessing specific reports in Analyzer. The Analyzer system database, (BOStore by default) records all this information in the log table (dbo.Logs ) based on log settings, however, it is a little bit inconvenient to get the information from the log table because all of the actions in action column are displayed as numeric values. There is an action code reference in an action code file. https://cs.strategycompanion.com/hc/en-us/articles/217990468-Log-Table-Action-Code-dbo-logs-

Using the codes, we can now build the following SQL query to make the user actions more readable and you can also create a small cube in Analyzer for analyzing this information.

SELECT  [LogTime], [ObjectName] ,[ObjectType]

,CASE [Action]

WHEN 0 THEN 'Unknown'

WHEN 1 THEN 'Login'

WHEN 2 THEN 'Logoff'

WHEN 3 THEN 'Load'

WHEN 4 THEN 'Insert'

WHEN 5 THEN 'Update'

WHEN 6 THEN 'Delete'

WHEN 7 THEN 'Execute'

WHEN 8 THEN 'SaveAs'

WHEN 9 THEN 'Send'

WHEN 10 THEN 'ToExcel'

WHEN 11 THEN 'Export'

WHEN 12 THEN 'Import'

END AS [User Action]  

,[UserName], [UserHost],1 AS [Count] 

FROM [BOStore].[dbo].[Logs]

 

After processing, you will get a report like this:

 

 

Or this:

 

 

 

 

The following steps will teach you how to create this cube in Analyzer.

 

1. Please go to “My Data Packages”.

 

2. Please select “new data package”

 

 

3. Input the cube name in “Name”. For example: Users Action

 

 

4. Input your SQL Server Name (example:strategycompani) and click the “Test Connection”

 

 

5. Please copy the SQL Query above and paste it in “SQL Command”.

 

 

6. Leave everything by default and click “save and process”

 

 

7. After the process is done, you can select “create report”.

 

8. Finally, you can see the schema tree and create a report. (Done!)

 

 

 

 

 

Please kindly note:

 

1. you can always update the cube by clicking the “process” button.

 

 

2. You can select which actions to log using the Log Policy Configuration. In order to keep the BOStore database small, Analyzer has a default setting for the maximum log size 10000 records. So, the data in dbo.Logs table will be cleaned after it reaches 10000 records. However, you can change the number as you like or backup dbo.Logs table periodically.

 

 

You can find the above “Log Policy Configuration” in Analyzer (System configuration --> system log --> config log policy)

 

 

 

 

This is an example showing how the BOStore data works with Analyzer. You can base on your own business requirement to modify the SQL query and create dashboards in Analyzer.