Stefan on SharePoint

Restricted Column Names for SharePoint Lists

StefanKeirGordon — Tue, 25 Aug 2009 06:29:00 -1400

Tried to create a column in my list called "UniqueId" today.

The column creates just fine, and works within the UI. However, if you try to programtically update the value SPListItem["UniqueId"] -- You'll quickly get an error.

This is because SharePoint realized that UniqueId was actually already in-use by the system, and named your column something like UniqueId1.

To find out what your actual field is named, go to edit your column and look at the end of the URL in the address bar. You should see your field name there.

Cheers!

How do custom Role Providers work in SharePoint?

stefankeirgordon — Fri, 21 Aug 2009 19:50:28 -1400

Lately I've been doing quite a bit of work with custom authorization scenarios in SharePoint, using the Microsoft Geneva framework for claims based security. This means custom membership and role providers which are relatively complicated.

Understanding how SharePoint uses your role provider is critical to designing and debugging even a modestly complicated security scenario. I've put together a flow-chart which hopefully helps explain when identities are created, what methods SharePoint calls, and just generally how your custom providers are used.

This is a work in progress, and I will include how membership providers are leveraged during assigning of permissions at a later date.

Click the image to see it full size.

SharePoint Stock Ticker

stefankeirgordon — Wed, 29 Jul 2009 20:24:14 -1400

A frequent corporate intranet requirement is to show a stock ticker value some where in the master page, or home page. Doing this using free services is a bit of a challenge, and the ideal scenario is to periodically cache the value into the site, where it can then be easily used without generating a large amount of traffic to the stock value provider.

Yahoo provides a stock service that given the proper URL, with return a comma delimited text result.

For example, try this URL:

http://finance.yahoo.com/d/quotes.csv?s=msft&f=l1c1

The ticker symbol, MSFT is self explanatory. The "l1c1" code says that we want two bits of information:

"l1" which is the last sell price
"c1" which is the last change value.

Here is a table of available codes for the Yahoo stock data. You can also put multiple symbols in the URL.

a	Ask	a2	Average Daily Volume	a5	Ask Size
b	Bid	b2	Ask (Real-time)	b3	Bid (Real-time)
b4	Book Value	b6	Bid Size	c	Change & Percent Change
c1	Change	c3	Commission	c6	Change (Real-time)
c8	After Hours Change (Real-time)	d	Dividend/Share	d1	Last Trade Date
d2	Trade Date	e	Earnings/Share	e1	Error Indication (returned for symbol changed / invalid)
e7	EPS Estimate Current Year	e8	EPS Estimate Next Year	e9	EPS Estimate Next Quarter
f6	Float Shares	g	Day's Low	h	Day's High
j	52-week Low	k	52-week High	g1	Holdings Gain Percent
g3	Annualized Gain	g4	Holdings Gain	g5	Holdings Gain Percent (Real-time)
g6	Holdings Gain (Real-time)	i	More Info	i5	Order Book (Real-time)
j1	Market Capitalization	j3	Market Cap (Real-time)	j4	EBITDA
j5	Change From 52-week Low	j6	Percent Change From 52-week Low	k1	Last Trade (Real-time) With Time
k2	Change Percent (Real-time)	k3	Last Trade Size	k4	Change From 52-week High
k5	Percebt Change From 52-week High	l	Last Trade (With Time)	l1	Last Trade (Price Only)
l2	High Limit	l3	Low Limit	m	Day's Range
m2	Day's Range (Real-time)	m3	50-day Moving Average	m4	200-day Moving Average
m5	Change From 200-day Moving Average	m6	Percent Change From 200-day Moving Average	m7	Change From 50-day Moving Average
m8	Percent Change From 50-day Moving Average	n	Name	n4	Notes
o	Open	p	Previous Close	p1	Price Paid
p2	Change in Percent	p5	Price/Sales	p6	Price/Book
q	Ex-Dividend Date	r	P/E Ratio	r1	Dividend Pay Date
r2	P/E Ratio (Real-time)	r5	PEG Ratio	r6	Price/EPS Estimate Current Year
r7	Price/EPS Estimate Next Year	s	Symbol	s1	Shares Owned
s7	Short Ratio	t1	Last Trade Time	t6	Trade Links
t7	Ticker Trend	t8	1 yr Target Price	v	Volume
v1	Holdings Value	v7	Holdings Value (Real-time)	w	52-week Range
w1	Day's Value Change	w4	Day's Value Change (Real-time)	x	Stock Exchange
y	Dividend Yield

