Category Archives: Powershell

Adding Powershell Script inside XML

At times, you will require to read your data in XML format using Powershell Script. Possible reason to do that is such that you can amend the XML file without editing your original Powershell Script. That’s provided that the standardized Powershell Script can fulfill your requirements.

To cater for More flexibility, you can actually insert Powershell Script within your XML. Here is how you can perform that.

Note that this required a minor change in your Powershell Script. (Not to worry, you only need to edit this once and for all).


$xml = (Get-Content .\yourxml.xml)

$expression = $xml.Scripts.script

#Optionally you can put a foreach loop to run each of your script

Invoke-Expression $expression

Below is the simple xml of mine

<scripts>
 <script>   Write-Host "Halo";   Write-Host "Testing";
 </script>
</scripts>
The output looks like this
Adding Powershell script into XML

SPListItem.Delete Best Practise – SharePoint SPList

Today, My Client has a new requirement to delete certain records from a SharePoint Custom List which contains 80+ k of items. First thing came into my head is to run a powershell script to delete the item based on a certain filter condition. (In my case, it is to delete item with column “Application” equals to “Event”)

I drafted this powershell script and ran with Administrator rights.


$web = Get-SPWeb <SharePoint URL>

$listName = "LIST NAME HERE"

$list = $web.Lists[$listName]

$items = $list.Items | Where {$_["Application" ] -match "Event"}

foreach($item in $items)

{

Write-Host "Deleting " $item.ID

$item.Delete()

}

Well, with the huge number of list item in the list, one SPListItem took me 40+ second to delete! That’s ridiculous. And what if i have 10k record to be deleted? It would take up 400k seconds (111 hours) to complete!

After few minutes of googling, some suggested to use CAML Query and limit the number of query by using RowLimit = 1000

This doesn’t really help as i really want to delete them ALL at once!

The better way to delete the items is to change the way you query the data especially when comes to retrieving the List item. Here it goes


$web = Get-SPWeb <SharePoint URL>

$listName = "LIST NAME HERE"

$list = $web.Lists[$listName]

$query = New-Object Microsoft.SharePoint.SPQuery

$query.RowLimit = 999999

$query.Query = "<Where><Eq><FieldRef Name='Application'/><Value Type='Text'>Event</Value></Eq></Where>"

$items = $list.GetItems($query)

foreach($item in $items){

Write-Host "Deleting" $item.ID

$list.GetItemById($item.ID).Delete()

}

Woosh!!

the script completed in 30+ mins!

Simple Web Services call using PowerShell

At times, you will be requiring to test your web service if it is working.
To do that, simply fire up your PowerShell and run the following scripts

$cre = Get-Credential
#system will prompt for login and password
#This would be a standard NTLM authentication
$proxy = New-WebServiceProxy -URI <YOUR WEB SERVICE URL HERE> -Credential $cre

Enter $proxy to check if the object is added

To Check the Operation parameters, Enter $proxy.AddAction (for example)

FAST Search Keyword Query with Scope using Powershell

Just wanted to share the following script of mine. Hope it helps.


#Parameters

#-scope : The search scope that you defined in Central Admin

#-keyword: Keyword to search.

Param
(
[string]$scope = $null,
[string]$keyword = $null
)

if($keyword -eq "")
{
Write-Host "You must key in the Keyword"
exit
}

