Monday, 6 September 2010

Viewing SharePoint properties as lists, tables and CSV in PowerShell

There are often times where it can be useful to gather a list of object properties in SharePoint for reporting, operations, or governance purposes. Fortunately, this is a very easy task in SharePoint 2010 with PowerShell. Once you have set up a variable to define your object (site collection, site, list, item, etc.), you can use a single line of script to output its properties to the PowerShell console or text file in a list or table format.

If you are viewing the properties of a single object, then the best command to use is Format-List, which simply lists each property on a separate line. For example, the script below lists the properties of a site:

$web = Get-SPWeb http://portal
$web | Format-List

The output will look as follows:

Format-List

However, it is quite likely that you only want to show specific properties associated with an object, rather than a very long list of all of them. For example, you may want to show the name of the site along with its URL, owners, created date, and the date an item was last modified. Here you can use the following command:

$web | Format-List -property Title, Url, SiteAdministrators, Created, LastItemModifiedDate

This will show the following output:

Format-List2

To save this output to a text file, use the Out-File command, as follows:

$web | Format-List -property Title, Url, SiteAdministrators, Created, LastItemModifiedDate | Out-File -FilePath c:\webdetails.txt

If you would like to include other properties in addition or instead of those above, then use this command to list all properties available for the object:

$web | Get-Member -MemberType Property

Don’t forget to dispose of your SPWeb object properly when you’re finished:

$web.Dispose()

The Format-List command works well for single objects, but for multiple objects it is best to show the results in a table. The command to use here is Format-Table. For example, you may want to show a list of all sites in a site collection, along with the properties shown in the previous example for each site:

$site = Get-SPSite http://portal
$site | Get-SPWeb -limit all | Format-Table -wrap -property Title, Url, SiteAdministrators, Created, LastItemModifiedDate

The output to this command will look as follows:

Format-Table

To save this output to a file, use the Out-File command, as follows:

$site | Get-SPWeb -limit all | Format-Table -wrap -property Title, Url, SiteAdministrators, Created, LastItemModifiedDate | Out-File -FilePath c:\webdetails.txt

However, there is a problem with this format if you want to use the data in a CSV file. As you can see from the SiteAdministrators column in the screenshot above, the Format-Table cmdlet wraps longer passages of text to new lines in the text file. If you were to try and create a CSV file from this data, it would treat the wrapped text as a new line. To overcome this, there is quite a neat Export-Csv cmdlet available in PowerShell, which you can use in this example as follows:

$site | Get-SPWeb -limit all | Select-Object Title,Url,SiteAdministrators | Export-Csv -Path c:\webdetails.csv –notype

This will output to a CSV file in the following format:

"Title","Url","SiteAdministrators"
"PAC Portal","http://portal","Microsoft.SharePoint.SPUserCollection"
"Search","http://portal/search","Microsoft.SharePoint.SPUserCollection"
"Team",http://portal/team","Microsoft.SharePoint.SPUserCollection

We’re not done yet though. You will see from the output above that we have another problem – it has exported our SiteAdministrators property values as “Microsoft.SharePoint.SPUserCollection” instead of the comma-delimited list of site administrators that we got from the Format-Table command. The only way I can come up with to fix this is to perform a join for this type of multi-valued property. This can be done by replacing the property name in the Select-Object command with @{Name=’PropertyName’;Expression={[string]::join(";", ($_.PropertyName))}} instead. So for our example, the Export-Csv command will now look as follows:

$site | Get-SPWeb -limit all | Select-Object Title,Url,@{Name=’SiteAdministrators’;Expression={[string]::join(";", ($_.SiteAdministrators))}} | Export-Csv -Path c:\webdetails.csv –notype

This will fix the CSV file to look like this:

"Title","Url","SiteAdministrators"
"PAC Portal","http://portal","i:0#.w|pacdomain2\spadmin;i:0#.w|pacdomain2\phil.childs"
"Search","http://portal/search","i:0#.w|pacdomain2\spadmin;i:0#.w|pacdomain2\phil.childs"
"Team","http://portal/team","i:0#.w|pacdomain2\spadmin;i:0#.w|pacdomain2\phil.childs"

Don’t forget to dispose of your SPSite object properly when you’re finished:

$site.Dispose()

