List:Brisbane MySQL Users Group« Previous MessageNext Message »
From:Mark Unwin Date:September 22 2005 6:07am
Subject:Comparison of table entries in the same table
View as plain text  
I have a 'software' table in my database, as described below.
The field software_uuid is a link to a specific machine, in another
I run a scan each day, and populate this table - not deleting anything.
This data is input ONLY - no queries can be run, only an insert.
The client cannot actually talk to the database, it passes the
information to a second PC that can. The second PC can't talk back to
the client, it only accepts the SQL inserts, and sends them to the
Each scan would generate new software_id, and a different timestamp -
most other stuff would be the same (assuming nothing installed or
removed). The fields software_detect_date, software_detect_current_scan
& software_no_detect_date are not currently used - they are left over
from a previous incarnation. They can be removed or used, as needed.
How can I compare the results of all scans run against a given machine,
and return what has been installed & removed between each scan (if
anything). Basically an audit trail.

IE - For machine name test_pc

Software_uuid  |  software_name     |  software_timestamp  |  
test_pc        |  WinRAR            |  20050905090000      |  detected
test_pc        |  Adobe Acrobat     |  20050905090000      |  not
test_pc        |  WinZip 8.0        |  20050901090000      |  detected
test_pc        |  MS Office 2003    |  20050901090000      |  detected
test_pc        |  Adobe Acrobat     |  20050901090000      |  detected

Or something similar.
Extending on this, how can I return all software detected in the last x
days ?
I have been using the following to return a list of machines and their
latest timestamp from another table called 'system'.
select system_uuid, MAX(system_timestamp) from system group by
This is working fine.
Another looping query I have to return all software from a given
machine, on it's latest scan is
<<system_uuid is provided>>
select system_uuid, MAX(system_timestamp) AS timestamp from system group
by system_uuid
<<from this we get the latest timestamp>>
INSERT into software_temp SELECT software_name, software_timestamp,
software_uuid FROM software WHERE software_uuid = '$system_uuid' AND
software_timestamp = '$timestamp'
<<We get a list of currently installed software>>

This is all done in PHP.
Hope that's not too much info (better to provide too much, rather than
not enough).
Any help much appreciated.
Mark Unwin.
| Field                        | Type                | Null | Key |
Default | Extra          |
| software_id                  | int(10) unsigned    |      | PRI | NULL
| auto_increment |
| software_uuid                | varchar(100)        |      |     |
|                |
| software_name                | text                | YES  |     | NULL
|                |
| software_version             | text                | YES  |     | NULL
|                |
| software_location            | text                | YES  |     | NULL
|                |
| software_uninstall           | text                | YES  |     | NULL
|                |
| software_install_date        | text                | YES  |     | NULL
|                |
| software_publisher           | text                | YES  |     | NULL
|                |
| software_install_source      | text                | YES  |     | NULL
|                |
| software_detect_date         | date                | YES  |     | NULL
|                |
| software_detect_current_scan | varchar(20)         | YES  |     | NULL
|                |
| software_no_detect_date      | date                | YES  |     | NULL
|                |
| software_system_component    | char(2)             | YES  |     | NULL
|                |
| software_timestamp           | bigint(20) unsigned | YES  |     | NULL
|                |

This message and any attachments, or any part
of it is intended solely for the named addressee.  

Reading, printing, distribution, storing, commercialising
or acting on this transmission or any information it contains, by anyone other than the
addressee, is prohibited. If you have received this message in error, please destroy all
copies and notify 
Qld Police Credit Union Ltd on +61 7 3008 4444 or by replying to the sender.

This message may contain legally privileged and
confidential information, and/or copyright material
of QPCU or third parties.

QPCU is not responsible for any changes made
to a document other than those made by QPCU,
or for the effect of the changes on the document's meaning.
You should only re-transmit, distribute or commercialise
the material if you are authorised to do so.

Any views expressed in this message are
those of the individual sender.  You may not rely on this message as advice unless
subsequently confirmed by fax or letter signed by an Officer or Director of QPCU, or 
an Authorised Representative QPCU.

QPCU advises that this e-mail and any attached files should be scanned to detect viruses. 
QPCU accepts no liability for loss or damage (whether caused by negligence or not)
resulting from the use of any attached files.

Information regarding Privacy can be found at the QPCU web site. ( )

General Advice Warning

Any advice has been prepared without taking into account your particular objectives,
financial situation or needs.  For that reason, before acting on the advice you should
consider the appropriateness of the advice having regard to your own objectives,
financial situation and needs.  Where the advice relates to the acquisition, or possible
acquisition, of a particular financial product, you should obtain a Product Disclosure
Statement relating to the product and consider the Product Disclosure Statement before
making any decision about whether to acquire the product.

Comparison of table entries in the same tableMark Unwin22 Sep