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

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

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")
Answered 01/14/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
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.
Answered 01/15/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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...
Answered 01/15/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
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
Answered 01/15/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Perfect! Thanks airwolf. You da man.
Answered 01/15/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
edited out.
Answered 01/18/2010 by: dtuttle
Purple Belt

Please log in to comment
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.
Answered 01/19/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
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?
Answered 01/19/2010 by: dtuttle
Purple Belt

Please log in to comment
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.
Answered 01/19/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Sounds like having the Kbox track by IP or MAC might not be right for your organization.
Answered 01/20/2010 by: dtuttle
Purple Belt

Please log in to comment
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.
Answered 01/21/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Yeah, I think kace is going to fix the dup' BIOS S/N next release though.
Answered 01/28/2010 by: dtuttle
Purple Belt

Please log in to comment
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?
Answered 12/27/2010 by: timantheos
Orange Senior Belt

Please log in to comment
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?
Answered 12/29/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Got this one figured out. Thanks.
Answered 12/29/2010 by: timantheos
Orange Senior Belt

Please log in to comment
Answer this question or Comment on this question for clarity