30 comments:

  1. Hi great post! One question do you know if this can be done to sub sites? I need this same info but not for the root site, but the sub sites under the root site. There are about 100 sub sites and I would like to gather this info in a spreadsheet for those sites without having to run this command for all 100 sites individually.

    ReplyDelete
  2. Get-SPSite | Get-SPWeb -Limit all | ForEach-Object { $_ | Format-List -property Title, Url, SiteAdministrators, Created, LastItemModifiedDate}

    ReplyDelete
  3. dự án chung cư vinhomes nguyễn trãi là một quần thể đô thị hiện đại chung cu vinhomes nguyen trai được trang bị nội thất tinh tế thiết kế vinhomes nguyễn trãi sang trọng vinhomes nguyen trai đẳng cấp kết hợp cùng tiện nghi vinhome nguyen trai cao cấp vinhome nguyễn trãi là điểm đến lý tưởng vincom nguyen trai đầy đủ cho người tiêu dùng vincom nguyễn trãi có bán giày nam các loại giày công sở nam đẹp nhất, giày lười nam đẹp.Công ty thiết kế nội thất hàng đầu tại hà nội, chuyên thiet ke noi that

    ReplyDelete
  4. This article is interesting and useful. Thank you for sharing. And let me share an article about health that God willing will be very useful. Thank you :)

    Penyebab Telat Haid dan Solusinya
    Cara Menghilangkan Benjolan di Kelopak Mata

    ReplyDelete
  5. It is a time consuming process to locate a legitimate, high-quality designer Michael Kors Handbags On Sale at a decent price. One brand name that is sought after is the Michael Kors Bags On Sale. Anywhere you see high demand you will find people out to make a quick buck.

    ReplyDelete
  6. It is a time consuming process to locate a legitimate, high-quality designer Michael Kors Handbags Clearance at a decent price. One brand name that is sought after is the Michael Kors Outlet Store. Anywhere you see high demand you will find people out to make a quick buck.

    ReplyDelete
  7. Designer Exposure es un buen lugar para comprar su Bolsos Michael Kors original.
    The Woven Tote es también una selección impresionante en el Bolsos Michael Kors Baratos.
    Bolso de alta calidad que debe contemplar absolutamente un Bolsos Michael Kors Outlet.
    Du kommer att upptäcka en handfull detaljer som du kan förvänta dig att komma över på en vanlig Michael Kors Rea.
    Du kan hitta ett antal platser som ger Väska Michael Kors.
    Sortimentet är fantastiskt för alla som letar efter en MK Väska.
    Håll dina ögon öppna för den här säsongens val som kommer att presenteras under bara några månader och det kommer utan tvekan att bli spektakulärt.

    ReplyDelete
  8. وتمتلك أجدد المواد الكيميائية العاملة على مكافحة جميع أشكال الحشرات، كما أنه آمن على الإطلاقً على الأطفال ولا يبقى ضرر في استعماله .
    شركة مكافحة حشرات بالرس
    شركة رش مبيدات بالرس
    تطوير المبيدات الحشرية
    ارخص شركة مكافحة حشرات

    ReplyDelete
  9. شركة اللمسة الأخيرة تقدم لك الحل الأمثل فلا حشرات بعد اليوم ولن تعود مرة أخرى. فنحن نستخدم أفضل المبيدات العالمية الفعالة صديقة البيئة التي لا تترك رائحة ولا سيوثر على صحة الأنسان ويقوم باستخدامها عمال مدربون يقومون برش المبيدات بشكل علمي مما يضمن لك الراحة التامة نرجو التواصل على هذا الرقم 0580002467
    شركة رش مبيدات بأبها
    شركة مكافحة حشرات بأبها
    شركة مكافحة النمل الابيض بأبها
    شركة رش مبيدات بخميس مشيط
    شركة مكافحة حشرات بخميس مشيط
    شركة مكافحة النمل الابيض بخميس مشيط
    شركة رش مبيدات بالقصيم
    شركة مكافحة حشرات بالقصيم
    شركة مكافحة حشرات بجازان
    شركة رش مبيدات بجازان

    ReplyDelete
  10. Both things are possible if you carry Michael Kors Handbags Wholesale. If you are a woman who goes for innovative designs, a designer Michael Kors Bags On Sale is perfect for you. Offering a huge selection of chic purses, handbags, shoes and accessories, Michael Kors Outlet Online Store celebrates womanhood in an entirely unique way. Michael Kors Factory Outlet Online Store At Wholesale Price are one of the most sought-after handbags worldwide. We all agree that diamonds are a woman's best friend; however Official Coach Factory Outlet Online are absolutely next in line. To Coach Outlet Sale aficionados, don't fret because we have great news: a discount Official Coach Outlet Online isn't hard to find. If you are a smart shopper looking for a good buy and great deals on your next handbag purchase, you can go to Official Coach Outlet Online.

    Friendly Links: Toms Shoes Womens | Toms Clearance

    ReplyDelete
  11. Air Jordan 12 Retro Black White, Dr. Thomas Inglesby, Johns Hopkins. Brian Stokes Mitchell, the Actor's Fund. Jordan Black 1s, Even before the pandemic, 258 million children and youth were out of school worldwide. There have been so many missed opportunities in the past. This is a defining moment to rethink the future of education, and the transformation that could be achieved through universal access to high quality education.Today we must rise up to the previously unthinkable challenge of providing learning without schools.

    Do your part to protect yourself if you feel like a deal is too good to be true, it usually is.Work at home scams. {tag: Yeezy Boost V3 White}Jordan 1 Black Red, There's strong voice work from Will Forte, Alessia Cara, Jane Krakowski, Martin Short and Terry Crews but Gervais and his droll narration steals the show. "It's hard to leave home for the first time," he says, "although I was six days old when I left. All my folks ever did for me was lick my eyeballs open and sent me packing.".

    For most people, that's not too much of an issue. And the problems pretty much end there as far as headphones are concerned. Bluetooth in itself isn't infallible, of course, but that's another topic entirely. {tag: Adidas Yeezy Cream White 350 V2}

    You try to just put your finger on what's happening, and then you get told to go home. {tag: Cloud White Yeezy Reflective Laces}"Police in Carrollton, near Dallas, arrested Lorraine Maradiaga, 18, late Tuesday morning after arranging her surrender to the city jail. {tag: Yeezy Boost Off White Yellow}

    Black And Grey Jordan Shirt, The Honors College at Auburn University is home to nearly 1,500 of the best and brightest students from each of the university's academic colleges and schools. They may choose to become involved in undergraduate research and complete an Honors thesis or a professional presentation. They may also choose to enroll in graduate level courses, which can count toward their graduate degree if they continue at Auburn.Partnering with faculty across campus, the Honors College offers roughly 190 Honors courses each year with an average class size of 25 30.

    ReplyDelete