At The Beginning

At the beginning, God saw chaos in the land of computing and decided to take action. So…

On the first day, he created the System/360 mainframe and peripherals, and brought standards to the land of chaos.

On the second day, God saw the mainframe was lonely, and created RJE stations, terminals and time-sharing, bringing access to millions. And he was pleased.

On the third day, God saw that the mainframe was too large and only for the few and the rich, and he created DOS, the Intel 8080 processor and the smaller IBM PC to free the people.

On the fourth day, God saw the PC was lonely and limited, and Ethernets, file servers and the Internet were born, bringing computing freedom to the masses. And he was pleased.

On the fifth day, God saw the PC was too bulky and stationary, and created the handheld Apple Newton and Palm Pilot’s to untether the people and have computing fit in their pockets and purses.

On the sixth day, God saw the PDA was lonely and limited and created cellular data networks, which begat the iPhone and the many Android variants, and computing was carried everywhere by everyone. And he was pleased.

On the seventh day, God created billions of tiny processors running everything from cars to refrigerators and connected them to the Internet using Bluetooth, low-power RF, 5G cellular and dozens of other technologies to form the Internet of Things.

And he was pleased.

And so he rested.

But the CIO won’t get a good night’s sleep for years to come.

Google Drive Report

If you’re like me, you’ve accumulated a lot of files on your Google Drive and getting a summary might provide some helpful insights. The following Google App Script will output a summary report by Mime type (e.g. pdf, jpeg) with the total number of files and the total number of bytes used, sorted descending so the largest total number of bytes comes first. It also includes a few statistics on the Google Drive usage. You will find that certain Google file types do not count against your space quota and will have a zero file size.

To use this script...

• Create a Google App Script (New … More … Google App Script) in your Google Drive, delete any code you see, cut and paste the code below into the script, save the script and give it a name of your choosing.
• Create a new Google document (New … Google Docs) to hold the report, give it a name, type in a few characters (gibberish is fine) and format those characters in the font type and size you want the output report to take.  Copy (CTRL+C) the document id from the URL (the gibberish part that will look something like 1azjbDuyyT7DNvSfKsP9kOugGukF3iVN5lZ9hud47_aU), then save the document.
• Paste (CTRL+V) the document ID into the Google App Script, replacing the bolded “YOUR FILE ID GOES HERE” on line 8.
• Run the script (Run … AllDriveFiles or the arrowhead icon).
• When the script is finished, you can view your report file.

The output (partial example) will look something like this.

Report generated on Tue Mar 14 2017 14:55:25 GMT-0400 (EDT)

Google Drive Storage Used is 3454522976 Bytes (3294.49 MB)
Google Drive Storage Limit is 123480309760 Bytes (117760.00 MB)
Google Drive Storage Percent Used is 2.80%

--------------------------------------------

Type = application/pdf Count = 2256 Size = 2167898741 Bytes (2067.47 MB)
Type = image/jpeg Count = 3525 Size = 1706782169 Bytes (1627.71 MB)
Type = video/mp4 Count = 8 Size = 369721056 Bytes (352.59 MB)
Type = video/quicktime Count = 3 Size = 162988797 Bytes (155.44 MB)
Type = video/x-m4v Count = 1 Size = 33350360 Bytes (31.81 MB)

If this isn’t exactly what you need, I hope it will serve as a useful starting point and reference.

----------------------------- Google App Script Code ----------------------------------------

function AllDriveFiles() {
//  
// Get date to output on the report
var today = new Date();
var todaysdate = new Date(today.getTime() - 1 * 24 * 60 * 60 * 1000);
var date = todaysdate.toDateString();
// Open the output file by its ID
var report = DocumentApp.openById('YOUR FILE ID GOES HERE');
//  
// Remove any text already in the report
report.setText("  ");
//  
// Print the date
str = 'Report generated on ' + today;
report.getBody().appendParagraph(str);
report.getBody().appendParagraph(' ');  
var used = DriveApp.getStorageUsed();
//
// Print amount of Drive space being used
str = 'Google Drive Storage Used is ' + used + ' Bytes (' + (used/1048576).toFixed(2) + ' MB)';
report.getBody().appendParagraph(str);
var limit = DriveApp.getStorageLimit();
//
// Print the total amount of Drive space
str = 'Google Drive Storage Limit is ' + limit + ' Bytes (' + (limit/1048576).toFixed(2) + ' MB)';
report.getBody().appendParagraph(str);
percent = used*100/limit;
//
// Print the percentage of the total space used by Drive - this does not include sources like GMail
str = 'Google Drive Storage Percent Used is ' + percent.toFixed(2) + '%';
report.getBody().appendParagraph(str);
report.getBody().appendParagraph(' ');
report.getBody().appendParagraph('--------------------------------------------');
report.getBody().appendParagraph(' ');
//
// Get all Drive files and store the total file count and total file space by each unique Mime Type
var arrType = [];
var arrCount = [];
var arrSize = [];
var files = DriveApp.getFiles();
while (files.hasNext()) {
 var file = files.next();
 arrLen = arrType.length;
 for (i = 0; i < arrLen; i++) {
   if (file.getMimeType() == arrType[i]) {
     arrCount[i]++;
     arrSize[i] = arrSize[i] + file.getSize();
     i = arrLen + 10;
     }
 }
 if (i == arrLen) {
    arrType[i] = file.getMimeType();
    arrCount[i] = 1;
    arrSize[i] = file.getSize();
    }
}
//
// Sort the arrays by descending total file size
arrLen = arrType.length;
var sorted=0;
var i=0;
while (sorted == 0) {
 sorted=1;
 while (i < arrLen-1) {
   if (arrSize[i] < arrSize[i+1]) {
     Type = arrType[i];
     Count = arrCount[i];
     Size = arrSize[i];
     arrType[i] = arrType[i+1];
     arrCount[i] = arrCount[i+1];
     arrSize[i] = arrSize[i+1];
     arrType[i+1] = Type;
     arrCount[i+1] = Count;
     arrSize[i+1] = Size;
     sorted=0;
     i=0;
   }
   else {
     i++;
   }
 }
}
//
// Print each detail line
arrLen = arrType.length;
for (i = 0; i < arrLen; i++) {
 str = 'Type = ' + arrType[i] + ' Count = ' + arrCount[i] + ' Size = ' + arrSize[i] + ' Bytes (' + (arrSize[i]/1048576).toFixed(2) + ' MB)';
 report.getBody().appendParagraph(str);
 }
}

