How To: Log and notify when Schlage BE469 lock is locked or unlocked with the keypad

[Tim Wilson]

Part 1: Introduction

 

The purpose of this series of posts is to explain in detail how to configure notifications and logging via network resources on an ISY whenever the keypad is used to lock or unlock a Schlage Z-Wave deadbolt.

 

Resources required

• Schlage BE469 Z-Wave deadbolt. I have the new Schlage Connect model, but I believe any BE469 lock will work.
• An account at Zapier.com (free). It's possible to do the same thing with IFTTT, but I find the interface and functionality more intuitive with Zapier.
• An account at Pushover.net (free). Pushover provides push notifications to iOS, Android, and desktop systems.
• An account at Airtable.com (free). Airtable is an intuitive, online relational database system. It has a rich API, sharing features, and integrates well with Zapier and IFTTT.
• ISY network module. The notification and logging in this how-to use HTTP POST requests to trigger Zapier and Pushover.

Prerequisites

 

In addition to the items in the section above, you need to have the Schlage lock added as a device to your ISY. If configured properly, the ISY will show the status of the deadbolt (e.g., Locked or Unlocked) as well as the user number associated with the keycode that was last used to lock or unlock the deadbolt.

 

You will need a database created in Airtable ready to receive the information from the lock and unlock events. A detailed Airtable tutorial is beyond the scope of this how-to, but the basic setup will be described in that section.

 

Finally, your ISY must be able to access the Internet in order to send the HTTP requests to Zapier and Pushover.

 

How-To Sections

 

Part 2: Setting up Pushover to send notifications from the ISY

Part 3: Creating an Airtable database

Part 4: Setting up a Zapier webhook

Part 5: Creating network resources in the ISY

Part 6: Creating the ISY program to trigger notification and logging

Edited May 16, 2017 by Tim Wilson

[G W]

I do all this on the ISY with no external programs. Why do you need steps 2, 3 and 4?

 

Sent from my Nexus 6P using Tapatalk

[Tim Wilson]

Thanks for the question, Gary. I'd love to see your solution.

 

As for me, I like the native notification functionality that Pushover provides. Text messages work too, but I prefer the notifications.

 

Airtable makes it possible to create a long-running record of lock/unlock events, and I already use it for lots of other database functions such as tracking my vehicle maintenance, managing my youth archery team, and tracking various aspects of a non-profit I run. 

 

Airtable's API might be possible to access directly from the ISY, but Zapier provides a handy abstraction for doing simple things like adding records.

[Tim Wilson]

Part 2: Setting up Pushover
 
Pushover is a free service (with limits) that allow for native push notifications to iOS, Android, or desktops. While it's possible to do notifications via text, native push notifications usually arrive faster and can be more succinct with additional formatting options. It's possible to generate push notifications by sending email to a custom email address, but we'll be using an HTTP POST to generate the notification directly.
 
After creating a free Pushover account, you'll need to add the devices you'd like to receive notifications in the "Your Devices" section. Pushover automatically creates a "User Key" associated with your account.

Pushover user key

The next step is to create an "application" within the Pushover system.

Pushover home screen

Creating a new Pushover application

Once you have created your "ISY" application, you'll get an API key to use with your ISY network resource in the HTTP POST request.

Pushover application API token

Take note of your "User Key" and the "API Token." You'll need those later to create your push notification from the ISY.

[Tim Wilson]

Part 3: Creating an Airtable database
 
I like to describe Airtable as the database took that Google should have created and included in their G Suite. It's user friendly and powerful enough to use for production-level projects. Their implementation of table relationships is especially well done. If you've done any database development you know how solving many-to-many relationships requires lots of extra tables. Airtable take care of the many-to-many relationships in the background gracefully. The free version of Airtable is quite robust, and I don't expect to run into any of its limits anytime soon.
 
For this project, I created a database (called a "Base" in Airtable's parlance) called "Home Access." When you create a new database, you can use one of their templates as a starting point, upload a CSV file, or start from scratch. I started from scratch and created four tables: Access Log, User Codes, Status Codes, and Alarm Codes. (I'm not using the Alarm Codes table at this point.) Creating multiple tables allows me to do lookups with the data I get from the ISY and create fields that make more sense.
 
Here's what the database looks like with all of the fields in the Access Logs table visible and some sample data.

Airtable access log

From left to right:

• "Timestamp" is the primary key and based on a formula that converts the "Datetime" field to the proper timezone.
• "Datetime" comes from the ISY via Zapier.
• "Status Code" is the lock status as reported by the ISY.
• "Action" is the verb associated with the action that was performed on the lock. I felt this made more sense than using the status code directly.
• "Lock User" is the user number associated with the keypad combination used to lock or unlock the deadbolt. This also comes from the ISY.
• "Person" is the name associated with the user number. This value is a lookup from the "User Codes" table.

The entries in the "Status Code" and "Lock User" fields are blue which indicates that those data are linked to corresponding tables. The field to the right of each of those are looked up through the linked fields. Under normal cirmcumstances, I keep those linked fields hidden, and the table looks like this:

