Archive | March, 2012

PowerShell: redirect (System.Xml.XmlDocument).Save() to console

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

Now I know where to look to remind myself.

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
$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!

How to setup a remote syslog server in CentOS 6

I wanted to have a cisco device send it’s logs to a Centos box for troubleshooting. I just wanted to do a “tail -f” against the error logs. Seems that syslog is now rsyslog in Centos 6. To setup rsyslog to accept syslog logs from other devices, you need to:

1. uncomment out the following lines (not the description lines, the ones that start with “$”)

# Provides UDP syslog reception
$UDPServerRun 514

# Provides TCP syslog reception
$InputTCPServerRun 514

2. Add a line or two like these below to say where you want the logs written:

:fromhost-ip,startswith,’192.168.1.’ /var/log/remote.log
& ~
:fromhost-ip,isequal,”″ /var/log/servername.log
& ~

3. service restart rsyslogd

4. add a hole in iptables for 514 (UDP and TCP)

-A INPUT -m state –state NEW -m udp -p udp –dport 514 -j ACCEPT
-A INPUT -m state –state NEW -m tcp -p tcp –dport 514 -j ACCEPT

5. service iptables restart

6. create a new logrotate.d config file in /etc/logrotate.d:

rotate 5
/bin/kill -HUP `cat /var/run/ 2> /dev/null` 2> /dev/null || true

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/>'

$webclient = New-Object System.Net.WebClient;
$webclient.UseDefaultCredentials = $true

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.

Update CRM 2011 from an Excel Sheet using PowerShell and the oData/REST endpoint

Let’s say you exported a group of Contacts from CRM 2011 using the “Export to Excel” button and you selected the “Make this data available for re-importing by including required column headings” button. The important thing that this does is it adds the GUID for the contact to the first column.
Now, let’s say you have people edit this spreadsheet and make updates to contact data. Our workflow was that if you edited a contact, you highlighted the cell you modified, and marked the contact as modified (in a new column). All I would need to find all the columns/fields that have been modified (they are highlighted), and I should be able to loop through them and update the record via the GUID. I wrote the following VB function to generate the update string that will be exported with the GUID of the contact. This function looks for a cell that has a background color, then grabs the column heading for that field, and generates an update string. An example of an update string will look like this:


Function GenerateUpdateString(rRange As Range)
Dim rCell As Range
Dim vResult As String
vResult = "{"
For Each rCell In rRange
If rCell.Interior.ColorIndex > 0 Then
         ColumnHead = Cells(1, rCell.Column).Value
         vResult = vResult & "`""" & ColumnHead & "`"":`""" & Trim(rCell.Value) & "`"","
End If
Next rCell
If Right(vResult, 1) = "," Then
vResult = Mid(vResult, 1, Len(vResult) - 1)
End If
vResult = vResult & "}"
GenerateUpdateString = vResult
End Function

Now I just use the following powershell code to loop through the columns in the exported csv and update each record.

$CSVFile=import-csv -path c:\Contacts.csv
foreach ($line in $CSVFile){
$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")
# EXample  $line.UpdateString="{`"Telephone1`":`"555-555-5555`",`"Address1_Telephone1`":`"555-555-5555`"}"

Waiting for some real data to try with, but so far, my small test data works. Will let you know how it works. What do you think?

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() {

if ($All){
$DatabaseName=$(Invoke-Sqlcmd -server $ServerName "SELECT name FROM sys.databases")
elseif($DatabaseName -eq $NULL){
write-host "You must use the -DatabaseName parameter"
$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")
    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

How we recovered from a Microsoft SQL 2005 suspect msdb database

I am not a MS SQL Server guru. Honestly, it is a “set it and forget it” technology for me. After our last patching episode, we ended up with a SQL Server Agent offline, and our msdb file was listed as suspect. Seems like there are 2 ways to recover from this. 1 restore a backup, or two recreate with this method. Since we had a backup from the night before, we started with this method.

  1. First, we restored the msdb file from Symantec Backup Exec to another SQL server as a user database.  Since it was the middle of the day, and we could not take down the SQL server, we wanted to get a copy of the the MSDBData.mdf and MSDBlog.ldf files. This was the quickest way to do it.
  2. We stopped the SQL server, moved the suspect msdb files aside, and placed the restored versions in the right place.
  3. Started the SQL Server, and SQL Server Agent came back online. All seems happy

Powered by WordPress. Designed by WooThemes