I created an asset inventory report via the Report Wizard some time ago. It now seems to be broken after upgrading to 5.5.90548. The SQL generated by the wizard is:

SELECT MACHINE.NAME AS SYSTEM_NAME, SC.DISCONNECT_TIME, SYSTEM_DESCRIPTION, MACHINE.CLIENT_VERSION, MACHINE.IP, USER_LOGGED, OS_NAME, CS_MANUFACTURER, CS_MODEL, ASSET_DATA_5.FIELD_37, ASSET_DATA_5.FIELD_41, A38.NAME AS FIELD_38, BIOS_SERIAL_NUMBER FROM MACHINE  LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID   ORDER BY SYSTEM_NAME

When I run the report, I get the following error:

mysql error: [1054: Unknown column 'ASSET_DATA_5.FIELD_37' in 'field list'] in EXECUTE("SELECT MACHINE.NAME AS SYSTEM_NAME, SC.DISCONNECT_TIME, SYSTEM_DESCRIPTION, MACHINE.CLIENT_VERSION, MACHINE.IP, USER_LOGGED, OS_NAME, CS_MANUFACTURER, CS_MODEL, ASSET_DATA_5.FIELD_37, ASSET_DATA_5.FIELD_41, A38.NAME AS FIELD_38, BIOS_SERIAL_NUMBER, MACHINE.ID as TOPIC_ID FROM MACHINE LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID ORDER BY SYSTEM_NAME") 

I've tried creating a brand new report with the same settings and come up with the same error. What happened?

Answer Summary:
http://www.kace.com/support/resources/kb/solutiondetail?sol=123921
Cancel
1 Comment   [ + ] Show Comment

Comments

  • leave out ASSET_DATA_5.FIELD_37 in the query and see if it works. The database was also upgraded during the update so that column my be a dif number or be gone
    • After some experimentation, ASSET_DATA_5.FIELD_37, ASSET_DATA_5.FIELD_41, and A38.NAME appear to be linked to asset fields in our Computer asset type. None of the other asset fields in that type appear to be working either. Is there a way to rebuild the database so everything links back together again?
Please log in to comment

Community Chosen Answer

1

Found the answer here: http://www.kace.com/support/resources/kb/solutiondetail?sol=123921

Knowledge Article 123921

Title: K1000:Reporting: Running report based on Computer and selecting Asset fields fails to run

Description:

When running a report where ASSET's have been selected from computer topic , it will fail with a SQL error, Bug K1-15751, this is fixed in 6.0

Cause:

The report fails with a SQL error. K1-15751

Resolution:

Workaround is to create a new report and use ASSET as a subtopic instead of under Computer topic.

Choose Action > Add new report wizard.

Fill out fields and next to "report topic" select Computer.

Click on "Add a sub topic" and select "Asset-Computer:.

Click Next.

Select  the information you want  but instead of selecting Asset info from under computer section go to the bottom of the page and select from Asset section.

Continue your report as normal and Save and test.

Answered 05/02/2014 by: tdickinson
Orange Senior Belt

  • cool, mine was a corruption in the database itself.
Please log in to comment

Answers

0

you will need to open a ticket with Kace.  They had to tether in on the backend and relink the one column of mine that got broke.  The 5.5 upgrade also made changes/upgrades to the db files

Answered 05/02/2014 by: SMal.tmcc
Red Belt

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