Tag Archives | PowerShell

PowerShell to list SharePoint 2007 lists

I wanted to get a quick list of all the lists in our SharePoint 2007 environment. With PowerShell 2, it is easy.

$lists = New-WebServiceProxy -UseDefaultCredential -uri http://sharepoint2007.company.com/_vti_bin/lists.asmx?WSDL
$lists.GetListCollection().List | select Title, Name

PowerShell to get a users DistinguishedName

Why can’t I remember that to find a user’s Distinguished Name all i have to do is type the PowerShell cmdlet:

Get-ADUser username

And for a group:

Get-ADGroup groupname

And for a computer:

Get-ADComputer computername

Why can’t I remember that? It is also a quick way to find the OU of an object!

PowerShell code to query a table, and put the XML results into a SharePoint Document Library

If you look at this prior post, I showed how you can use an xml file in a document library as a source for an input box’s jQuery autocomplete. I wanted to automate the creation of these XML files and upload them to a SharePoint Document Library. For example, I wanted to query Microsoft CRM for all our contacts, and have them appear as an autocomplete for a search input box. I wrote the following PowerShell script to automate this process:

Function JBM-SQL-XMLQueryResultsToSharePointDocLibrary{
PARAM([parameter(Mandatory = $true)]$SQLServerName,[parameter(Mandatory = $true)]$DatabaseName,[parameter(Mandatory = $true)]$Query,[parameter(Mandatory = $true)]$DocLibraryURL,[parameter(Mandatory = $true)]$FullPathFileName)
$FileName=$FullPathFileName.split("\")[$FullPathFileName.split("\").Count -1]
$xml=Invoke-Sqlcmd -ServerInstance $SQLServerName -database $DatabaseName -query "$Query" | ConvertTo-Xml -NoTypeInformation -As String
$output=$xml -replace '<Property Name="([^"]+)">([^<]+)</Property>', '<$1>$2</$1>' -replace '<Property Name="([^"]+)"\s*/>','<$1/>'
$output.save("$FullPathFileName")

$webclient = New-Object System.Net.WebClient;
$webclient.UseDefaultCredentials = $true
$webclient.UploadFile("$DocLibraryURL/$FileName","PUT","$FullPathFileName")
}

I had to use this thread’s method to change the XML produced by ConvertTo-Xml. The default output puts the field name in to a Property Name tag.

Example: Default = <Property Name=”Version”>0</Property> . I wanted it to be = <Version>0</Version>

In theory, this can be used for any SQL query.

PowerShell Modules – Export-ModuleMember only if plugin is installed!

I have been working to move my scripts to modules. It just occurred to me that I can conditionally Export-ModuleMember if a plugin is installed. For example:

if (Get-PSSnapin -registered -Name "SqlServerProviderSnapin*" -ErrorAction SilentlyContinue) {
Export-ModuleMember Function01,Fuinction02,Function03
}

Or I could export based on $env:computername. I like that idea. Only this scripts that are supposed to run on that machine are available!

This just occurred to me.

 

My PowerShell command to backup SQL server

Below is my PowerShell code to backup SQL servers. This will create a folder in the destination with the ServerName, then a subfolder with the date, and then a subfolder with the hour. You can backup a single database or all of them. You must have the PowerShell SQL snap ins installed:

Add-PSSnapin -name SqlServerProviderSnapin110 -ErrorAction SilentlyContinue
Add-PSSnapin -name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue

Maybe this code will help someone:

Function JBMURPHY-SQL-BackupDB() {
PARAM($ServerName=$env:computername,$Destination="\\serverName\Share\Path",$DatabaseName,[switch]$All)

if ($All){
$DatabaseName=$(Invoke-Sqlcmd -server $ServerName "SELECT name FROM sys.databases")
}
elseif($DatabaseName -eq $NULL){
write-host "You must use the -DatabaseName parameter"
return}
else{
$DatabaseName=$(Invoke-Sqlcmd -server $ServerName "SELECT name FROM sys.databases WHERE name = '$DatabaseName'")
}

foreach ($db in $DatabaseName){
    $folderDate=$(get-date -uformat "%Y-%m-%d")
    $folderHour=$(get-date -uformat "%H")
    $dbName=$db.Name
    new-item "$Destination\$ServerName\$folderDate\$folderHour" -type directory -force
    $sqlcmd="BACKUP DATABASE [$dbName] TO DISK = N'$Destination\$ServerName\$folderDate\$folderHour\$dbName.bak' WITH NOFORMAT, NOINIT,  NAME = N'$($dbName) FullBackup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
    Write-Host $sqlcmd
    invoke-sqlcmd -query "$sqlcmd" -Server $ServerName -QueryTimeout 1200
}
}

PowerShell code to update a CRM 2011 field (using REST/oData)

In this earlier post I showed how to loop through all the contacts in CRM 2011. Next thing I wanted to do was to update a field on each Account. So I needed to figure out how to update data, not just read it. Here is the code to do that:

	$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
	$url="http://your.crm.com/Instance/xrmservices/2011/OrganizationData.svc/AccountSet(guid'GUIDofAccount')"
	$webclient = new-object System.Net.WebClient
	$webclient.UseDefaultCredentials = $true
	$webclient.Headers.Add("Accept", "application/json")
	$webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
	$webclient.Headers.Add("X-HTTP-Method", "MERGE")
	$stringToUpload="{`"AccountNumber`":`"123456`"}"
	$resultString=$webclient.UploadString($url,$stringToUpload)

PowerShell, JSON, oData and CRM 2011 (or SharePoint 2010)

I am working on how to consume data from/to SharePoint 2010 and from/to CRM 2011. I decided to try and see if I can get the data to display in PowerShell, figuring if I can get it there, I should be able to get it anywhere?  Here is the code to loop through all the Contacts in a CRM 2011 deployment.

