SharePoint Report – Missing Managers

Trying to roll out SharePoint at an org can have it’s hard times. One is trying to get User Profile sync working well and making sure the Org Browser works well goes along with that. You can sync over the manager from AD and everything falls into place, but their might be users you are pulling that don’t have a manager set. Now of course you can query AD for this, but you would have to already know the filters and OU’s you are pulling into SharePoint. Another way to do this (and of course, disclaimer here, don’t try this at home if you are scared of querying SharePoint databases, and yes, it probably isn’t recommend, but I am doing it anyways). Here is a query you can use to get the User’s with no manager, and also join it back to get some other attributes such as department, office, and title so you can figure out where they are and who their manager might be (helpful in a larger org). You can easily throw this in an SSRS report, and have it email whomever maintains the managers in AD or in your organization. (Note, SP2010_ProfileDB might not be the name of your actual profile DB, you would have to change that in the query below)

SELECT up.RecordId,PreferredName,NTName,Email, office.Office, titles.JobTitle, dept.Department
	FROM dbo.UserProfile_Full up
	LEFT OUTER JOIN (SELECT [RecordID]
      ,[PropertyVal] AS 'Office'
  FROM [SP2010_ProfileDB].[dbo].[UserProfileValue]
  WHERE PropertyID = 11) office ON up.RecordId = office.RecordId
	
	LEFT OUTER JOIN (SELECT [RecordID]
      ,[PropertyVal] AS 'JobTitle'
  FROM [SP2010_ProfileDB].[dbo].[UserProfileValue]
  WHERE PropertyID = 13) titles ON up.RecordId = titles.RecordId
	
	LEFT OUTER JOIN (SELECT [RecordID]
      ,[PropertyVal] AS 'Department'
  FROM [SP2010_ProfileDB].[dbo].[UserProfileValue]
  WHERE PropertyID = 14)dept ON up.RecordId = dept.RecordId
	
	WHERE
	Manager IS NULL 
	ORDER BY Office
Advertisements

One thought on “SharePoint Report – Missing Managers”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s