User Profile Lookup.sql

DECLARE @name VARCHAR(100)
SET @name='%Smith%'

SELECT U.DISPLAY_NAME AS DisplayName, LP.HIERARCHICAL_NAME AS PrimaryLocation, U.USER_NAME AS UserName, U.EMAIL AS Email, S.DISPLAY_NAME AS ReportsTo, U.IS_INACTIVE AS Inactive, U.IS_DISABLED AS Disabled
FROM ENGINE.USER_SETTINGS U
LEFT JOIN DATACENTER.LOCATION_PROFILE LP ON U.PRIMARY_LOCATION_ID=LP.LOCATION_PROFILE_ID
LEFT JOIN ENGINE.USER_SETTINGS S ON U.REPORTS_TO_ID=S.USER_ID
WHERE U.IS_GROUP=0
  AND U.DISPLAY_NAME LIKE @name
ORDER BY 1