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!
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
$ModLoad imudp.so
$UDPServerRun 514
# Provides TCP syslog reception
$ModLoad imtcp.so
$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,”192.168.1.33″ /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:
/var/log/remote.log
{
daily
rotate 5
missingok
notifempty
sharedscripts
postrotate
/bin/kill -HUP `cat /var/run/syslogd.pid 2> /dev/null` 2> /dev/null || true
endscript
}
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.
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:
“{`”Telephone1`”:`”555-555-5555`”,`”Address1_Telephone1`”:`”555-555-5555`”}”
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){
$url="http://crm.server.com/Instance/xrmservices/2011/OrganizationData.svc/ContactSet(guid'$($line.GUID)')"
$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`"}"
$stringToUpload=$line.UpdateString
$resultString=$webclient.UploadString($url,$stringToUpload)
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?
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
}
}