Patrick's SharePoint Blog

SharePoint's Booming world

Archive for the ‘SharePoint 2007’ Category

Gathering SharePoint Sites Data using SQL

Posted by Patrick Boom on August 5, 2010

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

Posted in SharePoint 2007 | Tagged: , , | 3 Comments »

SharePoint 2010 Guidance

Posted by Patrick Boom on July 5, 2010

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!

Posted in SharePoint 2007, SharePoint 2010 | Tagged: , , , | 2 Comments »

XSS leak in SharePoint 2007

Posted by Patrick Boom on June 24, 2010

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.

Posted in SharePoint 2007 | Tagged: , , , | Leave a Comment »

Using Powershell to change registry keys for search crawler

Posted by Patrick Boom on May 17, 2010

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!

Posted in PowerShell, SharePoint 2007 | Tagged: , , , , , | 5 Comments »

Migrating to SharePoint 2010 – Some Thoughts Part 2

Posted by Patrick Boom on May 6, 2010

This week, we got one of our customized applications from SharePoint 2007 to work on a SharePoint 2010 machine. As as described in my previous post this does not go without problems. We tried the content database move method, meaning we made a backup of one of our 2007 content databases and restored that database on the 2010 SQL server.

We then attached the database to the SharePoint 2010 farm by using the following command:

stsadm -o addcontentdb -url http://sp2010 -databasename SP2007_Content_DB -preserveolduserexperience true

This command will initiate an upgrade of the database. Once upgraded, the sites are accessible from SharePoint 2010. There are a couple of things we noticed when we restored sites this way.

1. We can upgrade their look and feel using the Visual Upgrade settings from the site settings menu. This will however overwrite any custom look and feel you might have enabled for your site.
2. Document libraries that came out of the box in the 2007 site will easily migrate to 2010, including all its features, like multiple select and the ribbon interaction. However, custom document libraries based on their own shema.xml, will continue to have the 2007 functionality, even if visually upgraded. So the multiple select, ribbon interaction and so on will not work in these libraries. They are usable though, but not with all 2010 features.

Prior to this exercise, we needed to make sure all necessary code and customizations were installed on the system. But our solution that created our own look and feel (like custom master pages and stylesheets) really messed up the entire environment. I would enable alright, but your site would be crippled. Worst thing though, it would not reset to its original setting, meaning our site was dead in the water. So be very carefull when upgrading to 2010 with visual elements like master pages and layouts.

Posted in SharePoint 2007, SharePoint 2010 | Tagged: , , , | Leave a Comment »

 
%d bloggers like this: