SharePoint Powershell | Get Detailed Information about Correlation IDs in ULS

I use this often enough I thought I’d write a quick post about it.  Basically, whenever an issue occurs on SharePoint we get a nice friendly error message with a correlation ID, which is great for troubleshooting the issues assuming that we have a nice timeframe for when the issue occurred.  We can quickly open the ULS Viewer, locate the correct log file, and filter for the correlation ID.   Unless of course we don’t have a time frame or we run a large farm, then it becomes more of a biblical “seek and ye shall find” approach.

That is where the beauty of PowerShell comes in.  There is a great command called Get-SPLogEvent which allows us to access the ULS logs through PowerShell and returns results from a Unified Logging Service (ULS) trace log.

Now, this is a great tool in general for being able to write monitoring scripts for SharePoint by using the following:

Get-SPLogEvent -MinimumLevel "Error"

Use the StartTime and EndTime flags and you can basically monitor your system every couple of minutes and if an Error level item is returned you can send yourself an email using the Send-MailMessage command.

However, what inspired this post is that I yet again received an e-mail with a screenshot of a friendly SharePoint error page telling me “An unexpected error has occurred.” (Do we every have an expected error?).  So, logging into the server and firing up my handy PowerShell windows I can use the following to find the specific Correlation ID in the ULS to quickly diagnose the issue.

Bring up the SharePoint 2010 Management shell on your server and enter the following:

Get-SPLogEvent | ?{$_.Correlation -eq "Correlation ID of Error"} | ft Category, Message -Autosize

Substituting the “Correlation ID of Error” string for the GUID in your screenshot.

If you want more detail than just the Category  and Message, just select the additional fields:

Get-SPLogEvent | ?{$_.Correlation -eq "Correlation ID of Error"} | select Area, Category, Level, EventID, Message | Format-List

and if you want to pipe it into a file so you can email it off to your SharePoint support group (because sometimes in large organizations there is more than just one person responsible for the SharePoint farm) you can use this to create a file:

Get-SPLogEvent | ?{$_.Correlation -eq "Correlation ID of Error"} | select Area, Category, Level, EventID, Message | Format-List > c:\SPCorrelationErr.log

And just like that, easy ways to extract the information you are looking for to help troubleshoot your SharePoint farm.

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.