Tag Archives: User Profiles

SharePoint | Creating A Skills Matrix From SharePoint User Profiles Properties

So recently on a LinkedIn discussion it was asked if it was possible to build an out of the box Skills Matrix from the information stored in the Skills field of the SharePoint User Profiles.  Having written about how to create a SQL View to get User Profile Property Bag Values in a previous post, I thought this would be another interesting exercise on a real world use for these views.

Setting up the view is pretty simple, here is the structure we’ll use (See original post for how to set everything up):

CREATE VIEW [dbo].[vw_UserProfilePropertyBag]
AS
SELECT     upf.NTName AS Login, upf.PreferredName AS Name, upf.Email, upf.LastUserUpdate AS Modified, pl.PropertyName AS Property, upv.PropertyVal AS Value, 
                      upv.SecondaryVal AS SecondaryValue
FROM         SharePoint_User_ProfileDB.dbo.UserProfile_Full AS upf INNER JOIN
                      SharePoint_User_ProfileDB.dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
                      SharePoint_User_ProfileDB.dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID

Quick note, in my original posting last year, I only looked for value.  However, with certain SharePoint fields where keywords are used, Value ends up being a GUID and what we’re really interested in is the SecondaryValue field which has human readable content, or as I like to call them actual words.

Once you have your view, running it will produce some nice results:

Login

Name
Email
Modified
Property

Value

SecondaryValue
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 UserProfile_GUID 1A89D7FA-BLAH-BLAH-BLAH-1B5C0A3642DD NULL
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SID 0x010500000000000BLAHBLABLAHBLA NULL
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 AccountName Domain\Admin NULL
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 FirstName Admin NULL

 

Looking at the Property field, we can find the specific property we want (You can also go into Central Admin and manage User Profile Properties to see what the field names are called, but a lot of them will be self-evident by either their name or the values they contain) which in this case would be the “SPS-Skills” property, so a quick query of our PropertyBag view for just this Property will give us a great list of all the profiles with their associated skills:

SELECT *
  FROM [WSS_Content_Views].[dbo].[vw_UserProfilePropertyBag]
  where Property = 'SPS-Skills'

Login

Name
Email
Modified
Property

Value

SecondaryValue
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills D272CB6F-9286-4DFA-A62A-48AA4F189B3B Mad skills
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills 26F8647A-DE5C-48A1-A923-8E791093E23C C#
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills 6B8B9757-F7D8-4C8C-908B-873956DCD78C SharePoint
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills 86519AFC-3643-4D49-A77C-BBBE373EAFFC Social Media

So now that we have our raw data we can do a couple different things with it that aren’t possible with out of the box SharePoint.  Obviously we can query for a specific name to give us a list of their skills, but we can do that already in SharePoint by just looking at their user profile page.  However, we can also query for a specific SecondaryValue, and get a list of all the people who have that skill in their profile.  If we want to take this a step further and create a skills matrix of all the users in the company, just follow these steps:

  1. Run the query for SPS-Skills in something like SQL Manager
  2. Highlight the data in SQL Manager results table and copy and paste it into a new Excel Workbook
  3. Now highlight the data in Excel (I use Excel 2013), click the INSERT tab and click PivotTable
  4. image
  5. If you’ve done everything right so far, a dialog box will pop up looking like this:
  6. image
  7. Click the OK button to add the PivotTable into a new worksheet.
  8. From here, you can set up a basic PivotTable to show you a Users/Skills matrix by selecting the fields and dropping them into the correct Rows/Columns/Values regions:
  9. image
  10. And now you have what could be a very large User/Skills matrix.

That’s just the basics, obviously there’s lots of room to improve the functionality and usability.  You can for instance link the data as a query an external SQL data source.  You can include additional properties so you can further filter the PivotTable by department or office location.  You could event publish the Excel spreadsheet to SharePoint and use Excel services and web parts to expose the data to the end users on their own SharePoint sites.  So with a little creativity and a couple of views you can create some very slick “no-code” applications that rely on data stored in the User Profiles.

SharePoint 2013 – Importing User Profiles from AD LDS

So we’re going out of the box here, standard disclaimer is that MS won’t support the use of the cmdlets referenced in this post for on premises deployments.

