Tag Archives: SharePoint 2007

Gathering SharePoint Sites Data using SQL

Ever got into a large farm environment where you kind of lost track of who is using the sites, which sites there are and how you could contact your user base? Sure, tools are available to give you some kind of BI solution that could help you in this respect.

But I  just wanted a simple view of all the site collections that are within a particular web application (possibly split over multiple content databases) and for each site collection, the logins and email addresses of the people who are within the ‘Owner’ group or similar named groups. As an extra bonus, I wanted to extract the current site Quota, the quota they already used en when the site was last changed.

Like said, some of this information is already available in some form, but I found it hard to get a complete simple overview of all site collections, especially if the numbers start exceeding the thousands. Final requirements,  it should be fast enough to be delivered upon request and the output should be easy to manipulate using analysis tools.

So, couple of ways to get this information, but in fact, only two. Either obtain the information through the object model (with either C# or PowerShell) or use the more dirty (and unsupported) approach of querying the database directly. Although I am kind of in favor of doing it the correct way (i.e. through the object model), this has one very big disadvantage. It is SLOW and has quite some impact on the resources of a server. If your site collections start approaching the thousands or ten-thousands , then running the script could take a considerable amount of time. And we do not want that, do we?

So, for this one time, I decided to go the direct route. Because the query is just going to read the database, there is no danger of corrupting the database. But keep in mind that the database may change during service packs and that a read action may block a write action, because of the lock on the table. But because the query will run for a very short time and outside office hours, I find it acceptable.

Below is the query I used to get the data. Now, I am no SQL expert, so the query could probably be fine tuned or even simplified. Any suggestions in that area are well appreciated. But this one works 😉 It will give you the following information for each site collection in your content database:

— Id (GUID) of the site
— FullUrl (of the root web)
— Site Title
— Current Disk Consumption (bytes)
— Current Quota (bytes)
— Creation date
— Latest change
— Nr Sub webs
— Comma separated string with owner logins
— Comma separated string with owner email addresses
— Comma separated string with all user logins that have access to the site
— Comma separated string with all users email addresses that have access to the site

You can further expand or decrease the list if you want to obviously. Below is the script that I created to get the data:

Select
  Sites.Id
, Webs.FullUrl
, Webs.Title
, Sites.DiskUsed
, Sites.DiskQuota
, Sites.TimeCreated
, Sites.LastContentChange
, SiteWebs.NrWebs
, USERDETAILS.OwnerLogins
, USERDETAILS.OwnerEmail
, USERDETAILS.ALLLogins
, USERDETAILS.AllEmail
From Sites
 inner join Webs on Sites.RootWebId = Webs.Id
 inner join (Select SiteId, Count(*) as NrWebs from Webs group by SiteId) SiteWebs On Sites.Id = SiteWebs.SiteId
 inner Join
 (SELECT Sites.Id
   , (SELECT STUFF(
    (SELECT ‘;’ + CAST(UserInfo.tp_Login as VARCHAR(100)) 
     FROM UserInfo
   inner join Groups on Sites.Id = Groups.SiteId
   inner join GroupMembership on GroupMembership.MemberId = UserInfo.tp_Id
    and GroupMembership.GroupId = Groups.Id
    and GroupMembership.SiteId = Sites.Id
     WHERE tp_SiteId = Sites.Id
    and Groups.Title LIKE ‘%owner%’
     FOR XML PATH(”)),1,1,”)
   ) OWNERLOGINS
   , (SELECT STUFF(
    (SELECT ‘;’ + CAST(UserInfo.tp_Email as VARCHAR(100)) 
     FROM UserInfo
    inner join Groups on Sites.Id = Groups.SiteId
    inner join GroupMembership on GroupMembership.MemberId = UserInfo.tp_Id
    and GroupMembership.GroupId = Groups.Id
    and GroupMembership.SiteId = Sites.Id
     WHERE tp_SiteId = Sites.Id
    and Groups.Title LIKE ‘%owner%’
     FOR XML PATH(”)),1,1,”)
   ) OWNEREMAIL
   , (SELECT STUFF(
    (SELECT ‘;’ + CAST(UserInfo.tp_Login as VARCHAR(100)) 
     FROM UserInfo
   inner join Groups on Sites.Id = Groups.SiteId
   inner join GroupMembership on GroupMembership.MemberId = UserInfo.tp_Id
    and GroupMembership.GroupId = Groups.Id
    and GroupMembership.SiteId = Sites.Id
     WHERE tp_SiteId = Sites.Id
     FOR XML PATH(”)),1,1,”)
   ) ALLLOGINS
   , (SELECT STUFF(
    (SELECT ‘;’ + CAST(UserInfo.tp_Email as VARCHAR(100)) 
     FROM UserInfo
    inner join Groups on Sites.Id = Groups.SiteId
    inner join GroupMembership on GroupMembership.MemberId = UserInfo.tp_Id
    and GroupMembership.GroupId = Groups.Id
    and GroupMembership.SiteId = Sites.Id
     WHERE tp_SiteId = Sites.Id
     FOR XML PATH(”)),1,1,”)
   ) ALLEMAIL
 FROM Sites) USERDETAILS ON USERDETAILS.Id = Sites.Id
order by TimeCreated

The only thing is that this script can be run on a single database at the same time. What if you have multiple databases? Well, quite easy, because our web application in the object model knows which content databases are assigned to him, like below:

SPWebApplication app = SPWebApplication.Lookup(uri);

Console.WriteLine(“Gathering content databases…”);
SPContentDatabaseCollection dbCollection = app.ContentDatabases;

Console.WriteLine(“Gathering statistics…”);
foreach (SPContentDatabase db in dbCollection)
{
}

The SPContentDatabase object also holds a property called DatabaseConnectionString, which we can use to connect to the database. Sweet right? Final thing you we should solve are the multiple result sets. Well, we can use the DataSet.Merge() method for that.  So, get each result set in a local DataSet and merge it with the master.

Then you can export the DataSet in any way you like. This is really fast, only a couple of seconds to get the data over thousands of site collections.

Finally, some caveats in this approach:

1. It is not officially supported, although there is not much that can go wrong.
2. Retest the app after a Service Pack or hotfix, as the database schema might have changed.
3. The security context in which the app runs needs to have access to the databases. Basically, the application pool account of the web application has these permissions.

Let me know if you have other ways of getting these results in a fast way, preferably without 3rd party code. Also let me know if you can achieve the same result with a less complex SQL statement 😉

Advertisements

SharePoint 2010 Guidance

Developing applications for SharePoint could be a challenge at some times. This becomes even more true when different project teams from different suppliers work on solutions for a single paltform. This requires sufficient rules and guidelines to ensure that applications can co-exist and support can be provided without to much of diversity between the several applications.

For SharePoint 2007 and 2010, Microsoft created the SharePoint Guidance. This comprises a set of best practices and libraries that gives developers some starting points and guidelines on how to accomplish certain things within SharePoint 2007/2010. This also includes some common application scenario’s that can help designers to design their SharePoint application and provides a common ground between development teams. The version for SharePoint 2010 has been released last month.

The SharePoint guidance package is also supported by a community to help develop and assist in the use, which is certainly beneficial.  I would strongly recommend any designer or developer for SharePoint to check both out.

You can find the SharePoint Guidance at the following links:

SharePoint 2007

SharePoint 2010

As always, happy coding and have fun!

XSS leak in SharePoint 2007

So, been a while since my last post. This is caused by the dutch elections and of course the World Championship soccer in South Africa. General outcome, dutch team advanced to the next round and political landscape is a mess. Let us focus on SharePoint once again 😉

During a security audit, one of our customers encountered a security breach in SharePoint, caused by the help.aspx. More specifically, /_layouts/help.aspx. It is possible on this page to insert JavaScript in the url and modify the page layout, offering a possibility for phishing.  An explanaion of the breach can be found here.

We have reported this to Microsoft and they have released a hotfix to counter this problem. The hotfix is reported in KB arcticle KB 2028554. In intranet situations, the risk is minimal as the attack needs to come from the inside. But for internet facing sites, this could pose a problem. You can easily test whether or not your site is prone to the breach, by using below url on your site:

http://<enteryoursitehere>/_layouts/help.aspx?cid0=MS.WSS.manifest.xml%00%3Cscript%3Ealert%28%27XSS%27%29%3C/script%3E&tid=X

If your site is prone, you will see a JavaScript popup stating your site is hacked. For our customer, we requested the hotfix from MS and deployed it to our test environment. What is important to note here is that, like all hotfixes, there are two versions of the hotfix (or actually 4). One for WSS and one for MOSS. These are also mentioned in different knowledge base articles, to keep things simple.

WSS 3.0 hotfix: KB983444
MOSS hotfix: KB979445

Both have 32 and 64 bit versions, which totals to 4. The fix we are looking for resides in the WSS 3.0 hotfix, although applying the MOSS hotfix also on a environment that is running MOSS is obviously a good thing. But to resolve the matter at hand, only the WSS hotfix is needed.

 Till next time.

Using Powershell to change registry keys for search crawler

In one of my earlier posts, I described a way to use PowerShell to change settings in a SharePoint 2007 farm.  That started out as my first PowerShell script and I have witnessed the power behind it all.

In our project, we needed a server wide adjustment of the registry for the SharePoint index servers across all farms. Of course, multiple ways to do this, but here is where PowerShell shows its power. I needed to change two registry settings, both covering the maximum file size that is crawled by the indexer. By default, this is 16 MB (mutiplied by 4), leaving a total of 64 MB indexed in each file. As we now enlarged trhe maximum upload size to 100 MB, we needed the crawl settings to be adjusted. To be honest, I was quite surprised it would only take me two lines of script to make this work. The registry keys in question were those below:

HKLM\SYSTEM\CURRENTCONTROLSET\
HKLM\SOFTWARE\MICROSOFT\OFFICE SERVER\12.0\SEARCH\GLOBAL\GATHERING MANAGER

And to change these, the following lines of script would suffice. I have also added some additional lines to check whether we actually are at the index server, otherwise, these changes would have no effect. Pay special attention to the way the registry is accessed. In principle, it is accessed as a file path.

Set Search Crawler Settings

Write-Host “Set registry values for search”
Write-Host “=======================================================================”
Write-Host “”

if ( (Get-Itemproperty  -Path “hklm:\SOFTWARE\Microsoft\Office Server\12.0\Search\Global”).Index -eq 1 ) {
    Write-Host -f green “Validated that this server is the index Server ” 
} else  {
    throw “This server is the not the SharePoint index Server ” 
}

Write-Host “Processing MaxTextFilterBytes”
set-itemproperty “hklm:\system\currentcontrolset\control\contentindex” MaxTextFilterBytes -type “DWord” -value “104857600” -force

Write-Host -f green “Done…”
Write-Host “Processing MaxDownloadSize”
set-itemproperty “hklm:\software\microsoft\office server\12.0\search\global\gathering manager” MaxDownloadSize -type “DWord” -value “26214400” -force

Write-Host -f green “Done…”
Write-Host -f green “Script completed”
Write-Host “=======================================================================”
Write-Host “”

In addition to these registry changes, a couple of more settings need to be set to get the entire maximum upload size working. Above registry changes instruct the search crawler to enlarge the crawled file size. But this still does not allow SharePoint to upload larger files.  Besides SharePoint changes, OS level changes also need to be make on the client side to allow the WebDAV protocol to upload larger files. These are however out of scope of this blog post. In short, three steps are needed to support larger files on SharePoint:

1. Change the crawler settings to support larger files in the registry.
2. Change the Maximum File Size for the Web Application.
3. Change the connection time out setting on the SharePoint search service. (OSearch)

To change the crawler settings, the PowerShell script is located above. To execute steps 2 and 3 in above list, the following PowerShell scripts do the job:

Set Maximum File Size for Web Application

[Void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”)
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
if ($farm -eq $null ) {
    throw “Could not connect to the SharePoint farm”
}

Write-Host “”
Write-Host “====================================================================”
Write-Host “”
$websvcs = new-object  Microsoft.SharePoint.Administration.SPWebServiceCollection($farm)
if ($websvcs.Length -eq 0 ) {
    throw “Could not connect to any SharePoint Web Service in the farm”
}
$FileSize = 100
$MaximumFileSize = 0

foreach ($websvc in $websvcs) {
    foreach ($webapp in $websvc.WebApplications) {
 if (($webapp.MaximumFileSize -ne $FileSize) -and ($webapp.MaximumFileSize -lt $FileSize))
     {
  Write-Host “Set file size for web application $webapp.Name”
  $webapp.MaximumFileSize = $FileSize
  $MaximumFileSize = $webapp.MaximumFileSize
  $webapp.Update()
  Write-Host -f green “New file size $MaximumFileSize MB”
 } else {
         Write-Host -f green “Maximum file size for $($webapp.Name) was already set to or larger than $FileSize MB”
 }
    }
}
Write-Host -f green “Script completed”
Write-Host “====================================================================”

Set Connection TimeOut for Search Crawler

[Void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”)
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
if ($farm -eq $null ) {
    throw “Could not connect to the SharePoint farm”
}

Write-Host “====================================================================”
Write-Host “”

$searchsvcs = @($farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.Office.Server.Search.Administration.SearchService]})
if ($searchsvcs.Length -eq 0 ) {
    throw “Could not connect to any SharePoint Search Service in the farm”
}

