Support

Expand all | Collapse all

User Report with Duration

Jump to Best Answer
  • 1.  User Report with Duration

    Posted 05-04-2017 15:28
    How to generate a user report in Jama with Login Time, Logout Time, Duration , License Type ?
    I was trying a query on database but I found logoutTime entry was same as loginTime in userlogentry table.

    Also, can get the 'Denied User' details in Jama, when someone is unable to login due to license threshold exceeded.


    ------------------------------
    Thanks,
    Arumit Garg
    Bose

    ------------------------------


  • 2.  RE: User Report with Duration
    Best Answer

    Posted 05-05-2017 10:28
    Hi Arumit,

    try this query, it was tested with SQL Server.

    SELECT
    li.eventDate loginDateTime
    ,CAST(li.eventDate AS DATE) LoginDate
    ,li.eventUser UserId
    ,(select top 1 lo.eventDate from evententry lo where lo.id>li.id and lo.eventtype=9 and lo.eventuser=li.eventuser order by lo.id) LogoutDateTime
    ,CAST(((select top 1 lo.eventDate from evententry lo where lo.id>li.id and lo.eventtype=9 and lo.eventuser=li.eventuser order by lo.id)-li.eventDate) as time) Duration
    FROM evententry li
    where li.eventtype=8 order by 1 desc

    Harald

    ------------------------------
    Harald Hotz-Behofsits
    Frequentis AG
    ------------------------------



  • 3.  RE: User Report with Duration

    Posted 05-08-2017 04:21
    This answers the 2nd part concerning the failed logins:

    SELECT count(*), cast(logintime as date),(select firstname+' '+lastname from userbase where id=userid) username
    FROM userlogentry where logincode=3
    group by cast(logintime as date),userid
    order by cast(logintime as date),3

    again tested with SQL Server.

    ------------------------------
    Harald Hotz-Behofsits
    Frequentis AG
    ------------------------------



  • 4.  RE: User Report with Duration

    Posted 05-08-2017 08:54
    Nice, we borrow this as well :)

    ------------------------------
    /Ekaterina
    ------------------------------



  • 5.  RE: User Report with Duration

    Posted 05-08-2017 15:29
    Hi Harald,

    Thank you for quick reply. This is what I was looking for and it helps me.

    Is there any way we can get this output in Jama UI. I mean, can we create a report in Jama using this sql query ?
    So that we can get the live data any time.

    Thanks,

    ------------------------------
    Arumit Garg
    Bose
    MA
    ------------------------------



  • 6.  RE: User Report with Duration

    Posted 05-10-2017 01:36
    Hi, out of the box - not.. BIRT is only used in Jama for generating reports like Velocity.
    http://help.jamasoftware.com/index.html?birt_custom_report_guide.htm

    ------------------------------
    /Ekaterina
    ------------------------------



  • 7.  RE: User Report with Duration

    Posted 06-19-2017 07:42
    Hi,

    I'm late for the discussion, but I stumbled across this during my search and I have a solution how to show real time data from report to the dashboard.

    1. Create your report in BIRT
    2. Create Apache Tomcat server
    3. Upload the report to tomcat, so that you can see the report in Tomcat
    4. Get the link for HTML report
    5. Create widget in dashboard
    6. Insert iframe in the rich text field for the widget. In the iframe just paste the HTML report link

    ! Everybody who wants to see the report, must have access to the link and it will update on each widget refresh or page reload.

    Good luck!

    ------------------------------
    Rudolfs Sviklis
    AA Projekts
    ------------------------------