I often get asked to help customers write PowerShell scripts to aid with the reporting and administration of their SharePoint environment and as you can probably see from my previous Blog posts, this is something I rather enjoy! Recently a customer asked me to help them write a script that would extract information from SharePoint (for example a list of Sites) and then upload this information to a SharePoint document library. This was actually a lot easier than I anticipated and I thought I would share :)

Please find the script below, this has been tested on SharePoint 2010 and should work on 2013 too. the highlighted values need to be updated to reflect your environment -

• $Output = The location to store the actual output of the script, this is deleted once the output file has been uploaded to SharePoint.
• $WebURL = The web that contains the list that you would like to upload the output file to.
• $ListName = The name of the document library to upload the output file to
• Get-SPSite etc = Replace this with commands to extract the relevant information from the farm, in this example I'm simply reporting a list of sites for illustration purposes, which is probably as basic as you can get!

ASNP *SharePoint* -EA SilentlyContinue
#Declare Variables
$Output = "D:\Logs\Output.txt"
$WebURL = "http://intranet.contoso.com/Sites/IT"
$ListName = "SharePointInfo"

#Create something to upload, in this case a list of all sites
Get-SPSite | Out-File -FilePath $Output
 
#Upload the results to SharePoint
$File = Get-Item $Output
$Stream = $File.OpenRead()
$Web = Get-SPWeb $WebURL
$List = $Web.Lists["$ListName"]
$FileCollection = $List.RootFolder.Files
$FileCollection.Add($File.Name,$Stream,$true)
$Stream.Close()
$File.Delete()

Brendan Griffin

Updated to reflect feedback from Wes MacDonald - http://social.technet.microsoft.com/profile/wes%20macdonald/

Another day, another excuse to write a PowerShell script! This time I was working with a customer who needed to create a Site Collection for their HR department with a large number of Sub-Sites (one for each team within the department), each Sub-Site required unique permissions. Rather than spending an hour doing this through the UI I put together a script to automate this.

Update the values highlighted and run, this will create a single Site Collection and a Sub-Site beneath for each of the department team names contained in text file referenced by the $Departments variable, it will remove any spaces from the names to ensure that the URL is valid and will use the original department name for the display name of the site. The parameters used to create the Site: New-SPSite and Sub-Site: New-SPWeb can be tweaked to match your particular requirements.