To use these values in SharePoint, I've built a custom feature and timer job, which combined with a list in the root of your site, downloads updated stock values for your symbols every 5 minutes. You configure which symbols to download information for by adding them to your list.

Once you have the data safely in your list, you can show it on any page using a simple data view, or ideally a simple ajax call using jQuery against the lists.asmx web service to save your end-users some load-time.

The Solution

First let's look briefly at how the solution works, then we'll discuss how to install it, and provide all the downloads at the bottom of the post.

The solution is very simple, with a code file for the timer job, and a code file for the feature receiver, which will install our timer job when the feature is activated.

I started with Andrew Connell's Timer Job code, so the solution will build directly to a WSP from Visual Studio using the custom build targets. You can also read there about how the feature activation installs the job. I've changed this very little.

The actual stock data is downloaded with a trivial function leveraging the System.Net.WebClient as follows:

    private string[] StockData(string ticker)
    {
        System.Net.WebClient client = new WebClient();
        string csv = client.DownloadString(

               string.Format("http://finance.yahoo.com/d/quotes.csv?s={0}&f=l1c1",ticker));

        client.Dispose();
        return csv.Split(',');
    }

And here is the (somewhat rough) execute function that runs every 5 minutes to update your list:

    public override void Execute (Guid contentDbId) 
    {
      // get a reference to the current site collection's content database
      SPWebApplication webApplication = this.WebApplication;
      SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];

      
      if (contentDb.Sites.Count > 0)
      {
          try
          {
              SPList stockList = contentDb.Sites[0].RootWeb.Lists["StockValue"];

              if(stockList!=null)
              {
                  contentDb.Sites[0].RootWeb.AllowUnsafeUpdates = true;
                  
                  foreach (SPItem item in stockList.Items)
                  {
                      if (item.Fields.ContainsField("Symbol") &&

                          item.Fields.ContainsField("Value") && item.Fields.ContainsField("Change"))
                      {
                          string[] data = StockData(item["Symbol"].ToString());
                          item["Value"] = data[0];
                          item["Change"] = data[1];
                          item.Update();
                          
                      } 
                  }
                  contentDb.Sites[0].RootWeb.AllowUnsafeUpdates = false;
              }
          }
          catch 
          { 
              //You should consider adding logging
          }
      }
    }

Notice the way we get our list using the content DB, your mileage may vary. Logging would also be a nice addition.

Install

In addition to the solution, I've provided a WSP for those that would like to simply install this. Here is how you would use it:

Step 1:

Create a new list in the root of your site, called "StockValue"

Step 2:

Add columns to the list. All strings. "Symbol", "Value", and "Change".

"Single Line of Text" is the type for all three, everything else left as default.

Your list should look like this:

Step 3:

Add your ticker symbols to the list, by creating a new row and populating the Symbol field. You can optionally populate the Title field. You can add 1 or as many as you would like.

Step 4:

Install the solution. The WSP (SharePoint Solution) is available for download at the bottom of the post. You will install this as you would any other WSP. From a server on the farm use STSADM to add and deploy.

C:\>stsadm -o addsolution -filename c:\stefangordon.stockvaluejob.wsp

C:\>stsadm -o deploysolution -name stefangordon.stockvaluejob.wsp

               -immediate -allowgacdeployment

Give the farm a minute or two to deploy the solution.

Step 5:

Active the feature in your site collection, from Site Settings->Site Collection Features

This would be the site which has the list from step 2 in it, at the root, called "StockValue".

Step 6:

Wait 5 minutes, for the job to run and update your list. It should then contain the stock values. If it is after-hours, the change amount will always be 0.

You can go check the status of the job in Central Admin, to see if it has run:

Downloads

Download the Visual Studio 2008 solution.

Download the compiled WSP.

Customize SharePoint Search Rendering

stefankeirgordon — Tue, 21 Jul 2009 04:14:41 -1400

This morning I threw together some quick instructions for a client on customizing the Core Results Web Part and adding additional fields to the MOSS Search Results. I hadn't planned on posting it, but as I already had it done I thought it might help someone else. Certain text blacked out to protect the innocent!

Here we will be customizing the People Results page by adding a new link to the results, which passes the account name of the user as a query string parameter. This process would be the same to add any field to any of the results pages.

All the results rendering for your main search results is in the core results web part, so here I do a People search, edit the page and modify the core results part:

The part passes a bunch of data to the XSL we provide for rendering, but by default the field I would like to use, AccountName isn't passed. You can pass anything that a managed property exists for in the Managed Properties section of the Shared Service provider (under Search Settings).

