Tuesday, 30 August 2011

Creating bulk Active Directory user and group accounts in PowerShell using Server 2008 R2 cmdlets

This one is a departure from my normal SharePoint-related articles. Back in the day, I maintained a few batch and VBS scripts that created multiple Active Directory users and groups from CSV files. These had obvious uses for creating test accounts on a development environment, but I also used them from time to time on customer production environments when it came to provisioning new user accounts on mass.

One of the new features introduced in Windows Server 2008 R2 was the inclusion of 76 cmdlets delivering extensive Active Directory management capabilities using PowerShell. The challenge was to upgrade my old batch files to PowerShell versions using these new cmdlets, building in the following features:

  • Create bulk user accounts from a CSV file, including specifying attributes and account options (e.g., change password at next logon)
  • Create bulk group accounts from a CSV file, specifying a description and group type (e.g., global, security)
  • Assign multiple users to groups as members using a CSV file
  • Exception handling so that the script would check first to see if the account or group member existed before attempting to make the change
  • To achieve this, I have created three PowerShell functions – one to create users, one to create groups, and a final one to read the CSV file, kick off one of the other two functions depending on the type of account to be created, and finally add users as group members. Once these functions are loaded in a PowerShell console, it only requires a single command to create either users or groups – along with an appropriate CSV file, of course.

    Rather than go through each stage of these functions in this article, I have instead decided to annotate the script at various points so that you see what is happening. To get the functions ready for use, save them into a PS1 file using notepad or your favourite script editor and load it from a PowerShell console on a Windows Server 2008 R2 domain controller.

    #Import the PowerShell module containing AD cmdlets
    Import-Module ActiveDirectory
       
    #Read a CSV file with the user or group details and create an account in AD for each entry
    Function Create-ADAccountsFromCSV {
        Param (
            [parameter(Mandatory=$true)][string]$CSVPath,
            [parameter(Mandatory=$true)][string]$Type,
            [parameter(Mandatory=$true)][string]$OrgUnit
            )
     
        if (($Type -ne "Group") -and ($Type -ne "User"))
        {
            Throw New-Object System.ArgumentException("Type parameter must be specified as either 'User' or 'Group'.")
        }
     
        #Read the CSV file
        $csvData = Import-CSV $CSVPath
        foreach ($line in $csvData) {
           
            #Create a hash table of the account details
            $accountTable = @{
                'givenName'=$line.FirstName
                'sn'= $line.LastName
                'displayName'= $line.DisplayName
                'sAMAccountName'= $line.sAMAccountName
                'password' = $line.Password
                'description' = $line.Description
                'ou' = $OrgUnit 
            }
                   
            if ($Type -eq "User")
            {
                #Call the function to create a user account
                CreateUser -AccountInfo $accountTable
            }
     
            if ($Type -eq "Group")
            {
                #Call the function to create a group account
                CreateGroup -AccountInfo $accountTable
               
                #Get new group
                $groupFilterString = "samAccountName -like `"" + $line.sAMAccountName + "`""
                $group = Get-ADGroup -Filter $groupFilterString
               
                #Walk through each member column associated with this group
                $memberColumnNumber = 1
                $memberColumn = "Member" + $memberColumnNumber
               
                #While a member column still exists, add the value to a group
                while ($line.$memberColumn)
                {
                    #Check if user is already a member of the group
                    $member = Get-ADGroupMember $group | where { $_.samAccountName -eq $line.$memberColumn }
                   
                    #If not already a member, add user to the group
                    if ($member -eq $null)
                    {
                        write-host "Adding" $line.$memberColumn "as a member to group" $group.Name
                        try
                        {
                            $userFilterString = "samAccountName -like `"" + $line.$memberColumn + "`""
                            $user = Get-ADUser -Filter $userFilterString
                            Add-ADGroupMember -Identity $group -Members $user
                        }
                        catch
                        {
                            write-host "There was a problem adding" $line.$memberColumn "as a member to group" $group.Name "-" $_ -ForegroundColor red
                        }
                    }
                    else
                    {
                        write-host "User" $line.$memberColumn "not added to group" $group.Name "as it is already a member" -ForegroundColor blue
                    }
                   
                    $memberColumnNumber = $memberColumnNumber + 1
                    $memberColumn = "Member" + $memberColumnNumber
                }
            }
        }
    }        

    #Create an Active Directory user
    Function CreateUser {
      Param($AccountInfo)
     
        try
        {
            #Check to see if the user already exists
            $userFilterString = "samAccountName -like `"" + $AccountInfo['sAMAccountName'] + "`""
            $user = Get-ADUser -Filter $userFilterString
           
            #If user not already created, create them
            if ($user -eq $null)
            {
                write-host "Creating user account:" $AccountInfo['sAMAccountName']
               
                #Create the user account object
                New-ADUser -SamAccountName $AccountInfo['sAMAccountName'] `
                           -Name $AccountInfo['displayName'] `
                           -DisplayName $AccountInfo['displayName'] `
                           -GivenName $AccountInfo['givenName'] `
                           -Surname $AccountInfo['sn'] `
                           -Path $AccountInfo['ou'] `
                           -ChangePasswordAtLogon $true `
                           -AccountPassword (ConvertTo-SecureString $AccountInfo['password'] -AsPlainText -Force) `
                           -Description $AccountInfo['description'] `
                           -Enabled $false
           
                #Set 'User must change password at next logon' to true after user has been created
                #For some reason, the option wasn't set during New-ADUser - could be a bug?
                $user = Get-ADUser -Filter $userFilterString
                Set-ADUser $user -ChangePasswordAtLogon $true          
            }
            else
            {
                write-host "User" $AccountInfo['sAMAccountName'] "not created as it already exists" -ForegroundColor blue
            }
        }
        catch
        {
            write-host "There was a problem creating the user" $AccountInfo['sAMAccountName'] "-" $_ -ForegroundColor red
        }
    }

    #Create an Active Directory group
    Function CreateGroup {
        Param($AccountInfo)
     
        try
        {
            #Check to see if the group already exists
            $groupFilterString = "samAccountName -like `"" + $AccountInfo['sAMAccountName'] + "`""
            $group = Get-ADGroup -Filter $groupFilterString
           
            if ($group -eq $null)
            {  
                write-host "Creating group account:" $AccountInfo['sAMAccountName']
               
                #Create the group account object
                New-ADGroup -SamAccountName $AccountInfo['sAMAccountName'] `
                            -Name $AccountInfo['sAMAccountName'] `
                            -Path $AccountInfo['ou'] `
                            -GroupScope Global `
                            -GroupCategory Security
            }
            else
            {
                write-host "Group" $AccountInfo['sAMAccountName'] "not created as it already exists" -ForegroundColor blue
            }
        }
        catch
        {
            write-host "There was a problem creating the group" $AccountInfo['sAMAccountName'] "-" $_ -ForegroundColor red
        }  
    }

    Creating users

    Once the functions have been loaded in a PowerShell console, you are ready to start creating user accounts from a CSV file. The header of the CSV file for this example should be, as follows:

    sAMAccountName,FirstName,LastName,DisplayName,Description,Password

    Something to note is that I have used a very limited number of attributes when creating a user account – for example, I haven’t specified an Office, Department, Phone Number, Address, etc. You can extend the script to include these by adding extra columns to the CSV file and adapting the CreateUser function in the script, using the appropriate parameters for the New-ADUser cmdlet, as specified on TechNet here.

    Once you have these columns populated with user accounts, save the CSV file to a local or network path. For this example, I am going to copy the file into C:\Scripts and call it UserAccounts.csv.

    You now need to decide which Organisational Unit (OU) to store the users in Active Directory. I could have added this information to an extra column in the CSV file and made a slight modification to the script, but I decided to use a single OU in the PowerShell command instead. You can always move them to a different OU afterwards using the Active Directory Users and Computers console, if required.

    To create my users in the OU “Staff” for the domain “acme.local”, I would need to use the following command:

    Create-ADAccountsFromCSV -CSVPath "C:\Scripts\UserAccounts.csv" -OrgUnit "OU=Staff,DC=acme,DC=local” -Type "User"

    The script will output operation progress to the console, including a warning when it discovers that an account already exists and will not attempt to create it.

    image

    Creating groups and assigning members

    The CSV file for adding groups should have the following header:

    sAMAccountName,Member1,Member2,Member3,Member4,Member5

    The first column should contain the group name, followed by a column for each group member. Note that although I have only included Member 1 to 5 in the example above, you can have as many members as you like by creating extra columns in the header for Member6, Member7, etc.

    As with adding users. there are also extra parameters available for the New-ADGroup cmdlet, as specified on TechNet here.

    Once you have the CSV file formatted with the correct groups and their members, save it to a local or network path for use with the script. For this example, I am going to copy the file into C:\Scripts and call it GroupAccounts.csv. To create my groups in the OU “Groups” for the domain “acme.local”, I would need to use the following command:

    Create-ADAccountsFromCSV -CSVPath "C:\Scripts\GroupAccounts.csv" -OrgUnit "OU=Groups,DC=acme,DC=local” -Type "Group"

    As with creating users, the script will output progress to the PowerShell console, including warnings when either a group already exists or a user is already a member of the group - in both cases it will ignore these operations and move on without making a change.

    image

    Wednesday, 24 August 2011

    Creating a retention policy to start a workflow in SharePoint 2010 using PowerShell

    The script in this article was inspired by this one from Yaroslav Pentsarskyy, where he uses PowerShell to create a retention policy in SharePoint 2010. This works well for the built-in expiration actions (see http://msdn.microsoft.com/en-us/library/microsoft.office.recordsmanagement.policyfeatures.expiration_members.aspx), but I thought it was worth publishing a revised script for creating a retention policy to start a workflow as it requires a few extra properties in the policy XML.

    The scenario for this script was that I wanted to create a retention policy on a specific content type associated with the Pages library on a number of sites. The workflow itself was associated directly to the Pages library and not to a site content type. The retention policy must start the workflow for each page, 20 days after it is created in the Pages library.

    The workflow was created in SharePoint Designer and called “Review Page” - all it does is set a Status column to Expired. I then assigned the workflow to the “Content Page” content type on the Pages library of a site (see this article for details on how to do this in PowerShell) with the association name “Review Page Test”, as shown below:

    Workflow to content type

    We can now use PowerShell to create a retention policy to start this workflow for any page associated with the “Content Page” content type 20 days after it is created in the Pages library. Before the script can be run, you will need to load the following function in a PowerShell console with the SharePoint add-ins loaded:

    function Update-IMPStartWorkflow($siteURL, $wfAssociationName, $listName, $contentType, $WfStartColumn)
    {

        #Get web object
        $web = Get-SPWeb $siteURL
        write-host "Examining site:"$web.Title"at"$web.ServerRelativeUrl -ForegroundColor green
           
        try
        {
            #Do the following if a list exists with the name specified by the user - e.g., Pages
            if ($web.Lists[$ListName]) {

                write-host $web.Title"has a list called"$ListName -ForegroundColor green
               
                #Get the list
                $list = $web.Lists[$ListName]
               
                #Create list policy if one does not exist already
                #$policy = [Microsoft.Office.RecordsManagement.InformationPolicy.ListPolicySettings]($list)
                #if (!$policy.ListHasPolicy)
                #{
                #    $policy.UseListPolicy = $true
                #    $policy.Update()
                #}
               
                #Get the content type
                $ct = $list.ContentTypes[$contentType]
               
                #Get the Workflow template (parent association) ID from the association name
                $wfAssociationId = $ct.WorkflowAssociations.GetAssociationByName($wfAssociationName, 1033).ParentAssociationId.ToString()
                #Get the start column internal name and Id
                $fieldId = $list.Fields[$WfStartColumn].Id.ToString()
                $fieldName = $list.Fields[$WfStartColumn].InternalName
               
                #Create a new policy
                [Microsoft.Office.RecordsManagement.InformationPolicy.Policy]::CreatePolicy($ct, $null)
                $newPolicy = [Microsoft.Office.RecordsManagement.InformationPolicy.Policy]::GetPolicy($ct)    
               
                #Generate policy XML using the values required,
                #injecting column and workflow details from the variables assigned above
                $newPolicyFeatureXml = "<Schedules nextStageId=`"2`">"+
                "<Schedule type=`"Default`">"+
                "<stages>"+
                "<data stageId=`"1`">"+
                "<formula id=`"Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration.Formula.BuiltIn`">"+
                "<number>20</number>"+
                "<property>"+$fieldName+"</property>"+
                "<propertyId>"+$fieldId+"</propertyId>"+
                "<period>days</period>"+
                "</formula>"+
                "<action type=`"workflow`" id=`""+$wfAssociationId+"`" />"+
                "</data>"+
                "</stages>"+
                "</Schedule></Schedules>"
               
                #Add retention policy      
                $newPolicy.Items.Add("Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration", $newPolicyFeatureXml)
               
                write-host "Added retention policy to start workflow"$wfAssociation.Name"on content type"$ct.Name"for list"$list.Title"in site"$web.Title
            }
            else
            {
                #Report if the site does not have the list specified by the user
                write-host $web.Title"does not have a list called"$listName -ForegroundColor red
            }
        }
        catch
        {
            write-host "There has been an error:"$_ -ForegroundColor red
        }
        finally
        {
            #Dispose of the Web object
            $web.Dispose()
        }
    }

    Note that the important part of this script is where you generate the XML for the policy and assign it to the $newPolicyFeatureXml variable. This XML defines the properties required for setting the event, action and recurrence details.

    If you need to set a recurrence on the policy, then you can add some extra properties to the "<data stageId=`"1`">"+ line. For example, to set a recurrence period of every 1 day, replace this line with the following XML:

    "<data stageId=`"1`" recur=`"true`" offset=`"1`" unit=`"days`">"+

    Once the function has been loaded, it can be called with the following command to create a retention policy for the “Content Page” content type, on the Pages library of the site http://portal, to start the workflow with the association name “Review Page Test”:

    Update-IMPStartWorkflow -siteURL http://portal -wfAssociationName "Review Page Test" -listName "Pages" -contentType "Content Page" -WfStartColumn "Created"

    The Information Management Policies administration page on the Pages library for the “Content Page” content type should now look as follows:

    Retention Policy

    Another thing to note is how the workflow association ID is injected into the XML. An early draft of my script used the following line to get the workflow association ID:

    $wfAssociationId = $ct.WorkflowAssociations.GetAssociationByName($wfAssociationName, 1033).Id.ToString()

    However, running the script produced an “Invalid retention stage defined” error message in the Information Management Policies administration page against the retention policy:

    clip_image002

    To resolve this I needed to use the Parent Association ID (the workflow template) rather than the Association ID (the workflow instance), as follows:

    $wfAssociationId = $ct.WorkflowAssociations.GetAssociationByName($wfAssociationName, 1033).ParentAssociationId.ToString()

    I have added this information to the article in case you run into the same error message either in PowerShell or C# code and are looking for a solution…

    Saturday, 13 August 2011

    SharePoint PowerShell Timer Jobs solution by Ingo Karstein

    I was just thinking wouldn’t it be great if someone developed a way of running PowerShell scripts from timer jobs in SharePoint 2010 when a quick search revealed that someone already has! That person is Ingo Karstein, who has published his work on Codeplex for everyone to use.

    You can get full details from his blog at http://ikarstein.wordpress.com/2011/08/04/sharepoint-powershell-timer-jobs-run-powershell-scripts-in-sharepoint-timer-service-context/ and check out the solution itself from http://sppowershelltimerjob.codeplex.com/.

    It’s only in beta at the moment, but it already looks like a very professional piece of work, with script editing in the browser, history list, run enable/disable, option to execute on a specific server, and full integration into the Central Administration UI.

    Nice work Ingo!

    Friday, 12 August 2011

    Diagnose MissingWebPart and MissingAssembly issues from the SharePoint Health Analyzer using PowerShell

    After posting articles on diagnosing MissingFeature and MissingSetupFile errors in the SharePoint 2010 Health Analyzer and Test-SPContentDatabase operation, I have become slightly obsessed with doing the same for other errors, too. I have also had my fair share of them on migration projects recently, so I can’t be the only one experiencing them at the moment.

    In this article I am going to focus on MissingWebPart and MissingAssembly errors. As stated in previous articles, there is no silver bullet to solving these errors in all cases, but the scripts offered here will allow you troubleshoot the errors further to find exactly where they are happening in the content database. Once you know this, you have a fighting chance of being able to solve the problem.

    MissingWebPart Error

    In this example, I have received the following error whilst running a Test-SPContentDatabase operation after a content database migration from SharePoint 2007 to 2010. It also appears in the SharePoint Health Analyzer under the “Configuration” category with the title “Missing server side dependencies”:

    Category        : MissingWebPart
    Error           : True
    UpgradeBlocking : False
    Message         : WebPart class [4575ceaf-0d5e-4174-a3a1-1a623faa919a] is referenced [2] times in the database [SP2010_Content], but is not installed on the current farm. Please install any feature/solution which contains this web part.
    Remedy          : One or more web parts are referenced in the database [SP2010_Content], but are not installed on the current farm. Please install any feature or solution which contains these web  parts.

    As you can see, the error gives you a “WebPart class” GUID, the name of the content database, and how many times it is referenced in the database, but little else. What we need to find out here is either the name of the web part or on which pages it is referenced in the database.

    For this I am going to reuse the Run-SQLQuery PowerShell script that I introduced in my article on MissingSetupFile errors:

    function Run-SQLQuery ($SqlServer, $SqlDatabase, $SqlQuery)
    {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server =" + $SqlServer + "; Database =" + $SqlDatabase + "; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    $DataSet.Tables[0]
    }

    Once you have loaded the function in a PowerShell console, you can run it by using the Run-SQLQuery command with the options relevant to your deployment. For [MissingWebPart] errors, you need to run a SQL SELECT query on the “AllDocs” table in the content database exhibiting the problem, joining to the “AllWebParts” table in order to find details about the missing web part. For example, you would type the following command to find details of the web part with the class ID “4575ceaf-0d5e-4174-a3a1-1a623faa919a”, as reported in the error above:

    Run-SQLQuery -SqlServer "SQLSERVER" -SqlDatabase "SP2010_Content" -SqlQuery "SELECT * from AllDocs inner join AllWebParts on AllDocs.Id = AllWebParts.tp_PageUrlID where AllWebParts.tp_WebPartTypeID = '4575ceaf-0d5e-4174-a3a1-1a623faa919a'" | select Id, SiteId, DirName, LeafName, WebId, ListId, tp_ZoneID, tp_DisplayName | Format-List

    Yes, it is a pretty long command, but it will produce a very useful output, as shown in this example:

    Id             : 6ab5e70b-60d8-4ddf-93cb-6a93fbc410be
    SiteId         : 337c5721-5050-46ce-b112-083ac52f7f26
    DirName        : News/Pages
    LeafName       : ArticleList.aspx
    WebId          : dcc93f3e-437a-4fae-acea-bb15d5c4ea7d
    ListId         : 7e13fe6c-3670-4d46-9601-832e3eb6a1e4
    tp_ZoneID      : Body
    tp_DisplayName :

    Id             : b3fcfcd2-2f02-4fe9-93e4-9c9b5ecddf5b
    SiteId         : 337c5721-5050-46ce-b112-083ac52f7f26
    DirName        : Pages
    LeafName       : Welcome.aspx
    WebId          : 2ae0de59-a008-4244-aa66-d8f76c79f1ad
    ListId         : d8f083f0-16b9-43d0-9aaf-4e9fffecd6cc
    tp_ZoneID      : RightColumnZone
    tp_DisplayName :

    This tells us that the web part has been found on two pages (the references mentioned in the MissingWebPart error). SiteId tells us the site collection and WebId the site where the pages are located. We also have a DirName showing the relative path and the page name itself against the LeafName property. If you’re lucky, you might get the display name of the web part against the tp_DisplayName property, but if not, you should at least be able to tell which zone the web part has been added to by looking at the tp_ZoneID property.

    Easily the best way of resolving these issues is to do as the error suggests and install the missing feature or solution containing the web part, but if this is not possible or feasible to do in your scenario, we can discover the site collection URL from the GUIDs using PowerShell and then remove the offending web parts from the pages specified.

    To find the site collection URL using the information output from the query, type the following command:

    $site = Get-SPSite -Limit all | where {$_.Id -eq "337c5721-5050-46ce-b112-083ac52f7f26"}
    $site.Url

    One you have the site collection URL, you can use the relative path specified by the DirName property to find the location of the file. To remove the web part from the page, type the page URL in the browser and add ?contents=1 to the end of it. For example, to open the web part maintenance page for the ArticleList.aspx page specified in the output, type the following URL in the browser:

    http://portal/news/pages/articlelist.aspx?contents=1

    You can then highlight the offending web part (normally called ErrorWebPart for MissingWebPart errors) by ticking the box and clicking Delete. The screenshot below shows a web part maintenance page to give you an idea of the UI, but not an example of an ErrorWebPart as I had already removed them!

    image

    Note: If you remove an ErrorWebPart from a publishing page with versioning switched on, you may have to delete all earlier versions of the page before the error disappears from the SharePoint Health Analyzer or Test-SPContentDatabase report. This is because the web part will still be referenced from these versions, even though you removed it from the currently published page.

    MissingAssembly Error

    MissingAssembly errors look similar to this one:

    Category        : MissingAssembly
    Error           : True
    UpgradeBlocking : False
    Message         : Assembly [PAC.SharePoint.Tagging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b504d4b6c1e1a6e5] is referenced in the database [SP2010_Content], but is not installed on the current farm. Please install any feature/solution which contains this assembly.
    Remedy          : One or more assemblies are referenced in the database [SP2010_Content], but are not installed on the current farm. Please install any feature or solution which contains these assemblies.

    I normally find MissingAssembly errors appear as the result of an event receiver, which is still registered on a list or library but part of a feature/solution no longer present on the farm.

    In most cases, you may be able to look at the assembly name reported in this error and know what the problem is straight away. As before, the best way of resolving this is to reinstall the missing solution file. However, if you are not able to install the solution (e.g., maybe it only works in SharePoint 2007 and not 2010), then you may want to find the lists where the event receiver is installed and either remove the event receiver from the lists or delete the lists themselves.

    To troubleshoot this issue we can re-use the Run-SQLQuery function used to help find missing web parts above. The table we need to look at this time though is called “EventReceivers”. For example, you would type the following command to find details of the assembly called “PAC.SharePoint.Tagging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b504d4b6c1e1a6e5”, as reported in the error above:

    Run-SQLQuery -SqlServer "SQLSERVER" -SqlDatabase "SP2010_Content" -SqlQuery "SELECT * from EventReceivers where Assembly = ‘PAC.SharePoint.Tagging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b504d4b6c1e1a6e5'" | select Id, Name, SiteId, WebId, HostId, HostType | Format-List

    This will produce an output similar to the following:

    Id       : 657a472f-e51d-428c-ab98-502358d87612
    Name     :
    SiteId   : 337c5721-5050-46ce-b112-083ac52f7f26
    WebId    : 2ae0de59-a008-4244-aa66-d8f76c79f1ad
    HostId   : 09308020-45a8-41e4-bbc0-7c8d8cd54132
    HostType : 2

    Id       : 0f660612-6be0-401e-aa1d-0ede7a9af8da
    Name     :
    SiteId   : 337c5721-5050-46ce-b112-083ac52f7f26
    WebId    : 2ae0de59-a008-4244-aa66-d8f76c79f1ad
    HostId   : 09308020-45a8-41e4-bbc0-7c8d8cd54132
    HostType : 2

    As with the MissingWebPart error before, we can use these GUIDs to get the site collection and site hosting the list with the missing event receiver, as follows:

    $site = Get-SPSite -Limit all | where {$_.Id -eq "337c5721-5050-46ce-b112-083ac52f7f26"}
    $web = $site | Get-SPWeb -Limit all | where {$_.Id -eq "2ae0de59-a008-4244-aa66-d8f76c79f1ad"}
    $web.Url

    The HostId property is the GUID of the object containing the event receiver. The HostType is the object type – in this case, HostType “2” means the event receiver host is a list. You can look at the other host types by checking this article on MSDN: http://msdn.microsoft.com/en-us/library/ee394866(v=prot.13).aspx.

    Now we know the GUID refers to a list, we can get it using PowerShell with this command:

    $list = $web.Lists | where {$_.Id -eq "09308020-45a8-41e4-bbc0-7c8d8cd54132"}

    To remove the list completely, type the following command:

    $list.Delete()

    To keep the list intact and just remove the offending event receiver, copy the Id property from the Run-SQLQuery output into this command:

    $er = $list.EventReceivers | where {$_.Id -eq "657a472f-e51d-428c-ab98-502358d87612"}
    $er.Delete()

    If you do decide to delete the list completely, ensure you also remove it from the site Recycle Bin and Site Collection Recycle Bin to ensure the file is removed from the content database. If not, the error may not disappear from the Health Analyzer or Test-SPContentDatabase operation.

    If you found this article looking for information on how to diagnose MissingFeature issues in the SharePoint Health Analyzer, rather than MissingWebPart or MissingAssembly issues, then have a look at this article for help. For information on troubleshooting MissingSetupFile errors, take a look at this article.

    Monday, 1 August 2011

    Enable search on an External Content Type in SharePoint 2010 using PowerShell

    When you create a custom BDC External Content Type (or “Entity”) for use in SharePoint 2010 and deploy it in the Business Data Connectivity Service Application, it will not be “crawlable” from the SharePoint search application by default.

    There is a great article from Chaks here on the detail, so I do not see any need to repeat the article he has written. However, one approach that may be preferable when configuring the entity and Lob System Instance could be to use PowerShell instead.

    In my example, I have created a very simple External Content Type (ECT) to show a people table from a SQL database. As you can see from the screenshot below, the option to crawl the ECT is set to “No”:

    image

    To retrieve the ECT for configuration in PowerShell, you can use the Get-SPBusinessDataCatalogMetadataObject cmdlet provided with SharePoint 2010. The script below will retrieve the “People” ECT shown above:

    $entity = Get-SPBusinessDataCatalogMetadataObject -BdcObjectType "Entity" -Name "People" -Namespace "http://portal.pacdomain.local" –ServiceContext “http://centraladminURL:port

    Next, the following lines of script retrieve the “ReadList” method instance, as suggested in Chaks article:

    $methodinstance = $entity.MethodInstances | where {$_.Name -eq "ReadList"}

    It is here that we need to add the “RootFinder” property. If you wish to view a list of the current properties set on the method instance, simply type $methodinstance.Properties. To add a new property, type the following command:

    $methodinstance.Properties.Add("RootFinder", "")

    You may also wish to switch on the optional “UseClientCachingForSearch” property, which can speed up the crawls to the ECT, but ensure that you read this article first before doing so as there are some caveats to think about:

    $methodinstance.Properties.Add("UseClientCachingForSearch", "")

    Once you have finished making these changes, update the method instance as follows:

    $methodinstance.Update()

    The ECT will now show as “Crawlable”:

    image

    Note, that you can also use the “Remove” method to delete any property set on the method instance. This could be used to remove the search option on an ECT – even those created in SharePoint Designer, where the “crawlable” option is switched on by default. For example, type the following to remove the “UseClientCachingForSearch” property:

    $methodinstance.Properties.Remove("UseClientCachingForSearch")
    $methodinstance.Update()

    Next, we need to switch on the “ShowInSearchUI” property for the Lob System Instance. This allows the BDC model instance to be seen in the SharePoint search administration interface when creating a content source to crawl business data. At this point you will need to know the name of the Lob System Instance for this ECT – if you’re not sure what this is, type $entity.LobSystem.LobSystemInstances to find it. In this example, the name is “Test Database”:

    image

    We can now use this name to get the Lob System Instance, configure the ShowInSearchUI property, and update the item:

    $lobSI = $entity.LobSystem.LobSystemInstances | where {$_.Name -eq "Test Database"}
    $lobSI.Properties.Add("ShowInSearchUI", "")
    $lobSI.Update()

    Again, the property can be removed using the “Remove” method as before, should you wish to hide a Lob System Instance from the search administration interface again.