/build/static/layout/Breadcrumb_cap_w.png

Duplicate assets by BIOS S/N

Sql code::

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

0 Comments   [ + ] Show comments

Answers (17)

Posted by: ondrar 7 years ago
Black Belt
1
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.

ASSET_DATA_5 - Computer/Device-type Assets
ASSET_TYPE_ID = 5 - Only return Computer/Device-type Assets
FIELD_10004 - Serial Number (yours may be different)

SELECT 
  ASSET.NAME AS 'Computer Name', 
  ASSET_DATA_5.FIELD_10004 AS 'Service Tag' 

FROM ASSET_DATA_5 

JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID 

WHERE 
  ASSET.ASSET_TYPE_ID = 5
  AND ASSET_DATA_5.FIELD_10004 IN 
    (SELECT ASSET_DATA_5.FIELD_10004
    FROM ASSET_DATA_5 
    GROUP BY ASSET_DATA_5.FIELD_10004
    HAVING COUNT(*) > 1
    )

ORDER BY ASSET_DATA_5.FIELD_10004 


Example
Computer NameService Tag
ComputerA1234567
ComputerA21234567
ComputerB9876543
ComputerB29876543
Hope this helps somebody else!
Posted by: ondrar 7 years ago
Black Belt
1
And one for duplicate Device Inventories:


SELECT 
  MACHINE.NAME AS 'Computer Name', 
  MACHINE.BIOS_SERIAL_NUMBER as 'Service Tag'

FROM MACHINE 

WHERE 
  MACHINE.BIOS_SERIAL_NUMBER IN 
    (SELECT MACHINE.BIOS_SERIAL_NUMBER
    FROM MACHINE 
    GROUP BY MACHINE.BIOS_SERIAL_NUMBER
    HAVING COUNT(*) > 1
    )

group by MACHINE.BIOS_SERIAL_NUMBER
Posted by: RichB 14 years ago
Second Degree Brown Belt
0
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")
Posted by: airwolf 14 years ago
Red Belt
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.
Posted by: RichB 14 years ago
Second Degree Brown Belt
0
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...
Posted by: airwolf 14 years ago
Red Belt
0
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
Posted by: RichB 14 years ago
Second Degree Brown Belt
0
Perfect! Thanks airwolf. You da man.
Posted by: dtuttle 14 years ago
Purple Belt
0
edited out.
Posted by: RichB 14 years ago
Second Degree Brown Belt
0
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.
Posted by: dtuttle 14 years ago
Purple Belt
0
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?
Posted by: RichB 14 years ago
Second Degree Brown Belt
0
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.
Posted by: dtuttle 14 years ago
Purple Belt
0
Sounds like having the Kbox track by IP or MAC might not be right for your organization.
Posted by: RichB 14 years ago
Second Degree Brown Belt
0
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.
Posted by: dtuttle 14 years ago
Purple Belt
0
Yeah, I think kace is going to fix the dup' BIOS S/N next release though.
Posted by: timantheos 13 years ago
Orange Senior Belt
0
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?
Posted by: RichB 13 years ago
Second Degree Brown Belt
0
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?
Posted by: timantheos 13 years ago
Orange Senior Belt
0
Got this one figured out. Thanks.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