Automate Work and Life with IFTTT and Office 365 Flow

I'm a huge fan of automation for situations where I find myself doing something mindlessly over and over again, like punching a clock. The way I approach these problems is to find a way to eliminate some portion of the task and iterate till it just happens on it's own.

To avoid confusion, when I say punching a clock what I mean is knowing a rough approximation of how long I worked on a given day so that when I need to put in my "billable vs. non-billable" time at the end of the week I have a rough idea of how much time I spent on working each day. As such the way I "manually" accomplished this was putting the time I arrived at work and the time I left into a spread sheet.

So, step one, avoid having to open excel to enter the information. This was easy using a feature of Office 365 called Flow. It's an application that smells very similar to the populate If This Then That site (a.k.a. Flow makes it easy to setup operations that interact with other parts of Office 365, such as creating files in OneDrive based off of attachments in an e-mail, creating reminders in Outlook, even putting info into a spreadsheet.

Quick aside, Microsoft is bad at naming things, like really bad. In the case of Flow it's both the application as well as an instance of an activity that you create. So going forward when I say I created a "Flow" I mean I created an activity within the application Flow... right, bad at naming things... but I digress.

All flows are comprised of at least two parts. An event/trigger and an action. For example, I could create a flow which would be described as follows...

Every time I receive an e-mail (i.e. trigger/event) take all attachments on the e-mail and place them in OneDrive under a folder with the same name as the "from" e-mail address (i.e. action taken).

Pretty handy. 

So in my case, the action was pretty easy, put a row of data in a spread sheet. But what would be my trigger? After some digging I landed on an interesting feature of Flow the application. Along with the Web Site/App up in the Office 365 subscription there is a mobile application you can install on any mobile device. Along with that you can create a "button" which can act as a trigger, so right from your phone you just tap and the trigger is fired. It has the added bonus of providing GPS data, if you chose, which can also be embedded in the flow for use in the action. So now my flow could be described as..

Every time I press a button in the mobile Flow App on my device (i.e. trigger/event) put a row of data in a spread sheet I specify that includes the time the flow started to represent when I start/end work along with the GPS of where I pressed the button (i.e. action taken)

At first I thought this was great and wonderful and was impressed that I had done all of this in the course of a few hours. However, after the novelty wore off I found that often I would forgot to press the button, and per my original goal I was still mindlessly doing something, even if it was a much smaller process.

Enter IFTTT. IFTTT is probably also something that an enterprise could use; however, I most often think of it as a consumer service. IFTTT has a much easier way to describe creating activities, which they refer to as recipes. These recipes have the same "ingredients" as a flow. A trigger followed by an action. I should mention that Flow seems to be easier to customize as well as to support more extensive chained activities, but I digress. IFTTT also has a mobile app and, unlike flow, it supports GEO-Fencing, or the idea of a trigger based on entering or exiting a set of geo coordinates.

I had already been using IFTTT for my "personal" automation projects. As such I had already had been using IFTTT to trigger an SMS to my lovely wife when I arrived at work or when I was heading out. In hindsight I'm wondering why this didn't hit me right away, but simply forwarding the SMS I sent to my spouse as an e-mail to my work address could be the I needed for the FLOW.

So by "coupling" IFTTT and FLOW I was able to get the best of all worlds. I just go about my business and my spread sheet is updated for me. I even left the button flow in place so that when I work remote I can still keeping things down to a single button press and move on.

What type of activities would like to automate in your day to day routine?

Fire in London helped preserve the city of Savannah GA through the modern age

A view of Savannah as it stood the 29th of March 1734
By Pierre Fourdrinier and James Oglethorpe
[Public domain or Public domain], via Wikimedia Commons
Once upon a time, before my wife and I had kids, we visited the city of Savannah, GA. We had the opportunity to take a carriage ride through some of the most oldest portions of the planned city. A detail of the tour that has stuck out in my mind was how much of the original street remained unchanged. The reason this stuck out was, as I had found in visiting a few other historic locations on the east cost of the US, many locations as old, if not older, had to be altered to accommodate motor vehicles. So why was that not the case for the planned city of Savannah GA? Because of fires in London. Huh?

The founder of Savannah was James Oglethorpe. Born in England in 1696, he would have been familiar with the fires that ravaged that part of the world in 1666. The great fire of London burned through much of the city due to many factors, one of which was the way homes were built, basically right up against one another. Out of that fire a new approach to city building came into fashion that of having easements between homes to help avoid the spread of fire.

This fascinated me as it turned out that in attempting to address the concern of fire by spacing out plots of lands and having larger pathways between the homes it resulted in a design that allowed the original layout to survive, more or less, down to our modern age.

As time progressed and the advent of items such as cars came along Savannah found it self having plenty of room to allow cars to flow in both directions, for sidewalks to be put in, for construction of sewers and electricity to be distributed all without the need to tear down or reroute the flow of movement.

Understanding the chain of events that caused choices to be made can be one of the most rewarding results in study of history.

So dear reader, what historical tidbits have captured your interest?

