Page Comparison

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 a an "system_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 "system_id" is determined and passed (along with the other details) to each other section (table). Every table has two timestamp columns, "first_timestampseen" and "timestamplast_seen". The "first_timestampseen" value is populated whenever an insert occurs - hence this value reflects the first time an item was reported in the audit script. The "timestamplast_seen" value is inserted when an item is first seen, or updated when an item is seen in subsequent audit script(s). There is a an "oa_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 PHP page at /code_igniter/application/models/)m_devices_components.php
• If it gets a match on the above values, combined with component.status = 'y' and the system_id, the current timestamp OR the previous timestamp, .id and a system.status of "production", then an existing entry exists for this piece of equipment.
• In the case of hard drives, it simply updates the timestamp current flag to 'y' to reflect the component is still 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)current column is 'y'.

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

 We can determine if something was installed after the initial audit - first timestamps seen 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 systemcurrent = 'n'.

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

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 timestampsfirst_seen and last_seen.

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

How do we

...

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.

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

determine device uniqueness?

When we receive data about a device we check the following columns for matches. If we get a match and the existing entry has a status of 'production', we update this device.

The code for this currently resides in code_igniter/application/models/m_system.php.

Devices are considered the same if they have the following attributes in common: UUID & hostname, dbus_identifier*, FQDN, serial & device type, MAC address and config item**, ip address and config item**, hostname and config item**.

* In 1.12.8 we use the dbus_uuid in Linux to determine uniqueness. This is being reverted in 1.12.8.1 because ESX does not recreate this identifier upon cloning a mchine, hence possibly causing false positive matching.

** In the configuration of Open-AudIT you can select discovery_hostname_match (and mac, ip) to enable this matching. 

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.

...

: hostname, dns_hostname, sysName.