Update to REST API

Still playing around with the REST API. You can read the official Microsoft documentation HERE.

You can add filters to your URL string to refine what items you see. Try this with a Choice column:

http://%5Byoursite%5D/_vti_bin/listdata.svc/%5BList title]?$filter=[Choice column title]/Value eq ‘[Value you want to filter on]’

Be sure to remove the square brackets and everything in between. Also, be sure to pick a choice column that does not allow multiple selections. I’m still playing around and learning more every time I try something, but I figured I would share this little nugget.

SharePoint’s REST API and PowerPivot will change your life

Make sure – before you read any more of this post – you have ADO.Net Data Service Update for .NET Framework 3.5 SP1 installed in your environment. Linkage:  http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=2343

Now with that out of the way, let me say again, the SharePoint REST API and PowerPivot will change your life.

Slap this into the URL Address bar:


From there you’ll be presented with a list of all the lists and libraries that are compatible with the REST API. Grab the list you’re interested in and tack it on to the end of the above URL like so (case sensitive so be as exact as possible):

http://site.net/sites/sitename/_vti_bin/listdata.svc/%5BList Name]

You should then be presented with an ATOM feed (looks something like an RSS feed). You may or may not see much, which is fine. The background XML has all the metadata of the list/library you’ll need.

Next, fire up Excel with the PowerPivot add-in installed. Open the PowerPivot window and select “From Data Feeds.”


