Open-AudIT

I use MAC addresses as the uuid. Occasionally we get duplicate system_name entries. This happens when a motherboard or NIC card is replaced. It also seems to happen with latops that switch between wireless and wired. I've written a script to query for a duplicate system_name and purge the duplicates, saving the one with the latest timestamp.

To run this vbscript you will need the MySQL ODBC driver installed on the system you will be executing the script from. It can be found at http://dev.mysql.com/downloads/connector/odbc/3.51.html.

I scheduled the script to run daily by issuing the following command:

[code]schtasks /create /tn "Purge Dupes" /tr "cscript c:\bin\purgedupes.vbs" /sc daily /st 08:00:00 /ru "System"[/code]

Here is the vbscript:

[code]set myconn = createobject("adodb.connection")
connection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=openauditserver;PORT=3306;DATABASE=openaudit; USER=openaudit;PASSWORD=password;OPTION=3;"
myconn.open (connection)
set result = createobject("adodb.recordset")
sql = "select system_name, count(system_name) as NumOccurances FROM system Group by system_name having ( count(system_name) > 1);"
set result = myconn.execute(sql)
if not result.EOF then
while not result.EOF
wscript.echo "Data for " & result("system_name") & "#:" & result("NumOccurances")
'wscript.echo result("system_name") ' & ";" & result("system_model")
sql2 = "select * FROM system WHERE system_name = " & "'" & result("system_name") & "'" & " ORDER BY system_timestamp DESC;"
set result2 = myconn.execute(sql2)
for i=1 to Cint(result("NumOccurances"))
wscript.echo result2("system_name") & ";" & result2("system_uuid") & ";" & result2("system_timestamp")
If I > 1 Then
wscript.echo "Deleting entry with timestamp :" & result2("system_timestamp")
deletequery = "DELETE FROM battery WHERE battery_uuid = " & "'" & result2("system_uuid") & "';"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM bios WHERE bios_uuid = " & "'" & result2("system_uuid") & "';"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM browser_helper_objects WHERE bho_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM firewall_auth_app WHERE firewall_app_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM firewall_ports WHERE port_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM firewire WHERE fw_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM floppy WHERE floppy_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM graphs_disk WHERE disk_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM groups WHERE groups_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM hard_drive WHERE hard_drive_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM iis WHERE iis_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM iis_ip WHERE iis_ip_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM iis_vd WHERE iis_vd_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM invoice WHERE invoice_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM keyboard WHERE keyboard_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM manual_software WHERE man_soft_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM mapped WHERE mapped_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM media WHERE media_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM memory WHERE memory_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM modem WHERE modem_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM monitor WHERE monitor_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM mouse WHERE mouse_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM ms_keys WHERE ms_keys_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM network_card WHERE net_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM nmap_ports WHERE nmap_other_id = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM notes WHERE notes_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM optical_drive WHERE optical_drive_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM partition WHERE partition_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM passwords WHERE passwords_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM processor WHERE processor_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM scsi_controller WHERE scsi_controller_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM scsi_device WHERE scsi_device_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM service WHERE service_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM shares WHERE shares_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM software WHERE software_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM sound WHERE sound_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM startup WHERE startup_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM system WHERE system_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM system_audits WHERE system_audits_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM system_man WHERE system_man_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM system_security WHERE ss_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM tape_drive WHERE tape_drive_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM usb WHERE usb_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM users WHERE users_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
deletequery = "DELETE FROM video WHERE video_uuid = " & "'" & result2("system_uuid") & "'"
set deletequeryresult = myconn.execute(deletequery)
End If
result2.movenext()
next
result.movenext()
wend
else
wscript.echo "No Entry"
end if

[/code]