ITNinja

I know this is an old thread, but the report is still relevant.  I used the query above, and grouped the results by the serial number, so that I only got the duplicate computers, but it only gave one line per serial number, which made it a little more confusing.  So I modified it with the ideas from here:  http://stackoverflow.com/questions/3797799/show-all-rows-in-mysql-that-contain-the-same-value, and now it gives me one row per duplicated computer.

And one for duplicate Device Inventories:

mysql error: [1054: Unknown column 'ASSET_DATA_5.FIELD_37' in 'field list'] in EXECUTE(
"selecT COUNT(ASSET_DATA_5.FIELD_37) as n, ASSET.NAME, ASSET_DATA_5.FIELD_37 as BIOS
FROM ASSET_DATA_5
JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
group by ASSET_DATA_5.FIELD_37
having n > 1 LIMIT 0")

Rich, that error means there is no FIELD_37 field in your ASSET_DATA_5 table. ASSET_DATA_5 should be the Computers asset table, as it is in Dale's and mine... however, this may be different depending on when your KBOX was purchased (the default database tables may have been arranged differently over time). You need to verify that the ASSET_TYPE_ID on your Computer assets in the Assets table is 5 (if not, use the appropriate ASSET_DATA_# table). Then, find the field name for the field related to the serial number and replace FIELD_37 with the appropriate field.

Thanks airwolf. ASSET_DATA_5 is the same for me but not FIELD_37. I changed the "37" to "156" in three places and now it is working:

selecT COUNT(ASSET_DATA_5.FIELD_156) as n, ASSET.NAME, ASSET_DATA_5.FIELD_156 as BIOS
FROM ASSET_DATA_5
JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
group by ASSET_DATA_5.FIELD_156
having n > 1

Now I'd like the same data for duplicate serial numbers in the Computer (Machine) database and not Assets...

Rich, the same process applies. Just change the ASSET_DATA_5 to MACHINE and FIELD_156 to BIOS_SERIAL_NUMBER:

SELECT COUNT(MACHINE.BIOS_SERIAL_NUMBER) as n, MACHINE.NAME, MACHINE.BIOS_SERIAL_NUMBER as BIOS
FROM MACHINE
group by MACHINE.BIOS_SERIAL_NUMBER
having n > 1

Perfect! Thanks airwolf. You da man.

edited out.

This report helps identify extra records that shouldn't be there but are. Many computers have changed names or IP number and that causes a new KBOX record, even though the serial number is the same The serial number is the primary key between assets and computers. I see almost 100 of these duplicate records where there should only be 50 records. It also identified several computers with bogus serial numbers like 00000000, empty, and yyyyy. It also identified 759 computers reporting no serial number.

It also identified several computers with bogus serial numbers like 00000000, empty, and yyyyy. It also identified 759 computers reporting no serial number.

That is a problem with the WMI, not the Kbox... although this is a very common problem. I believe you can rebuild the WMI, it work sometimes - other it wont. The kace site might have how to do it.


I'm not sure if I understand what you are talking about with the IP address though... How do you have Kbox track your assets?

We are using a system that has all of the MAC addresses so when a computer dynamically requests an IP address it gets statically assigned an IP address. If the computer's MAC address is not in the system then the computer doesn't get an IP address. The KBOX computer records often get duplicated when a computer name is changed. Although the BIOS serial number, IP number and KUID hasn't changed a second record is created with the new computer name! It doesn't happen all the time and usually as expected the computer record reflects the name change and all the rest of the history is maintained.

We are constantly fighting this duplication problem and especially when running out of KBOX client licenses. We have automated reports for duplicated records with same IP, serial number or name.

Another frequent issue is when a wireless computer changes from it's statically assigned wireless IP to it's statically assigned wired IP. A duplicate record gets created and needs to be deleted. Usually this doesn't happen and the KBOX record just adjusts the IP number it had when it checked in but it is frustrating when it does happen.

Assets are not even in my focus since there are many extra records in there from old computers and this duplication problem. I'm focused on getting the Computer records accurate and it is a constant task. We have close to 9,000 computers according to KBOX.

Sounds like having the Kbox track by IP or MAC might not be right for your organization.

It's tracking assets by serial number and other information. Duplicates happen. Reports are now automatically sent for duplicate computer records by serial number, name, or IP. A computer with a new motherboard that didn't get the BIOS updated reports a (blank) serial number so many duplicates get created.

Yeah, I think kace is going to fix the dup' BIOS S/N next release though.

I was able to create a report with the above SQL code and the report ran fine for me. It returned only 3 records for me, however the 3 records returned did not have matching serial numbers. One was a laptop, one a desktop and the 3rd a new machine that's in asset, but not yet in production. Each machine is a different make/model type and each has a unique serial listed in the asset and inventory detail.

Any ideas why these results might be happening?

Which SQL are you referring to - duplicate serial numbers in Assets or Machines? How many computers or assets do you have? Are the serial numbers similar?

Got this one figured out. Thanks.