MySQL Database design for Audit information requiring updates
Posted on 2011-03-09
I have a load of Audit information in a database which holds MAC Address, Site, Desk Position and some hardware information (disk size, memory installed, CPU Type, Bios Revision/Date etc).
We are now going to audit the information on a quartely basis where each site runs the audit script and collects the data again. The data collection just extracts hal information and dmi data from the PC which is run thru a bash script to pull out the information above.
For the initial load of the data this was easy because I just loaded the information for each site and did not have to worry about any changes. Now that we need to audit the site information however just reloading the data, although easier, will not flag up any changes that may have occurred. We are trying to catch if the PC has moved desk position/site or if it has hace CPU/Memory or disk changed for lower spec hardware (they all need to be the same).
The only problem is that I need some ideas how to go about it.
Can anyone help with some ideas that I can use to catch this and perhaps if there is a way in mysql to add the record to another column if the record detail is different from the existing data. I'm after a detailed overview so I can get some ideas together.
The system will eventually be written in php and mysql as the current system is already there. The database information is read only to users.