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 18.104.22.168
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 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(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_________________,
' ' " ",
'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
jivedw_activity_meta AM, -- this is where the activity types are stored
jivedw_object_meta OM -- has names for Object types
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'
jivedw_user.firstname || ' ' || jivedw_user.lastname,