5 Replies Latest reply on Apr 3, 2014 12:57 PM by allanlewis67

    export of users in a permission group

    mnevill

      Here is a query I built that will return the username and e-mail address of all members in a specific system group, or all groups. Note this is not a social group, but a permission group/system group.  You can see a list of these in the Admin Console by going to  People > Management > Group Summary .

       

      All system groups and their members:

      SELECT

      jivegroup.groupid, jivegroupuser.userid, jivegroup.name, jiveuser.username, jiveuser.email

      FROM

        public.jivegroup, public.jivegroupuser, public.jiveuser

      WHERE jivegroup.groupid = jivegroupuser.groupid AND jivegroupuser.userid = jiveuser.userid

       

      All members of a specific system group:

      SELECT

      jivegroup.groupid, jivegroupuser.userid, jivegroup.name, jiveuser.username, jiveuser.email

      FROM

        public.jivegroup, public.jivegroupuser, public.jiveuser

      WHERE jivegroup.groupid = jivegroupuser.groupid AND jivegroupuser.userid = jiveuser.userid AND jivegroup.name = 'case sensitive system group name'

       

      Make sure to replace case sensitive system group name with the name of the specific group you want (ex: 'System Administrators'). 

       

      Here is an example of the output:

      groupiduseridnameusernameemail
      10032001System Administratorsadminadmin@localhost
      10032008System Administratorsexample-useruser@example.com
        • Re: export of users in a permission group
          mnevill

          I originally posted this in Jive Analytics space instead of the Jive Analytics User Group group. 

          • Re: export of users in a permission group
            allanlewis67

            Hi Matt,

             

            I found this most helpful, thanks.  Just want to point out that if some of members are also Admins for the group then get multiple rows, so you might want to add a row like following to it:

             

            and administrator <> 1

             

            Here's my equivalent of your first group"

             

            SELECT

            G.groupid, GU.userid,

            G.name + (CASE g.federated WHEN 1 THEN '__FEDERATED' ELSE '' END) as Group_,

            U.username, U.firstName + ' ' +u.lastName as Name_

            FROM

              jivegroup G, jivegroupuser GU, jiveuser u

              WHERE G.groupid = GU.groupid AND GU.userid = U.userid

              and administrator <> 1

              order by g.name, USERNAME

              • Re: export of users in a permission group
                Kara Francis

                We've added some additional columns to a similar query to help us know where these users reside in the organization and some other profile data, including their universal user ID (so people can use that data and compare against other systems; can also be used to add to a social group via the admin essentials plug-in).  We also have a column for their enabled/disabled profile status so that we can see if we need to clean up the group and remove disabled users.  We also have a column to state whether it is tied to a status level.  This is helpful for multiple reasons, but one reason is that if you delete a permissions group, but don't remove it from the status level settings, then you break all other status level settings that fall below the broken one.  (it sure took us awhile to troubleshoot why most of our status levels were not showing up - once we removed the status level that referenced the deleted group, everything was working again)

              • Re: export of users in a permission group
                mnevill

                If anyone has additional complete queries they have tested please post them as a reply.  Just make sure to explain what the query does and if possible show an example of the output.  Ideally someone (including non-technical folks) can just take these queries and copy/paste them into Postgres to get an export.