3 Replies Latest reply on Mar 23, 2011 2:41 AM by mitchella

    How do I create a Query that shows End Users and Analysts without Groups or Roles

    Rookie

      Hi All

       

      I've created a SQL Server back-end query that returns all user types and whether they have been allocated to a Group or Role.

       

      The Group allocation (or not) is determined by the field tps_primary_group_guid in table tps_user

      The Role allocation (or not) is determined by the field tps_role_id in table tps_user_role

       

      The Query syntax is as follows:

      SELECT     TOP(100) PERCENT dbo.tps_user.tps_title AS Expr1, dbo.tps_user.tps_primary_group_guid, dbo.tps_user_type.tps_name,

                          dbo.tps_user_role.tps_role_id

      FROM          dbo.tps_user INNER JOIN

                               dbo.tps_user_type ON dbo.tps_user.tps_user_type_guid = dbo.tps_user_type.tps_guid LEFT OUTER JOIN

                               dbo.tps_user_role ON dbo.tps_user.tps_guid = dbo.tps_user_role.tps_user_id

       

      When I attempt to create this as a query in LANDesk Query Designer I cannot seem to locate any of the guid's.

       

      Anyone have any ideas on how to create this in LANDesk Query Designer?

       

       

      Thanks
      Nigel.