3 Replies Latest reply on Dec 17, 2014 5:20 PM by mack_torres

    User Report

    rclee@fdbhealth.com

      Is there a way to run a quick report on my users, including user added fields?

       

      Specifically,

      • Username
      • UserID
      • Name
      • Registered
      • Last Login
      • Active status
      • <Custom field>
      • <Custom field>

       

      The only way I can see getting a list is by copying and pasting from the user list.

       

      I'm on Jive 6 hosted.

       

      Thanks!

      -Roger

        • Re: User Report
          daniel.marotta

          Hi Roger,

           

          Do you have the Community Manager Reports plugin? You can run the User Adoption and Profile Completion reports and export them to csv.

           

          OR

           

          If you have an in-house BI tool, you can download .dmp file via the Cloud Admin tool and generate custom reports that way.

           

          -Dan

            • Re: User Report
              rclee@fdbhealth.com

              Thanks Dan.

              Jive support told me this:

              "Unfortunately running custom queries is out of the scope of Jive Support. I do however, see that you have Database Dumps enabled so you should be able to run your own custom queries.  I am attaching some documentation on Database Dumps: How To: Working With Database Dumps and also a list of schemas to help you create your custom query: SchemaSpy - sbs6a.public  "

               

              Essentially, you just have to request access to the Jive Cloud Admin and have them run a dump on a weekly/monthly basis. Then you download the dump (in this case the System db dump), restore the db dump into a Postgres database and run a query to pull out the info.

               

              The longest thing was to try to figure out how to convert the date/time that Jive stores into a real date. Here's the query:

              select a.userid, a.username, a.firstname, a.lastname, b.value as FDB_Acct, to_timestamp(a.lastloggedin/1000), to_timestamp(a.creationdate/1000), a.userenabled

              from jiveuser a, jiveuserprofile b

              where b.fieldid=5001 and b.userid=a.userid

               

              Note the "to_timestamp" converts the number into a "real" date and time. Also, jiveuserprofile.fieldid = 5001 was the custom field that I had created for the profile. Other custom fields have different field IDs.

               

              -Roger

            • Re: User Report
              mack_torres

              Just figured this out for onpremise users, here is the SQL Query:

               

              SELECT jiveUser.userID, jiveUser.username, jiveUser.firstName, jiveUser.lastName, jiveUser.userEnabled, jiveUser.creationDate, jiveUser.lastLoggedIn, jiveUserProfile.fieldID, jiveUserProfile.value

              FROM jiveUser

              INNER JOIN jiveUserProfile

              ON jiveUserProfile.userID=jiveUser.userID WHERE fieldID=5001 AND userEnabled=1;

              1 person found this helpful