Tag Archives: Batch

Archive and Transfer your Google Mail

When an employee leaves your company you may need to archive.

Using the Chrome browser:

1. Sign into your google account.

2. In a new tab, browse to the following address: https://takeout.google.com/settings/takeout/custom/gmail,calendar

3. Follow the wizard.  You can choose to make an archive just of your eMail and calendar, or you can select information from other services such as Google Drive.

4. Once you have selected the services that you want,  click on Next


This shows the file version (zipped),

5. Click on Next again   This starts the archive process.  You can access the archive after it created by clicking on the link that Google sends to your eMail account.

Advertisements

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.

Brightpearl API: Add UPS Tracking Numbers

We have now been using our web store for about a month, and for the most part things have been going pretty smoothly. One issue has been sending orders to our warehouse, and we’ve got a pretty good Powershell script that creates a comma delimited text file (.csv) of order numbers and address information from queries to the Brightpearl API. This file is sent daily to the warehouse via FTP, and warehouse staff  import the orders into their UPS Worldship program.

Screenshot_042715_044611_PM

The second half of this saga is to obtain the UPS Tracking number for each shipment. Once the shipment has been processed in UPS Worldship, a tracking number is generated and stored in the UPS Worldship record for that shipment. Worldship has an export function which will add the tracking number to a .csv file of order numbers and tracking numbers that we can use to update the order record in Brightpearl. The structure of this file, (which is completely customizable) is:

Order Number – In our case it is the Brightpearl sales order number
Tracking Number – from UPS. These look like “1Z 041 388 03 8331 4101”
Expected Delivery Date.
The .csv file looks like this. (UPS loves long field names).

ShipToCustomerID,ShipmentInformationLeadTrackingNumber,ShipmentInformationDeliveryDateTransitTime
"100064","1Z0413880373533722","20150429"
"100020","1Z0413880373302132","20150504"
"100068","1Z0413880373810940","20150430"
"100074","1Z0413880374436157","20150430"

The next step is to walk through the .csv file, find an order number, and update the custom field PCF_TRACKING in Brightpearl to contain the tracking number. Here is the Powershell call to update a single record:

PS>$BPOrders=Invoke-RestMethod `
 -Uri http://ws-use.brightpearl.com/public-api/myBPAccount/order-service/order/100541/custom-field `
 -Body $body `
 -Headers $headers `
 -Method Patch

There are a couple points of interest here. For the most part it is “standard” Powershell syntax for the Invoke-RestMethod.
1. We invoke this by assigning the results of the API call to $BPOrders
2. The call has several lines; the line continuation character is a accent aigu or back-tick.
3. Note that this query uses a $headers variable which includes the two validation properties for the Brightpearl query: , the name of the application and the security token for the application. These are stored as a hashtable.

PS>$headers
brightpearl-app-ref myappreference
brightpearl-staff-token mystaff-tokenXYZ123

More on obtaining the authcode here.

4. The $body variable is also created as a hashtable, but then converted to JSON, and placed between square brackets. This variable contains three parameters, the operation that you are performing on the record, the field that you want to modify, and the value that you want to put in the field. The syntax below simply says, “Replace the contents of the /PCF_TRACKING field with the value of 12345”.

$body=[ordered]@{"op"="replace";"path"="/PCF_TRACKING";"value"="12345"}
$body=($body | ConvertTo-JSON)
$body=("["+$body+"]")

The result is:

PS>$body
[{
"op": "replace",
"path": "/PCF_TRACKING",
"value": "12345"
}]

5. The -Method parameter is a “Patch”. This allows you to replace the contents of a single field in a record rather than replace an entire record as happens when you use PUT.

6. Finally note in the Invoke-RestMethod call, the URI contains “custom-field”. This is a literal, it isn’t the name of your custom field. The name of the custom field is contained in the body.I In the example above, it is “/PCF_TRACKING”

The above API call will replace the contents of a single field in a single record. The next step is to be able to loop through the .csv file, and for each record, find the corresponding record within the Brightpearl database, and update its Tracking number field.

Oh, one more thing, the results of the operation are contained in $BPOrders. The API actually returns the contents of ALL custom fields. You can choose which ones you want to see using dot notation.


PS>$BPOrders.response.PCF_TRACKING
12345

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: Formatting Dates

