This report will show all of the Computer Assets that don't have a matching computer in inventory. This could be used to see all machines from AD that don't yet have an agent, for example, if you import a computer list into the Computer Asset.

Here is the query:

select * from ASSET
where ASSET_TYPE_ID = 5
and MAPPED_ID not in (select ID from MACHINE)

Attachment

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
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
Answered 02/25/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
This would be useful if I wanted to delete assets that were dropped out of inventory by MIA settings.
Answered 02/26/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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."
Answered 02/28/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
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.
Answered 03/01/2010 by: GillySpy
Seventh Degree Black Belt

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

Please log in to comment
0
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
Answered 03/01/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
Did you check the box to auto-generate XML?
Answered 03/01/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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?
Answered 03/01/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
ORIGINAL: airwolf

Did you check the box to auto-generate XML?

Yes.
Answered 03/01/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
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?
Answered 03/01/2010 by: RichB
Third Degree Green Belt

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