Tag Archives: PowerShell

Microsoft Gallery | My PowerShell Script Reaches 100 downloads

Back when I was doing a lot of testing with loading test data into sharepoint lists and then dumping them to run another set of test data, the traditional method of deleting one at a time from the list was really slow, so I created a script to use the ProcessBatchData command to create an XML command structure to delete the items in batches so I could run several automated tests over with different conditions.  Worked well enough that I went ahead and uploaded it to the MS gallery for others to use who were in the same situation.  Just checked and we’ve passed the 100 downloads milestone.  So yeah!  Glad people find it useful

The Truncate all Items in an SPList in SharePoint Using ProcessBatchData Command contribution has been downloaded 100 times from the Office TechNet Gallery. Awesome!

Gallery | 5 days ago

Recently I’ve been working on some other PowerShell scripts to walk a website and record broken links for SharePoint 2010, so once it’s a little more bulletproof I’ll have to post that as well.

PowerShell–Check for PSSnapIn Before You Load It

Many of the scripts that I build end up being handed off to other groups for implementation or to run on a regular basis.  Because of this, any red on the screen from an error message usually ends up in a call.  In some cases where the script is something that is just run via a copy-and-paste into the shell, I’ll instruct the person running the script to run from the SharePoint 2010 Management Shell, but not everyone follows instructions, after all – PowerShell is PowerShell, right?

So then I went to including the following a the top of my scripts to make sure my scripts for SharePoint would run properly in PowerShell:

Add-PSSnapin Microsoft.SharePoint.PowerShell;

And that worked well, except of course when they were actually already running in the SharePoint 2010 Management Shell, in which case, more red text!  End result is another call.


So now I use the following code snippet at the top of my PS scripts:

if(-not(Get-PSSnapin | where { $_.Name -eq "Microsoft.SharePoint.PowerShell"}))
      Add-PSSnapin Microsoft.SharePoint.PowerShell;

And this way, if it’s run from regular PowerShell or the SharePoint 2010 Management Shell, no red text!



Which means no calls!

PowerShell Profiles

Now that I’ve been using PowerShell for a while, I’ve found that as I remote onto different servers I’m having to use the Add-PSSnapIn commandlet and remember the various names of snap-ins that I want to use.  Because I’m always looking for shortcuts along the way I’ve found this little gem that I can put in the profile.ps1 file to effectively load all snap-in modules that are on the server:

get-pssnapin -registered | add-pssnapin –passthru

This can be added to individual servers profile.ps1 files, but that will affect all users who remote into the server as well.  Since I’ve got a network My Documents, I can place the script inside the “My Documents\WindowsPowerShell\profile.ps1” file and now for any server I remote into, when I open the PowerShell app it will load all of the registered Snap-in modules on that server.  The file can be further customized with colors and other preferences.

SharePoint 2010 | Information Architecture Diagram Using PowerShell and Visio

As SharePoint consultants, we don’t always have the luxury of building our farms and the site structures from the ground up.  Sometimes we walk into a situation where the farm and sites have already been up for several years.  However, having a well-planned Information Architecture is critical for any successful SharePoint implementation.  As SharePoint gains in popularity and use, sites are created as one-offs, and after a while our environments start to run wild.  Sites are created without our knowledge or placed as sub-sites of other sites where they don’t really belong.  The power of SharePoint to make web technology accessible to the average information worker is also one of the biggest elephants in the room when it comes to proper and successful implementations.

At some point, whether you are designing the information architecture for the first time or you are going through a regular audit and review of your site structures, you’ll need to get the big picture.  What sites are where?  How do you start the conversation with management that site sprawl is affecting the usability of the system?  When dealing with management, the one thing I’ve learned is that they don’t want to see code or lengthy documents.  If you can’t get your point across in 2 minutes they’ll tune out.  So you have to start the conversation with a visual tool.  A graphical representation of the sites and subsites in a web application would be the perfect tool to give the 50,000 foot view of your sites.  Unfortunately we don’t get that out of the box with SharePoint.  However, this sort of diagram is easy to create with a little bit of PowerShell know how and some Visio diagramming wizards.

We’re looking to create a Visio diagram that makes it easy to visualize site and subsite structures.  The diagram should have relevant and important data about each site.  The layout should show how wide or deep our web application goes and allow us to find the sites that are buried deep inside out information architecture that should be brought up closer to the root.  If our information architecture is too wide we should be able to identify sites that can be pruned or moved under other sites in order to properly categorize them and make them more visible to the end user.

Gather SharePoint Data With PowerShell

