3 Replies Latest reply on Aug 22, 2016 7:05 AM by Shaaer Hassan

    Inspector query for users with multiple roles

    Eric Bruce Apprentice

      I am trying to write a query for inspector that would use data from ariba.user.core.Role and ariba.user.core.User

       

      The goal is to identify every user where

       

      a. has role 'Overseas User'

      AND

      b. has role 'Contract Manager'

       

      select * from ariba.user.core.User where "Roles".UniqueName like 'Contract Manager' and "Roles".UniqueName like 'Overseas User'

       

      This runs with no results but I know via the GUI that there are users who meet this criteria. Am I simply unaware of the correct syntax here?

        • Re: Inspector query for users with multiple roles
          Sandeep Jain Master

          Hello Eric,

           

          Please try the below query -

           

          SELECT u,u.UniqueName,u.Name,r.UniqueName,r.Name FROM ariba.user.core.User u LEFT OUTER JOIN ariba.user.core.Role r USING u."Roles" where r.UniqueName IN ('Contract Manager','Overseas User') ORDER BY u.UniqueName,r.UniqueName

           

          Hope that works.

           

          Thanks

          Sandeep Jain

          1 of 1 people found this helpful
            • Re: Inspector query for users with multiple roles
              Eric Bruce Apprentice

              This returns what appears to be the same results: users with 'Overseas User' and users with 'Contract Manager' roles.

               

              My goal is to only return the users whose account contains both roles.

               

              Changing the query as below returns no results.

               

              SELECT u,u.UniqueName,u.Name,r.UniqueName,r.Name FROM ariba.user.core.User u LEFT OUTER JOIN ariba.user.core.Role r USING u."Roles" where r.UniqueName ='Contract Manager' and r.UniqueName = 'Overseas User' ORDER BY u.UniqueName,r.UniqueName