Tag Archives: Shell

Hosting with Linode

Let us sing the praises of Linode, a provider of cloud-hosting for Linux-based applications and web sites. At work we’ve had two of these for a year, and they just work, exactly if the servers were sitting in the next room, except that they provide a slick management interface, and you can mix and match among different disk sizes, RAM, processors, redundancy, and various flavors of Linux. Access is via their web site, or via a secure terminal session.

Here’s a shot of the backup screen, (click to enlarge).

 The Linode-provided backup is great for basic backup. It makes backups on a weekly and daily basis. It also has a provision for a snapshot, which is a great way to back up the server before doing upgrades. Full details are on their web site, including the costs, which are a few dollars per month depending on the size of the Linode.

There are some limitations…for example with MySQL database transactions. Linode recommends that you perform a data dump of your MySQL data so that the dump files get backed up.

Set default text editor in Ubuntu

I was looking at our crontab on our backup server. This server is an Ubuntu 12.x LTS machine, and the logs for this were being sent to my predecessor, and I wanted to change the eMail address. The usual procedure is to run the following command to see and edit the contents of the crontab file:

crontab -e

This brings up the crontab file for the root user.  Crontab is probably for another day, but basically the script shows a MAILTO address that I wanted to change.

MAILTO=”myusername@mydomain.org”
# Edit this file to introduce tasks to be run by cron.
#  m h  dom mon dow   command
0 23 * * * rsync -avz root@192.168.214.71:/opt/mysql_backup/ /backup/hive
0 18 * * * /backup/scripts/rsync_agave.sh
0 17 * * * /backup/scripts/rsync_basil.sh
0 1  * * * /backup/scripts/rsync_mysql1.sh
0 4  * * * /backup/scripts/rsync_mimic.sh
0 2  * * * /backup/scripts/rsync_petal2.sh

Running the crontab-e opens up the file in the default editor. Well, I didn’t even realize I had a default editor on this machine, and the file opened in vim, which is an archaic program, beloved by Unix freaks.  I prefer the nano editor, especially because I don’t use a text editor much, and I know how nano works.

After some digging it appears that the default editor is set as an environment variable specific to the user.  It can be changed by running the following command:

export EDITOR=nano

You can view your current environment variables, by typing

printenv

There will be a line similar to

EDITOR=nano 

In Ubuntu, you can also use the following command: 

sudo update-alternatives –config editor

This will bring up a list of editors from which you can choose your favorite.

Powershell: Basic Address List Processing

One of the great things that was always a little bit fun (well, for some us… what passes for fun) is processing lists using Unix/Linux shell scripts and tools. When you are in practice, you can perform miracles; leap tall buildings in a single bound. Let’s see what we can do with a file using Powershell commands.

I received a file of “lapsed” donors. These are donors to our organization that gave to one of our campaigns in the past, but haven’t given recently. We know they were our friends in the past, and we think they still are, so we’d like to contact them for a one-time mailing, and/or add them to our master mailing lists.

The file is named “lapsed.csv”. The csv extension suggests this is a text file with a “comma separated values. And indeed, if I look at this file, at the powershell prompt, it is easily visible.

PS C:UsersLarrypowershell> cat lapsed.csv

This shows a comma delimited file with a header line:

Addressee,first,spouse,Organization,Street,City,State,ZIP
Joe Blow, Joe,,,123 W 57TH St Apt 123,New York,NY,10019
Jill Smith,Jill,Howard Services,123 Poor Farm Rd,Colchester,VT,05446

Our standard is:

Org, fname, lname, address1, address2, city, state, zip

So, among other things, we’re going to want to change the order of the information in the fields, as well as the field names.

First thing to do is to import the file into a single PowerShell variable, and then see what we’ve got.

PS>$lapsed= Import-CSV lapsed.csv
PS>$lapsed

Addressee : Joe Blow
 first : Joe
 spouse :
 Organization : Paul C Bunn Elementary
 Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP : 10019
Addressee : Jill Smith
 first : Jill
 spouse :
 Organization : Howard Services
 Street : 123 Poor Farm Rd
 City : Colchester
 State : VT
 ZIP : 05446