$TimeOut = 90
foreach ($searchsvc in $searchsvcs) {
    if (($searchsvc.ConnectionTimeOut -ne $Timeout) -or ($searchsvc.AcknowledgementTimeout -ne $Timeout))
    {
        Write-Host “Set connection and acknoledgement timeouts for $($searchsvc.Name) to $Timeout”
        $searchsvc.ConnectionTimeOut = $Timeout;
        $searchsvc.AcknowledgementTimeout = $Timeout;
        $searchsvc.Update()
 Write-Host -f green “Done…”

    } else {
        Write-Host -f green “Connection timeout for $($searchsvc.Name) was already set to $Timeout”
    }
}

Write-Host -f green “Script completed”
Write-Host “====================================================================”

Thats it! Using three simple scripts, we can change the settings in our entire farm to use new file upload settings and have it supported by the sarch crawler.

CU!

Using PowerShell 1.0 to change web application setting in SharePoint 2007

Sure, PowerShell is already available for a long time on Windows 2003, also in combination with SharePoint 2007. But untill this time, I could avoid the use 😉

But with PowerShell 2.0 becoming more important to SharePoint 2010, it was time to dive a little bit into this scripting language.
At my current customer, the maximum upload size for documents needed to be increased to 100 MB, from the default setting of 50 Mb. Obviously, we could do this using the Central Admin, but it becomes more of a problem when there are a lot of web applications, hence the choice for scripting. Also, a lot of other settings on different layers need to be adjusted to make this work, for example WebDAV settings in Vista and registry settings for the crawler, but this post only covers the PowerShell script to change the setting in the web application general settings.

