5 Replies Latest reply on Apr 28, 2017 12:09 PM by Madhusudhan Khambham

    Need to pull the users who never accessed my project

    Madhusudhan Khambham

      Hi All,

       

      I am trying to develop one report which should give the list of users who never accessed my project.

      I was able to get who accessed it. But, not the users who never accessed.

       

      Can someone help me by providing some suggestions.

       

      Thanks in Advance.

      Madhu K

        • 1. Re: Need to pull the users who never accessed my project
          Jeff Strauss

          Madhu, do you have SQL for the users that accessed your project?  It should be easy enough to reorganize it to have the users table be the primary and the left outer join to your project / workbook data.

          • 2. Re: Need to pull the users who never accessed my project
            Madhusudhan Khambham

            Thanks Jeff,

             

            Will try with this and update you.

             

            Thanks & Regards

            Madhu K

            • 3. Re: Need to pull the users who never accessed my project
              Madhusudhan Khambham

              Hi Jeff,

               

              No Luck Jeff.

               

              For users who accessed the project at least once i used Historical events table left outer join with hist users and  inner join with users table. I was basically checking the created at date field and counting the number of users accessed.

               

              Please correct me, if it is the right approach or i am going wrong.

               

              Thanks & Regards

              Madhu K

              • 4. Re: Need to pull the users who never accessed my project
                Jeff Strauss

                Hi Madhu.

                 

                Try this out and let me know if it works.  On my initial test, it seems to be working, though I didn't 100% validate yet.

                 

                The concept is:

                1. Get a list of all users from hist_users

                2. Join the list of users up to the historical_events and build a result set of users + project - this gets you the users that have accessed the project

                3. Take this list of users + project and do a where not in with an inner join to eliminate the users that have accessed the project

                 

                 

                select distinct

                hu.name || ' ' || coalesce(he.pname, ' ') username_and_project

                from

                hist_users hu

                left outer join (select p.id, p.name pname, hist_actor_user_id from historical_events he, hist_projects p

                                 where historical_event_type_id = '84' and he.hist_project_id = p.id and p.name = 'PROJECT_NAME') he

                on hu.id = he.hist_actor_user_id

                where

                hu.name || ' ' || coalesce(he.pname, ' ') not in

                                           (select hu.name || ' ' || coalesce(he.pname, ' ') from

                                            hist_users hu inner join (select p.id, p.name pname, hist_actor_user_id from historical_events he, hist_projects p

                                                                      where historical_event_type_id = '84'

                                                                      and he.hist_project_id = p.id) he

                                                          on hu.id = he.hist_actor_user_id

                                            where he.pname = 'PROJECT_NAME'

                                             )

                1 of 1 people found this helpful
                • 5. Re: Need to pull the users who never accessed my project
                  Madhusudhan Khambham

                  Thanks  a lot Jeff for taking time and helping me, Sorry to respond little late, I didn't checked my inbox.

                   

                  I took a different approach and were able to pull the numbers i was looking for.

                   

                  Since we have a AD group and assign the permissions to access my project.

                   

                  step 1: I took the (Groups) inner join (group_users) inner join (_users)  - I took the count of the users.

                  Step 2:  I took (historical_events) left outer join (hist_users) inner join(_users) - took the count of the users whose value is not null.

                  step 3 : blended these two data sources.

                  Step 4 : used Size() function and User name in the Rows shelf and filtered the Null values coming from the User Name field of Step 2.

                   

                  It gave me the count of users who didn't accessed till now.

                   

                  Thanks & Regards

                  Madhu K