Repurposing an ESP 8266 as a stock ticker

A while back a good buddy of mine gave me a pretty sweet little arduino. It’s purpose was to reach out to the osquery github periodically and get the number of open pull requests and issues we had open, which was pretty relevant to me for some time.

Original ESP8266

Lately though I’ve been wondering what else I could do with this little device, and being relatively new to the world of Arduino, I decided to set out with a specific goal in mind. I’ve been pretty interested in various stock prices lately, so I figured I’d see if I could get this lil’ guy acting like a stock ticker as the board came with an Arduino feather OLED. Overall this isn’t too challenging, there’s plenty of tutorials out there for leveraging an ESP8266 to get a remote resource and then display this to the OLED, however I had a lot of trouble tracking down good examples of interfacing with an API over HTTPS. The examples definitely exist, but I figured I’d make my code public as it took me some time to aggregate all the pieces needed. In particular there’s an entire SSL library, BearSSL, that makes interacting with HTTPS endpoints pretty simple, all you need is the Sha1 fingerprint of your remote resource and the HTTPClient class handles the rest.

Serial.println("Fetching resource from " + uri);
  std::unique_ptr<BearSSL::WiFiClientSecure>secure_client(new BearSSL::WiFiClientSecure);


  HTTPClient https;
  auto ret = https.begin(*secure_client, uri);

  if (!ret) {
    Serial.println("Failed to connect to URI with " + String(ret));
    return 1;

  auto status = https.GET();
  if (status != HTTP_CODE_OK) {
    Serial.println("HTTPS GET failed with code " + String(status));
    return status;

  resp = https.getString();
  return status;

While it wasn’t too difficult getting the Arduino to fetch the resources, I had a bit of trouble finding a good financial API. Turns out they’re all super interested in making money or something. I eventually stumbled across Alpha Vantage, which has been pretty decent, unfortunately their API limits aren’t great at 5 calls/minute, 500 requests/day. This wouldn’t be a huge issue if I could batch up all of the ticker symbols I wanted to fetch summaries for at once, but I haven’t been able to figure out how that part works, so for now my stock data is pretty stale, and I eventually exhaust my API counts and just display nulls :\

While I was moving to the Great Pacific Northwest, the structure of the 3d printing on the original board broke, so I had to improvise a bit. Here’s where I ended up:

New Stock Ticker That solder job tho.

Overall it was a pretty great entry project to Arduino grabbing content from a remote endpoint over a TLS connection. Here’s a link to the code base in case anyone else is trying to hit HTTPS endpoints with an ESP 8266 on Arduino. Really this project was just setting the stage for my next post, where I built an arduinio to take the temperature and post the data to a remote SIEM, more to come soon, Happy hacking!

Building and deploying osquery extensions on Windows

Considering extensions on osquery are getting more and more support, I figured I'd throw up this guide for building osquery extensions on Windows in C++, as we're still working on developing osquery python extensions for Windows. What follows are the build steps for developing Windows C++ extensions in osquery:

First, from within the master osquery repo, drop your extensions implementation file into a subdirectory under external. In the below sample I created a new directory for my implemenation files (this is needed) called extension_test, and I created a .cpp with my code living in this folder:

C:\Users\thor\work\repos\osquery [master ≡]
λ  cat .\external\extension_test\sample_extension.cpp
// Note 1: Include the sdk.h helper.
#include <osquery/sdk.h>

using namespace osquery;

// Note 2: Define at least one plugin.
class ExampleTablePlugin : public tables::TablePlugin {
  tables::TableColumns columns() const override {
    return {
      std::make_tuple("example_text", TEXT_TYPE, ColumnOptions::DEFAULT),
      std::make_tuple("example_integer", INTEGER_TYPE, ColumnOptions::DEFAULT),

  QueryData generate(tables::QueryContext& request) override {
    QueryData results;
    Row r;

    r["example_text"] = "example";
    r["example_integer"] = INTEGER(1);
    return results;

// Note 3: Use REGISTER_EXTERNAL to define your plugin.
REGISTER_EXTERNAL(ExampleTablePlugin, "table", "example");

int main(int argc, char* argv[]) {
  // Note 4: Start logging, threads, etc.
  osquery::Initializer runner(argc, argv, ToolType::EXTENSION);

  // Note 5: Connect to osqueryi or osqueryd.
  auto status = startExtension("example", "0.0.1");
  if (!status.ok()) {
    LOG(ERROR) << status.getMessage();

  // Finally shutdown.
  return 0;

We can now make use of the osquery build scripts to generate the Visual Studio solution and also build the sample code for us. The CMake logic in our build scripts will automatically build any projects living under external so long as they're named extension_.

C:\Users\thor\work\repos\osquery [master ≡]
λ  .\tools\make-win64-binaries.bat
-- Welcome to osquery's build-- thank you for your patience! :)
-- For a brief tutorial see:
-- Building for platform Windows (windows, windows10)
-- Building osquery version  2.8.0-14-g9d332617 sdk 2.8.0
mkdir: cannot create directory 'C:/Users/thor/work/repos/osquery/build/windows10/generated': File exists
-- Configuring done
-- Generating done
-- Build files have been written to: C:/Users/thor/work/repos/osquery/build/windows10
Microsoft (R) Build Engine version 14.0.25420.1
Copyright (C) Microsoft Corporation. All rights reserved.
  osquery_extensions.vcxproj -> C:\Users\thor\work\repos\osquery\build\windows10\osquery\extensions\osquery_extensions.dir\Release\osquery_extensions.lib
  osquery_extensions.vcxproj -> C:\Users\thor\work\repos\osquery\build\windows10\osquery\extensions\osquery_extensions.dir\Release\osquery_extensions.lib
  osquery_tables_tests.vcxproj -> C:\Users\thor\work\repos\osquery\build\windows10\osquery\Release\osquery_tables_tests.exe
Test project C:/Users/thor/work/repos/osquery/build/windows10
    Start 1: osquery_tests
1/5 Test #1: osquery_tests ....................   Passed    1.92 sec
    Start 2: osquery_additional_tests
2/5 Test #2: osquery_additional_tests .........   Passed   38.74 sec
    Start 3: osquery_tables_tests
3/5 Test #3: osquery_tables_tests .............   Passed    1.94 sec
    Start 4: python_test_osqueryi
4/5 Test #4: python_test_osqueryi .............   Passed   67.82 sec
    Start 5: python_test_osqueryd
5/5 Test #5: python_test_osqueryd .............   Passed   19.57 sec

100% tests passed, 0 tests failed out of 5

Total Test time (real) = 130.09 sec

Now that we have our sample extension built we have two options. We can either launch Visual Studio and use this to build our extension project, or if we'd prefer to avoid VS, you can manually invoke msbuild to compile the project code for us.

I recommend just leveraging the Visual Studio route, however for the ambitious below are the instructions for manually invoking. It basically amounts to use a new Powershell commandlet to invoke the vcvarsall.bat script (note you could also just use a VS Native Build Tools command prompt, but I love my shell ^.^), and then invoking msbuild with our project name:

C:\Users\thor\work\repos\osquery [master ≡]
λ  cd .\build\windows10\

C:\Users\thor\work\repos\osquery\build\windows10 [master ≡]
λ  (Get-Command Invoke-BatchFile).Definition

  param([string]$Path, [string]$Parameters)
  $tempFile = [IO.Path]::GetTempFileName()
  cmd.exe /c " `"$Path`" $Parameters && set > `"$tempFile`" "
  Get-Content $tempFile | Foreach-Object {
    if ($_ -match "^(.*?)=(.*)$") {
      Set-Content "env:\$($matches[1])" $matches[2]
  Remove-Item $tempFile

C:\Users\thor\work\repos\osquery\build\windows10 [master ≡]
λ  Invoke-BatchFile "$env:VS140COMNTOOLS\..\..\vc\vcvarsall.bat" amd64

C:\Users\thor\work\repos\osquery\build\windows10 [master ≡]
λ   msbuild osquery.sln /p:Configuration=Release /p:PlatformType=x64 /p:Platform=x64 /t:external_extension_test /m /v:m

Microsoft (R) Build Engine version 14.0.25420.1
Copyright (C) Microsoft Corporation. All rights reserved.


  LINK : /LTCG specified but no code generation required; remove /LTCG from the link command line to improve linker performance
  external_extension_test.vcxproj -> C:\Users\thor\work\repos\osquery\build\windows10\external\Release\external_extension_test.ext.exe

Woot! We now have a external_extension_test.ext.exe that'll interoperate with our osquery binaries. Now let's look at how we deploy this extensions to our enterprise and hook it up to an osquery process.

To do this one will require a deployment process for shipping the binaries, and also for setting file permissions on extensions. Below I assume you take this step, and just show what's required to get the extension talking to the osquery service on startup:

C:\Users\thor\work\repos\osquery [master ≡]
λ  cp .\build\windows10\external\Release\external_extension_test.ext.exe C:\ProgramData\osquery\extensions\example.exe

C:\Users\thor\work\repos\osquery [master ≡]
λ  cat C:\ProgramData\osquery\osquery.flags

C:\Users\thor\work\repos\osquery [master ≡]
λ  . .\tools\provision\chocolatey\osquery_utils.ps1
C:\Users\thor\work\repos\osquery [master ≡]

λ  Set-DenyWriteAcl C:\ProgramData\osquery\extensions 'Add'

C:\Users\thor\work\repos\osquery [master ≡]
λ  osqueryi --flagfile=C:\ProgramData\osquery\osquery.flags
Using a virtual database. Need help, type '.help'
osquery> .tables
  => etc_services
  => example
  => file
osquery> select * from example;
| example_text | example_integer |
| example      | 1               |
osquery> .quit

C:\Users\thor\work\repos\osquery [master ≡]
λ  cat C:\ProgramData\osquery\osquery.conf
  "options": { },
  "schedule": {
    "system_info": {
      "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
      "interval": 3600
    "extension_sample": {
      "query": "SELECT * FROM example;",
      "interval": 5,
      "snapshot": "true"

C:\Users\thor\work\repos\osquery [master ≡]
λ  Start-Service osqueryd

C:\Users\thor\work\repos\osquery [master ≡]
λ  Get-Service osqueryd

Status   Name               DisplayName
------   ----               -----------
Running  osqueryd           osqueryd

C:\Users\thor\work\repos\osquery [master ≡]
λ  tail -f C:\ProgramData\osquery\log\osqueryd.snapshots.log
{"snapshot":[{"example_integer":"1","example_text":"example"}],"action":"snapshot","name":"extension_sample","hostIdentifier":"TESTFAC-MMFN45S","calendarTime":"Tue Sep 26 19:32:30 2017 UTC","unixTime":"1506454350","epoch":"0"}
{"snapshot":[{"example_integer":"1","example_text":"example"}],"action":"snapshot","name":"extension_sample","hostIdentifier":"TESTFAC-MMFN45S","calendarTime":"Tue Sep 26 19:32:35 2017 UTC","unixTime":"1506454355","epoch":"0"}
{"snapshot":[{"example_integer":"1","example_text":"example"}],"action":"snapshot","name":"extension_sample","hostIdentifier":"TESTFAC-MMFN45S","calendarTime":"Tue Sep 26 19:32:41 2017 UTC","unixTime":"1506454361","epoch":"0"}
{"snapshot":[{"example_integer":"1","example_text":"example"}],"action":"snapshot","name":"extension_sample","hostIdentifier":"TESTFAC-MMFN45S","calendarTime":"Tue Sep 26 19:32:46 2017 UTC","unixTime":"1506454366","epoch":"0"}

λ  l C:\ProgramData\osquery\log\

    Directory: C:\ProgramData\osquery\log

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----        9/26/2017  12:32 PM            490 osqueryd.INFO.20170926-123230.10976
-a----        9/26/2017  12:17 PM              0 osqueryd.results.log
-a----        9/26/2017  12:33 PM           3632 osqueryd.snapshots.log

There's a lot happening up above, let's walk through some of this step-by-step.

First, we copy the extension binary we built earlier to C:\ProgramData\osquery\extensions (Again Note in your environment it's assumed you'd deploy here using Chef or Puppet). Once the binary is in place, we need to set the proper file permissions to assure the binary will load. To do this we make use of our helper Powershell libraries by 'dot' sourcing the script, and invoking the Set-DenyWriteAcl cmdlet with . .\tools\provision\chocolatey\osquery_utils.ps1, and then we call the function, Set-DenyWriteAcl C:\ProgramData\osquery\extensions 'Add'.

Now that the binary is in place, we update our osquery flags file to turn on extensions, ensure the full path to our extensions binary is in our autoload file, C:\ProgramData\osquery\extensions.load, and then finally ensure we have some scheduled queries setup in osquery.conf. If you're using a TLS server for your configuration, you'd simply want to schedule a few queries against your extension there.

Lastly, we turn everything on locally, and verify we're getting logged output into our filesystem logger plugin.

Happy Hacking!

Local configuration for osquery on Windows

I pretty commonly get asked by folks for a generic Windows configuration for osquery, as the example configuration pack in the osquery repository favors posix systems a bit (Something we're hoping to make better).

While the configuration is a core component to what queries one is interested in for their enterprise, we typically perform most of the daemon configuration through the --flagsfile. Below is the flags file I typically use with the following config.


And here's the corresponding osquery.conf

  "schedule": {
    "heartbeat": {
      "query": "select si.hostname, si.uuid, si.computer_name, up.total_seconds as uptime from system_info si, uptime up;",
      "interval": 900,
      "snapshot": "true"
    "windows_events": {
      "query": "select * from windows_events;",
      "interval": 180
    "interace_addr_mac": {
      "query": "select ia.address, ia.mask, id.mac from interface_addresses ia, interface_details id where ia.interface = id.interface;",
      "interval": 300,
      "snapshot": "true"
    "logged_in_users": {
      "query": "select user, time, pid from logged_in_users where type='active';",
      "interval": 300,
      "snapshot": "true"
    "listening_ports": {
      "query": "select * from listening_ports;",
      "interval": 300,
      "snapshot": "true"
    "processes": {
      "query": "select * from processes;",
      "interval": 300,
      "snapshot": "true"
    "scheduled_tasks": {
      "query": "select * from scheduled_tasks;",
      "interval": 3600
    "startup_items": {
      "query": "select * from startup_items;",
      "interval": 3600,
      "snapshot": "true"
    "drivers": {
      "query": "select * from drivers;",
      "interval": 86400,
      "snapshot": "false"
    "services": {
      "query": "select * from services;",
      "interval": 86400,
      "snapshot": "false"
    "etc_hosts": {
      "query": "select * from etc_hosts;",
      "interval": 86400,
      "snapshot": "false"
    "windows_patches": {
      "query": "select * from patches;",
      "interval": 86400,
      "snapshot": "false"
    "system_users": {
      "query": "select * from users;",
      "interval": 86400,
      "snapshot": "false"
  "decorators": {
    "load": [
      "SELECT uuid AS host_uuid FROM system_info;",
      "SELECT user AS username FROM logged_in_users ORDER BY time DESC LIMIT 1;"
  // Fill this out with custom packs, or the example packs from the repo
  "packs": { }

Hello world.

First post!

I need some content to edit colors and such. More to come! For now here's some generic content, along with some Powershell code that builds Apache Thrift libraries statically for osquery :D

#  Copyright (c) 2014-present, Facebook, Inc.
#  All rights reserved.
#  This source code is licensed under the BSD-style license found in the
#  LICENSE file in the root directory of this source tree. An additional grant
#  of patent rights can be found in the PATENTS file in the same directory.

# Update-able metadata
# $version - The version of the software package to build
# $chocoVersion - The chocolatey package version, used for incremental bumps
#                 without changing the version of the software package
$version = '0.10.0'
$chocoVersion = '0.10.0-r4'
$packageName = 'thrift-dev'
$projectSource = ''
$packageSourceUrl = ''
$authors = 'thrift-dev'
$owners = 'thrift-dev'
$copyright = ''
$license = ''
$url = "$"
$parentPath = $(Split-Path -Parent $MyInvocation.MyCommand.Definition)
$patchfiles = @(
  Join-Path $parentPath "patches/thrift-dev.patch"

# Invoke our utilities file
. $(Join-Path $parentPath "osquery_utils.ps1")

# Invoke the MSVC developer tools/env
Invoke-BatchFile "$env:VS140COMNTOOLS\..\..\vc\vcvarsall.bat" amd64

# Time our execution
$sw = [System.Diagnostics.StopWatch]::startnew()

# Keep the location of build script, to bring with in the chocolatey package
$buildScript = $MyInvocation.MyCommand.Definition

# Create the choco build dir if needed
$buildPath = Get-OsqueryBuildPath
if ($buildPath -eq '') {
  Write-Host '[-] Failed to find source root' -foregroundcolor red
$chocoBuildPath = "$buildPath\chocolatey\$packageName"
if (-not (Test-Path "$chocoBuildPath")) {
  New-Item -Force -ItemType Directory -Path "$chocoBuildPath"
Set-Location $chocoBuildPath

# Retreive the source
if (-not (Test-Path "$packageName-$")) {
  Invoke-WebRequest $url -OutFile "$packageName-$"

# Extract the source
$sourceDir = Join-Path $(Get-Location) "thrift-$version"
if (-not (Test-Path $sourceDir)) {
  $7z = (Get-Command '7z').Source
  $7zargs = "x $packageName-$"
  Start-OsqueryProcess $7z $7zargs
Set-Location $sourceDir

# Build the libraries
$buildDir = New-Item -Force -ItemType Directory -Path 'osquery-win-build'
Set-Location $buildDir

# Patches are applied in this section before build
# Windows TPipe implementations are _very_ noisy, so we squelch the output
Add-Content `
  -NoNewline `
  -Path "$buildDir\..\lib\cpp\CMakeLists.txt" `
  -Value "`nadd_definitions(-DTHRIFT_SQUELCH_CONSOLE_OUTPUT=1)"

# Generate the solution files
$cmake = (Get-Command 'cmake').Source
$cmakeArgs = @(
  '-G "Visual Studio 14 2015 Win64"',
Start-OsqueryProcess $cmake $cmakeArgs

# Build the libraries
$msbuild = (Get-Command 'msbuild').Source
$sln = 'Apache Thrift.sln'
$targets = @(
foreach ($target in $targets) {
  $msbuildArgs = @(
  Start-OsqueryProcess $msbuild $msbuildArgs

  # Bundle debug libs for troubleshooting
  $msbuildArgs = @(
  Start-OsqueryProcess $msbuild $msbuildArgs

# Lastly build the Thrift Compiler
$msbuildArgs = @(
Start-OsqueryProcess $msbuild $msbuildArgs

# If the build path exists, purge it for a clean packaging
$chocoDir = Join-Path $(Get-Location) 'osquery-choco'
if (Test-Path $chocoDir) {
  Remove-Item -Force -Recurse $chocoDir

# Construct the Chocolatey Package
New-Item -ItemType Directory -Path $chocoDir
Set-Location $chocoDir
$includeDir = New-Item -ItemType Directory -Path 'local\include'
$libDir = New-Item -ItemType Directory -Path 'local\lib'
$binDir = New-Item -ItemType Directory -Path 'local\bin'
$srcDir = New-Item -ItemType Directory -Path 'local\src'

Write-NuSpec `
  $packageName `
  $chocoVersion `
  $authors `
  $owners `
  $projectSource `
  $packageSourceUrl `
  $copyright `

# Rename the Debug libraries to end with a `_dbg.lib`
foreach ($lib in Get-ChildItem "$buildDir\lib\Debug\") {
  $toks = $lib.Name.split('.')
  $newLibName = $toks[0..$($toks.count - 2)] -join '.'
  $suffix = $toks[$($toks.count - 1)]
  Copy-Item `
    -Path $lib.Fullname `
    -Destination "$libDir\$newLibName`_dbg.$suffix"
Copy-Item "$buildDir\lib\Release\*" $libDir
Copy-Item "$buildDir\bin\Release\*" $binDir
Copy-Item -Recurse "$buildDir\..\lib\cpp\src\thrift" $includeDir
Copy-Item $buildScript $srcDir
choco pack

Write-Host "[*] Build took $($sw.ElapsedMilliseconds) ms" `
  -ForegroundColor DarkGreen
if (Test-Path "$packageName.$chocoVersion.nupkg") {
  Write-Host `
    "[+] Finished building $packageName v$chocoVersion." `
    -ForegroundColor Green
else {
  Write-Host `
    "[-] Failed to build $packageName v$chocoVersion." `
    -ForegroundColor Red