Interestingly, SharePoint 2013 does not officially support the importing of AD Lightweight Directory Services, and it is generally recommended that we approach it with the same sneakernet approach of LDIF files.  Interesting that with 2013’s new method of importing user attributed from Active Directory (AD Direct Mode) they did not include support for LDS.  But at least they managed to bypass the somewhat clumsy method of using Forefront Identity Manager (FIM) and the UPS service (Everyone remember the “stuck on starting” issue and having the Farm Admin in the local admin group?).  This is especially confusing as they also refer to it as the “light-weight Active Directory Import option”.  In our case, we’ll need the UPS to get LDS attributes into our SharePoint environment.  Still have to use the “old” ways, and in general this method is not something you want to do for a production environment.

 

image

There are numerous limitations to this method, and of course there are numerous reasons why reading users and attributes from LDS would be useful, especially when dealing with test data LDS.

I think MS gives their reasons for not officially supporting due to Admin limitations (really???)  However, PowerShell again comes to the rescue for those of us looking to get our environments up quickly with an LDS set.

Even though SharePoint 2013 does not officially support importing from AD LDS, one can import users from AD LDS using the *-SPProfileSyncConnection  SharePoint PowerShell cmdlets:

#First get our UA and say we're not going to use the ILM
$UA = @(Get-SPServiceApplication | ? {$_.TypeName -eq "User Profile Service Application"})
$UA.NoILMUsed = 1
$UA.Update()

#Now set up the connection to the LDS
Add-SPProfileSyncConnection -ProfileServiceApplication $UA -ConnectionForestName <FQDN of the AD LDS Server> -ConnectionDomain <Domain of the AD LDS User with Replicate Directory Permissions> -ConnectionUserName <AD LDS User with replicate directory permissions> -ConnectionPassword <AD LDS Password of user with Replicate DIrectory Permissions> -ConnectionSynchronizationOU <DN of OU To be imported>

 

While this does work, it has some major drawbacks, as the cmdlet is meant for Office 365 installs, not on premise and has other drawbacks such as ignoring the NetBIOSDomainNamesEnabled flag and various other limitations.:

  1. The account running the PowerShell host must be added as an administrator for the UPA

    This isn’t really a limitation but it upsets purists. Instead of using the Proxy, we need the UPA itself, and this means we must have at least the Manage User Profiles administration rights on the UPA. If you don’t have this and attempt to run the cmdlet, you will receive the generic error from FIM, “MOSS MA Not Found”.
  2. There is no DisplayName parameter

    The name of the connection will be the NETBIOS name of the domain, i.e. the ConnectionDomain parameter. This will also be used for the Description. This also means that you can add only one connection per domain. Now this is strong recommended practice, but it prevents some scenarios from being possible with this cmdlet and is a major oversight.
  3. There’s no option to create more than one connection per forest

    Since you have to specify the ConnectionDomain parameter. Again more than one connection per forest is strongly discouraged but there are numerous scenarios where this is needed. Again this cmdlet is no use to you if you are in that boat.
  4. If you specify the same ConnectionDomain parameter, the system will overwrite the ConnectionSynchronizationOU, ConnectionUserName and ConnectionPassword parameters.
  5. If the connection cannot be created due to a FIM error, the command completes

    No errors are reported at all! We still need to use miisclient.exe to verify things have worked!
  6. Remove-SPProfileSyncConnection does not delete sync connections!

    The Remove-SPProfileSyncConnection cmdlet only removes the ConnectionSynchronizationOU specified, will not delete the connection itself. There is no way to use these cmdlets to delete sync connections.
  7. These cmdlets only work for Active Directory Sync Connections

But as I said, it’s good for loading in all sorts of test data without having to deal with the production AD server on the network and most of the limitations will not affect small installs or test farms.

SharePoint 2013–Interesting features

SharePoint is now more social

Microsoft recently unveiled their 2013 preview, and has made some significant strides in adding social to its collaboration platform, specifically in the user profile department.  User profiles are now more social than ever, with the ability to post descriptions and add links to discussions and documents shared by the user.  You can also follow any object on SharePoint including lists, documents, libraries and even sites.

What is also interesting is that Microsoft has changed terminology for permissions.  It is now called “Sharing”, very similar to Google’s docs.