Paste in the URL above (e.g. http://site.net/sites/sitename/_vti_bin/listdata.svc/%5BList Name]) and then click Next. It’ll chew on that for a second. Then click Finish. The list will now be imported into memory (i.e. PowerPivot does its thing). Keep in mind here that the REST API only imports 1000 items in at a time. However, for a lot of lists and use cases this is more than sufficient. [See update below.]

Close the PowerPivot window. Go back to Excel and click Insert > Pivot Table/Chart. Then click the “external data source” radio button and then click “Choose Connection”


Find the PowerPivot Data connection you just created above and click Open. Then click OK.

From here you can add all the Values, Slicers, Filters, etc. you want.


Now how will this change your life? The applications of this are endless. One example could be a project list sliced and diced to your users’ liking and displayed via Excel Services web part. Users get the familiar Excel look and feel while you can rest comfortably knowing that they’re not manipulating your list data. You get far more control over the look and feel too as opposed to PerformancePoint. In addition, you get the ability to integrate outside data as well (try integrating TFS or Oracle data in PerformancePoint). Winking smile

But why not just use SSRS? Good question. Not everyone has access to SSRS. That takes a considerable amount of time, energy, resources, and cash to get that going. But again, with the ability to use slicers and allow your users to see what they want, when they want, that alone makes this a pretty powerful feature in your bag of tricks.

Anyone else tried this? Any other use cases come to mind?

UPDATE 4/20/2012

So only 1,000 items will show up in the ATOM view; however, PowerPivot can pull ALL the items down. VERY COOL! I tested it on a list with +32K items and PowerPivot had no problem.

SharePoint Joel beat me to it!: SharePoint on a Mac

While I’ve been busy working on my Mac all week, SharePoint Joel has posted two really good posts that pertain to SharePoint on a Mac; namely ActiveX. You can see his browser support post HERE and his latest post about ActiveX HERE.

Overall there are some subtle, and not so subtle differences when viewing SharePoint on a Mac. From a user perspective the experience is the same, for the most part.

Let’s start with the optimal configuration:

  • Office for Mac 2011
  • Safari (with username and password stored in Keychain) – I outline how to accomplish this HERE

Quite often Admins will tell you that FireFox is the ideal browser on Macs, and while I tend to agree, you’ll get an authentication prompt upon your first open of the site, something most users find insanely annoying. Safari (at least in my environment) does not prompt you when you have successfully saved your password to the keychain. Of course, your mileage may vary.

Issues with known workarounds

  • Rich Text Editor – user must upgrade column type to Enhanced Text in order to customize content; however, user may have ability to drop in html tags (have yet to test this).
  • Open with Explorer, Upload Multiple Documents – Office for Mac comes shipped with “Document Connection.” This little add-in will accomodate Explorer views (to some degree) and multiple uploads. I was a huge fan of this all week.
  • OneNote – There is no native support for OneNote on a Mac; however, you can edit OneNote’s in the Office Web App. Some may find this difficult to use long-term but I thought it was sufficient.

Known Limitations without workarounds

  • Connect to Outlook, Connect to Office, and Sync to SharePoint Workspace– Outlook for Mac does not connect to SharePoint, at all; Office does not connect to SharePoint, with a few slight exceptions; SharePoint Workspace is not offered on Macs.
  • Datasheet View – requires an ActiveX control, which Macs do not support.
  • Drag and Drop Web Parts – Cannot be moved by using drag and drop on Web Part pages. Users must click Edit on the Web Part, select Modify Web Part, and then select the zone from the Layout section of the Web Part properties page. Web Parts can be moved using drag and drop on Pages.
  • Explorer View – not available on Macs.
  • File upload and copy, New Document, Slide Libraries – requires ActiveX control.
  • Editing PowerPivots – now while this may not be a SharePoint thing per se, it’s incredibly frustrating that I can’t edit PowerPivot workbooks. I can’t even edit it in the Office Web App. Throw me a bone here Microsoft. This one bullet point will be a non-issue for a good majority of you out there.

Overall the experience was pretty good. I now have a better understanding of the challenges Mac users have to deal with when viewing SharePoint. Microsoft has tried to make accommodations for Mac users. I have a great appreciation for Document Connection, and personally I think it’s better than SharePoint Workspace since it’s so scaled down.

Ultimately, I found the amount of ActiveX in SharePoint to be overwhelming. I have almost a decade of SharePoint experience.  I know the workarounds and the tips and tricks to get the job done. The average user, not so much.  For me, I found little things like the lack of Windows Explorer, PowerPivot support, and no support for dragging and dropping web parts to be mildly annoying. I’d be interested to hear what the hard-core Mac user’s opinion is and see if they would find these items to be small nuisances or major roadblocks to full-on adoption.  Annoyances/possible roadblocks aside, I still feel from a document and web content mgmt standpoint that there aren’t many tools that equal (or surpass) SharePoint’s power, but we have a long way to go before the Apple faithful jump-in head first. With the Mac users at Trek we’re going to start small and work our way out.

Next steps for me will to do an even more comprehensive write-up specific to Trek and put on a SharePoint for Mac Lunch ‘N Learn. Stay tuned.

Mac Experiment Week: Day #1

So this week I’ll be using a Mac as my primary device at work. Figured I should see how the “other” half lives so I can help them out when they have SharePoint issues.

Initial reactions:
1. I am a hotkey fanatic. More so than I ever thought.
2. Links lists in My Content are an extraordinary idea. Maybe I should take my own advice from time to time.
3. CoRD is one of the best RDP managers I’ve used regardless of OS.

I’ll continue to chronicle my experiences this week here and on Twitter. Any helpful software, hotkeys, advice, etc. you can provide would be much appreciated.

Add Form Filter to filter single column

A manager had a request the other day for a filter that would allow him to quickly search for an item in a list by the ID. I had done this numerous times at my former employer but couldn’t remember how to. I reached out to 2 former colleagues (Erich and Erik) and they helped me out.

  1. Site Actions > Edit Page
  2. Add or Insert web part
  3. Choose Forms > HTML Form Web Part
  4. Edit Web Part
  5. Click Source Editor button in edit web part pane
  6. Add the following code
  7. Click OK
  8. Choose Connections > Provide Form Values to > Title of the list you wish to filter in the web part’s dropdown
  9. Leave “Get Filter Values From” and click Configure
  10. Choose the Consumer Field Name you wish to filter
    Note: You cannot choose more than one column with this type of filter
  11. Click Finish
  12. Click Stop Editing in the Ribbon

You can also find more details around this on Microsoft’s site:  LINK.

BBQ and poor web site performance

For those who don’t know, I participate in BBQ competitions in throughout the year with my father and brother (link to our team site: www.logjamminbbq.com).

Last night, registration for the Sam’s Club National BBQ Tour events started. There just so happens to be one here in Madison July 14th through the 15th.

I figured I’d get to the site a few minutes early to beat the rush. At 6:55 PM – on the dot – I plugged www.kcbs.us into my browser and I was met with this:


Surely they had to have a contingency plan for this sort of traffic. Several of last year’s event sold out in 20 minutes.

I was able to get registered for the event but it took close to 20 minutes, 2 computers, and my iPhone. Others weren’t so lucky. Furthermore, KCBS’ site performance was so bad that it also took down BBQ Brethren (a popular message board for BBQ enthusiasts) for several minutes with people trying to post about their inability to get registered for the Sam’s Club events. There’s no excuse for site performance like this, especially when hosting plans and Amazon’s EC2 offerings are so cheap.

Thinking about all of this lead me to ask the question: How would you have handled this if you were the administrator for KCBS’ site? Bigger hosting plan? Amazon EC2? Rackspace? Office 365?

Troubleshooting SharePoint Timer Service Issues

From time to time I’ll come into work and notice that the Timer Service will be stopping unexpectedly. Every minute or so the timer service will stop and the server will start it again within another minute or two. It’s quite annoying and I’ve spent many a wasted morning tangling with it.  This post will be continually updated as I wrestle with the beast. Here are the steps I have so far. Start at #1 and work your way down until the problem fixes itself. Try to give about 5-10 minutes between each step:

  1. If you notice in the service console that the timer service is currently running hit “Restart the service”
  2. Do a full stop then start of the Timer Service
  3. Clear timer cache on farm (link)
  4. Update spfarmacc credentials
    • stsadm -o updatefarmcredentials -userlogin domain[timer service account] -password <>
  5. Check to make sure the ForeFront Identity Manager is running on your Sync server

Will continue to update with steps as I learn more.

Event IDs 8311, 6803, 6110, and 6801 after new SSL certs installed and December CU

Read Technet Forum HERE

Read Stackexchange post HERE

About a week ago I installed new SSL certificates. Few days after that I configured the export thumbnailPhoto attribute for the User Profile Sync. I’ve never actually been able to successfully export the photos into AD. I see the following error in both the Event Viewer and the ULS logs:


An operation failed because the following certificate has validation errors:nnSubject Name: CN=[REDACTED], OU=Domain Control Validated – QuickSSL(R) Premium, OU=See http://www.geotrust.com/resources/cps (c)11, OU=2945119243, O=[REDACTED], C=US, SERIALNUMBER=[REDACTED]nIssuer Name: CN=GeoTrust DV SSL CA, OU=Domain Validated SSL, O=GeoTrust Inc., C=USnThumbprint:[REDACTED] nnErrors:nn SSL policy errors have been encountered. Error code ‘0x2’..

Not much help there. Contacted MS support and was told to install the latest CU (December 2011). Tested the CU earlier this week and decied to install during my change window yeseterday. Completed with no trouble and checkout went fine. I then try to kick off a Full Sync and now I’m seeing the 3 following Events in the logs:


The management agent “MOSS-[GUID]” failed on run profile “MOSS_FULLIMPORT_[GUID]” because the server encountered errors.


The management agent “MOSS-[GUID]” step execution completed on run profile “MOSS_FULLIMPORT_[GUID]” but the watermark was not saved.

Additional Information

Discovery Errors : “0”

Synchronization Errors : “0”

Metaverse Retry Errors : “0”

Export Errors : “0”

Warnings : “0”

User Action

View the management agent run history for details.


The extensible extension returned an unsupported error.

The stack trace is:

“System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. —> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.

at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)

