none
How to get list of user connected to project server by day wise RRS feed

  • Question

  • HI,

    I need to get a report as how many times user  is connected to the project server in a week.

    I have enabled web analytical reports but its showing only how many times a user is connected to site.I need date also

    Project server 2010 version.

    Can any one help me.

    EX:

    Resource name           Date                 Connected (yes/No)

    Vijay  kumar            1-11-2014             yes

    Vijay  kumar            2-11-2014             yes               

    Vijay  kumar            3-11-2014             no

    Vijay  kumar            4-11-2014             yes

    Vijay  kumar            5-11-2014             yes

    Vijay  kumar            6-11-2014              no

    Vijay  kumar            7-11-2014             yes


    Thank You, Kumar KSV



    • Edited by kumar ksv Friday, November 14, 2014 6:32 AM
    Friday, November 14, 2014 6:30 AM

Answers

  • Hi Kumar,

    See the below blog. Hopefully it helps you -

    https://badalratra.wordpress.com/2014/03/24/active-users-report-last-accessed/

    Please note solution query is hitting the ProjectServer_Published DB in the report which is not supported by Microsoft. Please use it at your own risk.


    Cheers, Badal

    Tuesday, November 25, 2014 8:53 AM
  • What Badal Ratra gave you would be the basis for your query to populate your own table...Project Server does not give you a way to do a daily connect report

    One of the ways I would go about doing this would be to run a nightly query to update a table - fill it in with the necessary data and run your report off that newly created table

    High Level Steps:

    (1) Create a table in the Reporting Database call DailyVisits
    (1a) The table would have Name/Date/Connected columns 
    (1b) You wouldn't need a no answer for connected columns since the answer would always be yes, the result set would ONLY have users who connected for each day.  And really you would only need 2 columns since the "connected" column would always be Yes...

    (2) Create a query that would query the published database (msp_resources) for the current day and store the result set in the table DailyVisits

    (3) Put this query in a nightly task to append to the DailyVisits table @ 11:55pm (for example)

    (4) Let the query run and you'll have only the users who connected for that day - the DailyVisits table would represent users who visited every day.  

    As long as the query runs nightly you'll have the information you're looking for - the one caveat is that it will only be starting from the day you start running the query.  

    You could also append the older user (last_connect) information from the resources table from other users to populate the dates but that would only represent a single entry/single last connect date.  This "old" information would represent the base for the table.

    hth

    • Marked as answer by kumar ksv Friday, January 23, 2015 5:49 AM
    Tuesday, November 25, 2014 1:06 PM

All replies

  • Hi Kumar,

    See the below blog. Hopefully it helps you -

    https://badalratra.wordpress.com/2014/03/24/active-users-report-last-accessed/

    Please note solution query is hitting the ProjectServer_Published DB in the report which is not supported by Microsoft. Please use it at your own risk.


    Cheers, Badal

    Tuesday, November 25, 2014 8:53 AM
  • HI Badal Ratra,

    Thank you for the reply,

    I need users list by day wise connected but with this query we can only get last connected.


    Thank You, Kumar KSV

    Tuesday, November 25, 2014 11:54 AM
  • What Badal Ratra gave you would be the basis for your query to populate your own table...Project Server does not give you a way to do a daily connect report

    One of the ways I would go about doing this would be to run a nightly query to update a table - fill it in with the necessary data and run your report off that newly created table

    High Level Steps:

    (1) Create a table in the Reporting Database call DailyVisits
    (1a) The table would have Name/Date/Connected columns 
    (1b) You wouldn't need a no answer for connected columns since the answer would always be yes, the result set would ONLY have users who connected for each day.  And really you would only need 2 columns since the "connected" column would always be Yes...

    (2) Create a query that would query the published database (msp_resources) for the current day and store the result set in the table DailyVisits

    (3) Put this query in a nightly task to append to the DailyVisits table @ 11:55pm (for example)

    (4) Let the query run and you'll have only the users who connected for that day - the DailyVisits table would represent users who visited every day.  

    As long as the query runs nightly you'll have the information you're looking for - the one caveat is that it will only be starting from the day you start running the query.  

    You could also append the older user (last_connect) information from the resources table from other users to populate the dates but that would only represent a single entry/single last connect date.  This "old" information would represent the base for the table.

    hth

    • Marked as answer by kumar ksv Friday, January 23, 2015 5:49 AM
    Tuesday, November 25, 2014 1:06 PM
  • Thank You very much Badal Ratra & kbwrecker,

    Using the last connect value my Sql developer has written some trigger & Created the report.


    Thank You, Kumar KSV

    Friday, January 23, 2015 5:49 AM