Revision

  $Id: ftimes-map-macsql.base,v 1.2 2008/12/23 21:00:17 klm Exp $

Purpose

  This recipe demonstrates how to use MySQL and FTimes map data to
  create a MAC timeline.

Motivation

  Often, it's useful to create a timeline of file activity based on
  MAC times -- MTime, ATime, and CTime.  Respectively, these
  timestamps represent the last time a given file was modified,
  accessed, or its status (i.e., inode) changed.

Requirements

  Cooking with this recipe requires an operational, UNIX-based MySQL
  server preloaded with map data according to the following recipe:

    http://ftimes.sourceforge.net/Files/Recipes/ftimes-map-dbprep.txt

  The commands presented throughout this recipe were designed to be
  executed within a Bourne shell (i.e., sh or bash).

Time to Implement

  Assuming that you have satisfied all the requirements/prerequisites,
  this recipe should take approximately 15 minutes to implement.

Solution

  1. Start MySQL's Command Line Interface (CLI) and create two tables:
     all_time and mac.  If these tables already exist, then either
     drop them or remove any records in them.

       CREATE TABLE IF NOT EXISTS all_time (time DATETIME NOT NULL, PRIMARY KEY (time));
       DELETE FROM all_time;

       CREATE TABLE IF NOT EXISTS mac (time DATETIME NOT NULL, mac VARCHAR(3) NOT NULL, name BLOB NOT NULL, joiner VARBINARY(32) NOT NULL, INDEX time_index (time));
       DELETE FROM mac;

  2. Populate the all_time table by inserting all distinct MTime
     timestamps.

       INSERT IGNORE INTO all_time (time) SELECT DISTINCT mtime FROM snapshot;

  3. Repeat step two for both ATime and CTime timestamps.

       INSERT IGNORE INTO all_time (time) SELECT DISTINCT atime FROM snapshot;
       INSERT IGNORE INTO all_time (time) SELECT DISTINCT ctime FROM snapshot;

  4. Next populate the mac table.

       INSERT INTO mac SELECT time, CONCAT(IF(time=mtime, 'M', '.'), IF(time=atime, 'A', '.'), IF(time=ctime, 'C', '.')) AS mac, name, joiner FROM all_time, snapshot WHERE time=mtime;
       INSERT INTO mac SELECT time, CONCAT(IF(time=mtime, 'M', '.'), IF(time=atime, 'A', '.'), IF(time=ctime, 'C', '.')) AS mac, name, joiner FROM all_time, snapshot WHERE time=atime;
       INSERT INTO mac SELECT time, CONCAT(IF(time=mtime, 'M', '.'), IF(time=atime, 'A', '.'), IF(time=ctime, 'C', '.')) AS mac, name, joiner FROM all_time, snapshot WHERE time=ctime;

  5. At this point the MAC data is ready to be viewed.  Retrieve a few
     records with the following query:

       SELECT DISTINCT mac, time, name FROM mac ORDER BY time DESC LIMIT 10;

     If that output looks reasonable, drop the LIMIT clause and run
     the query again to view all of the output.

       SELECT DISTINCT mac, time, name FROM mac ORDER BY time DESC;

     If there's too much data to easily view it from within the MySQL
     CLI, you can use the following query to write the output to a
     file, which can be viewed in an editor or browser.  Note: the
     output file you choose must not exist.

       SELECT DISTINCT mac, time, name INTO OUTFILE '/tmp/mac.output' FIELDS TERMINATED BY '|' FROM mac ORDER BY time DESC;

  6. Appendix 1 contains a script that performs the steps listed
     above.  This script assumes that user authentication is not
     required (or that it is handled via my.cnf).  It also assumes
     that the table containing map data is called 'snapshot'.  Adjust
     those parameters as necessary before attempting to execute the
     script.

Credits

  This recipe was brought to you by Klayton Monroe.

References

Appendix 1

  The following command may be used to extract this Appendix:

    $ sed -e '1,/^--- ftimes-map-macsql.sh ---$/d; /^--- ftimes-map-macsql.sh ---$/,$d' ftimes-map-macsql.txt > ftimes-map-macsql.sh

--- ftimes-map-macsql.sh ---
#!/bin/sh

ALL_TABLE=all_time
MAC_TABLE=mac
MAP_TABLE=snapshot

if test $# -ne 2; then
  echo ""
  echo "Usage: $0 dbname outfile"
  echo ""
  exit 1
else
  DB=$1
  OUTFILE=$2
fi

if [ -r ${OUTFILE} ]; then
  echo "OutFile=${OUTFILE} Error='File must not exist.'"
  exit 2
fi

mysql ${DB} <<EOF
CREATE TABLE IF NOT EXISTS ${ALL_TABLE} (time DATETIME NOT NULL, PRIMARY KEY (time));
DELETE FROM ${ALL_TABLE};
CREATE TABLE IF NOT EXISTS ${MAC_TABLE} (time DATETIME NOT NULL, mac VARCHAR(3) NOT NULL, name BLOB NOT NULL, joiner VARBINARY(32) NOT NULL, INDEX time_index (time));
DELETE FROM ${MAC_TABLE};
INSERT IGNORE INTO ${ALL_TABLE} (time) SELECT DISTINCT atime FROM ${MAP_TABLE};
INSERT IGNORE INTO ${ALL_TABLE} (time) SELECT DISTINCT mtime FROM ${MAP_TABLE};
INSERT IGNORE INTO ${ALL_TABLE} (time) SELECT DISTINCT ctime FROM ${MAP_TABLE};
INSERT INTO ${MAC_TABLE} SELECT time, CONCAT(IF(time=mtime, 'M', '.'), IF(time=atime, 'A', '.'), IF(time=ctime, 'C', '.')) AS mac, name, joiner FROM ${ALL_TABLE}, ${MAP_TABLE} WHERE time=mtime;
INSERT INTO ${MAC_TABLE} SELECT time, CONCAT(IF(time=mtime, 'M', '.'), IF(time=atime, 'A', '.'), IF(time=ctime, 'C', '.')) AS mac, name, joiner FROM ${ALL_TABLE}, ${MAP_TABLE} WHERE time=atime;
INSERT INTO ${MAC_TABLE} SELECT time, CONCAT(IF(time=mtime, 'M', '.'), IF(time=atime, 'A', '.'), IF(time=ctime, 'C', '.')) AS mac, name, joiner FROM ${ALL_TABLE}, ${MAP_TABLE} WHERE time=ctime;
SELECT DISTINCT mac, time, name INTO OUTFILE '${OUTFILE}' FIELDS TERMINATED BY '|' FROM ${MAC_TABLE} ORDER BY time DESC;
EOF
--- ftimes-map-macsql.sh ---