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?
Community Chosen Answer
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):
If you get stuck anywhere, just post what you have.