MySQL Database design for Audit information requiring updates

Hi Everyone,

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.
LVL 19
joolsAsked:
Who is Participating?
 
Lukasz ChmielewskiConnect With a Mentor Commented:
I would not worry about additional fields for the changes.
Just make another one for the next scan - having the scanning date as a timestamp or just datetime. Then you have both records clear as possible and you know which one is later.
You can work on that by comparing the values.
0
 
Lukasz ChmielewskiCommented:
By another one I mean another full record within the database.
0
 
sihar86Commented:
could you tell me how initiate process work?
I mean how it work, from the server call every client
or
every client send data to database in server
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
joolsAuthor Commented:
I have a Ghost Audit image which is deployed to the site, this image collects the information about the PC it is running on and copies it to a central server.

I just run a script on the central server to strip out all the bits I really want to log in the database and use an insert with the required values.

The MAC Address is a unique key so I can avoid issues as the MAC Address is also used to name the PC's with their assigned desk name, having an additional full record in the database may not work as this would clash with the MAC Address information. Unless I've misunderstood your point that is.
0
 
sihar86Connect With a Mentor Commented:
from your explanation,
server collect all information about client first.

then you want monitor the change that occur in client

The possibility:
1. Your application in server that retrieve information from client
must be work periodically ( every 30 minutes, or .....)
2. When data retrieved, application check data that stored in database,
  if there is any change or not,
4. In table audit, you make the trigger in mysql.
  So when data in table audit updated, mysql move the old one to temporary table
  In the final, you have 2 table:
 - master table (latest data or new one)
 - historical table (old data)

Regards,


Sihar
0
 
joolsAuthor Commented:
Thanks Chaps,

I've been looking at the "duplicate" information thing first mentioned in Roads_Roads post, Sihar, you basically elaborated with some more detail on the same idea and I think this is the only way I can easily implement this at the moment.

I'll give the Q a couple more days to see if anyone has some different ideas, in the mean time I'll carry on with the database modifications as these will be easily changed if I go in a different direction.
0
 
joolsAuthor Commented:
Thanks for the ideas, I've split the points between you.
0
All Courses

From novice to tech pro — start learning today.