ASNP *SharePoint* -EA SilentlyContinue
$Owner = "CONTOSO\JohnDoe"
$WebAppURL = "http://intranet.contoso.com/"
$Departments= Get-Content "C:\Departments.txt"
$Site = New-SPSite ($WebAppURL + "Sites/" + "HR") -Template STS#0 -OwnerAlias $Owner
$Site.RootWeb.CreateDefaultAssociatedGroups($Owner,$null,$Site.Title)
Foreach ($Department in $Departments) {$Web = New-SPWeb -Url (($Site.URL) + "/" + $Department.replace(" ","")) -Name $Department -Template STS#0 -UniquePermissions;$Web.CreateDefaultAssociatedGroups($Owner,$null,$Web.Title)}

Example $Departments input file:

Brendan Griffin

I was recently delivering a SharePoint Risk Assessment on a customers SharePoint 2010 environment, one of the issues identified by the assessment was that the List View Lookup Threshold for all Web Applications had been increased from its default setting of 8 to a huge 15! This is a really bad idea. Setting this to a value greater than 8 can cause some serious performance issues due to the potential increased load that this can place on SQL, this is described in Designing large lists and maximizing list performance - http://technet.microsoft.com/en-us/library/cc262813(v=office.14).aspx#Throttling.

The customer I was working with wasn't actually sure why this had been changed and also which (if any) lists relied on the setting being increased from it's default value. I put together a PowerShell script that iterates through all Lists within all Web Applications within the local farm and outputs the URL of all lists that have 8 or more custom Lookup Fields added (this doesn't include any of the out of the box Lookup Fields). The idea being that they could then work with the list owners to re-architect the list, with a view to reducing the List View Lookup Threshold back down to 8.

All you need to do is specify a suitable location to output this information to (highlighted) and run the script.

"Adding Sharepoint snapin..." | Write-Host -ForegroundColor Yellow
asnp *SharePoint* -ErrorAction SilentlyContinue
$Log = "C:\Log.txt"
"Lists with greater than 8 lookup fields: " + (Get-Date) > $Log
"------------------------------------------------------------" >> $Log
Foreach ($WebApp in (Get-SPWebApplication))
{
"Checking Web Application " + $WebApp.Name | Write-Host -ForegroundColor Green;
Foreach ($Site in $WebApp.Sites)
{
"-Checking Site " + $Site.Url | Write-Host -ForegroundColor Green;
Foreach ($Web in $Site.AllWebs)
{
"--Checking Web " + $Web.Url | Write-Host -ForegroundColor Green;
Foreach ($List In $Web.Lists)
{$Fields = $List.Fields | Where {$_.Type -eq "Lookup" -and $_.SourceID -ne "http://schemas.microsoft.com/sharepoint/v3"};
if ($Fields.Count -gt 7) {"List found with " + $Fields.Count + " lookup fields, Name:" + $List.Title + ", Web URL:" + $Web.Url + ", List URL:" + $List.DefaultViewUrl >> $Log}
}
}
$Site.Dispose()
}
}
"!Please check the log file for further details!" | Write-Host -ForegroundColor Yellow

Brendan Griffin

Following on from Steve's great post yesterday about creating site Navigation from CSV it got me thinking, what If I want to use this to create the Global Navigation on a farm that already has a hotch-potch TopNav structure?

If this is the situation you are in then the following PowerShell is your friend.. Essentially it runs through all the site collections in your farm and removes all of the TopNav apart from the initial 'Home' Tab.

You could also use this PS to clean up if you have used Steve's Script in a test environment and you are trying to figure out which Nav structure suits you best..... or if you got the .csv input wrong :)

Thanks goes to Steve for all his help :) 

WARNING - THIS WILL DELETE ALL OF THE TOP NAVIGATION BAR FOR ALL OF YOUR SITE COLLECTIONS IN ALL OF YOUR WEB APPLICATIONS WITH A SINGLE CLICK.

Enjoy..

Andy

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

    $farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
    $webServices = $farm.Services;
   
    foreach ($webService in $farm.Services)
    {
        if (!($webService -is [Microsoft.SharePoint.Administration.SPWebService]))
        {
            continue;
        }

 foreach ($webApp in $webService.WebApplications | Where-Object {($_.DefaultServerComment -ne 'SharePoint Central Administration v3')})
        {
            if ($webApp -is [Microsoft.SharePoint.Administration.SPAdministrationWebApplication])
            {
                continue;
            }

$siteCollection = $webApp.Sites     
           
foreach ($site in $siteCollection)
{
 
$web=$site.OpenWeb()
$tn = $web.Navigation.TopNavigationBar
[int]$count=$tn.Count                   
while($count -ne 0)
{
  if ($tn[$count-1].Title -ne "Home")
  {
    $tn[$count-1].Delete()
  }
  $count--
}
}
}
}

Another day, another PowerShell script! This time a script that will return details about the Site Template that a SharePoint site is using. A customer of mine has a large number of custom site definitions and regularly needs to determine the template that a specific site uses to aid with troubleshooting. Very simple script, but I thought it was worth sharing anyway.

The default behaviour of the script is to output the Title, Name and Description of the template used (highlighted in the script). This can be changed to meet your requirements, the following properties are available:

Simply save the following as a .ps1 file and execute passing the script the URL of a site as an argument (either an SPSite or SPWeb).

ASNP *SharePoint* -EA SilentlyContinue
$Web = Get-SPWeb $Args[0]
$Template = $Web.WebTemplate + "#" + $Web.Configuration
Write-Host "This site is using the following template:" -ForegroundColor Green
Get-SPWebTemplate | Where {$_.Name -eq $Template} | Select Title, Name, Description | Format-List

 Here is an example of the script in action.

Brendan Griffin

Working with a customer recently and a sub-site had magically disappeared in their SharePoint 2010 environment, it wasn't available to recover from the Site Collection recycle bin so they had to use their 3rd party backup tool to recover the sub-site, they really wanted to discover which user had deleted the sub-site as nobody was confessing! Two of the common ways that a user can delete a sub-site are via Site Actions>Site Settings>Delete this site or Site Actions>Manage Content and Structure (If the Publishing Feature is activated).

If the user deleted the site via Site Actions>Site Settings>Delete this site then the easiest way to identify who deleted the sub-site is to use Log Parser - http://www.microsoft.com/en-us/download/details.aspx?id=24659 (the old tools are the best!) to query the IIS logs for requests to /_layouts/webdeleted.aspx.

In this specific case, they couldn't identify the user using the Log Parser which suggested that the sub-site may have been deleted via other means, it turned out that Manage Content and Structure had been used to delete the sub-site, in which case it's simply a matter of checking the logs via Site Settings>Content and Structure Logs in the root web of the Site Collection. As you can see from the screenshot below, the sub-site /sites/site/subsite was deleted by User ID 9.

The one problem with the logs is that it reports the ID of the user within the Site Collection, which isn't much use on it's own. I put together a simple PowerShell function that takes a Site Collection and User ID as a parameter and returns the actual username.

Execute the following in PowerShell and then run the GetUserbyID function passing the Site Collection URL and User ID.

ASNP *SharePoint* -EA SilentlyContinue
Function GetUserbyID
{param($RootWebURL,$ID)
$RootWeb = Get-SPWeb $RootWebURL
$RootWeb.SiteUsers | Where {$_.ID -eq $ID}
}

In the example below, I identified the user as "CONTOSO\theculprit"

Brendan Griffin

One of our customers needed some help to build out navigation from a .csv file in their Microsoft Office SharePoint Server 2007 environment.

PowerShell seemed to be the most simple option for them; so my colleague Andy and I created the following script:

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

$url = "http://intranet"

$sc = New-Object Microsoft.SharePoint.SPSite($url)
$csv = Import-Csv -Path "e:\siteNav.csv"

foreach ($s in $sc)
{
    $w = $sc.OpenWeb()
    $tn = $w.Navigation.TopNavigationBar
   
   
    if (!$w.Navigation.UseShared)
    {
      
       foreach ($line in $csv)
       {
            if ($line.parent -eq "") {
                $l = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode($line.description,$line.link,"true");
                $tn.AddAsLast($l);
            }
           
            elseif ($line.parent -ne "") {
               
               $navBar = $tn.Navigation.TopNavigationBar
              
               foreach ($navItem in $navBar){
                    if ($navItem.Title -eq $line.parent)
                    {
                        $x = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode($line.description,$line.link,"true");
                        $navItem.Children.AddAsLast($x);
                    }
               }

            }
       }
      
       $w.Update()
       $w.Dispose()
   
    }
    
    $s.Dispose()
}

The CSV file was structured with three columns (Description, Link and Parent):