Using import-CSV, the file is converted into a series of custom objects with members that correspond to the existing field names …so further manipulations can be done using object manipulations, instead of just a bunch of searching and replacing. (Well that’s the theory anyway).

We can find out the number of records we have by looking at the count attribute.
 PS>$lapsed.count
 553

And we can see the “members” or field names of each record by using Get-Member

PS>$Lapsed |Get-Member

TypeName: System.Management.Automation.PSCustomObject
Name MemberType Definition
 ---- ---------- ----------
 Equals Method bool Equals(System.Object obj)
 GetHashCode Method int GetHashCode()
 GetType Method type GetType()
 ToString Method string ToString()
 Addressee NoteProperty System.String Addressee=Joe Blow
 City NoteProperty System.String City=New York
 first NoteProperty System.String first=Joe
 Organization NoteProperty System.String Organization=Paul C Bunn Elementary
 spouse NoteProperty System.String spouse=
 State NoteProperty System.String State=NY
 Street NoteProperty System.String Street=123 W 57TH St Apt 123
 ZIP NoteProperty System.String ZIP=10019

Ok…we knew the field names before by just looking at the raw .CSV file. But now we have seen how the Import-CSV command converts the .CSV file to an array of objects with a type of PSCustomObject.  Since each address record is an object, the way we manipulate it is to use object methods.

1. Add a lname field for the last name
PS>$lapsed | Add-Member -Name “lname”

That takes the $lapsed table and pipes it to the Add-Member cmdlet. This adds the the member to EACH object in the table, rather than adding it to the table itself.

2. Add a fname and lname fields for the first and last name
PS>$lapsed | Add-Member -Name “fname” -MemberType NoteProperty -Value “”

PS>$lapsed | Add-Member -Name “lname” -MemberType NoteProperty -Value “”

Now the fields look like this:
Addressee : Joe Blow
 first : Joe
 spouse :
 Organization : Paul C Bunn Elementary
 Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP :
 lname : ""
 fname : ""

3. Copy data from the first name and organization fields to their new fields.
PS>$lapsed | ForEach-Object ($_.fname) {$_.fname=$_.first}
PS>$lapsed | ForEach-Object ($_.org) {$_.org=$_.Organization}
This leaves us with a record looking like this.

Addressee : Joe Blow
 first : Joe
 spouse :
 Organization : Paul C Bunn Elementary
 Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP : 10019
 lname :
 fname : Joe
 org : Paul C Bunn Elementary

6. Having copied the data from the old fields to the new ones, we can delete the old fields.
There isn’t a cmdlet to remove an object member, so the you have to use a different nomenclature. (Note To Self… opportunity to make a custom cmdlet?)

PS> $lapsed | ForEach-Object ($_){$_.PsObject.Members.Remove(‘Organization’)}
PS>$lapsed | ForEach-Object ($_){$_.PsObject.Members.Remove(‘first’)}
PS>$lapsed | ForEach-Object ($_){$_.PsObject.Members.Remove(‘spouse’)}

Now a typical record is starting to look much more like what we want it to look like.

 Addressee : Joe Blow
 Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP : 10019
 lname :
 fname : Joe
 org : Paul C Bunn Elementary

7. We still need to pick out the last name from the Addressee field.
There might be a couple approaches to this using regular text search methods:
a. Given a string “Joe Blow”, we could find the first blank character, and then take anything to the right of if as our last name.
b. We could start at the right hand side and count backwards until we get to a space.
c. If there are word functions, we can choose the right-most word in the string.
d. Use the split function. This is what we’ll use.

PS> $lapsed | ForEach-Object ($_){$_.lname=$_.Addressee.split()[-1]}

8. Finally, we can eliminate the “Addressee” field
PS> $lapsed | ForEach-Object ($_){$_.PsObject.Members.Remove(‘Addressee’)}

Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP : 10019
 lname : Blow
 fname : Joe
 org : Paul C Bunn Elementary

9. Time to export back to a CSV file.
PS> $lapsed | Export-Csv -Confirm -Path “C:UsersLarryPowershellulapsed.csv” -NoTypeInformation

