Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL Database design for Audit information requiring updates

Posted on 2011-03-09
7
Medium Priority
?
454 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 750 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 750 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, we’ll look at how to deploy ProxySQL.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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