Airtable abbreviated access log

The "User Codes" table is a simple mapping of user numbers to names.

User table

Likewise, the "Status Codes" table maps the ISY lock status code to the action that was done on the lock.

Access code table

Note: this was not meant to be an Airtable tutorial. There is a lot of great help documentation on their web site. For a quick intro, their "12-Minute Airtable Guide" is a good place to start.

 

For this project, I like the idea that I can create a permanent record of uses of the front door lock. We have a cleaning service that comes to the house every couple weeks, and they use their own keycde to access the house. The Airtable database allows me to go back in time and see when they came and went. (It also works for noting when your children get home at night.)

[KeviNH]

I use two notifications -- one sends me a SMTP message, the other appends a log to a local HTML file on the ISY itself..   No cloud services involved.

[Tim Wilson]

I haven't tried the ISY's built-in web server yet. That's a cool idea. I could push that kind of stuff to my Raspberry PI too.

[Tim Wilson]

Step 4: Setting up a Zapier webhook
 
Zapier is a competing service to IFTTT. I find it more user-friendly and possessing richer integrations than IFTTT with some of the tools I use most often. Each combination of a trigger and action is called a “zap.” The free plan allows only simple trigger-action combinations, but the paid plans offer multi-step zaps that can support more complex workflows.
 
For this project, the trigger is an HTTP POST sent by a network resource on my ISY, and the action is a connection to Airtable that creates a new record in my database with the data in the POST request. They steps to creating a new zap are shown below.
 
Overview of Zap steps
 
The first step is to create a trigger using the “Webhooks” app. This is similar to the “Maker Webhooks” service that IFTTT provides. Once you create a webhook for your zap, you’ll get a URL that you will enter in your ISY’s network resource.
 
Webhook URL
 
Once the webhook is created, you can send a test request, and Zapier will confirm that it was received.
 
Webhook test
 
With the webhook set, now you can choose the appropriate action app. We’re using Airtable for this project.
 
Selecting an action app
 
Through Zapier, you can use HTTP to create a new record, update an existing record, or search for a record. We want to create a new record in the database for each lock/unlock event.
 
Select “Creater Record”
 
Then you connect to your Airtable account. If you’ve used the Airtable channel previously, this step will already be set to go.
 
Connect to Airtable
 
Once you’re connected to Airtable, you need to choose the database where the new records will live. In my case, that table is called “Home Access.” This is the stage where you map the variables in the HTTP POST to the fields in your Airtable database. Zapier will pull in all the relevants fields from the table. You just need to click on the ones you want to populate and associate the POST variable.
 
Map to your database fields
 
 
That should do it. If you have a tool like Insomnia or Paw, you can test whether the connection works and a record gets created before you even create the network resource on your ISY.

[Scottmichaelj]

I haven't tried the ISY's built-in web server yet. That's a cool idea. I could push that kind of stuff to my Raspberry PI too.

Your pictures are really small and low res. Maybe think about updating them?

[KeviNH]

I haven't tried the ISY's built-in web server yet. That's a cool idea. I could push that kind of stuff to my Raspberry PI too.

 

Personally, I try to avoid building anything reliant on cloud services, too many of them just disappear, the rest end up playing games with their pricing models, so I no longer trust anything cloudy.  I'd rather self-host and deal with the overhead.   I prefer VMs like Photon OS™ over Pi, unless I need the IO lines.

 

Your pictures are really small and low res. Maybe think about updating them?

They're HUGE if you click through all the way to the 1600x1400px originals.

[Tim Wilson]

Photon OS looks really cool. We are a big VMware shop at work, but I hadn't seen that before. If Apple would ever updated their Mac Mini, I'd probably buy one in a heartbeat for a nice, compact home server.

 

I feel your pain about the cloud services. I usually take a wait and see attitude with them, and I don't tend to put anything mission critical on a service I'm getting for free. With Airtable, for example, I can export anything of my databases any time I want, so I feel OK about using it. And I don't hesitate to move to a paid tier for a service that I use a lot and want to support.

[G W]

Plus, I want EVERYTHING that I rely upon to work without an Internet connection. I don't mind using cloud services for play but that's about it.

 

Sent from my Nexus 6P using Tapatalk

[Tim Wilson]

Plus, I want EVERYTHING that I rely upon to work without an Internet connection.

 

 

Fortunately, the deadbolt itself functions just fine without an Internet connection. If I have a network hiccup, and someone's access doesn't get logged, I can live with that.

[MikeD]

Hi Tim,

 

Thank you for putting together this detailed roadmap on how you set up the services for logging. I use Prowl for notifications that works similar to Pushover. In the past I was doing rudimentary logging locally but have no need for it presently since there is only a few family members accessing my BE469 and Prowl notifies me when they do. Your tutorials are very informative to those who have yet to go down this road!

 

~Mike

[Tim Wilson]

Part 5: Creating network resources in the ISY

Now that we've got the HTTP hooks set up with Pushover and Zapier, we can create the "Network Resources" on the ISY to trigger them. Network resources have many options, but for this project we’re using an HTTP POST request. You’ll find the Network Resources in the ISY menu shown below.

