SharePoint Audiences

We use audiences extensively and required a way to do a recon on which users are in which audiences. Obviously we can run some C# to get this but I got stuck with some arb error message and permission issues (nothing new for SharePoint). So I decided to do the “evil” and query the database directly. The table are in the User Profile database. Here are some example scripts:

— Gets the Audience Name
SELECT [OrgleID]
,[OrgleName]
,[OrgleNameDescription]
,[OwnerAccountName]
,[PartitionID]
FROM [SPS2010_UserProfileService_Profile].[dbo].[Orgle_List] where orglename = ‘[Audience Name]’

— Gets the Audience Memberships Count
SELECT TOP 1000 [OrgleID]
,[QueryCount]
,[GroupCount]
,[GroupType]
,[MemberShipCount]
,[ComputationSeconds]
,[OrgleLock]
,[LastRuleUpdate]
,[LastPropertyUpdate]
,[LastUpdateStart]
,[OrgleQueryID]
,[LastUpdate]
,[LastErrorID]
,[LastTouchTime]
,[CreateTime]
,[PartitionID]
FROM [SPS2010_UserProfileService_Profile].[dbo].[Orgle_Stats] where OrgleID = (SELECT [OrgleID] FROM [SPS2010_UserProfileService_Profile].[dbo].[Orgle_List] where orglename = ‘[Audience Name]’)

— Gets the full audience list
SELECT [OrgleID]
,[OrgleQueryID]
,[UserRecordID]
,[NTName]
,[PreferredName]
,[Email]
,[SID]
,[Manager]
,[bDeleted]
,[PictureUrl]
FROM
[SPS2010_UserProfileService_Profile].[dbo].[Orgle_QueryResult],
[SPS2010_UserProfileService_Profile].[dbo].[UserProfile_Full]
WHERE
OrgleID = (SELECT [OrgleID] FROM [SPS2010_UserProfileService_Profile].[dbo].[Orgle_List] where orglename = ‘[Audience Name]’) AND
[SPS2010_UserProfileService_Profile].[dbo].[Orgle_QueryResult].UserRecordID = [SPS2010_UserProfileService_Profile].[dbo].[UserProfile_Full].RecordID