/build/static/layout/Breadcrumb_cap_w.png

Computer Assets without matching Inventory item

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

Answers (10)

Posted by: airwolf 14 years ago
Red Belt
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.
Posted by: RichB 14 years ago
Second Degree Brown Belt
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
Posted by: airwolf 14 years ago
Red Belt
0
Did you check the box to auto-generate XML?
Posted by: GillySpy 14 years ago
7th Degree Black Belt
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?
Posted by: RichB 14 years ago
Second Degree Brown Belt
0
ORIGINAL: airwolf

Did you check the box to auto-generate XML?

Yes.
Posted by: RichB 14 years ago
Second Degree Brown Belt
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?
Posted by: GillySpy 14 years ago
7th Degree Black Belt
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
Posted by: airwolf 14 years ago
Red Belt
0
This would be useful if I wanted to delete assets that were dropped out of inventory by MIA settings.
Posted by: RichB 14 years ago
Second Degree Brown Belt
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."
Posted by: GillySpy 14 years ago
7th Degree Black Belt
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