Using PowerShell To Actually Do Good Things?

Yes, actually, believe it or not you can. One of my harping points of PowerShell functionality is that everyone starts by building scripts to install SharePoint. A good learning exercise I suppose, but check CodePlex and you’ll understand why I think re-inventing the wheel using PowerShell is pointless. “So, Mr. SharePoint Bartender, what do you use PowerShell for in your SharePoint environments?” Glad you asked.

Let’s say you run a SharePoint installation with a lot of web apps and sites. I mean a lot. If you do, have you ever been asked “Hey, can you give me the top 25 sites in the SharePoint farm? In a format I can understand? And present to management to help out with your raise next year?” (At this point, you should be shaking your head yes, and remembering where you linked to this blog site).

Three parts: 1. A SharePoint list with some custom columns to store the data. 2. On the same site as the list, deploy the SharePoint Chart WebPart, using the list as your datasource (I’m a huge fan of the giant doughnut chart, it certainly makes its presence known). 3. You need a PowerShell script you can run as a scheduled task that goes and gets this data and populates the list.

What’s that? Sure, I guess I can do that for you as well…

#Load the SharePoint cmdlets if they are missing
if (!(Get-PsSnapin | Where-Object {$_.Name -match "Microsoft.SharePoint.PowerShell"}))
  Add-PsSnapin Microsoft.SharePoint.PowerShell