AWS IoT Button and TP-Link Smart Plug

A TP-Link smart plug is inserted into a standard electrical outlet and is controlled by the Kasa smartphone app to turn on and off the power to whatever device, for example a lamp, that’s plugged into it. It can also be controlled by voice commands when paired with an Amazon Echo. I wanted to experiment with Amazon Web Service (AWS) Internet of Things (IoT) services using my purchase of an Amazon Programmable Dash Button to control the TP-Link. The design point was that any click (single, double or long press) of the button would turn the power off if it’s currently powered on, and vice versa. That required querying the current device status, parsing the data returned and issuing the proper on/off command. Even the most simple projects, when dealing with unfamiliar technology, leads to lots of challenges and learning, which I’ll share in this blog.

I split the project in two parts, dealing first with controlling the TP-Link from a known environment, namely my MacBook Air on my home wireless network. There is no documented API for the TP-Link, but thanks to a Google search turning up a shell script on George Georgovassilis’s Techblog, I had a starting point. Commands are sent to TCP port 9999 on the TP-Link, which requires a statically-defined, internal IP address so it doesn’t move around, and I defined that on the wireless router. Executing the shell script from a Terminal prompt worked without issue. Knowing that I would be issuing those commands from the Internet (AWS) side of things and not having the ability to statically define the external IP address of my home’s Internet connection, I created a dynamic DNS name using the DYNU (www.dynu.com) service. Now when my home IP address changes, that gets sent to DYNU and they update DNS. I opened port 9999 inbound on my firewall to just the TP-Link, connected my Mac outside my home network via my Android phone’s hotspot capability. Testing was successful and the first part of the project was complete.

The second part dealt with understanding the parts and flows of AWS. When the IoT button is clicked, a message is sent to AWS which is mapped via a Simple Notification Service (SNS) message to one or more AWS Lambda functions. These functions can be written in Python 2.7 or Javascript (Node.js), but not the shell script I’d used so far. Deciding on Python, a language I had never used, I took a short crash course to learn its general syntax and converted the shell script, including updating the script’s netcat calls to standard Python socket calls. After several rounds of additional learning, I had a syntactically proper program and testing began. I was surprised to find that the data returned when querying the TP-Link was slightly different than before, and used a print statement to log the new string and modified the Python program to match. The final hurdle was learning that the socket needed to be closed and a new one created after the query and before the on/off command sent.

So now I have a working IoT button, but two factors limit its usefulness in this purpose. First, it takes about five seconds between clicking the button and the TP-Link changing its power status. Second, the IoT button is limited to about one thousand clicks before its power runs out with no way to charge or replace its battery. Turning lights on and off once a day would drain the button in a little over a year. Turning Christmas lights on and off once a day during each December would be a more suitable, and handy, use case.

Below is the code, with the only required change is updating DNS name (or IP address) in the two bolded connect statements,

AWS Lambda Python 2.7 Function

import socket
import base64
#
def lambda_handler(event, context):
  on = base64.b64decode(bytes('AAAAKtDygfiL/5r31e+UtsWg1Iv5nPCR6LfEsNGlwOLYo4HyhueT9tTu36Lfog=='))
#
  off = base64.b64decode(bytes('AAAAKtDygfiL/5r31e+UtsWg1Iv5nPCR6LfEsNGlwOLYo4HyhueT9tTu3qPeow=='))
#
  query = base64.b64decode(bytes('AAAAI9Dw0qHYq9+61/XPtJS20bTAn+yV5o/hh+jK8J7rh+vLtpbr'))
#
# Query the TP_Link for its current power status
  s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
  s.connect(('YOUR IP OR DNS NAME', 9999))
  s.send(query)
  reply = base64.b64encode(bytes(s.recv(1024)))
  reply = reply[:7]
  s.close()
#
# If the TP_Link is off, turn it on, and vice versa
  s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
  s.connect(('IP OR DNS NAME', 9999))
  if reply == 'AAACPND':
      s.send(on)
  else:
      s.send(off)
  s.close()