5 Replies Latest reply on Feb 8, 2016 10:14 AM by Caton Guilbault

    Query to extract Places Information

    sonja

      Does somebody know a query to export information about places?

       

      I need to make an analysis in order to clean up our collaboration platform from inactive places

      I would need following Information

       

      All created places name

      Place type

      Group type

      Creation date

      Number of member

      Number of follower

      Last activity

      Number of content

        • Re: Query to extract Places Information
          Caton Guilbault

          Hey Sonia,

           

          Which version of Jive are you running? Some of this data moved between 5 & 6.

           

          I don't have one master query but I have a bunch of separate ones that you could aggregate or use as starting points at least. If you end up building something with all of this I would love to see it!

           

          • All created places name
          • Place type
            • These 2 are basically the jivedw_container table from the Analytics DB

           

          • Group type
          • Creation date
            • These 2 are in the jivesgroup table from the Core DB

           

          • Number of member
            • For Social groups this is in jivesgroupmember table from the Core DB

           

          • Number of follower
            • In 6+ this data is in the EAE DB
          select
              Streamid,
              jivestreamassociation.userid, 
              jivestreamassociation.objectid, 
              jivestreamassociation.objecttype, 
              jivestreamassociation.receiveemails,
              jivestreamconfiguration.name,
              to_timestamp(jivestreamconfiguration.createddate/1000) as Creationdate, 
              to_timestamp(jivestreamconfiguration.modifieddate/1000) as Modificationdate
          
          
          From jivestreamassociation 
          join jivestreamconfiguration ON (jivestreamconfiguration.id = jivestreamassociation.streamid)
          
          
          Where jivestreamassociation.objecttype=14
          and    jivestreamassociation.objectid= 3958 -- Space ID
          
          order by createddate desc
          

           

          • Last activity
            • This will depend on what you want to consider activity. If you want the last View day this might work
          select 
              container_id,
              max(day_ts)
              
          from jivedw_daily_spc_summary
          
          where     
              View_count !=0 
          
          group by container_ID
          

           

          • Number of content
            • This will be different for Groups & Spaces and for each content type but here are some queries to get you started
              • Group data query:
          select     jgroup.groupid, 
              jgroup.name, 
              jgroup.displayname,
              jgroup.userid as "AdminID", 
              juser.username as "AdminNT", 
              to_timestamp(jgroup.creationdate/1000) as "creationdate",
              to_timestamp(jgroup.modificationdate/1000) as "modificationdate", 
              jgroup.grouptype,
              jgprop.name, 
              replace (jgprop.name, 'contentType.','')as "ContentType", 
              jgprop.propvalue
          
          from jivesgroup jgroup 
          join jiveuser juser on juser.userid = jgroup.userid
          join jivesgroupprop jgprop on jgprop.groupid = jgroup.groupid
          
          where jgroup.name is not null 
          and jgprop.propvalue = 'true'
          --and jgroup.groupid = 1782
          
          order by jgroup.name asc
          
              • Space content types
          select jivecommunity.communityid, 
              jivecommunity.name, 
              to_timestamp(jivecommunity.creationdate/1000) as "Createdate",
              replace (jivecommunityprop.name, 'contentType.','')as "ContentType",
              jivecommunityprop.propvalue
              
          from jivecommunity
          left join jivecommunityprop on jivecommunityprop.communityid=jivecommunity.communityid
          where jivecommunityprop.name is not null 
          and jivecommunityprop.propvalue = 'true'
          order by jivecommunity.name asc
          

           

              • Space Doc Count
          select count(doc.internaldocid) as "Count", community.name
          from jivedocument doc
          left join jiveuser person on person.userid = doc.userid
          left join jiveobjecttype objtype on objtype.typeid = doc.containertype
          left join jivecommunity community on community.communityid = doc.containerid
          left join jivedocversion docversion on docversion.internaldocid = doc.internaldocid
          where docversion.state = 'published' and objtype.code = 'community'
          group by community.name
          order by "Count" desc
          
          2 people found this helpful
          • Re: Query to extract Places Information
            biray

            Hi Sonia,


            There's actually an easier way to get this information via the JIVE Community Manager reports.

             

            1. Go to Community Manager Reports
            2. Click on Places Activity (towards the bottom of the left-hand navigation)
            3. Adjust the date range
            4. Download the CSV

             

            Although it's not intuitive by looking at the list in the UI, the CSV file actually includes the following:

            • Place name
            • Display name
            • Creation Date
            • Place ID
            • Type (Community/Group)
            • Number of Views
            • Number of Likes
            • Number of Rates & Votes
            • Number of Responses
            • Number of Revisions
            • Number of Creates
            • Number of Followers
            • Groups Only: Group Type
            • Groups Only: Number of Members
            • Groups Only: Group Owner
            • Groups Only: Group Owner Username
            • Groups Only: Group Owner ID

             

            Hope that helps!

              • Re: Query to extract Places Information
                sonja

                Thanks Biray,

                but unfortunately this doesn't help me. :-(

                 

                I would like to know the groups that has not been modified in the last 6 months and the community manager report doesn't give me this information.

                The range you select refers to the activity on that time but it doesn t mean it was the last activity on that place.

                I would like to see all created groups and which of them has not been modified (No content created) in the last 6 months, in order to archive them and clean up the community.

                 

                The report in additional, as I understood,  calculate as activity also the view and in my opinion the objective of the clean up is indeed to avoid user wasting their time to find and view places where is nothing to see.