So, below is my first PowerShell script. Note that this script is by no means the best one. It could be extended with exception handling, parameters to specify action and size, but for the purpose of this post, it is clear enough.

[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local

Write-Host("Get all web applications within each web service")
$websvcs = @($farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]})

foreach ($websvc in $websvcs) {
  foreach ($webpp in $websvc.WebApplications) {
    $webapp.MaximumFileSize = 100
    $webapp.Update()
  }
}

Not much exiting stuff going on here right? First I load the Microsoft.SharePoint.dll assembly by calling the LoadWithPartialName method of the System.Reflection.Assembly class. Because this method is static, we use the ‘::’ operator. Once loaded, we get the local farm by calling the SPFarm.Local method. Again, becuase Local is a static method, we use the ‘::’ operator.

When done, we get all SPWebService objects within the Services collection of the Farm. This line is a little less obvious. In C#, we would use the SPFarm.Services.GetValue<SPWebService>() method. In PowerShell, we filter the Services collection by using a sort of SQL like syntax. Get all services where type (GetType()) equals (-eq) Microsoft.SharePoint.Administration.SPWebService. The rest speaks for itself and looks quite a lot like C# code.

There you have it, my first PowerShell 😉 Naturally, we could create a simple command line utility that does the same using C#, but these scripts are created faster and are also easy adjustable to fit needs.