In Brightpearl, if you want to select orders from a specific date, the date needs to be entered in the format of YYYY-MM-DD. By default PowerShell returns dates in a format based on your “culture” setting; in the U.S. this means the default is MM-DD-YYYY. (Who made this up by the way?) In fact…a standard call to the Get-Date returns a “long date / time” string.

PS>Get-Date
Thursday, November 20, 2014 12:46:19 PM

To just get the date, you add a couple parameters:

PS>Get-Date -DisplayHint Date -Format d
11/20/2014

This returns the system date in the default culture format.

To transform this to YY-MM-DD, there is a parameter which takes a Unix format string.

PS>Get-Date -UFormat %Y-%m-%d
2014-11-20

TechNet has a reference for all of the possible combinations and strings for the UFormat parameter.

Here’s the format for an order search using a hard coded date.
PS>$BPOrders=Invoke-RestMethod `
-Uri http://ws-use.brightpearl.com/public-api/nationalgardening/order-service/order-search?placedOn=2014-11-20 `
-Headers $headers `
-Method Get

We can put the date in a variable, and use that in the API call:

$Today=Get-Date -UFormat %Y-%m-%d

$BPOrders=Invoke-RestMethod `
-Uri http://ws-use.brightpearl.com/public-api/nationalgardening/order-service/order-search?placedOn=$Today `
-Headers $headers `
-Method Get

Rebootolator: Execute a Remote Linux Shell Script from Windows

Ok,  so, your mission, should you decide to accept it, is to restart mySQL and Apache on a remote server. This restarts a balky web site hosted by Apache, and also restarts a mySQL server which is used for a back-end for Drupal.

You want to execute this from your Windows computer.

The target computer runs CentOS 5.6 This is an (ancient) Red Hat Linux derivative, running (ancient) mySQL and Apache.

I ended up using PLink called from a Windows .CMD file to execute a bash shell script.  The shell script looks like this:

Rebootolator

#!/bin/bash -p
# Rebootolator – Reboots Apache and mySQL on a target Server
# LK Microdesign June 25, 2014
TERM=”xterm”
export TERM
clear
echo
echo ‘Rebooting Apache and mySQL on myServer’
echo ‘———————————–‘
echo ‘Restarting mySQL’
/etc/init.d/httpd restart
echo ”
echo ‘Restarting the Apache web server.’
/etc/init.d/mysqld restart
echo ‘Reboot procedure completed’


Note this script is not stored on the target server, but simply put in the same folder as the windows cmd file on my windows box.  

Now for the Windows command file: 

Reboot.CMD

:: Batch file to restart services on myServer
:: Restarts mySQL and httpd 
:: Uses the Rebootolator shell script
:: LK/Microdesign August 12, 2014  
@echo off
cls
echo. 
echo.
plink -ssh username@192.168.xxx.xxx -m rebootolator.sh -pw mypass
echo.
echo.

pause >nul | echo Press any key to exit. 

So, lets deconstruct the Windows Reboot.CMD file.
The first four lines are comment lines. Turns out, you can use two colons to preface a comment in Windows, (who knew?) instead of REM.
Line 5 turns off output to the screen.
Line 6 clears the screen.
Line 7 and 8 put in blank lines.
All the work happens on line 9, using the PLINK command. PLINK is the command line version of PUTTY, a free open source terminal program for Windows workstations. Both PLINK and PUTTY are pretty wonderful and highly recommended if you need to access Linux machines from Windows.
-ssh means “use the secure socket layer protocol to log into this machine”
username@192.168.xxx.xxx is a administrator’s account on the target machine,  probably the root account.
-m rebootolator.sh is the name of the shell script (above) that needs to run on the target machine.
-pw mypass is the password for the account used to log into the machine.

Deconstructing the Rebootolator.sh script:
#!/bin/bash -p  just means this is a BASH script
The two commands that actually restart the mySQL server, and the Apache server are: 
/etc/init.d/httpd restart
/etc/init.d/mysqld restart

The rest, (the echo commands) write out what  is happening at the command line. The Term command is my attempt to avoid a harmless error message that occurs when the script starts to execute.

Since I didn’t realize I could host the Rebootolator.sh script in my Windows folder, I originally though I’d have to log into one Linux box, and then execute the script on the target box.  Turned out the whole thing was simpler using PLINK, which is the equivalent of SSH and SSHPASS programs used to access remote machines from the Linux command line.