10. Almost done. The one frost is that the field order isn’t exactly as I’d like. This can be fixed with the Select-Object cmdlet.

PS > $lapsed | Select-Object -Property fname,lname,org,street,city,state,zip |
Export-CSV -Path “C:UsersLarryPowershellulapsed.csv” -NoTypeInformation

Recall when typing a string of commands with a pipeline, the pipe delimter will also act as a “newline”, so you can break the command up over the course of a couple of lines and have the full pipeline execute as one command.

Notes:

As they say on public television: “Many thanks to the following:”

“jrv” on Microsoft Technet
http://goo.gl/1Kyw07

“Root Loop” on StackOverflow
http://stackoverflow.com/questions/22029944/batch-or-powershell-how-to-get-the-last-word-from-string

More Info:
http://windowsitpro.com/powershell/csv-excel-or-sql-it-doesnt-matter-powershell

More about the split function in Powershell help
PS> Get-Help about_Split

Get-StateAbbreviation

Here is an updated version of my state abbreviation function, with changes prompted by Jeffery Hicks.
I’ve also added the Canadian provinces to the mix. The main change (apart from changing the name of the function to conform to the conventional Powershell verb-noun nomenclature), is to add the CmdletBinding section to specify a single parameter which needs to be supplied to run the function. The neat thing about this is that if you issue the command without the parameter it will automatically prompt for input.

function Get-StateAbbreviation {
#
# Takes upper, lower, or mixed case state name
# and return the two-letter abbreviation. 
# LK 3/17/15  rev. 3/26/15 per Jeffery Hicks 

# Still To Do: 
#   1. Full documentation 
#   2. Allow input from the pipeline 
#   3. Place in a module 

# Example calls: 
#   Get-StateAbbreviation Vermont     <-don't use parentheses
#   Get-StateAbbreviation -StateName Vermont
#   Get-StateAbbreviation "Vermont"
#   Get-StateAbbreviation $MyStateName 
# Note that this function has to appear before it is called in the code
# if it isn't part of a module. 

[CmdletBinding()]
param (
[Parameter(Mandatory=$True)]
   [string]$StateName
)

Switch ($StateName.ToUpper()) {

($StateName="ALABAMA")        {$shortenState="AL"}
($StateName="ALASKA")         {$shortenState="AK"}
($StateName="ARIZONA")        {$shortenState="AZ"}
($StateName="ARKANSAS")       {$shortenState="AR"}
($StateName="CALIFORNIA")     {$shortenState="CA"}
($StateName="COLORADO")       {$shortenState="CO"}
($StateName="CONNECTICUT")    {$shortenState="CT"}
($StateName="DELAWARE")       {$shortenState="DE"}
($StateName="FLORIDA")        {$shortenState="FL"}
($StateName="GEORGIA")        {$shortenState="GA"}
($StateName="HAWAII")         {$shortenState="HI"}
($StateName="IDAHO")          {$shortenState="ID"}
($StateName="ILLINOIS")       {$shortenState="IL"}
($StateName="INDIANA")        {$shortenState="IN"}
($StateName="IOWA")           {$shortenState="IA"}
($StateName="KANSAS")         {$shortenState="KS"}
($StateName="KENTUCKY")       {$shortenState="KY"}
($StateName="LOUISIANA")      {$shortenState="LA"}
($StateName="MAINE")          {$shortenState="ME"}
($StateName="MARYLAND")       {$shortenState="MD"}
($StateName="MASSACHUSETTS")  {$shortenState="MA"}
($StateName="MICHIGAN")       {$shortenState="MI"}
($StateName="MINNESOTA")      {$shortenState="MN"}
($StateName="MISSISSIPPI")    {$shortenState="MS"}
($StateName="MISSOURI")       {$shortenState="MO"}
($StateName="MONTANA")        {$shortenState="MT"}
($StateName="NEBRASKA")       {$shortenState="NE"}
($StateName="NEVADA")         {$shortenState="NV"}
($StateName="NEW HAMPSHIRE")  {$shortenState="NH"}
($StateName="NEW JERSEY")     {$shortenState="NJ"}
($StateName="NEW MEXICO")     {$shortenState="NM"}
($StateName="NEW YORK")       {$shortenState="NY"}
($StateName="NORTH CAROLINA") {$shortenState="NC"}
($StateName="NORTH DAKOTA")   {$shortenState="ND"}
($StateName="OHIO")           {$shortenState="OH"}
($StateName="OKLAHOMA")       {$shortenState="OK"}
($StateName="OREGON")         {$shortenState="OR"}
($StateName="PENNSYLVANIA")   {$shortenState="PA"}
($StateName="RHODE ISLAND")     {$shortenState="RI"}
($StateName="SOUTH CAROLINA")   {$shortenState="SC"}
($StateName="SOUTH DAKOTA")     {$shortenState="SD"}
($StateName="TENNESSEE")        {$shortenState="TN"}
($StateName="TEXAS")            {$shortenState="TX"}
($StateName="UTAH")             {$shortenState="UT"}
($StateName="VERMONT")          {$shortenState="VT"}
($StateName="VIRGINIA")         {$shortenState="VA"}
($StateName="WASHINGTON")       {$shortenState="WA"}
($StateName="WEST VIRGINIA")    {$shortenState="WV"}
($StateName="WISCONSIN")        {$shortenState="WI"}
($StateName="WYOMING")          {$shortenState="WY"}
($StateName="WASHINGTON DC")    {$shortenState="DC"}
($StateName="ALBERTA")               {$shortenState="AB"}
($StateName="BRITISH COLUMBIA")      {$shortenState="BC"}
($StateName="MANITOBA")              {$shortenState="MB"}
($StateName="NEW BRUNSWICK")         {$shortenState="NB"}
($StateName="NEWFOUNDLAND")          {$shortenState="NL"}
($StateName="LABRADOR")              {$shortenState="NL"}
($StateName="NORTHWEST TERRITORIES") {$shortenState="NT"}
($StateName="NOVA SCOTIA")           {$shortenState="NS"}
($StateName="NUNAVUT")               {$shortenState="NU"}
($StateName="ONTARIO")               {$shortenState="ON"}
($StateName="PRINCE EDWARD ISLAND")  {$shortenState="PE"}
($StateName="QUEBEC")                {$shortenState="QC"}
($StateName="SASKATCHEWAN")          {$shortenState="SK"}
($StateName="YUKON")                 {$shortenState="YT"}

Default {$shortenState="XX"}

} # Switch 

return $shortenState

} # Function

