5 Replies Latest reply on Jun 21, 2013 10:00 AM by jemjanik

    System Admins and Private Groups

    dsacks

      Kind of a random question, but is there any way to see if Jive System Admins are accessing private groups? (My assumption is No.) Or, maybe this is the question: can you tie a user to specific activity? We use the CMR reports and the Admin Console only for reporting purposes (in Jive 5). Thanks.

        • Re: System Admins and Private Groups
          tmaurer

          I Suspect you can using SQL queries against the full system logs. But nothing automated. Jem Janik might have some thoughts on this.

            • Re: System Admins and Private Groups
              jemjanik

              It should be in the analytics db. I can try to look at what the SQL would be tomorrow

                • Re: System Admins and Private Groups
                  jemjanik

                  Looks like group permission type is not in analytics so this query from the analytics tab pulls all the read activity (activity_type=10) for a member (see below where by username is mitchelj).  The fields it is returning in order are member's numerical userid, username, the timestamp of the read, the day id, what was read, the id of what was read, what kind of object is it, the container id, the container type, and the container name.

                   

                  select  jivedw_activity_fact.user_id,

                      jivedw_user.username,

                      activity_ts,

                      day_id,

                      jivedw_object.name as direct_object_name,

                      jivedw_object.object_id as direct_object_id,

                      direct_object_type,

                      jivedw_container.container_id,

                      jivedw_container.container_type,

                      jivedw_container.name as container_name,

                      indirect_dw_object_id,

                      indirect_object_type,

                      metadata

                  from     jivedw_activity_fact,

                      jivedw_container,

                      jivedw_object,

                      jivedw_user

                  where  

                      jivedw_user.username='mitchelj' and

                      jivedw_activity_fact.activity_type = 10 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.user_id=jivedw_user.user_id;

                   

                  You should be able to marry that up with data on the private groups with this query from production:

                   

                  select * from sgroup;

                   

                  Group types are as follows:

                  0 = Public

                  1 = Members Only

                  2 = Private

                  3 = Secret

                   

                  Then match your container_ids from the first query to the group_id in the second query.  It's not perfect. This doesn't figure out for you if any of the projects or blogs are in secret groups as they have a separate container id, but I don't have the time to figure that out at the moment. It should give you some idea if it's going on & if you want to figure it the rest out in more detail.

              • Re: System Admins and Private Groups
                bchamberlain

                We have an add-on framework that we built that provides this (and much more). In the simplest of terms, if Jive tracks the information in the database, we can report on it.  The framework is built using QlikView's BI tool and it sits on top of Jive's Analytics database. (v4.5, 5, or 6). As QlikView runs everything in memory, it is incredibly fast....you can spin through millions of rows of activity data in seconds.

                 

                I am happy to have a chat and demo with you if you have any interest.  (bill.chamberlain@centricconsulting.com)

                 

                Bill

                  • Re: System Admins and Private Groups
                    lbglg

                    Dear Bill,

                     

                    I would be grateful for a chat and demo sometime in the near future - the possibilities of QlikView seem highly useful!

                    Would first like to look into the system a little and consolidate what information/analytics I would like to track.

                     

                    If OK with you will send you a message to figure out a convenient time.

                     

                    Kind regards,

                    Louis