How to report upon absence of criteria

userid962

I want to create a report to show servers that are missing a specific installed application using data gathered via Software Inventory mechanism.  The only way I can do it now involves lots of manual comparison - 1 column to show servers where the application IS installed and 1 column to show ALL servers, then manually comparing both columns to spot the differences. Is there an easier way to do this? 

jrouviere

Not sure if there's an easier way to do it, but this is what I did (granted it's for looking for application templates, but you can do the same thing just pulling from the AssetInventory tables):

SQL Query:

SELECT DISTINCT IP_Address, Caption

FROM Nodes

Where NodeID not in (SELECT Nodes.NodeID FROM Nodes, APM_CurrentStatusOfApplication

                                    Where Nodes.NodeID = APM_CurrentStatusOfApplication.NodeID and

                                    APM_CurrentStatusOfApplication.ApplicationName LIKE '%<Application Template Name>%')

For your purposes you should be able to use:

SELECT DISTINCT IP_Address, Caption

FROM Nodes

Where NodeID not in (SELECT Nodes.NodeID FROM Nodes, AssetInventory_Software

                                    Where Nodes.NodeID = AssetInventory_Software.NodeID and

                                    AssetInventory_Software.Name LIKE '%<Software Name>%')

On my end I filtered quite heavily as well, because if you have switches and the like they will show in your results so you may need additional logic in the WHERE clause to target your specific set of Nodes.