Creating a DSC module to install Oracle Client and disable enable UAC

You ever get a task and think, how hard can this be? Then you begin down what turns into a seemingly never ending journey.

Welcome to mixing old and new technologies.

I found myself in the position of needing to install the Oracle Client on a Windows VM running in Azure using Desired State Configuration (a.k.a. DSC) and, well, let's just say it was an interesting journey.

Listed directly after this is the snippet of the DSC which I'm posting here in hopes that others who have a similar need may stumble across this and save themselves from headache.

I'll provide a break down of the major pieces. I have intentionally left the logging pieces in the script so that any who would be so bold as to cut and paste without reading over at least have a log to dig into.

A few important prerequisites to using this script.

  • You'll need a DSC module that downloads the Oracle Client Package, the one I used was for 11gR2 and includes an answer file for the client.
    • These items will be specific to your situation.
  • Your Local Configuration Manager (a.k.a. LCM) should be set to allow DSC to reboot.
  • This script WILL disable UAC and if for some odd reason after the reboot the script did NOT continue running UAC is LEFT in a disabled state...
Be sure you understand what this script is doing and how to diagnose the state of the VM once the script runs.

Script InsallOracleWithAnswerFile {
TestScript = {
$oraclePathTest = Test-Path HKLM:\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1;
$regVal = Get-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name EnableLUA;
if (($oraclePathTest) -and ($regVal.EnableLUA -eq 1)) {
return $True
else {
return $False
SetScript = {
$continueProcessing = $True;
$logTimestamp = Get-Date -Format yyMMddHHmm;
$logPath = "C:\MyFolder\OracleInstallStatus";
$logFile = "$logPath\install-$logTimestamp.log";
Add-Content -Path $logFile -Value "-- Begin Overall Oracle Install With Awnser --";
Add-Content -Path $logFile -Value "-- Opening - Get Curernt UAC VALUE --";
$regVal = Get-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name EnableLUA;
Add-Content -Path $logFile -Value "UAC Reg Key:";
Add-Content -Path $logFile -Value $regVal;

if ($regVal.EnableLUA -eq "1") {
Add-Content -Path $logFile -Value "-- Change UAC to disable (i.e. 0) --";
Set-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name EnableLUA -Value 0;
Add-Content -Path $logFile -Value "-- Set Global Falg to trigger Reboot --";
$global:DSCMachineStatus = 1;
$continueProcessing = $False;

if ($continueProcessing) {
Add-Content -Path $logFile -Value "-- Begin Check for Oracle Install --";
$oracleRegPath = "HKLM:\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1";
Add-Content -Path $logFile -Value "Oracle Reg Path:";
Add-Content -Path $logFile -Value $oracleRegPath;
$oraclePathTest = Test-Path $oracleRegPath;
Add-Content -Path $logFile -Value "Results of Testing Reg Path";
Add-Content -Path $logFile -Value $oraclePathTest;
if ($oraclePathTest -eq $False) {
$params = "-silent -nowelcome -noconsole -waitforcompletion -noconfig -responseFile C:\MyFolder\OraclePackage\Oracle11gClientx86\11g\x86\client\response\client_030514.rsp";
$oracleClientExe = "C:\MyFolder\OraclePackage\Oracle11gClientx86\11g\x86\client\setup.exe";
Add-Content -Path $logFile -Value "--- Starting Oracle Install ---";
Start-Process -FilePath $oracleClientExe -ArgumentList $params -Wait -Passthru;
Add-Content -Path $logFile -Value "--- Finish Oracle Install ---";
else {
Add-Content -Path $logFile -Value "Oracle Reg Key was found so NOT running install."

Add-Content -Path $logFile -Value "-- Secondary - Get Current UAC Value --";
$regVal = Get-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name EnableLUA;
Add-Content -Path $logFile -Value "UAC Reg Key:";
Add-Content -Path $logFile -Value $regVal;
if ($regVal.EnableLUA -eq "0") {
Add-Content -Path $logFile -Value "-- Change UAC to enable (i.e. 1) --";
Set-ItemProperty -Path registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name EnableLUA -Value 1;

Add-Content -Path $logFile -Value "-- Set Global Falg to trigger Reboot --";
$global:DSCMachineStatus = 1;

Add-Content -Path $logFile -Value "-- FINISH Overall Oracle Install with Answer --"
GetScript = {@{Result = "InsallOracleWithAnswerFile"}}
DependsOn = "[Archive]OraclePackageExtract"

Test Script

In short the test script is confirming two things.
  1. Is the Oracle Client installed based on the expected registry key being present.
  2. Is UAC in the ENABLED state, also checked by looking at the expected registry key value.
If either of these conditions are FALSE there is a need to run the set script.

Set Script

This is where it gets hairy interesting.
I'll skip commenting on the logging as it should be clear what the logs are doing, an interesting point about the way the logging is done is that it can indicate how many times this script is run since the log is written to a file with a date time stamp as the name which I found handy since I put myself into an infinite loop a few times.

Since I'll have to essentially provide myself a circuit breaker I set a continue flag at the start that I can use later on.

Due to the way the Oracle Installer works, even WITH the answer file you'll still get a UAC prompt before you can begin the install, thus the first part of this set script will obtain the value of the UAC via a registry call.

Once I have the UAC value I check to see the state of the value, in the case where it is ON (i.e. 1) I want to disable it so I change the registry value, also setting my continue flag to false and set the environment variable to tell DSC to reboot after this script is completed. Namely...
$gobal:DSCMachineStatus = 1;

At this point I'm checking my flag to ensure that I SKIP attempting to install the oracle client, this is because even thought we updated the registry it does go into effect until we reboot and thus no need to run the Oracle Installer.

So we log that we can't install the oracle client and continue on.

The next bit also results in NOT applying changes since the UAC value in the registry is technically STILL 1 because UAC isn't ACTUALLY persisted until reboot.

I am being sloppy at the end and RESETTING the DSCMachineStatus. Meh.

So now we reboot.

Now running through the script a second time AFTER the reboot we will be left in a state where the UAC IS disabled but we DO NOT have the Oracle Client installed, so when we run through the set script the oracle install WILL occur.

We finish out the second run by enabled UAC and another reboot.

When the system comes up on the third time UAC is ENABLED and Oracle Client is installed.

Do please let me know if you run into issues with this script in the comments.

Resilient Scripting is scripting that can be rerun... safely

Making a script that is resilient can mean many different things to different people, IMHO an important one is to be able to re-run a script "safely". By safely I mean to minimize side effects and to prevent negative consequences.

To illustrate let's say we have an install process and we need to log details about what happens when we run the install.

To keep this simple let's just focus on the logging requirement.

We want a log file that we can look at when an install happens. So a simple approach would be as follows.

Set-Content log.txt "Information about Install"

Nice and simple, we have satisfied the requirement. But let's see if we can make this one liner more resilient.

$logFileTime = Get-Date yyMMddhhmmss
$logFileName = $logFileTime + "_log.txt"
Set-Content $logFileName "Information About Install"

Now even if we run this install multiple times we will have a script for each instance of the install even when it happens on the same day during the same minute. Further more an interesting side effect of making a script more resilient is that we know can see how many times this install is run because we have a file for each instal attempt. So the benefits of making it more resilient compound.

I hope this simple example helps you dear reader see how taking a second pass at making a script more resilient, even a one liner can benefit from a second pass. 

Dogfooding is important for business policies as well

Perhaps you are unfamiliar with the term "dogfooding", in short it is the idea that a company or organization use its own Product or Service in addition to providing it to the others. A more complete definition can be found on Wikipedia under Eating your own dog food.

The reasons a group might chose to dogfood are varied, but an important reason is to ensure that they are familiar with the challenges with using the good or service, or to put it in business jargon... they want to find opportunities to make, what they make, better.

For groups that are just starting to do this it can be EYE OPENING what their customers/patrons are dealing with to use their stuff. Often the overall impact is great and everybody wins. So why not do this for policies within, let's say an IT organization. How would one go about dogfooding a policy you may ask? Thanks for asking... let me tell ya what I think.

For any organization to be successful there needs to be consistency in the way it approaches work. This is especially true for IT groups. The term consistent and repeatable is often spoken when discussing how to approach various aspects of the IT assembly line. At times the way to achieve this goal is to  determine a policy that governs the way IT associates do their work. Examples of this could be found in policies about documenting changes, how to request access to a system. You can see this even outside of IT, such as when a communications department issues a corporate policy on how to setup your e-mail signatures.

So back to the point I was making, it can be of GREAT benefit if the ones making the policy are sure to follow their own policy to ensure they are not creating any undo burden on those that the policy is enforced on. This can best be illustrated with a concrete example. Let's look at having a source code repository based on the GIT system with a Pull Request policy...

Now, most GIT systems have the concept of a branch policy that permits the owner to require a Pull Request to merge changes along with some level of approvers and perhaps even all comments to be resolved and a CI build to complete successful. If the corporate policy is to have this as the branch policy it stands to reason that even repos for the said owners should also have this policy. So why wouldn't they?

This is why a slippery slope mindset can occur and what can wind up causing groups to NOT dogfood their own policy. Further along our example, the policy maker has a repo which only he uses, so no reason to have a PR model, right I mean who is going to approve it if he's the only one working on the repo? This is where the real benefit of dogfooding the policy comes into play. Right off the bat it will require the policy maker to grasp the need of doing one of two things.

The first option would be to create an addendum to the policy which lifts the branch policy in terms of self review or having any reviews at all, but perhaps they still need to create a CI build which would again be ideal and help ensure the intent of the policy stick, i.e. that the intent of the policy is to have repos which are of high quality and the CI build on the PR prevents the master branch from being in a "unbuildable" state.

The second option, and the one I personally prefer, the policy owner would need to invite someone into the repo to do reviews thus further ensuring that policy is more fully adopted.

So if you find yourself in a position of setting policy it is helpful for all involved, including you dear policy maker, to adhere to the policy yourself.