If you look at the metadata property mappings, there is an Account Name already defined and mapped to the AD import field "People:AccountName(text)". This is the field I'll add to my search results.

Note: If you add a new field here, You'll need to do a full search crawl before it shows up.

So back in our Search Core Results edited web part, we choose "Selected Columns" under query options, and add this column into the mix:

You'll want to paste these snippets into an XML or text editor when you modify them, as the window that pops up is useless. You'll add one more entry to the list as shown below, for AccountName (case sensitive here)

Paste that back into the web part property, and go edit the XSL property:

The XSL in there is relatively small and manageable. You'll find a template called "Results" which is the actual result line-item rendering. It has several div's it renders out, Title, Description, srch-Metadata, etc.

The Search Metadata div is where they fit the URL's and such at the bottom of each entry, so it is where we want to add our link today. You could rewrite the entire results rendering if you'd like.

I did a simple case that just tacks the URL built from our new column on the next line. See the two additions in red I made to the XSL.

Note: Confusingly, the XSL is case sensitive on these columns as well, except it's a different case, they have to be all *lower* case. Example: In the columns XML I put "AccountName" but when I use it in the XSL, I must use "accountname".

My changes are below in red outline, they are as follows:

Added new variable called account based on our accountname column that was passed in. I will use this variable in string concatenation to generate my URL.
Used that variable (As well as the column) down below to add a new link in an HTML <a> tag.

When you are done editing, paste your XSL back into the XSL property for the web part (hopefully yours is prettier than mine) and you'll get your new results:

If it tells you it can't render the web part after you hit apply, something is wrong with your XSL.

Note: For more advanced XSL editing, you may want to use a real XSL editor such as Oxygen XML, which will let you test, debug, and preview your results. To do this you'll need to get a sample of the XML to work with, which you can do by pasting an "Identity" XSL into the XSL property, viewing the page source, and capturing your XML.

Identity XSL

stefankeirgordon — Tue, 21 Jul 2009 04:13:05 -1400

I'm constantly digging around to find a copy of an Identity XSL to use in my SharePoint development. As so much of SharePoint can be modified through XSL customizations, it's important to be able to get a copy of the source XML so you can go work in a proper XSL Debugger (such as Oxygen XML) to create your XSL files.

To do that, I use this identity XSL (for example, as my search results XSL) and it causes SharePoint to print out the raw source XML into the page source, where I can copy it and use it.

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <!-- IdentityTransform -->
    <xsl:template match="/ | @* | node()">
        <xsl:copy>
            <xsl:apply-templates select="@* | node()"/>
        </xsl:copy>
    </xsl:template>

</xsl:stylesheet>

Prepare Windows Server 2008 for SharePoint

stefankeirgordon — Mon, 20 Jul 2009 11:47:47 -1400

Before you can use Server 2008 (r1 or r2) as a Web Front End in your SharePoint farm, you'll need to enable some new roles.

With a new release of Server, and a new release of SharePoint on their way, I will have many posts up here regarding installations, so for the sake of normalization, I'll post a guide to adding these roles here, and refer to this post in future articles.

When you first start your Server 2008 box, you should get a panel for 'Initial Configuration Tasks'. You may choose to 'Add Roles' from here, or launch Server Manager and add roles from there.

You'll get the 'Add Roles Wizard', and you'll need to choose 'Web Server (IIS)'

Accept the prompts for prerequisites:

Add 'ASP.Net' and 'Windows Authentication' and accept prerequisites prompts again:

Finish the wizard, and you should get an 'Installation Succeeded' display:

Congratulations, you now have IIS and ASP.net working, so you can install SharePoint.

Monitoring Infrastructure Performance for SharePoint

stefankeirgordon — Fri, 17 Jul 2009 01:53:26 -1400

In a decent sized SharePoint farm there are *many* moving pieces. At least 5 servers, probably 10 nics, SAN's, SQL, MOSS, IIS, Load balancers and Kerberos issues. It can be difficult to figure out what is causing performance problems sometimes.

There are many good documents out there, especially the whitepaper on SharePoint Performance Optimization from MS IT, and simply familiarizing yourself with the common issues will help you identify many problems immediately.

That said, sometimes you can't get your hands on the hardware and the problem isn't obvious. Assuming you've verified SharePoint configuration and scanned the ULS logs first, I typically ask an Admin to pull these WMI Counters for me while generating load -- They are primarily on the SQL end, with the exception of checking NIC performance all around. I focus on that first, because 75% of the time I find the disk-subsystem or the NIC/Network is to blame.