Powershell: Limit API iterations in a single call

Problem:

Many APIs limit the number of iterations that you can make in a single API call. For example, Brightpearl limits you to getting information for a maximum of 200 orders in a single API call. If you place a call with more than 200 orders, it will simply return an error message. SmartyStreets also places a limit of 100 addresses that you can validate with a single API call.

Solution:

Dave Wyatt at PowerShell.org provides the following solution.

Lets assume there is an array of 1000 addresses which are returned by convertfrom-csv. Here are the first couple of records from the original .csv file.

PS>cat lapsed.csv
Addressee,first,spouse,Organization,Street,City,State,ZIP
Joe Dokes,Joe, Mary,,,601 W 57TH St Apt 361,New York,NY,10019
Mary Smith ,Mary,Howard,,347 Poor Farm Rd,Colchester,VT,05446
Lu-Anne Jorden,Lu-Anne,Jess,,9603 North Kiowa Rd.,Parker,CO,80138

Here is the command that we use to read in the list into the variable $bigLlist

$bigList =(cat lapsed.csv | convertfrom-csv | 
Select-Object Addressee, Organization, Street, City, State, Zip )

$bigList is a custom object with the following layout:

PS>$biglist | get-member
TypeName: Selected.System.Management.Automation.PSCustomObject
Name         MemberType    Definition 
----         ----------    ---------- 
Equals       Method        bool Equals(System.Object obj) 
GetHashCode  Method        int GetHashCode() 
GetType      Method        type GetType() 
ToString     Method        string ToString() 
Addressee    NoteProperty  System.String Addressee=Kamal Aboul-Hosn 
City         NoteProperty  System.String City=New York 
Organization NoteProperty  System.String Organization= 
State        NoteProperty  System.String State=NY 
Street       NoteProperty  System.String Street=601 W 57TH St Apt 361
ZIP          NoteProperty  System.String ZIP=10019