at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)

at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)

at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)

at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)

at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)

at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)

at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)

at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)

at System.Threading.ExecutionContext.runTryCode(Object userData)

at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Net.TlsStream.ProcessAuthentication(LazyAsyncResult result)

at System.Net.TlsStream.Write(Byte[] buffer, Int32 offset, Int32 size)

at System.Net.PooledStream.Write(Byte[] buffer, Int32 offset, Int32 size)

at System.Net.ConnectStream.WriteHeaders(Boolean async)

— End of inner exception stack trace —

at System.Net.WebClient.DownloadDataInternal(Uri address, WebRequest& request)

at System.Net.WebClient.DownloadData(Uri address)

at Microsoft.Office.Server.UserProfiles.ManagementAgent.ProfileImportExportExtension.DownloadPictures(ProfileChangeData[] profiles)

at Microsoft.Office.Server.UserProfiles.ManagementAgent.ProfileImportExportExtension.Microsoft.MetadirectoryServices.IMAExtensibleFileImport.GenerateImportFile(String fileName, String connectTo, String user, String password, ConfigParameterCollection configParameters, Boolean fFullImport, TypeDescriptionCollection types, String& customData)

Forefront Identity Manager 4.0.2450.34”

Any insight? I’ll still continue to work with the MS Case Engineer but wondered if anyone has seen something similar. I’ll post the outcome when/if we ever get there. 😉

Updated – Issue Solved!

So the issue was the SSL certificate after all. I have 2 web apps on 1 server. I only serve up one of those web apps though and stop the other site in IIS. Because both apps (i.e. sites) are binded to port 443 they can’t have different certs. Only 1 cert to 1 port. (Note: we’ll be adding another virtual NIC which will alleviate this issue but I’ll cover that some other day). So the My Site web app – which is stopped – has the wrong cert applied but I don’t care since it’s stopped in IIS. Looks like SharePoint does care. Each time I attempted to run a Full Sync it would choke on the cert because it didn’t match the web app’s URL. Applied a wildcard cert and magically everything worked. I’ll have to manually switch the certs over in order to complete the Full Sync until the virtual NIC is added.

Large Lists and Datasheet view in 2010

I’ve been in a never-ending battle with 2 large lists (29K items). I migrated the lists from MOSS, which took a GREAT deal of time, patience, and effort (topic of another blog post). I got the lists migrated, views setup, and permissions all where they needed to be. The views consisted of datasheet views since the users of these lists were used to doing massive uploads at once. I got it all setup and thought “job well done.” Well not really…

Shortly after everything was setup users noticed that they would be hit with the list view threshold of 5000 items. This setting is a global setting, and one that you don’t want to change. Just trust me on this. But their views worked fine in MOSS. What gives?

…taken from: http://technet.microsoft.com/en-us/library/cc262813.aspx

In MOSS there was no limit on datasheet views like there is in 2010 (note highlighted text above). My guess is that datasheet views were treated like Standard views meaning it would filter on un-indexed columns no problem. SharePoint 2010 queries a little differently than MOSS did so if you have a filter in a datasheet view that should return 100 items based on an un-indexed column, but the list has 10,000 items, you’ll hit the list view threshold since it is querying all 10,000 items. Adding an index will alleviate this issue.

You’ll want to index the column that the view is filtering on. In this case it was the “Status” column. I added the index, SharePoint crunched on it for about 5 seconds, and then I tested it with several real users and a test user. The lists are as good as new!