Bash Script to log file modifications with osquery

Here's a bash script that uses osquery to log which files in a specific folder have been modified over a 15 minute period. My use case here wasn't file integrity monitoring, for that you would want to use file events.

Here's the script:

#!/bin/bash

WORKSPACE_DIR=`echo ~/workspace`
LOG_DIR=`echo ~/Documents/Logs/osquery_file_logs/`
AGO_TIMESTAMP=`date -v-15M "+%s"`
LOG_FOLDER_NAME=`date "+%Y-%m"`
LOG_FILE_NAME=`date "+%Y-%m-%d"`
LOG_FILE="$LOG_DIR/$LOG_FOLDER_NAME/$LOG_FILE_NAME.txt"

mkdir -p "$LOG_DIR/$LOG_FOLDER_NAME"

touch $LOG_FILE

/usr/local/bin/osqueryi --csv --header=false "SELECT datetime(mtime,'unixepoch') AS file_last_modified_time, path FROM file WHERE path LIKE '$WORKSPACE_DIR/%%' AND type != 'directory' AND mtime > $AGO_TIMESTAMP ORDER BY mtime ASC;" >> $LOG_FILE

I tested this bash script on a Mac, but I think it would work just the same on linux. You'll need to install osquery first. If you set this up in a cron job running every 15 minutes, you'll have a nice log of what files where changed when.

A better way?

It has occurred to me that using osquery here is probably a bit overkill for this task, I think you could create a more rudimentary version of this script like this:

find $WORKSPACE_DIR -type f -newer $LOG_DIR/timestamp >> $LOG_FILE
touch $LOG_DIR/timestamp 

Using the -newer flag of the find command it will return all files newer than our $LOG_DIR/timestamp, and because we touch that file after the script runs, the next time it runs it will show all files changed since it was last run.

That doesn't include the last modified dates in the log file, but it is possible to do with a little more work.

Comments

[James Moberg]

I've been using "FileList" (free; Windows) to identify Full Path, Size, Last Modified, Last Accessed, Creation Date, Extension and MD5 Checksum and then save the data to a local CSV file.
jam-software.com/filelist

c:\FileList\FileList.exe  /MINDATE 2018-11-01 /NOTITLE /FULLPATH /FILTER *.cfm  /COLUMNS MD5 c:\www\ > c:\FileList\WWW_changes.csv

Check out the manual as there are lots of options and examples of how to use:
jam-software.com/filelist/manual.php