If you look at this in Powershell, it prints out the contents of each record.

Addressee    : Joe Dokes
Organization :
Street       : 109 Fern Ct.
City         : Delray Beach
State        : FL
ZIP          : 33444

Addressee    : Mary Smith
Organization :
Street       : 205 Dorado Dr
City         : Cherry Hill
State        : NJ
ZIP          : 08034

Addressee    : Lu-Anne Jorden
Organization :
Street       : PO Box 81666
City         : Fairbanks
State        : AK
ZIP          : 99708

Ok, so now we have the full list as an object. The list now needs to be subdivided into groups of 100.

$counter = @{ Value = 0 }
$groupSize = 100
$groups = $bigList | Group-Object -Property { [math]::Floor($counter.Value++ / $groupSize) }

The $counter variable is a hash table, initialized to zero.
The $groupsize variable is the size of the individual group that can be sent. In our example it is set to 100, for a maximum of 100 addresses to be sent at a time.
The $groups variable creates a custom object, with the following members:

PS>$groups | gm
   TypeName: Microsoft.PowerShell.Commands.GroupInfo

Name        MemberType Definition                                                      
----        ---------- ----------                                                      
Equals      Method     bool Equals(System.Object obj)                                  
GetHashCode Method     int GetHashCode()                                               
GetType     Method     type GetType()                                                  
ToString    Method     string ToString()                                               
Count       Property   int Count {get;}                                                
Group       Property   System.Collections.ObjectModel.Collection[psobject] Group {get;}
Name        Property   string Name {get;}                                              
Values      Property   System.Collections.ArrayList Values {get;}                      

If you print out the contents of $groups, you see the following list. (I’ve truncated for readability…)

