/build/static/layout/Breadcrumb_cap_w.png

Query to identify machines that once had software installed that has since been removed?

I'm attempting to locate all machines that no longer have a specific software installed.  I can query the Asset_History table for "Software item % removed from inventory%", but the problem is that this software is installed and removed from machines very often, or if a machine has since been reformatted and no longer has the software... but the asset history is riddled with "Found new software item..." and "Software item ... removed..."

So my thoughts were to query the machine table for all machines that currently do not have the software item installed and then from there sub query the history of all machines for any instance of that software item ever being installed, and that should list all machines that did have, but no longer do have, that particular software item.

Is there a more direct way to accomplish this, or am I on the right track?

Thanks


0 Comments   [ + ] Show comments

Answers (2)

Posted by: jverbosk 11 years ago
Red Belt
1

That's the right idea, but I would change it slightly - I would use the query on the MACHINE, MACHINE_SOFTWARE_JT and SOFTWARE tables as in inline view (subquery), then pass those results up to the query on the ASSET_HISTORY table.  Here's a few articles I've written that cover inline views in case examples would be helpful (the last two are ticket rules, but still the same concept of using an inline view to focus the results which get passed to the main query):

http://www.itninja.com/blog/view/k1000-reports-advanced-mysql-query-techniques-parsing-multiple-substrings-from-a-field-using-temporary-tables

http://www.itninja.com/blog/view/k1000-custom-ticket-rules-using-an-inline-view-to-query-update-tables-in-one-pass

http://www.itninja.com/blog/view/k1000-custom-ticket-rules-closing-parent-tickets-automatically-when-all-child-tickets-are-closed

If you get stuck anywhere, just post what you have.

John

Posted by: jknox 11 years ago
Red Belt
0

It might actually be a detection weakness for the particular software title: http://www.itninja.com/blog/view/k1000-computer-asset-history-shows-winamp-removed-from-inventory-and-found-again-multiple-times

Don't be a Stranger!

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

Sign up! or login

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