Protiviti / SharePoint Blog

SharePoint Blog

July 03
PowerShell as a Troubleshooting Tool

I like puzzles. Back in the day it was the Rubik’s cube. Today, I have multiple wooden puzzles on my desk. Working with SharePoint is sometimes a puzzle. One of the tools that I often fall back to when solving a SharePoint puzzle is PowerShell. In the past I might have written a quick console application to perform a repetitive task or search through lots of content on a site looking for a certain issue. Starting with SharePoint 2010, the availability of PowerShell with specific SharePoint cmdlets makes it even easier to do quick troubleshooting.




The Puzzle

Working with a client recently, I came across a situation where the sorted results from a custom search web part were sorted for the first few pages but then started over at the beginning of the alphabet again. It wasn’t the same items repeated—it was other items that should have been sorted together with the first group. One additional detail of the sorting of these items is important. A Yes/No field was added to the content type of these items to allow specific items to be highlighted at the top of the list. So the sorting of the results was first by this Highlight field and then alphabetically by Title.

The Hunch

The items that were marked with the Highlight flag were properly showing at the top of the list of results. I had a hunch that the division of the remaining items fell into these two groups:

1) items that were created or edited after adding the Highlight field; and

2) items that existed before the field was added and they had not been edited.

Did you know that a Yes/No field actually has three states? Yes, No, and null. Null values always add fun to data analysis. I’m not going to get into that religious discussion today—maybe another time.

The Test

So how do I test this theory? PowerShell to the rescue! On your SharePoint server under the “Microsoft SharePoint 2010 Products” group you will see “SharePoint 2010 Management Shell.” Run this to open a PowerShell window with the SharePoint cmdlets already loaded for you.

 

From here you can quickly get a site collection with Get-SPSite, get a site with Get-SPWeb or use the OpenWeb method on the SPSite object. (Yes, I still use my WingtipServer VM for testing. Thank you, Andrew Connell.)

 

What I want to do with PowerShell is to see what is being returned in this Highlight field for my search results. I found a nice starting point for a script to query the SharePoint Search through the object model at PowerShell Code Repository (http://poshcode.org/1304). With a few tweaks I had this PowerShell function that I used:

function Find-FullTextQueryResults

{

Param(

    [Parameter(Mandatory=$true)][string]$SiteUrl,

    [Parameter(Mandatory=$true)][string]$QueryText,

    [Parameter()][int]$RowLimit = 100

)

    Add-PSSnapIn Microsoft.SharePoint.PowerShell -EA 0

    $site = Get-SPSite -Identity $SiteUrl

    $q = New-Object Microsoft.Office.Server.Search.Query.FullTextSqlQuery -Arg $site

    $q.RowLimit = $RowLimit

    $q.ResultTypes = [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults

    $q.QueryText = $QueryText

    $tables = $q.Execute()

    $results = $tables["RelevantResults"]

 

    # Create a collection of objects to return

    $output = @()

 

    while ($results.Read()) {

        $o = New-Object PSObject

 

        # Add each returned field to the object

        0..($results.FieldCount-1) | foreach {

            Add-Member -InputObject $o -MemberType "NoteProperty" -Name $results.GetName($_) -Value $results[$_] #.ToString()

        }

 

        $output += $o

    }

 

    return $output

}

 

Side Note

The FullTextSqlQuery part of the script is very similar to something you would do in C# in a custom web part, for example. But there are a couple of interesting PowerShell tips to point out if you’re not aware of them.  First creating a new collection to return a set of objects is done with this line:

    $output = @()

 

Then objects can be added to that empty collection to be returned later. This is very helpful in a function where you want the results to be able to be consumed by other cmdlets.

The foreach cmdlet is useful for looping through a collection of objects, as in

    Get-SPWeb | foreach { $_.Owner }

 

But what if you just want to loop through the numbers 1 through 10 and do something? Another tip that comes in handy is creating a range of numbers as a collection on the fly. For this simple example,  1..10 does just that. Or in the real example of the function above, this statement gets the indexes for the $results collection:

    0..($results.FieldCount-1)

 

Enough of that rabbit trail. Using the Find-FullTextQueryResults function defined above we can run a command like this:

Find-FullTextQueryResults -SiteUrl http://wingtipserver -RowLimit 10 -QueryText "SELECT Title, Rank, Size, Description, Write, Path, Highlight FROM Scope() WHERE ( (""SCOPE"" = 'Products') ) ORDER BY ""Highlight"" DESC, ""Title"" ASC"

 

The results show that the Highlight field for the first couple items is 1 (the highlighted items that correctly show first). For the next group of items Highlight is 0, and they are sorted by Title. But then there is another group of items where the Highlight field is blank, and they are again sorted by Title. Just as I expected!

Solved

Viewing the item properties of these items shows the Highlight field as a checkbox, so it only shows two states. I couldn’t distinguish between a 0 (false) value and a value that was not set. They both showed as unchecked. So with some PowerShell I was able to reproduce the list of search result items that I was seeing on the web site; but more importantly, I was able to see the difference in values for the field that I couldn’t otherwise see.

 

With that knowledge, I was able to proceed to the next step and update all the empty values to 0, but that’s some PowerShell to show another day.

Quick Launch


© Protiviti 2019. All rights reserved.   |   Privacy Policy