Broken Report
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?
-
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 - SMal.tmcc 10 years ago
-
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? - tdickinson 10 years ago
Answers (2)
http://www.kace.com/support/resources/kb/solutiondetail?sol=123921
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.
Comments:
-
cool, mine was a corruption in the database itself. - SMal.tmcc 10 years ago
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