Each one has a recommended range to look for. These were pulled from a few MS documents. Hope they help you in the future identifying an infrastructure problem.

Counters to monitor on SQL Server:

Logical Disk: Average Disk sec/Read (Read Latency).

This counter indicates the time it takes the disk to retrieve data. On well-tuned I/O subsystems, ideal values are 1-5 ms for disk containing sql logs, and 4-20 ms for disks containing data (ideally below 10 ms).

Logical Disk: Average Disk sec/Write (Write Latency).

This counter indicates the time it takes the disk to write the data. On well-tuned I/O subsystems, ideal values would be 1-5 ms for log, and 4-20 ms for data (ideally below 10 ms).

Logical Disk: Current Disk Queue Length.

For this counter, lower values are better. Values above 20 may indicate a bottleneck

On Web Front ends and SQL:

Processor: % Processor Time: _Total.

This counter should be kept between 50 percent and 75 percent.

System: Processor Queue Length: (N/A).

This should be below two times the number of CPU’s.

Memory: Pages/sec: (N/A).

Monitor this counter to ensure that it remains below 100.

Network: Network Queue Length counter.

Monitor under load, and watch for spikes.

Configure Kerberos For SharePoint

stefankeirgordon — Fri, 17 Jul 2009 01:47:28 -1400

There are many articles on the web regarding this seemingly daunting task. Today I ran into one that is by far the most complete, and easiest to follow. Hopefully this will help you, or your clients, get the job done more easily:

http://www.windowsecurity.com/articles/Kerberos-Sharepoint-Environment.html

Tips for Devs #1: Enumerate Farm Sites and Pages

StefanKeirGordon — Tue, 23 Jun 2009 08:14:00 -1400

I've decided to do a little series on SharePoint Tips for Developers, which will focus on common tasks that aren't immediately obvious, hopefully saving people some time in their day to day work.

Our first tip is about enumerating the objects in your farm. Specifically, taking an SPFarm object, finding all the web applications in the farm, all the sites, and all the pages within them. Some portion of that enumeration is likely to come up quite often in your work.

First, Getting all Web Applications in a farm is probably one of the lesser known tasks:

        SPWebService webService = farm.Services.GetValue<SPWebService>("");
        foreach (SPWebApplication webApp in webService.WebApplications)
        {
           //Do Something with Web Application here
        }

In the above example, the variable farm is an SPFarm object, which you can get in a variety of ways, depending where your code is running. In a timer job you might just do "this.Farm" -- in a web part you can get the farm from the site object in SPContext.Current.

Now we can go through all the sites and webs. Probably the simplest way to do this is to loop through the Sites collection of your web app, and call a recursive helper function on each web, which will enumerate through all child webs regardless of depth.

        foreach (SPSite site in webApp.Sites)
        {
            //Pass web to helper function to enumerate
            GetWeb(site.RootWeb);

        }

        private void GetWeb(SPWeb web)
        {
            //TODO: Do something with the web object

            //Process the sub webs recursively
            foreach (SPWeb subweb in web.Webs)
            {
                GetWeb(subweb);
            }
        }

Once you have the web, there are a variety of ways you might find the pages you are interested in. For example, if it is a publishing site you might want to find all the Pages libraries and get only the published pages -- You could do this with the web object you have available in GetWeb(...):

        foreach (SPList list in web.Lists)
        {
            if(list.Title == "Pages")
            {
                foreach (SPListItem item in list.Items)
                {      
                    if (item.File.Exists && 
                        item.File.Url.EndsWith(".aspx") && 
                        item.Versions[0].Level == SPFileLevel.Published)
                    {
                        //Do something clever with the page which is item.File.Url
                    }
                }
            }

Note that above we check if the item is published by looking at the level attribute of the version.

SharePoint at DEFCON

stefankeirgordon — Sun, 21 Jun 2009 17:38:56 -1400

July 31st will be a great day for SharePoint, as that's when the DEFCON 2009 conference kicks off, and this year the conference will actually have a session about SharePoint 2007!

So why are a bunch of hacker-types interested in an enterprise portal solution?

Well, the talk that will be given centers on the SharePoint 2007 Knowledge Network add-on -- For those that aren't familiar with it, it basically monitors your email interactions to learn about your skills and involvement in an organization, and then populates your SharePoint user profile with that information to enable people to leverage each other better.

Here is a screen shot of a Knowledge Network populates my site:

And of the profile manager:

From what I've heard, this functionality won't be included in SharePoint 2009, but undoubtedly Microsoft will continue to develop this type of technology to power the enterprise 2.0.