So, in short, with SharePoint 2010 embracing PowerShell, we have no choice then to venture in the world of PowerShell.

Till next time!

Migrating to SharePoint 2010 – Some thoughts

With Microsoft SharePoint 2010 approaching for official release on May 12th 2010 (earlier for MSDN, SA, VL licenses), businesses are already looking at how we can easily migrate to 2010.

So is it just click and install? Or are any special arrangements needed? Yesterday, we tested one of our custom extension packs on the new platform (RC).
There is some good news and some bad news 😉 The good news is that features contained in the WSP pack were installed without a problem. The bad news is that some of the features in the pack introduced problems when activated.

Basically, all standard features, like content types, document libraries, list definitions and site definitions worked without a problem. Also the eventhandlers attached were also correctly attached in 2010.
So far so good. Features that were dependant on the 12 hive (for whatever reason, it is obviously not good practice) have a problem as the 12 hive is now called 14 😉

The features that did not work upon install were those that were dependant or interacted with Shared Service Provider components. As the architecture behind these are complete different in SharePoint 2010, I did not expected it to work without a fight anyway 🙂 The move to Service Applications, where the individual components of the Shared Service Provider were disconnected and isolated, caused several of my features to fail, as they could not find the Shared Service Provider. Also, some of the classes used for UserProfiles are now deprecated and replaced.