$proxy = Get-SPEnterpriseSearchServiceApplicationProxy
$query = New-Object Microsoft.Office.Server.Search.Query.KeywordQuery($proxy)
$query.QueryText = $keyword
if($scope -ne $null)
{
$query.HiddenConstraints = "scope:`"" +$scope + "`""
}

#Add any properties you would like to return here
$query.SelectProperties.Add("title") | Out-Null
$query.SelectProperties.Add("url") | Out-Null

$query.ResultTypes = [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults
$query.RowLimit = 100
$query.ResultsProvider = [Microsoft.Office.Server.Search.Query.SearchProvider]::FASTSearch

Write-Host "Executing keyword search..."
$results = $query.Execute()

$relevants = $results["RelevantResults"]
#Or you can use [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults instead
Write-Host "Total " $relevants.TotalRows " results"
Write-Host ""
$relevants.Table

Do note that this script is trying to make FAST Search query. If you are using SharePoint Enterprise Search, please change the ResultsProvider to SharePointSearch

Note also this is not FQL, i tried using “$query.EnableFQL = $true” and the scope didn’t work.

Event ID 3760 – SharePoint Foundation – SQL Database not found

Exception


SQL Database 'YOUR DATABASE_FOLLOWED BY GUID' on SQL Server instance 'YOUR INSTANCE' not found. 
Additional error information from SQL Server is included below.

Cannot open database "YOUR DATABASE_FOLLOWED BY GUID" requested by the login. The login failed.
Login failed for user 'YOUR USER ACCOUNT'.

After checking the SQL, i noticed that the database is not even exist in my database server. I suspect that the previous deleted service application jobs did not clean up properly. To get rid of this 3760 Critical error. Simply Execute the following powershell script.

Note: This script will delete all your non-existing databases. Please do a backup before executing it

Add-PSSnapin Microsoft.SharePoint.Powershell
$dbs = Get-SPDatabase | Where {$_.Exists -eq $false}
$dbs | ForEach-Object { $_.Delete()}

The error is now gone =)

SharePoint Calendar Connect to Outlook is not working. Events not synchronized

I came across this funny issue when my client is trying to download/synchronize SharePoint calendar events to his MS Outlook.

Connect to Outlook Ribbon Button

The calendar instance is added successfully into his Outlook. However, all the events are not populated and the following message shown in the outlook status bar.

Outlook Send/Receive Error

Resolution

Execute the following script via SharePoint Powershell. Kindly change the calendarName to your calendar name.


#Here we go.

$web = Get-SPWeb http://yoursite

$calendarName = "My Calendar"

$calendar = $web.Lists[$calendarName];

$calendar.ExcludeFromOfflineClient = 0;

$calendar.Update()

This script simply enable the calendar to be available for offline client download.

Optionally, you can go to your Calendar’s List Setting > Advanced Setting and turn “Offline Client Availability ” on.

Offline Client Availability

Go back to your Outlook, Hit “Shift” + F9 to refresh the calendar.

TADA~

SharePoint Calendar on MS Outlook

Turn on/off/ondemand Developer Dashboard of SharePoint 2010 using Powershell

Honestly, I personally prefer using Powershell than the old traditional Command Prompts. (no offense to those CMD lovers =X ). It will come to this day where you are asked or required to trace the performance of your SharePoint site. Fortunately, SharePoint 2010 comes with OOTB Developer Dashboard which allows you to track the response times of each of your module within a page.

It is not turned on by default, and here is the tricks to turn it on.

Simply execute the following Powershell Script.

$dds = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.DeveloperDashboardSettings
$dds.DisplayLevel = "On";
#"Off" to turn it off, or "OnDemand" to turn it on demand mode.
$dds.Update()

If you turn it on as “OnDemand” mode, you shall see an icon  Sharepoint 2010 Developer Dashboardon the top right hand corner of your SharePoint page if you didn’t modify your master page. [To be specific, the Sharepoint:DeveloperDashboardLauncher web control]

Add SPUser to SPGroup using Powershell

Adding user to SharePoint group manually is a painful process. Not to mention if you have tons of users using your SharePoint site.  To make life easier, I actually came out with a simple powershell script and XML (where you store the user mappings) to automate the process.


$webUrl = Read-Host "Enter SharePoint Web URL"
if($webUrl -eq "")
{
 $webUrl = "[Your default http]";
}
$web = Get-SPWeb $webUrl
$xml = (Get-Content .\PumpUser.xml)
#Put your xml (in this case 'PumpUser.xml') in the same folder where you place your powershell script
foreach($group in $xml.Groups.Group)
{
 $title = $group.Title
 $spGroup = $web.SiteGroups[$title]
 foreach($user in $group.Users.User)
 {
  $temp = $web.EnsureUser($user)
  $spGroup.AddUser($temp)
  Write-Host "Added "$temp" to "$spGroup.Name
 }
}

The xml goes like this.

<?xml version="1.0" encoding="UTF-8"?>
<Groups>
 <Group>
  <Title>Administrator Group</Title>
  <Users>
   <User>userLogin1</User>
   <User>userLogin2</User>
  </Users>
 </Group>
</Groups>

Simple and easy =)

Using Powershell to make MS SQL query

Making SQL query is pretty simple, hope the following give you a quickest way to get sql data without using SQL Management Tool. (Note: Windows 7 does come with Powershell 2.0, Hurray!)


$sql = New-Object System.Data.SqlClient.SqlConnection
$sql.ConnectionString = "Server=localhostsqlexpress;Integrated Security=true;"
$sql.Open()
$cmd = $sql.CreateCommand()
#Here is where you insert your own sql query
$cmd.CommandText = "exec sp_databases"
$cmd.ExecuteScalar()