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
Answers (2)
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
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
