I saw the code below somewhere, and could not remember how to do it later when I needed it. System.Xml.XmlDocument has a built in method to save the document to a file. I wanted to simulate that, but redirect it to the console. I finally found the code how to do it.
$xml = new-object System.Xml.XmlDocument
$xml.Save([Console]::Out)
Now I know where to look to remind myself.
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
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!
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
[xml]$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.
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.
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
}
}
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)
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!
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.exeIf you are in a remote PSSession:
[Environment]::GetCommandLineArgs()[0] = C:\Windows\system32\wsmprovhost.exe 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.