8 Replies Latest reply on May 12, 2014 2:12 PM by erinclements

    Day of latest activity for each group

    jemjanik

      This is a query I used and then stuck the data in excel along with the dump of all data in jivedw_sgroup to find groups not used in X amount of time.

       

      select max(d.begin_ts), c.container_id, c.name  
      from   
          jivedw_activity_fact a,  
          jivedw_container c,  
          jivedw_day d  
      where      c.container_type=700 and  
          a.dw_container_id=c.dw_container_id and  
          a.day_id=d.day_id  
      group by c.container_id, c.name;  
      
      

       

      basicially the query takes the latest date in the table with all activity (activity fact), the unique identifier of the container, and the container name

      then constrain to container_type 700 since 700 is social groups

      then join the tables together on the common fields.

       

      The dump of the group table is just

       

      select * from jivedw_sgroup;
      

       

      I'll post another query I used next week.

        • Re: Day of latest activity for each group
          timm

          Surprisingly helpful to have this, thank you.

          • Re: Day of latest activity for each group
            erinclements

            Hi Jem,

            I know this is an old thread, but I was wondering if you happened to be able to filter out or exclude groups that have been deleted from this query?  We were hoping to use something like this to assist us in a "group clean up". I ran this and it worked great, but a lot of the older ones on the list were groups that we "cleaned up" our last go around, and we were hoping to start from what was exisiting now!  Thanks for posting this, it's great!

              • Re: Day of latest activity for each group
                Caton Guilbault

                Hi Erin,

                 

                To get a list of current groups you can run the following from the application database:


                     select * from jivesgroup; 

                 

                This would give you a list to cross reference it also provides a grouptype field and the userid of who created the group.

                 

                Jive Group Types

                0- Open

                1 - Members Only

                2 - Private

                3 - Secret

                 

                 

                This is the query I usually run for our admins when we do cleanup's:

                 

                select     jgroup.name,

                              jgroup.displayname,

                              juser.username as "AdminNT",

                              to_timestamp(jgroup.creationdate/1000) as "creationdate",

                              to_timestamp(jgroup.modificationdate/1000) as "modificationdate",

                CASE

                    WHEN jgroup.grouptype = 0 THEN 'Open'

                    WHEN jgroup.grouptype = 1 THEN 'Members Only'

                    WHEN jgroup.grouptype = 2 THEN 'Private'

                    WHEN jgroup.grouptype = 3 THEN 'Secret'

                    ELSE 'FAIL'

                    END as "Group Type"

                 

                from jivesgroup jgroup

                join jiveuser juser on juser.userid = jgroup.userid

                 

                where jgroup.name is not null

                order by jgroup.name asc

                 

                 

                 

                 

                I hope this helps and let me know if you have any questions.