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
}
}
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.
In the first part of this tutorial, I pulled a couple of variables from the XML feed of my Google Reader’s “starred items”. Now I wanted to “process” the item and UnStar it. This was not easy for me to put together – it was my first attempt at working with the Google API.
First I needed to get authenticated against Google Services for accessing Google Reader. Here is the shell script to do this:
stty -echo read -p "Password: " password; echo stty echo RESULT=$(curl -s https://www.google.com/accounts/ClientLogin \ --data-urlencode [email protected] --data-urlencode Passwd=$password \ -d accountType=GOOGLE \ -d source=MyAppName \ -d service=reader)
This returned some html that included the AUTH code that I needed to add to the header of each Google Reader Request. I used this AUTH to get a Token (my understanding is that if I wanted to edit an item, I needed the token too). Here are the two pieces of code to parse the AUTH get the Token:
AUTH=$(echo "$RESULT" | grep 'Auth=' | sed s/Auth=//) TOKEN=$(curl -s --header "Authorization: GoogleLogin auth=$AUTH" http://www.google.com/reader/api/0/token)
Putting the AUTH together with the Token, and the Source and id from this post, you end up with a cURL command that can mark an item as UnStared:
curl -s http://www.google.com/reader/api/0/edit-tag?client=MyAppName --request POST --header "Authorization: GoogleLogin auth=$AUTH" \ --data-urlencode r=user/-/state/com.google/starred \ --data-urlencode async=true \ --data-urlencode s=$SOURCE \ --data-urlencode i=$OBJID \ --data-urlencode T=$TOKEN
I was surprised when i got this to work. I am still scared of APIs/REST.
A while ago, I wrote a post about using ruby to parse the xml feed of shared starred items in Google Reader. One thing that I did not like about this solution was that I could not get the URL AND mark the item as un-starred. Since I had been playing with REST in these two prior posts, I figured I could re-write my code to pull down starred item’s URLs, and mark them as un-starred. I wanted to eliminate ruby, as I just don’t use it that often and I feel like I am re-inventing the wheel every time. This too a lot longer than I thought it would, but I figured it out (I think)
As this article explains, you can share out your Google reader starred items. First step was the find the needed values from this XML feed and put the values into variables. I turned to xpath (xpath is installed by default on OS X, it is part of the Perl library XML::XPath).
For the second part of this tutorial, where we mark the item as “un-starred”, we need 2 variables form the Xml feed: id & source. My end goal was to put these URLs into Together.app, so I needed the URL too. Title was just for fun.
Here is the non ruby code to pull the variables that I needed out of the XML of the shared starred items in Google Reader.
XML=$(curl -s http://www.google.com/reader/public/atom/user/YOURUSERID/state/com.google/starred?n=1 | xmllint --format -)
OBJID=$(echo "$XML" | xpath "//entry/id" 2>/dev/null | awk -F"[<>]" '{print $3}')
TITLE=$(echo "$XML" | xpath "//entry/title" 2>/dev/null | awk -F"[<>]" '{print $3}')
URL=$(echo "$XML" | xpath "//entry/link/@href" 2>/dev/null | sed 's/\"//g' | sed 's/href\=//g' | sed 's/\ //g')
SOURCE=$(echo "$XML" | xpath "//entry/source/@gr:stream-id" 2>/dev/null | sed "s/gr:stream-id=//g" | sed "s/\"//g" | sed 's/\ //g')
After getting those variables, I wanted to put the URL in together.app (this has not changed since the previous post). Here is that code:
echo "Adding $TITLE to Together" osascript << EOT tell application "Together" to import url "$URL" as web PDF EOT
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)