Took me a while to figure this out. Should work with any oData source?

$url="http://your.crm.server/Instance/XRMServices/2011/OrganizationData.svc/ContactSet"
$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
while ($url){
	$webclient = new-object System.Net.WebClient
	$webclient.UseDefaultCredentials = $true
	$webclient.Headers.Add("Accept", "application/json")
	$webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
	$dataString=$webclient.DownloadString($url)
	$json=new-object System.Web.Script.Serialization.JavaScriptSerializer
	$data=$json.DeserializeObject($dataString)
	foreach ($result in $data.d.results){
		write-host "$($result.FullName) , $($result.EMailAddress1)"
	}
	Write-Host "Press any key to continue ..."
	$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
	if ($data.d.__next){
		$url=$data.d.__next.ToString()
	}
	else {
		$url=$null
	}
}

To loop through the items of a SharePoint 2010 list, you would change $url to:

$url=”http://sharepoint2010.server.com/_vti_bin/listdata.svc/Announcements”

Not sure if this would be valuable to anyone, but here it is!

How to tell if your PowerShell session is remote

I wanted to write a conditional to prevent certain things from runing if in a remote PSSession.

If you are in a standard PowerShell session the following is returned:

[Environment]::GetCommandLineArgs()[0] = C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

If you are in a remote PSSession:

[Environment]::GetCommandLineArgs()[0] = C:\Windows\system32\wsmprovhost.exe 

How to tell if your PowerShell function was called by name or by alias

I been wanting to write functions that act differently depending on how they were called. I am not sure if this is good practice or not, but I like the idea. Turns out all you need is “$MyInvocation.InvocationName” Look at they following code:

Function Test-Calling {
if ($MyInvocation.InvocationName -eq "cows"){Write-host "This function was called by an alias ($($MyInvocation.InvocationName))"}
if ($MyInvocation.InvocationName -eq $MyInvocation.MyCommand){Write-host "This function was called by name ($($MyInvocation.MyCommand))"}
}
set-alias cows Test-Calling

If you call the function by alias you get : This function was called by an alias (cows)
If you call the function by it’s name you get: This function was called by name (Test-Calling)

I hope that helps someone.


	

PowerShell script to force download and install WindowsUpdates

I have been using the UpdateHF.vbs vbscript for years to patch all my servers. I wrote a simple HTA to wrap it, and it gets executed by psexec. I wanted to take that script, strip it down, and convert it to PowerShell. My intension was to  run it through a PSSession but I get access denied. Maybe I will try launching it with SCCM?

I found these three scripts, that I chopped to together for a script that I wanted:

Function JBMURPHY-Install-WindowsUpdates {
PARAM([switch]$Install,[switch]$reboot)
	if($(Test-Path &quot;HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired&quot;)){
		if (!($reboot)){
    		write-host &quot;There are pending reboots, please pass the reboot command&quot;
    		return
		}
		else{
		restart-computer
		return
		}
	}

	Write-Host -nonewline &quot; + Searching for Updates: &quot;
	$UpdateSession = New-Object -ComObject Microsoft.Update.Session
	$Updates=$updateSession.CreateupdateSearcher().Search(&quot;IsAssigned=1 and IsHidden=0 and IsInstalled=0&quot;).Updates
	Write-Host &quot; Found [$($Updates.count)] Updates to Download and install`n`n&quot;

	$UpdatesCollection = New-Object -ComObject Microsoft.Update.UpdateColl
	$UpdatesDownloader = $UpdateSession.CreateUpdateDownloader()
	foreach ($Update in $Updates){

		# Add Update to Collection
		if ( $Update.EulaAccepted -eq 0 ) { $Update.AcceptEula() }
		$UpdatesCollection.Add($Update) | out-null

		# Download
		Write-Host -NoNewline &quot; + Downloading Update $($Update.Title)&quot;
		$UpdatesDownloader.Updates = $UpdatesCollection
		$DownloadResult = $UpdatesDownloader.Download()
		$DownloadResultResultCode = switch -exact ($DownloadResult.ResultCode)
		{
		  0   {&quot;NotStarted&quot;}
		  1   {&quot;InProgress&quot;}
		  2   {&quot;Succeeded&quot;}
		  3   {&quot;SucceededWithErrors&quot;}
		  4   {&quot;Failed&quot;}
		  5   {&quot;Aborted&quot;}
		}
		$Message = &quot; [{0}] &quot; -f ($DownloadResultResultCode)
		Write-Host -ForegroundColor Green $message
	}
	if (($Install) -and ($($Updates.count) -gt 0)) {
	write-host &quot;`n`nInstalling updates&quot;
	$Installer = $UpdateSession.CreateUpdateInstaller()
	$Installer.Updates = $UpdatesCollection
	$InstallerResult = $Installer.Install()
	$InstallerResultCode = switch -exact ($InstallerResult.ResultCode)
		{
		  0   {&quot;NotStarted&quot;}
		  1   {&quot;InProgress&quot;}
		  2   {&quot;Succeeded&quot;}
		  3   {&quot;SucceededWithErrors&quot;}
		  4   {&quot;Failed&quot;}
		  5   {&quot;Aborted&quot;}
		}
		$Message = &quot; Installation [{0}] &quot; -f ($InstallerResultCode)
		Write-Host $message
		Write-Host
	}
	if (($reboot) -and ($($Updates.count) -gt 0)) {
	if($(Test-Path &quot;HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired&quot;))
		{
    		write-host &quot;Rebooting&quot;
    		restart-computer
		}
	}
}

Thanks to those that put the original scripts together

Powered by WordPress. Designed by WooThemes