San Francisco Bay Area Endpoint Management User Group

 View Only
  • 1.  quick sql to show users manager

    Posted Aug 11, 2011 05:19 PM

    I was updating all my sql scripts and found one of my favorites still works on 7.0 and 7.1 as long as dbo.Inv_Global_Windows_Users is populated


    select ac.[name] ,ac.[last logon user],isnull([managerid],'No User ID')AS managerid
    from dbo.Inv_AeX_AC_Identification ac
    left outer join  (SELECT     u._ResourceGuid AS [user guid],
                        r.ChildResourceGuid AS [manager guid], u.UserId,  i.Name  AS managerid
                        FROM         dbo.ResourceAssociation r INNER JOIN 
                          dbo.vItem i ON r.ChildResourceGuid = i.Guid INNER JOIN  
                          dbo.Inv_Global_Windows_Users u ON r.ParentResourceGuid = u._ResourceGuid  
    WHERE     (r.ResourceAssociationTypeGuid = '049C633F-8413-42AE-93EA-F4EB7EDAFC65'))v2
        on ac.[last logon user] = v2.[userid]



  • 2.  RE: quick sql to show users manager

    Posted Aug 11, 2011 05:22 PM

    If you want to be more accurate you can use primary user

     

    --- Primary user to acidentify
    select ac.[name],ac.[last logon user],Prime.[primary user] ,v2.[userid],v2.managerid,gugd1.*
    from dbo.Inv_AeX_AC_Identification ac

    left outer join (SELECT     _ResourceGuid, MAX([user]) AS [primary user]  
    FROM         dbo.Inv_AeX_AC_Primary_User   
    WHERE     ([User] IS NOT NULL) AND ([User] <> '')
    GROUP BY _ResourceGuid  ) Prime                          
        on ac._resourceguid =  Prime._ResourceGuid

    left outer join  (SELECT     u._ResourceGuid AS [user guid],
                        r.ChildResourceGuid AS [manager guid], u.UserId,  i.Name  AS managerid
                        FROM         dbo.ResourceAssociation r INNER JOIN 
                          dbo.vItem i ON r.ChildResourceGuid = i.Guid INNER JOIN  
                          dbo.Inv_Global_Windows_Users u ON r.ParentResourceGuid = u._ResourceGuid  
    WHERE     (r.ResourceAssociationTypeGuid = '049C633F-8413-42AE-93EA-F4EB7EDAFC65'))v2
        on v2.[userid] =Prime.[primary user]
                                
    left outer join inv_global_user_general_details gugd1                          
        on v2.[manager guid] = gugd1._resourceguid