1 Reply Latest reply on Dec 10, 2014 7:11 AM by mnevill

    Many, many Analytics LOGIN records for some users having no other activity?

    allanlewis67

      We have a bunch of users who have many, many LOGIN records in Analytics DB (at least once a day) who have no other Jive activity, which is perplexing, as we really don't think they are logging in.  Have you heard of this or know why the Analytics DB is saying this?

       

      This is on-premises Jive 6.0.3.1

       

      We're just beginning to analyze our Analytics data after less than a year of being in Pilot mode; Jive is used only in my program. We do not have a huge user base, so we're trying to analyze how often users are logging in and what they are doing. We're needing to do this across all of the places, so the CMR Plugin reports aren't sufficient as they are for just one place.

       

      I just recently got access to query the Analytics DB since it is stored in an Oracle DB and our Core and EAE DBs are in SQL Server.

       

      The image below shows some of the Analytics DB data I extracted -- of all activity from November 24 through December 1, sorted by user, by activity times (descending.) it shows that user named 'Lara E' logged in over 22 times and did nothing else in Jive .Sometimes these logins are separated by a matter of minutes

      • The SQL select statement is at the bottom
      • We considered if these logins were caused by stored browser cookies and their browser set to reopen a Jive tab when they the browser. But I spoke to one person who has these login records and they told me they do not automatically start Jive in any browser and have not gone into Jive in weeks.
      • The apparent over-login issue correlates with data we're seeing with our Core DB's JiveUser table's LAST LOGGED IN values; we're seeing many users being listed as logged in each day (based upon a report we run that sorts by Core DB's JiveUser table's LAST LOGGED IN values descending) but not much activity, and no activity for the majority of them.
        • Probably not related, but we have an open issue with Jive Support about a much smaller set of users whose JiveUser table's LAST LOGGED IN value is before some real, true activity)
      • 'Log in' appears in the report for 'ACTION' where jivedw_activity_fact.ACTIVITY_TYPE = 160 (looked up from the related jivedw_activity_meta tables ACTIVITY_TYPE)
      • Our environment is set to log people out after 60 minutes of inactivity ( system property: auth.lifetime  60 )
      • The report excludes the MODIFY USER row created after each login record, which isn't useful and clogs up reports.

       

      Our Analytics is in an Oracle DB so following is in Oracle SQL*PLUS format

       

        SELECT

       

           (select substr(trim(DECODE(NAME, 'JIVE','PROD', 'JIVEU', 'uat',  'not_in_list_____')),1,4) from v$database) "ENVIR",

          substr(trim(jivedw_user.firstname || ' ' || jivedw_user.lastname),1,20) NAME,

             substr(trim(jivedw_activity_fact.USER_ID),1,7) user_,

          substr(trim(am.activity_name),1,16) ACTION,

          substr(OM.object_name || ' (' || trim(jivedw_object.object_type || ')'),1,26) WHAT,

           to_char(jivedw_activity_fact.ACTIVITY_TS, 'MM/DD/YYYY HH24:MI:SS') WHEN_________________,

          substr(trim(jivedw_object.name),1,55) object_NAME,

          '  ' " ",

           substr(trim(jivedw_container.name),1,24) PLACE_name,

           substr(trim(jivedw_object.object_id),1,9) objectID,

            DECODE(jivedw_container.container_type,

             '14','space',

             '37', 'blog', 

             '600', 'project', 

             '700', 'Social Group',     

             '2020', 'user container',

             '', '',

               'NOT CODED FOR') PLACE_type,

           substr(trim(jivedw_container.container_id),1,12) PLACEtypeID, -- these numbers are the id just for that type of place; e.g., if a space, it is the Space ID, not the Place ID

           to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as When_rpt_run

          

          

          FROM

       

          jivedw_activity_fact,

          jivedw_user,

          jivedw_object,

          jivedw_container,

          jivedw_activity_meta AM, -- this is where the activity types are stored

          jivedw_object_meta OM -- has names for Object types

       

          WHERE  

          jivedw_activity_fact.user_id = jivedw_user.user_id AND jivedw_activity_fact.direct_dw_object_id = jivedw_object.dw_object_id 

          AND jivedw_activity_fact.dw_container_id = jivedw_container.dw_container_id

          and jivedw_activity_fact.ACTIVITY_TYPE = AM.ACTIVITY_TYPE

          and jivedw_activity_fact.direct_object_type = OM.object_type

         

          and UPPER(am.activity_name || OM.object_name || jivedw_user.username) <> UPPER('MODIFYUSER' || jivedw_object.name)

      -- excludes the MODIFY USER row created after each login record, which isn't useful and clogs up report; see 1st line below

          -- PROD  Thomas Olphamd        2194    Modify           User (3)                   11/27/2014 01:38:15   wolp01                                                                              2194                                  01-DEC-2014 17:50:39         

          -- PROD  Thomas Olphamd        2194    Log in            User (3)                   11/27/2014 01:38:15   wolp01                                                                              2194                                  01-DEC-2014 17:50:39         

         

          -- following just to limit selection for testing

          and to_char(jivedw_activity_fact.ACTIVITY_TS, 'YYYY-MM-DD') >= '2014-11-24'

         

          order by 

          jivedw_user.firstname || ' ' || jivedw_user.lastname, 

          jivedw_activity_fact.activity_ts DESC

      /

        • Re: Many, many Analytics LOGIN records for some users having no other activity?
          mnevill

          If you use SSO I would have your admin check it out and view any applicable logs to make sure it is not causing this issue somehow.  It also may be worth tracking the user down and talking to them on the phone to see if they can explain their experience.  If this is happening with multiple users try to talk to several of them. If you come up dry or it seems to be pointing to a Jive issue, opening a Jive Support case is the way to go for something like this.