ITNinja

The Report Wizard can't use custom SQL. You need to create a SQL report and paste the query from Gerald into it. However, this won't purge assets - it will only report on them. In order to purge them, you'll have to write custom SQL rules for the help desk - but be VERY careful, because you can do some damage to the DB fairly easily with an improperly tested query.

I created a custom SQL Report with Gerald's Query and then when trying to generate an HTML report is when I see the error mentioning the Report Wizard.

I am unable to see anything at http://kbox/logs/tomcat_error so maybe there are no errors? The Output link does work (http://kbox/logs/tomcat_output) and is the same as going into the System/Settings/Logs/Tomcat Log. Below is the section generated after attempting to run the report:

08:42:30,777 INFO AdHocUser,http-127.0.0.1-8080-Processor25:36 - Creating new user from request.
08:42:30,778 INFO ReportServlet,http-127.0.0.1-8080-Processor25:84 - Processing event: run
08:42:30,778 INFO ReportManager,http-127.0.0.1-8080-Processor25:515 - Reconstructing report for: 1627
08:42:31,060 INFO ReportManager,http-127.0.0.1-8080-Processor25:270 - Running report as HTML
08:42:31,079 ERROR Digester,http-127.0.0.1-8080-Processor25:1132 - End event threw exception
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.commons.beanutils.MethodUtils.invokeMethod(MethodUtils.java:252)
at org.apache.commons.digester.SetNextRule.end(SetNextRule.java:216)
at org.apache.commons.digester.Rule.end(Rule.java:230)
at org.apache.commons.digester.Digester.endElement(Digester.java:1130)
at net.sf.jasperreports.engine.xml.JRXmlDigester.endElement(JRXmlDigester.java:187)
at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
at org.apache.xerces.parsers.AbstractXMLDocumentParser.emptyElement(Unknown Source)
at org.apache.xerces.impl.xs.XMLSchemaValidator.emptyElement(Unknown Source)
at org.apache.xerces.impl.dtd.XMLDTDValidator.emptyElement(Unknown Source)
at org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at org.apache.commons.digester.Digester.parse(Digester.java:1647)
at net.sf.jasperreports.engine.xml.JRXmlLoader.loadXML(JRXmlLoader.java:239)
at net.sf.jasperreports.engine.xml.JRXmlLoader.loadXML(JRXmlLoader.java:226)
at net.sf.jasperreports.engine.xml.JRXmlLoader.load(JRXmlLoader.java:214)
at com.jaspersoft.kace.adhoc.engine.ReportManager.runReport(ReportManager.java:274)
at com.jaspersoft.kace.adhoc.server.ReportServlet.service(ReportServlet.java:125)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at com.jaspersoft.kace.adhoc.util.CharsetFilter.doFilter(CharsetFilter.java:37)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.valves.RequestFilterValve.process(RequestFilterValve.java:276)
at org.apache.catalina.valves.RemoteAddrValve.invoke(RemoteAddrValve.java:81)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:595)
Caused by: net.sf.jasperreports.engine.JRException: Duplicate declaration of field : ID
at net.sf.jasperreports.engine.design.JRDesignDataset.addField(JRDesignDataset.java:519)
at net.sf.jasperreports.engine.design.JasperDesign.addField(JasperDesign.java:833)
... 47 more
08:42:31,084 ERROR ReportServlet,http-127.0.0.1-8080-Processor25:229 - Reporting Exception: com.jaspersoft.kace.adhoc.server.AdHocException: Exception running report.. java.lang.Exception: net.sf.jasperreports.engine.JRException: Duplicate declaration of field : ID

Did you check the box to auto-generate XML?

It appears the tomcat engine doesn't like it because each table has identical column names. The error: Duplicate declaration of field : ID is what leads me to this.

While, this would work as-is in most 3rd party tools, for reporting engine we will have to be specific:
select ASSET.NAME as ASSET,MACHINE.NAME as MACHINE from ASSET
LEFT JOIN MACHINE ON ASSET.MAPPED_ID=MACHINE.ID
WHERE
ASSET_TYPE_ID=5
and MACHINE.ID IS NULL

That is an example of how to make those two columns play nice. To add other columns I'll leave that as an exercise for the reader. What other columns do you want? Did you have luck with?

ORIGINAL: airwolf

Did you check the box to auto-generate XML?

Yes.

ORIGINAL: GillySpyselect ASSET.NAME as ASSET,MACHINE.NAME as MACHINE from ASSET
LEFT JOIN MACHINE ON ASSET.MAPPED_ID=MACHINE.ID
WHERE
ASSET_TYPE_ID=5
and MACHINE.ID IS NULL

This worked fine and generated a report listing 10,638 asset records not found in inventory, as expected. How would a custom ticket rule be written to delete those found records?

For customers with a lot of machines in inventory here is a more efficient version of this query:

select * from ASSET
LEFT JOIN MACHINE ON ASSET.MAPPED_ID=MACHINE.ID
WHERE
ASSET_TYPE_ID=5
and MACHINE.ID IS NULL

This would be useful if I wanted to delete assets that were dropped out of inventory by MIA settings.

We have 9,000 computers in inventory an 19,000 in Assets. I was looking forward to running this to purge the Assets but all I get is:

"Error Message:
Unexpected error occurred while running Report Wizard."

We will probably need to troubleshoot this in support, but the first place I look is in the error logs. For the reporting engine those are at:
http://yourkbox/logs/tomcat_error
http://yourkbox/logs/tomcat_output

Simply find the timestamp for when you attempted to run the report. If you cannot interpret the log then send it to support.