Solved

MySQL Database design for Audit information requiring updates

Posted on 2011-03-09
7
444 Views
Last Modified: 2013-12-13
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.
0
Comment
Question by:jools
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 250 total points
ID: 35092007
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35092009
By another one I mean another full record within the database.
0
 
LVL 2

Expert Comment

by:sihar86
ID: 35092128
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 19

Author Comment

by:jools
ID: 35095979
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
 
LVL 2

Assisted Solution

by:sihar86
sihar86 earned 250 total points
ID: 35096314
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
 
LVL 19

Author Comment

by:jools
ID: 35127036
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
 
LVL 19

Author Closing Comment

by:jools
ID: 35185443
Thanks for the ideas, I've split the points between you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now