PS>$groups
Count Name     Group                                                                                                              
----- ----     -----                                                                                                              
  100 0        {@{Addressee=Kamal Aboul-Hosn; Organization=; ...
  100 1        {@{Addressee=Chandler Dawson; Organization=; ...   
  100 2        {@{Addressee=Sidsel Heney; Organization=; ...
  100 3        {@{Addressee=John Marchetti; Organization=; ...
  100 4        {@{Addressee=Jane Ramsey; Organization=; ...
   59 5        {@{Addressee=James Tulloh; Organization=; ... 

This shows that I have 559 names in the original file which has been divided up into 5 groups of 100 and one of 59 names.

The next and final step is to iterate through each group and make the API call.

 
foreach ($group in $groups)
{
    $littleList = $group.Group | ConvertTo-Json
  
$Output = Invoke-RestMethod -Uri $Uri -Body $littlelist -ContentType application/json -Method Post 
}

The steps are:
For each group
Convert the addresses in one group to JSON
Assign it to the variable $littlelist
Send the contents of $littlelist as the body of the API call.
End Loop.

Powershell: Basic text processing

Problem:  I’ve received two files of eMail addresses. These represent one group that were awarded a grant, and much larger group that were rejected. I’m going to call these two files winners.txt and losers.txt.

I want to end up with the following:
1. Eliminate invalid eMail addresses
2. Eliminate duplicate records in both files
3. Check to see that anyone in the winners file does not appear in the losers file.

Although I’ve received these as an Excel Spreadsheet, I’ll use Excel to write them out as comma-delimited text files. Maybe later I’ll figure out how to work with the Excel spreadsheet directly from Powershell.

After exporting the eMail addresses from Excel they are contained in two .csv files….comma delimited text with each field enclosed in quotes. There were a huge number of blank lines at the bottom of each file, which I’m assuming are a result of the Excel export.

Working with the winners.txt file:

1. Count the number of lines in the file:
Get-Content winners.txt | measure-object -Line

2. Eliminate the blank lines. These were lines that had an empty string enclosed in double-quotes followed by a carriage-return, line feed..
 (Get-Content winners.txt -Raw)`
.Replace( "`"`"`r`n" ,"" )| Set-Content winners.txt

3. Eliminate the rest of the quotes
 (Get-Content winners.txt -Raw).Replace( "`"" , "" ) `
| Set-Content winners.txt

4. Validate for the presence of an @ character in each line. (Ok, I know there are some more robust email format evaluation routines using regular expressions or even .NET objects, but I’m reserving those for another time. Right now, I’m assuming that if a line includes an at sign ‘@’ then I can work with it).

(Get-Content winners.txt) | select-string "@" `
| Set-Content winners.txt

5. Eliminate duplicate lines
$fn=winners.txt
( Get-Content $fn ) | sort | get-unique >$fn

Note that you have to sort the lines to be able to get-unique.

Having done the above four steps on the winners’ file, I repeated them with the losers’ file. Then I compared the two files to find lines that appeared in both files, (which of course meant that my winners’ eMail addresses were present in the loosers’ file.)

5. Find lines that appear in both files.
Compare-Object -DifferenceObject $winners `
-ReferenceObject $loosers `
-ExcludeDifferent 

It turns out that all of my winners eMail addresses appeared in the loosers file as well. I deleted these with a manual search and replace… (but that’s another PowerShell story to look up).

Notes:
1. The parentheses around Get-Content in a command act like they do when doing arithmetic. They instruct PS to execute those commands first. This is necessary when modifying the conents  of a file without creating an intermediary file to hold the contents.  The downside is that the file is read entirely into memory, so there may be implications with especially large files.

2. The line continuation character is a back-tick character, “`”.  It also serves as the escape character when you need to include a particular character as part of a search string. So, in the search string in step 1,Replace( “`”`”`r`n” ,”” ) means, replace any line that has “” as its contents.

3. Find the default printer
Get-WmiObject `
-Query " SELECT * FROM Win32_Printer WHERE Default= $true"

From <https://wordpress.com/post/78494701/4&gt;

PowerShell: Shorten URLs with Google’s API

The following PowerShell code will return a shortened URL from a long URL using the Google link shortening URL API. Contrast this code with the code for Bit.ly. There are couple differences: 
1. Calling the Google API is done with a POST. With Bit.ly it is a GET. 
2. I’ve included an interactive prompt in the code below, that will get the long URL from the command line. Once the shortened link is printed, you can paste it to the clipboard. (Or…better yet, avoid mousing around, and pipe the result to the clipboard using the clip.exe utility 


PS >$MyShortURL.id | clip.exe 

3. If it doesn’t matter, and you are already using the Google API,  use Bitly; initial setup is a snap.  I worked out the Google version because we want to be able to track our shortened links using Google Analytics.  Links shortened by Google are automatically made into tracking links. 

4. The Google call uses the PowerShell commandlet Invoke-RestMethod. The Bitly call uses the Invoke-WebRequest commandlet.  

5. For troubleshooting, I used the HttpRequestor FoxFire plugin o make sure I was making the correct API call.  


# Generate shortened URL using the Google API 
# First time set up:  
# * Log in with your Google login name and password  
# * Go to the Google Developer Console at:
# * https://console.developers.google.com/project
# * Create a new project. 
# * Obtain an application key.(Don’t worry about oAuth)
# * Be sure that you allow requests from 
# * “all IP addresses” 

$APIKey=”xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”

# Hard coded long link below,or comment out line 17 and 
# uncomment 20-23 for an interactive command line 

#######################################################
# Paste in the URL that you want to shorten here. 
 $LongURL=”http://twitter.com” 
#
#######################################################
# $Instructions = “You can paste a long URL into the`
# command line by right-clicking the mouse.`n”
# $Instructions 
# $LongURL= Read-Host “Enter or Paste in the Long URL”
#######################################################

#Convert the Long URL to a JSON key/value 
$LongHash=@{“longUrl”=$LongURL}
$MyLongURI = $LongHash | ConvertTo-Json

# Make the API call
$MyShortURL=Invoke-RestMethod `
-Uri https://www.googleapis.com/urlshortener/v1/url?key=$APIKey `
-Body $MyLongURI `
-ContentType application/json `
-Method Post

# Print out the shortened URL 
$LongURL
$MyShortURL.id