Database for working data

Summary

File opengatemd.db : for OpengateMd

TABLE sessionmd = table of active sessions for OpengateMd (add a record at the start of a session, and delete it at the end of the session)

 Field Name
 Meaning  Commentary
 macAddress  MAC address

 string, primary key, MAC address of the terminal

 userId  User ID

 string, User ID registering the MAC address

 extraId  Extra ID

 string, optional ID for the user

 openTime  Open time
 integer, UNIX TIME, the time when the firewall is opened
 checkTime  Check time

 integer, UNIX TIME, the time when the packet relating the terminal is checked most recently

 ruleNumber  IPFW rule number
 integer, IPFW rule number allowing the terminal


 The record count is equal to the active session. Not increase with time.

TABLE macinfo = table for storing mac address and related information (store all of captured mac address (including addresses not registered in management database)

 Field Name
 Meaning  Commentary
 macAddress  MAC address
 string, primary key, MAC address of the terminal
 detectTime  Detecting time
 integer, UNIX TIME, the time when the MAC address is detected most recently
 ttl  Time to live  integer, TTL/Hop-limit of the packet, used for NAT/Router detection
 isNat  Flag for NAT
 integer, 1=via nat, 0=no nat/router, 2=via router,  -1=error, suspected from packet TTL value,


 The record count is equal to the number of mac addresses used in the network previously. Increase slowly.

TABLE macippair = table of active MAC-IP pairs for OpengateMd (add a record at finding a pair, and delete it at the end of the session)

 Field Name
 Meaning  Commentary
 macAddress  MAC address

 string, MAC address of the terminal

 ipAddress  IP address

 string, IP address of the terminal

 findTime  Find time
 integer, UNIX TIME, the time when the pairl is found


 The record count is equal to the active pairs. Not increase with time.

File opengatemng.db : for OpengateMmng (opengatemchk,opengatemreg,opengatemup)

TABLE maccheck = table for mac address checking (use as mac address list in opengatemchk page)

 Field Name
 Meaning  Commentary
 macAddress  MAC address
 string, primary key
 ipv4  IPv4 address
 string
 ipv6  IPv6 address
 string
 pid  Process ID
 integer, the pid of the child process for closing  at the temporary open
 ruleIpv4  IPFW rule number for IPv4 address
 integer, rule number for IPv4 at temporary open
 ruleIpv6  IPFW rule number for IPv5 address
 integer, rule number for IPv6 at temporary open
 detectTime
 detecting time
 integer, UNIX TIME, from opengatemd/macinfo table


 The record count is equal to the MAC list in opengatemchk page. Not increase with time.

TABLE cookietable = table for cookie management (use for authentication in opengatemchk, opengatemreg and opengatemup pages) 

 Field Name
 Meaning
 Commentary
 cookie  cookie
 string, primary key, used for authentication key
 saveTime  Saved time  integer
 userId  User ID  string
 extraId
 Extra ID  string
 userType
 User type
 integer, 0=Normal User, 1=Administrator
 mailDefault
 Default mail address
 string, from management DB
 macAddress
 MAC address
 string MAC address


 The record older than 24 hours are deleted in process.
Not increase with time.

File opengate.db : for Opengate

TABLE session = table for opengate sessions

 Field Name
 Meaning
 Commentary
 cookie  cookie  string, primary key, value of http cookie
 userid  User ID  string, user ID (userid@extraid)
 pid  Process ID
 integer, pid for the watching process of the terminal
 openTime  Open time
 string, starting time of the session
 closeTime  Close time
 string, ending time of the session, set '-' before end
 clientAddr4  IPv4 address
 string, IPv4 address of the terminal
 clientAddr6
 IPv6 address
 string, IPv6 address of the terminal
 macAddr
 MAC address
 string, MAC address of the terminal
 ruleNumber4
 IPFW rule number for the IPv4
 integer, IPFW rule number for the IPv4 address
 ruleNumber6
 IPFW rule number for the IPv6
 integer, IPFW rule number for the IPv6 address
 duration
 Usage duration
 integer, usage limiting time(sec)
 durationEntered
 Usage duration enerd by user  integer, usage limiting time requested by user(sec)
 cookieAuth
 Flag for cookie auth
 integer, 0=false/1=true
 language
 Language for the web page
 string, 'ja', 'en' etc
 watchMode
 Watch mode
 string, the mode for watching the usage end, 'HTTP'=by http connection, 'TIME'=by time length


 The record count is increased with time. Please remove old records periodically.