Another thing I noticed was that when I created a document library from my installed doc lib definition, it would be created, but function like the 2007 version. The row selection, checkboxes and 2010 look and feel were not available, meaning that the xml definition also needs an upgrade if you fully want to utilize the 2010 functions.

In short, some actions are indeed needed, before one could easily move to 2010. For standard sites though, that do not contain any drastic customizations, it is failry easy, using the content database attach method.

Nice day everone!

How to use assembly redirection in WSP’s

This article was posted on my previous blog located at http://blog.avanadeadvisor.com/blogs/patrick_boom in February. I moved it here.

Assembly redirection, a technique already introduced and available since the first versions of the .NET framework, is in my opinion not widely used in our line of work.
Sure, product suppliers like Microsoft itself use it frequently during updates, but somehow I have not come across this too often in my career.

To explain the concept of assembly redirection is a little bit beyond the scope of this blog, but I would like to elaborate on using this when using it with SharePoint 2007 WSP’s.

In large projects, especially with a lot of users in an already live environment, executing updates on the platform should be done with care and clear preparation. This is even truer when the update at hand involves shared frameworks, which multiple other projects use and you do not have direct control over. Of course, one could update the component without changing its version number and strong name, but this would not be very good practice, as version management and maintenance become much harder, if not impossible. The answer, of course, is assembly redirection.

