5 Replies Latest reply on May 14, 2014 3:43 PM by Caton Guilbault

    SQL query for seeing who has viewed a space?

    ngable

      Hi all, let me start by saying I am not a DB admin or SQL guy. However I'm finally set up to query our analytics database, but am having trouble coming up with a query for what I want to do given my lack of knowledge with SQL.

       

      The goal is to see a list of registered users that have accessed a specific container and all subcontainers by date. It's fine if the query is only for a single container, as I can run it individually for all the sub-spaces it would just be awesome if there's an easy way to fold it all up into one query. We'd also like to get at which specific documents have been viewed within that space. Any help would be appreciated - I've done some deep searching through this community and haven't been able to find anything quite like this.

       

      EDIT - I should add, I've also been having some trouble with running any sort of query. I restored the database via pgadmin and am trying to work with the data in qlikview, but whenever I run any query I get an error for "cross database references not implemented." I see there's plenty of documentation on this in google, but haven't been able to work up a solution. Has anyone seen something similar & found a fix for it?

       

      Thanks in advance if anyone is able to help, or point me in the right direction!

       

      Cheers,

      Nick

        • Re: SQL query for seeing who has viewed a space?
          ngable

          I've found bits and pieces of queries other have wrote that look like they could get at this, although none are exactly what I'm looking for. I'll compile what I've found and post them here to get the ball rolling. I'll also keep you all updated on the "cross DB references" troubleshooting as I dig into that.

            • Re: SQL query for seeing who has viewed a space?
              Caton Guilbault

              Hi Nick,

               

              Here is a query to get you started, it counts unique users of a space but it can be altered to look for views instead. You could also change the container.name where to an IN with a list of container ID's if you wanted results from more than one place.

               

               

              SELECT     jivedw_container.name,

                  COUNT(DISTINCT jivedw_activity_agg_month.user_id)

                 

              FROM jivedw_activity_agg_month, jivedw_container, jivedw_month

               

              WHERE jivedw_container.dw_container_id= jivedw_activity_agg_month.dw_container_id

                  AND jivedw_activity_agg_month.month_id=jivedw_month.month_id

                  AND jivedw_month.month_of_year=2 --(this is the number of the month constraint)

                  AND jivedw_month.year =2014 --(This is the Year constraint)

                  AND jivedw_container.container_type =14 --(for spaces, other container codes can be put here)

                  AND jivedw_container.name like 'Community Reporting' -- (container name)

                 

               

              GROUP BY jivedw_container.name

               

               

              Let me know if you have any questions.

            • Re: SQL query for seeing who has viewed a space?
              mark.cablayan

              Hey Nick,

               

              This will get you all of the distinct users that accessed a space--you'll need to change the container_id and container_type to match the space your looking for:

               

              select distinct ju.username

              from jivedw_activity_fact jf

                  inner join jivedw_container jc on jf.dw_container_id = jc.dw_container_id and jc.container_type = 14

                      and container_id = 1234

                  left outer join public.jivedw_user ju on jf.user_id = ju.user_id

               

              Hope this helps.