Speaking of taking the best of some third parties, Facebook and Twitter get a nod with the Community Site and Community Portal templates, with things like mentions (@) and hash tags (#) added to discussions.  This makes it easy to not only follow people but see what they are following and collaborate on a whole new level.

Clouds and Apps

Introduced in this version of SharePoint are Apps.  The new App store will be home to what Microsoft may be planning as the rival to iTunes and Google Play. What will probably give corporate Information Security nightmares will allow the user to be able to try an app and have it installed for that user (or companies can buy apps to be made available to all corporate users).  In many ways the apps replace the concept of features, and out of the box I noticed that what used to be called document libraries and other specialized lists are now called “apps”.

Whether running on-premise or in the cloud, there seems to be very little difference to the look and feel, another nod to Microsoft’s big push to move solutions towards the cloud in a big way.  I am reminded of Bill Gates’ speech from way back in 2008 at Stanford University:

Today, we’re still very device-centric, and we rely on the user to move information between their phones, and their phones and their PCs, and their PCs and their PCs. Well, as we get this sort of unlimited power in the cloud, both in terms of computation and storage, the ability to move that data automatically so that if you buy a new phone your information just shows up, if you borrow a PC your data is there but only available to you, that will become commonplace.

It’s all about data.  I believe that very slowly we’ll see an erosion of the personal computer and in many ways we’re moving back towards the paradigm of the mainframe.  Everything sits out in the cloud and is accessed through dumb-terminals and other devices.

Steve Ballmer said it best during his speech at University of Washington on March 4th, 2010:

"This is the bet for the company, for the cloud, we’re all in."

And I believe with this next version of SharePoint they’re quickly moving in that direction.  PC’s don’t look like they used to 10 years ago, neither do phones.  It’s no longer about they user experience siting at a desk running an application, but about using a device and accessing your data.

SharePoint 2010–SQL View to get User Profile Property Bag Values

Ok, first the disclaimer:

Microsoft does not endorse or support the direct manipulation of the Content or Service Application databases directly (with the exception of the logging DB). Doing so would invalidate your support with Microsoft. I recommend that you query the API if you need access to this information programmatically.

Now for the good stuff. There may be cases where you need to expose or review the User Profile properties and for whatever reason the API just isn’t a good solution. For example, you are interfacing with a legacy system that does not understand Web Services but you want to expose a current list of all SharePoint users and their associated properties for systems like an external phonebook or a Roles-Based access system. But let’s say that the system is only able to interface with something like SQL views or CSV files (yuk!). Here is a solution that should be somewhat future-proof (at least until the next version of Office/SharePoint 15 comes around).

1. Create an empty Database on your SharePoint SQL Server. Per my standards, I always call it “WSS_Content_Views”.

a. I HIGHLY recommend that you never-ever create a view, table, or stored procedure inside an actual live SharePoint Content database as these can and do get overwritten/dropped/die when you apply a patch, cumulative update, or service pack.

2. Run the following script:

CREATE VIEW [dbo].[vw_UserProfilePropertyBag]
AS
SELECT     upf.NTName AS Login, upf.PreferredName AS Name, upf.Email, upf.LastUserUpdate AS Modified, pl.PropertyName AS Property, upv.PropertyVal AS Value
FROM         SharePoint_User_ProfileDB.dbo.UserProfile_Full AS upf INNER JOIN
                      SharePoint_User_ProfileDB.dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
                      SharePoint_User_ProfileDB.dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID

EDIT: In some cases, the PropertyVal will be a GUID identifier, and the human readable values we want are stored in SecondaryVal, so in order to get both values you’ll want to create a view that looks like this instead:

CREATE VIEW [dbo].[vw_UserProfilePropertyBag]
AS
SELECT     upf.NTName AS Login, upf.PreferredName AS Name, upf.Email, upf.LastUserUpdate AS Modified, pl.PropertyName AS Property, upv.PropertyVal AS Value, 
                      upv.SecondaryVal AS SecondaryValue
FROM         SharePoint_User_ProfileDB.dbo.UserProfile_Full AS upf INNER JOIN
                      SharePoint_User_ProfileDB.dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
                      SharePoint_User_ProfileDB.dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID

 

This is assuming that SharePoint_User_ProfileDB is where your user profile information is stored. Out of the box it usually looks something like this: “User Profile Service Application_ProfileDB_c09fc7e8a1c444d9a9822583c7545ab3”; However spaces and GUIDS in databases always to bug me so I usually change the DB name during the setup.

3. Now you can query against the view, using the following:

SELECT * FROM [WSS_Content_Views].[dbo].[vw_UserProfilePropertyBag]

4. Your result set will contain the following, listing out each property pairing that you have set up in the User Profile, including custom properties:

Login

Name
Email
Modified
Property

Value

Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 UserProfile_GUID 1A89D7FA-BLAH-BLAH-BLAH-1B5C0A3642DD
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SID 0x010500000000000BLAHBLAHBLAHBLAHBLABLAHBLAHBLAHBLA
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 AccountName Domain\Admin
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 FirstName Admin

5. From this master view of the User Profile Property Bag, you can then create further customized views to roll-up the information that the external application might need into one row per Login, specifying only the specific fields that you want to expose to the external application. For example, if you wanted a basic phonebook view:

CREATE VIEW [dbo].[vw_BasicExternalPhonebook]
AS
SELECT     dbo.vw_UserProfilePropertyBag.Login, dbo.vw_UserProfilePropertyBag.Name, dbo.vw_UserProfilePropertyBag.Email,
                      dbo.vw_UserProfilePropertyBag.Value AS Manager, vw_UserProfilePropertyBag_1.Value AS Title, vw_UserProfilePropertyBag_2.Value AS WorkPhone
FROM         dbo.vw_UserProfilePropertyBag LEFT OUTER JOIN
                      dbo.vw_UserProfilePropertyBag AS vw_UserProfilePropertyBag_2 ON dbo.vw_UserProfilePropertyBag.Login = vw_UserProfilePropertyBag_2.Login LEFT OUTER JOIN
                      dbo.vw_UserProfilePropertyBag AS vw_UserProfilePropertyBag_1 ON dbo.vw_UserProfilePropertyBag.Login = vw_UserProfilePropertyBag_1.Login
WHERE     (dbo.vw_UserProfilePropertyBag.Property = N'Manager') AND (vw_UserProfilePropertyBag_1.Property = N'Title') AND
                      (vw_UserProfilePropertyBag_2.Property = N'WorkPhone')

Will create a view with one line for each Login that contains the following fields:

Login Name Email Manager Title WorkPhone

This solution isn’t totally elegant, but neither is querying for data directly from a SharePoint database.

SharePoint 2010–User Information Lists and User Profile Cleanup

Recently I was working on a farm and finally got around to being able to enable the User Profile Sync to Active Directory.  Everything worked beautifully, but there was a problem in SharePoint with orphaned users in the UIL.  There are two reasons why obsolete users or groups can exist in the SharePoint Server 2010 user profile store:

  • Obsolete users: The My Site cleanup timer job is not active. The User Profile Synchronization timer job marks for deletion users who have been deleted from the directory source. When the My Site cleanup job runs, it looks for all users marked for deletion and deletes their profiles. Respective My Sites are then assigned to the manager for the deleted user and an e-mail message notifies the manager of this deletion.
  • Obsolete users and groups: Users and groups that were not imported by Profile Synchronization exist in the user profile store. This can occur, for example, if you upgraded from an earlier version of SharePoint Server and chose to only synchronize a subset of domains with SharePoint Server 2010.

Since this was upgraded from a 2007 farm and there was a period of time when the Farm was not syncing with AD, we had a period of time when several colleagues were removed from AD who had been using the new upgrade farm; However, they still had entries in the UIL, and once the UPA was set up to sync with AD, SharePoint orphaned these UIL entries.  The solution for this is a pretty simple PowerShell script.

1. Open SharePoint PowerShell window.

2. Enter the following in order to get the User Profile Service guid.

Get-spserviceapplication

3. Now using the UPS ID, type the following

$upa = Get-spserviceapplication <identity>

4. To see your Orphaned Accounts, use the following:

Set-SPProfileServiceApplication $upa -GetNonImportedObjects $true

5. Assuming everything looks as it should and you want to get rid of these Orphaned Accounts, use the following command (which is the point of no return as there is no recycle bin for these orphaned users).

Set-SPProfileServiceApplication $upa -PurgeNonImportedObjects $true

And now your orphaned accounts in the User Information List is all cleaned up!