davidmerrick · March 7, 2021 21:27 · Sep 2, 2019 · Sep 2, 2019 · Aug 30, 2019 · Aug 27, 2019
diff --git a/README.md b/README.md
@@ -23,16 +23,7 @@ sudo launchctl load /Library/LaunchDaemons/com.facebook.osqueryd.plist
 
 Select the most recent values from Athena:
 
-`WITH summary AS (
-    SELECT p.hostidentifier, 
-           p.calendartime, 
-           p.columns.gigs_free as gigs_free, 
-           ROW_NUMBER() OVER(PARTITION BY p.hostidentifier 
-                                 ORDER BY p.calendartime DESC) AS rk
-      FROM osquery_merrick p)
-SELECT s.hostidentifier, s.calendartime, s.gigs_free
-  FROM summary s
- WHERE s.rk = 1`
+`WITH summary AS ( SELECT p.hostidentifier, p.unixtime, p.calendartime, p.columns.gigs_free as gigs_free, ROW_NUMBER() OVER(PARTITION BY p.hostidentifier ORDER BY p.unixtime DESC) AS rk FROM osquery_merrick p) SELECT s.hostidentifier, s.calendartime, s.gigs_free FROM summary s WHERE s.rk = 1`
 
 # Alerting
 

diff --git a/README.md b/README.md
@@ -34,6 +34,10 @@ SELECT s.hostidentifier, s.calendartime, s.gigs_free
   FROM summary s
  WHERE s.rk = 1`
 
+# Alerting
+
+- For now, I'm just writing my own custom alerting, but this looks promising: https://github.com/airbnb/streamalert.
+
 # Reference:
 - https://medium.com/@crhuber/macos-security-with-osquery-and-aws-kinesis-firehose-7acabdab2363
 - https://osquery.readthedocs.io/en/stable/installation/install-osx/
diff --git a/README.md b/README.md
@@ -19,6 +19,21 @@ sudo cp com.facebook.osqueryd.plist /Library/LaunchDaemons/
 sudo launchctl load /Library/LaunchDaemons/com.facebook.osqueryd.plist
 ```
 
+# Useful SQL
+
+Select the most recent values from Athena:
+
+`WITH summary AS (
+    SELECT p.hostidentifier, 
+           p.calendartime, 
+           p.columns.gigs_free as gigs_free, 
+           ROW_NUMBER() OVER(PARTITION BY p.hostidentifier 
+                                 ORDER BY p.calendartime DESC) AS rk
+      FROM osquery_merrick p)
+SELECT s.hostidentifier, s.calendartime, s.gigs_free
+  FROM summary s
+ WHERE s.rk = 1`
+
 # Reference:
 - https://medium.com/@crhuber/macos-security-with-osquery-and-aws-kinesis-firehose-7acabdab2363
 - https://osquery.readthedocs.io/en/stable/installation/install-osx/
diff --git a/README.md b/README.md
@@ -9,5 +9,16 @@ Set up a Kinesis stream called `osquery-firehose`.
 2. Run the command in `osquery.sh`.
 3. This will log to `/var/log/osquery/osqueryd.results.log`
 
+# LaunchDaemon
+
+To create a LaunchDaemon which will run on startup, do the following:
+
+```
+sudo cp path/to/your.conf /var/osquery/osquery.conf
+sudo cp com.facebook.osqueryd.plist /Library/LaunchDaemons/
+sudo launchctl load /Library/LaunchDaemons/com.facebook.osqueryd.plist
+```
+
 # Reference:
-- https://medium.com/@crhuber/macos-security-with-osquery-and-aws-kinesis-firehose-7acabdab2363
+- https://medium.com/@crhuber/macos-security-with-osquery-and-aws-kinesis-firehose-7acabdab2363
+- https://osquery.readthedocs.io/en/stable/installation/install-osx/
diff --git a/com.facebook.osqueryd.plist b/com.facebook.osqueryd.plist
@@ -0,0 +1,22 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
+<plist version="1.0">
+<dict>
+  <key>KeepAlive</key>
+  <true/>
+  <key>Label</key>
+  <string>com.facebook.osqueryd</string>
+  <key>ProgramArguments</key>
+  <array>
+    <string>/usr/local/bin/osqueryd</string>
+  </array>
+  <key>StandardErrorPath</key>
+  <string>/var/log/osquery/launcher-stderr.log</string>
+  <key>StandardOutPath</key>
+  <string>/var/log/osquery/launcher-stdout.log</string>
+  <key>RunAtLoad</key>
+  <true/>
+  <key>ThrottleInterval</key>
+  <integer>60</integer>
+</dict>
+</plist>
diff --git a/README.md b/README.md
@@ -2,8 +2,7 @@ Goal here is to monitor disk space usage on my parents' iMac, push it to Kinesis
 
 # Prerequisites
 
