User Tracking

Comments

24 comments

  • Avatar
    Ofer

    Nicholas,

     

    Great post, thanks for sharing!

     

  • Avatar
    Tal

    Hello Ofer,

    Is this going to be available on future version?

    Tal

  • Avatar
    Mornay

    Great!! Thanks Nicholas...exactly what we want

  • Avatar
    Liran

    Hi,

    Thanks for this post.

    I still don't see the UserName field populating my logs, even after performing the change in the file 'C:\Windows\System32\inetsrv\config\applicationHost.config' and performing an restart to IIS.

    Any ideas?

    Regards, Liran

  • Avatar
    Nir

    i encountered the same problem as Liran did,

    any luck with that?

     

  • Avatar
    Ofer

    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. 

  • Avatar
    Ofer

    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.

  • Avatar
    Angelo

    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… 

     

  • Avatar
    Ofer

    That's awesome, thanks for sharing Angelo !  

  • Avatar
    Mazda Ebrahimi

    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
  • Avatar
    Ido Darnell

    This is great Mazda! Thanks for sharing!

  • Avatar
    John Wright

    Love your solution Mazda. I have it running now. However, most of my dashboards are still in V4.7 at the moment. Do you know what variables hold the username and dashboard name in V4.7?

  • Avatar
    Mazda Ebrahimi

    Sorry John, we signed up with Sisense just as version 5 was coming out, so I never really played around with 4.7

  • Avatar
    subbarao

    Hi All,

     

    Is this feature available in latest version or yet to add with Sisense?

     

    Thanks,

    Subba

  • Avatar
    John Wright

    Subba,

    Yes, but their version is not free. The solutions in this thread are free.

    John

  • Avatar
    Shraddha

    Hi,

    I am not able to get dashboard id and username details in the log when using IIS Advanced logging plugin. Can someone help?

    Shraddha

  • Avatar
    HK Chang

    Hi,

    I am also not able to get the username.

    HK Chang

  • Avatar
    yaswanth m

    Hi,

    Is this feature available for  sisense - build 6.2.1.61 ? 

    Thanks,

    Yaswanth

  • Avatar
    Ahuva Shabtai

    @Mazda, thank you for sharing your solution. I would like to try this, but I would first like to know if you the solution that you were using on Version 5 applies to Version 6.2.

    Thanks, 

    Ahuva

     

  • Avatar
    Sisense License

    Why does my CS-username not appear?

  • Avatar
    Aware Manager

    Does anyone have a solution to this? I can't see the username with the posted solution OR with Log Parser Studio.

  • Avatar
    Nicholas Ranum

    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.

    https://documentation.sisense.com/latest/administration/application-database/access-sisense-application-database.htm

    Hope this helps! I am the original author of this post.

  • Avatar
    Sisense License

    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. 

     

     

     

  • Avatar
    Aware Manager

    I'm getting the same error as the user above ^

Please sign in to leave a comment.