Information about how Open-AudIT processes and stores data

How do we process and store data?

NOTE - Updated for 1.12.8 with new fields and logic.

Each system (computer, network device, printer, et al) has an entry in the "system" table. Each system (from the "system" table) has an "id" column. This value is unique - it's an auto incrementing id. A system is determined to be unique by a the table below.

A system is audited and the result submitted to the server. The first table processed is the "system" table. The "id" is determined and passed (along with the other details) to each other section (table). Every table has two timestamp columns, "first_seen" and "last_seen". The "first_seen" value is populated whenever an insert occurs - hence this value reflects the first time an item was reported in the audit script. The "last_seen" value is inserted when an item is first seen, or updated when an item is seen in subsequent audit script(s). There is an "audit_log" table that contains details of each time an audit is submitted (including timestamp). Each sub-table also contains a 'current' column which is an enum with possible values of 'y' and 'n'.

So, for an example - "hard_drive".

• The system.id is retrieved, along with the timestamp of the previous audit submission and the "status" column.
• For each entry in the hard_drive audit result, the database is queried.
• It checks for hard drive model, serial, index and size.
• These values vary according to the item being processed - see the relevant model PHP pages (in code_igniter/application/models/).
• If it gets a match on the above values, combined with the system_id, the current timestamp OR the previous timestamp, and a status of "production", then an existing entry exists for this piece of equipment.
• In the case of hard drives, it simply updates the timestamp to reflect the current audit timestamp.
• If it does not get a match, it does an insert of the relevant details.

So, we can determine if something is currently installed - the timestamps match (on the system table and the relevant item table).

We can determine when something was detected - the "first_timestamp".

 We can determine if something was installed after the initial audit - first timestamps will be different.

We can determine if something is not currently installed, but previously was - the timestamp on the item is less than the timestamp on the system.

 We can determine the last time we detected an item - the timestamp on the item, when the timestamp is less than the current system timestamp.

At any given point, we can determine what was on a system - by using the oa_audit_log table and selecting the relevant components based on timestamps.

So, that's how we determine what's on or has been on a system.

How do we create a system key?

The order of creating a key to define a device as unique is below. When importing you MUST have one of the following (in order of preference): fqdn (which is the hostname and domain columns seperately - PHP will combine them), the IP Address or the Serial Number. If a computer is audited with an audit script, it will use a concatenation of the UUID and the hostname. If a computer is audited via Active Directory, it will use the FQDN in preference but if that is not available it will use the IP Address. Where possible, the first option will be chosen and where possible on subsequent audits, will be changed to the first option.

NOTE - We now have configuration options for some fields that we use. In the config section (menu -> Admin -> Configuration) you will see options for discovery_ip_match, discovery_name_match and discovery_mac_match. If ip is set to 'y', the system key will include it (where relevant). If discovery_name_match is set to 'y', the system key will include it where relevant. If discovery_mac_match is set to 'y', all mac addresses will be checked to determine a match.

NOTE #2 - This is changing for 1.12.8 and we will update this document when it happens.

How do we match a system key?

The order of preference to match one system against another is as follows: Obviously if we have the system_id, we simply match on that first.  If we don't have the system_id, in the model m_system.php is a function called find_system. This will attempt to:

1. Create a system key using UUID & hostname if possible and match
2. Create a system key using the fqdn if it exists and match
3. Create a system key using the hostname concatenated with the domain (to make a fqdn) and match
4. Create a system key using the ip address and match
5. Create a system key using serial number and match
6. Check if the MAC Address exists in the sys_hw_network_card_ip table and match
7. Check if the IP Address exists in the sys_hw_network_card_ip table and match
8. Check if the serial exists in the system table and match
9. Check if the serial exists in the man_serial field in the system table and match
10. Check if the hostname is in the system table and match

What do we use for a name?

Where possible, the first option will be chosen and where possible on subsequent audits, will be changed to the first option.