-Set up a Kinesis stream called `osquery-firehose`. Todo: Get the role set up correctly for this.
-
+Set up a Kinesis stream called `osquery-firehose`. 
 # Steps
 
 1. Download the config.

diff --git a/aws_perms.json b/aws_perms.json
@@ -0,0 +1,14 @@
+{
+    "Version": "2012-10-17",
+    "Statement": [
+        {
+            "Sid": "",
+            "Effect": "Allow",
+            "Action": [
+                "firehose:PutRecord",
+                "firehose:PutRecordBatch"
+            ],
+            "Resource": "ARN for your stream"
+        }
+    ]
+}
diff --git a/config.json b/config.json
@@ -2,10 +2,10 @@
   "options": {
     "host_identifier": "hostname",
     "schedule_splay_percent": 10,
-    "logger_plugin": "filesystem,aws_firehose"
+    "logger_plugin": "filesystem,aws_firehose",
     "aws_firehose_stream": "osquery-firehose",
-    "aws_access_key_id": "XYZ123",
-    "aws_secret_access_key": "XYZ123",
+    "aws_access_key_id": "nope",
+    "aws_secret_access_key": "nope",
     "aws_region": "us-west-2"
   },
   "schedule": {

diff --git a/README.md b/README.md
@@ -1,6 +1,11 @@
 Goal here is to monitor disk space usage on my parents' iMac, push it to Kinesis, and alert if it hits a certain threshold.
 
+# Prerequisites
+
+Set up a Kinesis stream called `osquery-firehose`. Todo: Get the role set up correctly for this.
+
 # Steps
+
 1. Download the config.
 2. Run the command in `osquery.sh`.
 3. This will log to `/var/log/osquery/osqueryd.results.log`

diff --git a/config.json b/config.json
@@ -2,7 +2,7 @@
   "options": {
     "host_identifier": "hostname",
     "schedule_splay_percent": 10,
-    "logger_plugin”: “filesystem,aws_firehose"
+    "logger_plugin": "filesystem,aws_firehose"
     "aws_firehose_stream": "osquery-firehose",
     "aws_access_key_id": "XYZ123",
     "aws_secret_access_key": "XYZ123",

diff --git a/config.json b/config.json
@@ -1,7 +1,12 @@
 {
   "options": {
     "host_identifier": "hostname",
-    "schedule_splay_percent": 10
+    "schedule_splay_percent": 10,
+    "logger_plugin”: “filesystem,aws_firehose"
+    "aws_firehose_stream": "osquery-firehose",
+    "aws_access_key_id": "XYZ123",
+    "aws_secret_access_key": "XYZ123",
+    "aws_region": "us-west-2"
   },
   "schedule": {
     "disk_usage": {

diff --git a/README.md b/README.md
@@ -1,4 +1,9 @@
 Goal here is to monitor disk space usage on my parents' iMac, push it to Kinesis, and alert if it hits a certain threshold.
 
-Reference:
+# Steps
+1. Download the config.
+2. Run the command in `osquery.sh`.
+3. This will log to `/var/log/osquery/osqueryd.results.log`
+
+# Reference:
 - https://medium.com/@crhuber/macos-security-with-osquery-and-aws-kinesis-firehose-7acabdab2363
diff --git a/osquery.sh b/osquery.sh
@@ -0,0 +1 @@
+sudo osqueryd --config_path=/path/to/config.json
diff --git a/config.json b/config.json
@@ -1,7 +1,12 @@
-"schedule": {
-"disk_usage": {
-  "query": "select path, type, round((blocks_available * blocks_size *10e-10),2) as gigs_free from mounts where path='/';",
-  "interval": 43200, // 12 hours in seconds
-  "description": "disk_usage",
-  "value": "Current disk usage"
-},
+{
+  "options": {
+    "host_identifier": "hostname",
+    "schedule_splay_percent": 10
+  },
+  "schedule": {
+    "disk_usage": {
+      "query": "select path, type, round((blocks_available * blocks_size *10e-10),2) as gigs_free from mounts where path='/';",
+      "interval": 43200
+    }
+  }
+}
diff --git a/README.md b/README.md
@@ -0,0 +1,4 @@
+Goal here is to monitor disk space usage on my parents' iMac, push it to Kinesis, and alert if it hits a certain threshold.
+
+Reference:
+- https://medium.com/@crhuber/macos-security-with-osquery-and-aws-kinesis-firehose-7acabdab2363
diff --git a/config.json b/config.json
@@ -0,0 +1,7 @@
+"schedule": {
+"disk_usage": {
+  "query": "select path, type, round((blocks_available * blocks_size *10e-10),2) as gigs_free from mounts where path='/';",
+  "interval": 43200, // 12 hours in seconds
+  "description": "disk_usage",
+  "value": "Current disk usage"
+},
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		sudo osqueryd --config_path=/path/to/config.json
No results found