Nobody would want to create this diagram by hand.  First off, imagine how long it would take if you had hundreds of sites.  Second, unless you have extremely tight governance controls already in place, the document would be outdated even before it was finished.The fist step is that we need to create a method of extracting the site hierarchy from the SharePoint farm that is easy and repeatable.  This is where PowerShell is a huge help for us.

  1. Remote to one of your SharePoint farm servers and open PowerShell
  2. Run the following PS command:
Get-SPWebApplication http://sharepoint| Get-SPSite -Limit All | Get-SPWeb -Limit All | Select Title, URL, ID, ParentWebID | Export-CSV C:\InfoArch.csv -NoTypeInformation

And what we get now is a nice set of records showing the following properties for each SPweb:

We could also choose to select additional properties of the SPWeb, it really depends on what other information you want to have available to you in the final Visio diagram.

Convert the CSV in Excel

Once we’ve created the CSV file with the elements we need for our Visio diagram, we need to go in and tweak a few things so that the final product formats correctly and shows a nice high level architecture.  In this case, we may find that there will be certain site collections with a ParentWebID GUID of “00000000-0000-0000-0000-000000000000″.  For our purposes, only the actual root of the web application should have this.  All the other site collections should be linked to the root by replacing their ParentWebID with the ID from the root site collection.  In order to do this:

1. Open the CSV file in Excel (Note: You’ll probably copy the CSV to your desktop, because MS Office should not be running on a SharePoint server)


2. To link all of the Site Collections to the root Site Collection, replace all ParentWebID’s that are “00000000-0000-0000-0000-000000000000″ (minus the Root’s) with the ID from the root Site Collection. So, in our example, all of the sites after the first record with a ParentWebID of “00000000-0000-0000-0000-000000000000″ would be replaced by the ID of the Root Site Collection “5b8bbd9b-1cb4-4c8f-a624-b464d08cd32c″ (except as noted where the first record is concerned).


3. Now that we have our data structured properly, save the file as XLSX so Visio can properly consume it as a data source.


Use Visio to Create the Information Architecture Diagram

1. Launch Visio 2010, and under the New selection, under the template categories you’ll want Business.  Select the Organization Chart Wizard

Visio 2010

Visio 2013 (Updated)

clip_image005 image


2. The wizard will start, choose ”Information that’s already stored in a file or database” and click Next


(I would update to show Visio 2013, but hey, they are the same dialog boxes!)

3. Choose “A text, Org Plus (*.txt) or Excel file, click Next.


4. Browse to where you saved the XLSX file and click Next.


5. Here is where the magic happens, instead of using the Employee/Manager relationships, we use ID for the Name, and ParentWebId for the Reports To fields.


6. This screen will be different, depending on what properties of the SPWeb you selected in your PowerShell script.  To keep it simple, I’ll just use the Title so that it will be the only label displayed on the diagram.


7. For shape data fields, it generally makes sense to leave all the columns in there.  This allows the Visio drill-down to be able to display all the relevant data about the SPWeb that you selected in your PowerShell scripts.  So for instance, if I had included the site owner property in my properties select, I would be able to click on a website shape and see who the site owner of the web was.


EDIT for Visio 2013: Don’t include pictures in my organization chart.


8. Select “I want to specify how much of my organization to display on each page” and click Next


9. For now, just keep the one page with your root Site Collection as the Employee at Top of Page and click Finish.


Once the Visio Organizational Chart Wizard completes its processing, you should have a single page diagram with the root Site Collection at the top of the page, and all the various sites and subsites under it (just like an org chart!).  Depending on how big your site is and your own computer processing power, this may take a few minutes to finish, so grab a cup of coffee and congratulate yourself on creating a great visual tool of your Information Architecture.

Explore the Visio Diagram of Your SharePoint Information Architecture

Once the process completes, you should have a single page Visio diagram with the Root at the top and Site Collections and subsites connected underneath.

EDIT: Visio 2013 diagram



Of course, when you select a particular site’s shape data, you can see all the additional information we chose to include from the SPWeb.


NOTE: Article edited to update to Visio 2013.

SharePoint 2010 – Cleaning up deleted databases

A service application database was deleted in SQL Server Management Studio. After a while, I noticed errors in the event viewer on the WFE and ULS logs complaining that SharePoint was not able to login to the database that I deleted. Obviously, SharePoint still thought that the database existed so I needed to find a way to remove it.

PowerShell to the rescue!  Using PowerShell, run the following command:

Get-SPDatabase | where {$_.exists -eq $false}

This will list all databases in SharePoint that no longer exist on the database server. If the list is accurate and you wish to remove the orphaned databases, run the following command:

Get-SPDatabase | where {$_.exists -eq $false} | foreach {$_.delete()}

All orphaned databases should now be removed and SharePoint should stop complaining about being unable to login to the non-existent database.