User Tracking
This is not a perfect approach but I have come up with a way to track usage of our dashboards. More specifically, which users are logging in. Rather than getting this information from SiSense, I went through IIS. This approach will only work if you are hosting the dashboards through IIS.
A) Follow this link (http://www.microsoft.com/en-us/download/details.aspx?id=7211), download and install the Advanced Logging add-on to the server hosting your Prism Web site
B) Once installed, open IIS and click on your server name then double click on Advanced Logging. See attached screen1
C) Click on Add Log Definition. See attached screen2
D) From here you setup the log however you'd like. See attached screen3
- Enter the name you want for the log. Mine is Prism_Web
- Check Publish real-time events so you can get updates as people interact with your dashboards
- Schedule is for how often you'd like a new log file to be created. Daily would be a good choice if you have a large user environment, Weekly or Monthly if you only have a few users. You can select never if you'd like to convert it to a CSV and create a dashboard from the information (could link it with Google Analytics data).
- Click on 'Select Fields' to pick fields that show up in the log. The three most important for user tracking are 'Date-Local', 'Time-Local', and 'Username'. You can select any of the others if you'd like but these are the most important
E) Click apply in the top right corner under Actions and you are set. Click on 'View Log Files' to open the folder containing the logs.
I had an issue where the UserName field wasn't populating. Follow the steps below to resolve this quickly.
A) Copy and Paste this link into Windows Explorer - C:\Windows\System32\inetsrv\config\applicationHost.config
B) Find this line of code:
<field id="UserName" sourceName="UserName" sourceType="RequestHeader" logHeaderName="cs-username" category="Default" loggingDataType="TypeLPCSTR" />
C) Change sourceType="RequestHeader" to sourceType="BuiltIn"
D) Save the file
Hope this helps everyone out. I implemented this a few weeks ago and I can already tell which departments are using my dashboards and which aren't. Good luck!
screen1.png
screen3.png
screen2.png
-
Hi,
The iis populating logs in the following folder : C:\inetpub\logs\LogFiles\W3SVC2
One can use log parser in order to export and analyze the data for example http://blogs.iis.net/carlosag/archive/2010/03/25/analyze-your-iis-log-files-favorite-log-parser-queries.aspx
We are still working on a 3rd application that will allow users to analyze the user usage, the application will eventually connect to our web back end server to allow the users to pull the dashboard name.
I'll shade more light as soon as we will start developing it.
-
Here is more important information from Nicholas!
First off, you will need this installed.
http://www.microsoft.com/en-us/download/details.aspx?id=24659
It’s Microsoft’s Log Parser, you can use this on its own but it’s very code intensive and not friendly to your average user.
To make it easier to implement you can download and install this
http://blogs.technet.com/b/exchange/archive/2012/03/07/introducing-log-parser-studio.aspx
It is called Log Parser Studio. This makes it so you can create a PowerShell script that will turn your log files into a csv file. The first link must be installed for Log Parser Studio to work.
Follow these steps:
1) Click on the ‘Create a new query’ button on the top left
2) Click on the ‘Choose log files/folders to query’ button and select the directory you wish to query.
- · C:\inetpub\logs\LogFiles\W3SVC2
3) Now you have your data set ready. The default query will get you started. The query I use is
- · SELECT date, time, cs-username, cs-method, cs-uri-stem, cs-uri-query, time-taken [logpath] order by date, time desc
i. Cs-username gives the user-name
ii. Cs-method tells you if it’s a POST or GET
iii. Cs-uri-stem is the folder the request is located in
1. /api/dashboards/241/…… (this gives you the psm dashboard ID)
2. /license/get/ (this will give you login time stamp for each user)
iv. Cs-uri-query is the file that is being requested
1. This can get you even more detailed info. I’m thinking you could see which selectors are used the most or which tables are being drilled into, etc. Haven’t dug into yet.
v. Time-taken is how long the request took to complete
4) Once you have your query set. Change it to read:
- · SELECT date, time, cs-username, cs-method, cs-uri-stem, cs-uri-query, time-taken into ‘C:/user_tracking/tracking.csv’ from [logpath] order by date, time desc
5) If this succeeds then a csv file will populate with the information. Click on the ‘Export query as powershell script’ button
That’s the basics on how to do it. From there you can dump the powershell into Task Scheduler and have it pull as often as you’d like. If your client’s IIS isn’t on the same server then you can create another job on Task Scheduler to send the csv to whatever server they want.
It will reference all the logs that are in the folder unless you tell it to specify a single log file. It will dynamically pull as new log files are created on the IIS server.
Also, I forgot to mention that you have to select a Log Type which is directly above the area for writing the queries. The log type is W3CLOG
Thanks for sharing Nicholas.
-
Hi,
You could create a batch file containing the query against log parser and schedule the job with windows task scheduler then dump the data in a given location overwriting the previous run.
Since you would then have an automated parsed dump of your logs, why not create a nice visual dashboard in Prism with it ?
1) create an elasticube with the csv dump file.
2) next, parse the cs-uri-stem string and extract information into new columns, such as the username, folder id and the dashboard id.
3) import the reference tables (the ones containing the Dashboard ID, Name & Container ID, Name) from Prism db in SQL Server - (perform the same operation for other the tables [e.g. containing the widget id, name] depending on the level of detail you want to achieve in your dashboard).
4) then add a user dimension table so you can aggregate and discover usage by different dimensions such as job title, city and so on (if you have a field to join or lookup the cs-username with, else skip this step).
5) after importing the tables, join them by cs-username, for the staff dimensions, and by other columns you have previously parsed (e.g. dashboard id), for the actual names of the objects you want to present.
6) once you have the elasticube, build a dashboard with all the needed slicing and dicing (queries per user, admin behaviour etc…).
If you schedule the log parser job and elasticube you’ll end up with a usage dashboard, always up to date, with no further work needed and much more fun than going through log files…
-
guys I took a different approach -
I created a little web app I can call that records its parameters to a database table. My call can store current time, user, dashboard, and a couple of key parameters specific to my setup, like what product and date range the user is looking at. There is some other code that sets window.selectedProduct and window.fromdt and window.todt values based on filter selections in my dash, which I'll add to the bottom of this post.
Then I added this to base.js (/clients/js/base.js)
function DashRefresh (se, ev, dtControl, productControl) {
var surl = 'http://[url to your web app]?user=' + $(".user-fullname").html() + '&dash=' + $(".transput-caption").html() + '&filters=[{selectedProduct:' + window.selectedProduct + ',fromDt:' + window.fromdt + ",toDt:" + window.todt + '}]'; ;
console.log(surl);
$.ajax({
url: surl
, type: 'GET'
, crossDomain: true
, jsonpCallback: 'callback'
, dataType: 'jsonp'
, success: function (data) { console.log(data); }
}).fail(function (XHR, status, error) { console.log(error); });}
And then, for each dash I want to track, I place this in the dash script:
dashboard.on('refreshend', function(se, ev){
DashRefresh(se,ev,"Date Range","Products");
});-----------
To capture parameter values, here's some code you can work with (you can also add something like this inside the DashRefresh function:
for (var i = 0; i < prism.activeDashboard.filters.$$items.length; i++) {
var datatype = prism.activeDashboard.filters.$$items[i].jaql["datatype"];
var currCol = prism.activeDashboard.filters.$$items[i].jaql["title"];
console.log("Current Column: '" + currCol + "'" );
console.log(datatype);
if (currCol == dtControl) {
console.log("Found DT");window.fromdt = prism.activeDashboard.filters.$$items[i].jaql["filter"].from;
window.todt = prism.activeDashboard.filters.$$items[i].jaql["filter"].to;
console.log("saving date range" + window.fromdt + "-" + window.todt);
}if (currCol == productControl) {
console.log("Found product filter");window.selectedProduct = prism.activeDashboard.filters.$$items[i].jaql["filter"].members[0];
console.log("saving product filter" + window.selectedProduct);
}After all this, now I have the data I want in the database and can just report on it with a sisense dash like this:
User_Stats.png -
Unfortunately this method stopped working when SiSense upgraded to version 5, I ran into the same issues y'all describe. The data is being stored in the mongodb instance on the server instead of through the IIS Logs now. I use the mongoexport command to transfer the data into json format.
"c:\Program Files\MongoDB\Server\3.4\bin\mongoexport" --host ServerName:27018 --db monitor --collection trace --fields timestamp,level,meta --sort "{_id: -1}" --out C:\OutputFolder\usage.json
This will get usage data in json format. You can run similar commands to get lists of users, groups, dashboards, etc. You'll need to further parse the json file depending on your end goal.
Alternatively, if you have the MongoDB ODBC connector as described in the link below, you can push the data into an elasticube to use how you want.
Hope this helps! I am the original author of this post.
-
Hi Nicholas,
Thanks for the Update.
I am currently trying to receive this information from the MongoDB however I can not change the readUser password.
I put in the token and place :
{
"userName": "ReadUser",
"password": "XXXXXXXXXX"
}Within the :
POST /app_database/change_database_user_password
But when I POST I receive Response Code: 401
Error: Index was outside the bounds of the array.
Please sign in to leave a comment.
Comments
24 comments