4N6 Database Information

Here is the 4N6 database table creation script: db_defn.sql

Here is a list of commonly used SQL queries:

• Get the pid of the parent of the given process, and the time that it forked to create the child. This used recursively to get all ancestors of a given pid:
  SELECT pid, date FROM event
WHERE syscall = 2 OR syscall = 190 OR syscall = 120
AND rc = ?
• Get the file descriptors returned by calls to open executed by the given process between the given times:
  SELECT rc FROM event
WHERE rc > 0
AND source_path = ?
AND syscall = 5
AND date >= ?
AND date <= ?
• Get all of the calls to dup executed by the given process between the given times:
  SELECT oldfd, newfd FROM dup, event
WHERE dup.parent = event.id
AND event.pid = ?
AND event.date >= ?
AND event.date <= ?
ORDER BY event.date
  This query, in conjunction with the previous query, can be used to figure out whether or not a given process opened a file and, if so, what file descriptors it has that point to that file. Factor in the first query, and you can do some recursive stuff to figure out if any ancestors of the process opened the file, as well.
  Note that close calls are not currently handled by this SELECT statement. I intended to handle them by performing a UNION of this statement with another statement to get information about close calls. However, you can't do UNION in MySQL 3.x. This is on my TODO list to fix.
• Query to extract all the PIDs of ssh sessions:
  SELECT DISTINCT e.pid, e.ppid, e.name, e1.pid, e1.ppid, e1.name
FROM event e, event e1
WHERE e.name LIKE "bash"
AND e.pid = e1.pid
AND e1.name LIKE "ssh"
• Query to generate the data written to the stdin, stdout and stderr by the ssh session as well as by all of its children:
  SELECT event.pid, event.id, io.fd, io.data
FROM io, event
WHERE io.parent = event.id
  Repeat this query for all the children of the ssh PID and their children, and so on. Then, using the "dup" and "ancestor" queries mentioned above and retrieve only the IO data which was written to the FD's corresponding to stdin, stdout and stderr. This step is necessary because there can be FDs other than 0, 1 or 2 that point to stderr, and there is the possibility that 0, 1 or 2 might not point to stdin, stdout and stderr.
• Query to get an ordered list of system calls that affect the open file- descriptor table for the current process (used by get_io.c:
  SELECT event.date, oldfd, newfd, 0, syscall, dup.id FROM dup, event WHERE
dup.parent = event.id AND
event.pid = %d AND
event.roll_count = %d AND
( dup.cmd = 0 OR dup.cmd = 2 OR dup.cmd IS NULL OR event.syscall = 6) AND
event.rc >= 0
UNION
SELECT event.date, fd, 0, 0, syscall , io.id FROM io, event WHERE
io.parent = event.id AND
event.pid = %d AND
event.roll_count = %d AND
event.syscall = 4 AND
event.rc >= 0
UNION
SELECT event.date, 0, 0, rc, syscall, event.id FROM event WHERE
event.pid = %d AND
event.roll_count = %d AND
(syscall = 120 OR syscall = 2 OR syscall = 190 OR syscall = 11)
ORDER BY event.date"