Network resources menu

Let’s set up the resource for the Pushover notification first.

First, you’ll need to go back to your Pushover account and retrieve the API token, user key, and HTTP path that were created when you set up your ISY application. In the screenshot below, you can see the options required in the Network Resource. Pay attention to each one because the settings have to be just right to trigger the notification.

Pushover request

The HTTP POST request consist of “Headers” and the “Body” which is the request’s payload. The headers are the metadata. They describe the contents of the request. The “Body” section in the lower left is where you will craft your notification.

The data is formatted into one line in the body of the request, and it doesn’t all show in the screenshot. Here’s the entire thing.

token=TOKEN&user=KEY&title=Front+door+access&message=${sys.node.ZW005_1.ST}+at+${sys.time}+by+user+#${sys.node.ZW005_1.USRNUM}

I removed my token and user key. Replace the text in all caps with the values from your Pushover account. Notice how the string is composed of a series of name and value pairs separated by an & character. Besides the token and user key that are used to authenticate me to the Pushover service, I’ve added two other fields: title (optional) and message.

The title variable contains the text “Front+door+access” where the + characters are replaced by spaces. You’re not allowed to have literal spaces in HTTP requests of this type. Using the optional title variable create a nice title for the push notification in a bold font.

Pushover notification in iPhone

The contents of the message variable are generated dynamically by substituting the values of the three ISY variables below:

• ${sys.node.ZW005_1.ST} is the current status (“ST”) of the lock.
• ${sys.time} is the current system time.
• ${sys.node.ZW005_1.USRNUM} is the user number (“USERNUM”) corresponding to the keycode used to lock or unlock the deadbolt.

Your lock variables will probably be different than mine because they correspond to the ID of the specific Z-Wave device in your ISY.

Go ahead and save your Network Resource and click “Save” on the main Network Resource screen too. Your notification should be ready to use.

Now we’ll configure the network resource for the Zapier webhook.

As before, pay careful attention to the options listed in my screenshot. (I obscured the path to my Zapier webhook.)

Zapier request

One difference in this case is that the Content-Type header has been set to “application/json”. Javascript Object Notation (JSON) is a convenient (and highly legible) way to express variables and their contents. It’s a common format in the world of web applications.

Like before, in the body of the request, everything is on one line. Here’s the JSON data expanded so you can see it better.

{
"timestamp": "${sys.date} ${sys.time24} -05:00",
"st": "${sys.node.ZW005_1.ST}",
"user_num": "${sys.node.ZW005_1.USRNUM}"
}

With this JSON example, you have a name/value pair with quotes around each separated by a colon with each pair separated by commas. It looks a bit like CSS notation if you’ve done some web development.

The “st” and “user_num” values should look familiar from the Pushover example. The “timestamp” is a bit different. I want to log the time that the deadbolt was locked or unlocked by the user, and I want to include the date this time. (The date hardly seems necessary for an push notification since you check and then dismiss it.) Make sure you use the same variable names that you set up when you created your Zapier web hook.

The contents of the “timestamp” variable is a simple combination of the system date and time plus a timezone offset (-05:00) that helps Airtable determine the local time. Use whatever offset from GMT that makes sense for where you live.

Save your network resource, and you should be ready to log to Airtable.

[Tim Wilson]

Part 6: Creating the ISY program to trigger notification and logging
 
We've finally arrived to the final section. Now it's time to create a short program to trigger the Pushover notification and Airtable record creation when the deadbolt is locked or unlocked. Fortunately, the final step is the easiest one.

Go to the "Programs" menu and click "New Program" at the bottom of the screen. Enter the following:

Notification program

If
     '1st Floor - Living Room / Front Door Lock' is switched Alarm Unlocked by Keypad
   Or '1st Floor - Living Room / Front Door Lock' is switched Alarm Locked by Keypad

Then
     Resource 'Zapier -- Front door keypad activity'
     Resource 'Pushover -- Front door keypad activity'

Else
     - No Actions - (To add one, press 'Action')

Your program will use the corresponding name of your deadbolt. Mine is called "Front Door Lock", and I have it in a folder called "1st Floor - Living Room".

Simply add both notifications to the "Then" portion of the program and you're all set. The notifications will also correspond to the names you gave yours.

Good luck.

[lilyoyo1]

I use the built in stuff from the ISY. Much less programming involved. Since all of my alerts have subject lines, I simply have my email forward them to the proper folders for logging.

[KeviNH]

I use the built in stuff from the ISY. Much less programming involved. Since all of my alerts have subject lines, I simply have my email forward them to the proper folders for logging.

ISY994 can also add a custom "From" address for the notification. So, for example a different sender for "Lock" versus "Unlock", then many smartphones let you set a custom message notification sound if you add each of these From addresses as a contact.  

 

So I have a creaky door opening as my "Unlock" notification sound, and a door closing ("thud")as the "Lock" sound, so I don't even have to look at my phone to know which event just happened.  I do something similar with my garage door alert.