# This function will execute a 'Dispose" method on any variables that have a .Dispose() method
function Dispose-All {
	Get-Variable -exclude Runspace | Where-Object {$_.Value -is [System.IDisposable]} | 
	        Foreach-Object {$_.Value.Dispose()

function Gather-Data{
Param (
# Get all the webapps in the farm, execept for the MySites, because no one cares about MySites (you may need to change the URL)
$sites = (Get-SPWebApplication | Where-Object{$_.URL -notlike "http://mysite*"} | select -expandproperty Sites | ForEach-Object{Get-SPSite $_.url})
ForEach($site in $sites){
	$web = (Get-SPWeb $site.URL)
	$monthUsage = ($web.GetUsageData("url", "lastMonth") | sort 'Total Hits')
	If($monthUsage -ne $null){$largeUse = $monthUsage[-1]
	$tHits = ($monthUsage | Measure-Object 'Total Hits' -Sum | Select -expand Sum)
	$usageData = @{
		waURL = $site.WebApplication.URL
		URL = $site.URL
		Title = $web.title
		Day30 = $tHits
		MostActive = $largeUse.Page
		MostActiveCount = $largeUse.'Total Hits'
	New-Object PSObject -Property $usageData
	$monthUsage = $null
	$tHits = $null


function List-Output{
	$newItem = $spList.Items.Add()
	$newItem["Web Application"]="$($_.waURL)"
	$newItem["Site Collection URL"]="$($_.URL)"
	$newItem["Site Title"]="$($_.Title)"
	$newItem["Last 30 Days Access Count"]="$($_.Day30)"
	$newItem["Most Active File"]="$($_.MostActive)"
	$newItem["File Access Count"]="$($_.MostActiveCount)"

$spList = (Get-SPWeb -identity "").Lists["SiteReport"]
ForEach($killItem in $spList.items){
$spList = (Get-SPWeb -identity "").Lists["SiteReport"]

Gather-Data | sort-object MostActiveCount -descending | select-object -first 25 | %{List-Output $_}

What this script does is pull the Usage Stats (you know, from the Usage DB that no one uses but takes up a ton of SQL server disk space) for the sites, drops the Top 25 hitters into the SharePoint list so you can point the Chart Web Part at that list as a datasource.

***** Some assembly required *****
Create a custom list with the following columns using the column type listed:
Web Application (Single line of text)
Site Collection URL (Single line of text)
Site Title (Single line of text)
Last 30 Days Access Count (Number)
Most Active File (Single line of text)
File Access Count (Number)

Again, use the built-in Chart Web Part and point to this list as a datasource. The script should run once a day at least, so the data will always be fresh. Once it’s complete, amaze and impress your friends with your giant doughnut chart showing the top 25 accessed sites.

Searching ULS Logs in Multi-Server Farms

If you have from 5 to a lot more SharePoint servers in your farm, and you need to locate a specific error in the ULS logs, well, it can be a pain. I’ve written a PowerShell script that will allow you to specify what column the error is in, when the error happened, and the general text of the error. The script will take your input and search the ULS logs on each server in the farm. There are the normal cautions about long searches, etc, but you should already know that.

The output is 2 files, one is a standard ULS log file that ULSViewer can open, the other is a short file, which contains the errors from the ULS with just a few fields.

Last thing, please feel free to make fun of my PowerShell scripting abilities. Everyone else I know does…

#Load up the SharePoint cmdlets if they are missing
if (!(Get-PsSnapin | Where-Object {$_.Name -match "Microsoft.SharePoint.PowerShell"}))
{Add-PsSnapin Microsoft.SharePoint.PowerShell}

$FarmInfo = Get-SPFarm
$spLogLocation = (Get-SPDiagnosticConfig | select -expand LogLocation)
$spLogLocation = "\" + ($spLogLocation -replace (":","$"))

$outputFile = "ULSSearchResults.log"
$fulloutputFile = "ULSViewerVersion.log"

if ((test-path $outputFile) -eq "True") {Remove-Item $outputFile -force}
if ((test-path $fulloutputFile) -eq "True") {Remove-Item $fulloutputFile -force}

$DateCheck = date
Add-Content $outputFile "ULS Error Search - Started $DateCheck"
Add-Content $outputFile "`r"

Write-Host @"
Choose which ULS Column To Search In
A:  Correlation ID
B:  Message
C:  Category
D:  EventID
E:  Level
F:  All Areas (Caution, this will take a long time to complete)

$q1 = Read-Host "Column"
$q1 = $q1.ToUpper()

If($q1 -eq "A"){$logcat = "Correlation"}
 elseif($q1 -eq "B"){$logcat = "Message"}
 elseif($q1 -eq "C"){$logcat = "Category"}
 elseif($q1 -eq "D"){$logcat = "EventID"}
 elseif($q1 -eq "E"){$logcat = "Level"}
 elseif($q1 -eq "F"){$logcat = "ALL"}
	Write-Host "`r"
	Write-Host "Selection Is Outside Of Accepted Values.  Rerun Script And Make A Valid Selection." -foregroundcolor "Yellow"
Write-Host @"
Choose A TimeFrame To Search In
A:  Last 10 minutes
B:  Last 30 Minutes
C:  Last 60 Minutes
D:  Last 12 Hours
E:  All Log Files

$q2 = Read-Host "TimeFrame"
$q2 = $q2.ToUpper()

If($q2 -eq "A"){$timcat = "10"}
 elseif($q2 -eq "B"){$timcat = "30"}
 elseif($q2 -eq "C"){$timcat = "60"}
 elseif($q2 -eq "D"){$timcat = "720"}
 elseif($q2 -eq "E"){$timcat = "ALL"}
	Write-Host "`r"
	Write-Host "Selection Is Outside Of Accepted Values.  Rerun Script And Make A Valid Selection." -foregroundcolor "Yellow"

If($timcat -eq "ALL"){$tframe = "All Log Files"}
 {$tframe = $timcat + " Minutes Ago"}

Write-Host @"
Enter or Paste In The Text To Search For

$errMsg = Read-Host "Error"

Add-Content $outputFile @"
*****  Begin ULS Log Search For Error:  $errMSG
Checking In Column: $logcat.  Within The Timeframe Of: $tframe

$timeframe = $null
$FarmServers = $FarmInfo.Servers | Where-Object {$_.Role -ne "Invalid"}
If($timcat -ne "ALL"){$timeframe = (Get-Date).AddMinutes(-$timcat)}
Write-Host "`r"

ForEach($spServer in $FarmServers){
	$logPath = "\\$($spServer.Address)" + $spLogLocation
	Write-Host "Processing Logs File At:  $logPath"
	If($timcat -ne "ALL"){
		If($logcat -ne "ALL"){$logResults = Get-SPLogEvent -Starttime $timeframe -Directory $logPath | Where-Object{$_.$logcat -like "*$errMsg*"}}
		{$logResults = Get-SPLogEvent -Starttime $timeframe -Directory $logPath | Where-Object{$_ -like "*$errMsg*"}}
		 {If($logcat -ne "ALL"){$logResults = Get-SPLogEvent -Directory $logPath | Where-Object{$_.$logcat -like "*$errMsg*"}}
		 {$logResults = Get-SPLogEvent -Directory $logPath | Where-Object{$_ -like "*$errMsg*"}}
	Add-Content $fulloutputFile $logResults
	ForEach($loggedItem in $logResults){
		$shortMessage = ($loggedItem.Message)
		If(($shortMessage.Length) -gt 125){$shortMessage = $shortMessage.SubString(0,125)}
		If($shortMessage -ne $Null){
		Add-Content $outputFile "$($spServer.Address)`t$($loggedItem.Timestamp)`t$($loggedItem.EventID)`t$($loggedItem.Category)`t$($loggedItem.Level)`t$($loggedItem.Correlation)`t$shortMessage"}}
Write-Host @"
ULS Log Search Complete
Simple output file:  $outputFile
ULSViewer compatible file:  $fulloutputFile

Finding WebSite Admins and Total List Items

This single line command will export Site Admins and List Item Count for every website in a given WebApplication. Very useful if the boss wants to shell out zero dollars for an auditing system.

 Get-SPWebApplication "<webappURL>" | select -expandproperty Sites |%{Get-SPWeb $_.URL} |%{$g1=($_.Lists | ?{$_.hidden -eq $false} |select ParentWebURL,Title,ItemCount,BaseType) ; $g2=($_.SiteAdministrators |Select -expand "DisplayName"); Add-Content "ReportFile.csv" "$g2,$g1"}