Assembly redirection involves a technique to forward a particular method call to another assembly at runtime. For example, an application is bound to version 1.0.0.0 of your component. When updating, you increment the version number to 1.1.0.0. When published, the application will still use version 1.0.0.0 of the assembly if it is still available. If not, it will break. Using assembly redirection, you can forward the call to your 1.1.0.0 version and even remove the 1.0.0.0 version. This would automatically upgrade any application that referenced it and in the process, you make sure the latest version is used.

One very important aspect of this method, is that you have to ensure, no guarantee, that you are backwards compatible.  Not doing this is bound to introduce problems. Secondly, you should not change the definition of the method in doing something completely different than its original scope, which could lead to unexpected behavior of its clients.

That said, how does this relate to SharePoint 2007? Well, suppose I have a SharePoint middleware layer that executes various common functions, on top of which SharePoint application are build. Using good practice, I publish this layer to the SharePoint platform using a WSP (or multiple), which in turn configures SharePoint (for example the SafeControls) and publishes the assemblies to the Global Assembly Cache. When time passes, I would like to update the framework by introducing new features (which would lead to a version increment) and bug fixes. I would then publish an updated WSP, which will retract the old one (and thus removing the configuration and the assemblies from the GAC) and install the new one. Doing this without assembly redirection will break all applications that reference the assemblies.

Ok, so, just include an assembly redirection policy, you think. That is where the trouble starts. To install an assembly redirection policy in the GAC, you need to convert it into an assembly, as the GAC only accepts assemblies. There are many articles on the internet that describe how to create such an assembly, so I will not go into that here. I use, like many others, WSP Builder (or its Visual Studio Extension) to create my WSP’s. If I include the policy assemblies in the GAC folder, WSP builder will also package that. Well, so far so good. When I install this WSP into SharePoint, it will result in an error, stating that the policy assemblies could not be published into the GAC. This is caused by a simple caveat in using policy assemblies. Because they are not ‘real’ assemblies in the true sense of the word, they are only allowed to be published to the GAC, if the original configuration file that was used to create the assembly, is located in the same directory. And the WSP omits this file. The current version of WSP builder does not include .config files when packaging, even if they are located in the same directory.

There are several ways in solving this problem. You could create your WSP manually, be writing the correct DDL that does includes the config files in the root of the WSP. You could modify WSP builder (as it is open source) to include these config files in the root of the WSP. I however, choose the outside approach. As a WSP is just a cabinet file with a different extension, I created a small app that uses cablib.dll (just like WSP Builder) to modify the created wsp and insert the config files at the root. This has two advantages:

  1. The config files are only needed during install, so using this method would avoid the config files being included in the manifest.xml and therefore permanently stored on the file system.
  2. I can choose whether or not to include the config files, or even update them afterwards, without rebuilding the WSP.

After this, the WSP installs just fine and the policy assemblies are included in the GAC, ensuring proper rerouting of method calls to old assemblies, therefore automatically upgrading any dependent applications in the SharePoint landscape.

A couple of final notes on the redirection part:

  1. Create one policy assembly for each version you wish to reroute. You cannot reroute multiple versions from within a single policy assembly.
  2. The naming of the policy redirection assembly is very strict and follows naming conventions. Not following these conventions will result in your assembly not being redirected.
    policy.<major>.<minor>.<assembly name>.dll
  3. Ensure that you only use the major and minor versions in the AssemblyVersion attribute. Build and revision numbers should never be included in the AssemblyVersion attribute, but in the AssemblyFileVersion attribute. For example:
    AssemblyVersion 1.0.0.0, AssemblyFileVersion 1.0.010043.0 (day 43 in the year 2010)
    AssemblyVersion 2.1.0.0, AssemblyFileVersion 2.1.010043.1

Assembly references are made using the AssemblyVersion attribute, thus giving you the possibility to publish bug fixes (which only increment the build and revision numbers), without the need for a policy redirection assembly.

Hope this can be of use to you. Let me know your thoughts! Any comments are appreciated.