Getting nodes with specific application

Use use the SWQL below to find servers installed with the SQL database engine

SELECT DISTINCT N.NodeID
	FROM Orion.Nodes N
	INNER JOIN  Orion.AssetInventory.Software OAIS ON N.NodeID   = OAIS.NodeID
	Where n.IsServer = TRUE
		and OAIS.Name like 'Microsoft SQL Server 2%'
		and OAIS.Name not like '%Common%'
		and OAIS.Name not like '%Setup%'
		and OAIS.Name not like '%Studio%'
		and OAIS.Name not like '%Database Engine Services%'
		and OAIS.Name not like '%Customer Experience Improvement%'
		and OAIS.Name not like '%Transact-SQL%'
		and OAIS.Name not like '%CLR Types%'
		and OAIS.Name not like '%Service%'
		and OAIS.Name not like '%Full text search%'
		and OAIS.Name not like '%VSS Writer%'
		and OAIS.Name not like '%Database Engine Shared%'
		and OAIS.Name not like '%tool%'
		and OAIS.Name not like '%Documentation Components%'
		and OAIS.Name not like '%Native Client%'
		and OAIS.Name not like '%T-SQL%'
		and OAIS.Name not like '%Data-Tier App%'
		and OAIS.Name not like '%Reporting Services%'
		and OAIS.Name not like '%Driver%'
		and OAIS.Name not like '%Hotfix%'
		and OAIS.Name not like '%MySQL%'
		and OAIS.Name not like '%Manage%'
		and OAIS.Name not like '%Book%'
		and OAIS.Name not like '%utilit%'
		and OAIS.Name not like '%Analysis%'
		and OAIS.Name not like '%Policies%'
		and OAIS.Name not like '%Backward%'
		and OAIS.Name not like '%OData Source%'
		and OAIS.Name not like '%Add-in%'
		and OAIS.Name not like '%ADOMD.NET%'
		and OAIS.Name not like '%Browser%'
		and OAIS.Name not like '%Report Builder%'
		and OAIS.Name not like '%Express%'
		and OAIS.Name not like '%Command%'
		and OAIS.Name not like '%Upgrade%' 
		and OAIS.Name not like '%Tools%' 
		and OAIS.Name not like '%Addin%'

Now this doesn't tell you if the services are running, but if you ran discovery on them I bet with the right access you would be able to add AppInsight for SQL. 

Now if you already have the services monitored, we might be able to find the monitored services with a different query. What's the end goal you are trying to get to? 

Maybe I didn't put a detail

My end goal is to find out what servers are missing and application monitor

I reread your post, I think I get it better now. Let me think a minute.

Ok this one is better, 2 filters for the installed SQL list above that you likely want to tweak, one filter for SAM applications named like SQL, one for components named like SQL. Tailor it to fit your needs. 

SELECT DISTINCT N.NodeID, N.Caption
	FROM Orion.Nodes N
	INNER JOIN  Orion.AssetInventory.Software OAIS ON N.NodeID   = OAIS.NodeID
	Where
        N.NodeID Not IN (SELECT DISTINCT App.Node.NodeID FROM Orion.APM.Application App WHERE Name like '%SQL%' )
        and N.NodeID Not IN (SELECT Comp.Application.Node.NodeID
FROM Orion.APM.Component Comp
where Name like '%SQL%')
		and OAIS.Name like 'Microsoft SQL Server 2%'
		and OAIS.Name not like '%Common%'
		and OAIS.Name not like '%Setup%'
		and OAIS.Name not like '%Studio%'
		and OAIS.Name not like '%Database Engine Services%'
		and OAIS.Name not like '%Customer Experience Improvement%'
		and OAIS.Name not like '%Transact-SQL%'
		and OAIS.Name not like '%CLR Types%'
		and OAIS.Name not like '%Service%'
		and OAIS.Name not like '%Full text search%'
		and OAIS.Name not like '%VSS Writer%'
		and OAIS.Name not like '%Database Engine Shared%'
		and OAIS.Name not like '%tool%'
		and OAIS.Name not like '%Documentation Components%'
		and OAIS.Name not like '%Native Client%'
		and OAIS.Name not like '%T-SQL%'
		and OAIS.Name not like '%Data-Tier App%'
		and OAIS.Name not like '%Reporting Services%'
		and OAIS.Name not like '%Driver%'
		and OAIS.Name not like '%Hotfix%'
		and OAIS.Name not like '%MySQL%'
		and OAIS.Name not like '%Manage%'
		and OAIS.Name not like '%Book%'
		and OAIS.Name not like '%utilit%'
		and OAIS.Name not like '%Analysis%'
		and OAIS.Name not like '%Policies%'
		and OAIS.Name not like '%Backward%'
		and OAIS.Name not like '%OData Source%'
		and OAIS.Name not like '%Add-in%'
		and OAIS.Name not like '%ADOMD.NET%'
		and OAIS.Name not like '%Browser%'
		and OAIS.Name not like '%Report Builder%'
		and OAIS.Name not like '%Express%'
		and OAIS.Name not like '%Command%'
		and OAIS.Name not like '%Upgrade%' 
		and OAIS.Name not like '%Tools%' 
		and OAIS.Name not like '%Addin%'

Im going throught it now but from the looks of it. It seems to long to find out if a server is missing an application monior.

I found 

SELECT DISTINCT NodeID, Caption

FROM Nodes

Where Caption like '%Server%' not in

(SELECT Nodes.NodeID FROM Nodes, APM_CurrentStatusOfApplication
Where Nodes.NodeID = APM_CurrentStatusOfApplication.NodeID and
APM_CurrentStatusOfApplication.ApplicationName = 'blahhblah')

This one seems more simple. I am very new to SQL/SWQL and I am looking at what you sent me and will work on it. 

I also do not need to know if the services are running. My bad. And it is suppose to be application templates ughh!

So you might be able to remove the line about the components if you just care about the application name. My first post was just a list of servers that the software asset inventoried from add remove programs. Its long because I want to to show to much not too little, but you should feel free to tune it how you want it. Either way it gives you the list of servers that should have SQL. If all your nodes are SQL servers, then I guess its not needed, but around here, most stuff doesn't have SQL installed. So when I find that list, I want to trim it to things that don't have a SQL monitor  on it, which is why I added the line about not in and then a query with all Applications named with SQL. In my mind that shows you servers that need a SQL monitor but don't have it. Here we only have app monitors on production servers, so I used a custom property to further filter it, you may have to do something similar. 

This explains what I am looking for

How can I configure filters in reports to be able to get a list of specific nodes that contain "prod" and missing a specific application template being assigned (I just want to occasionally check to ensure these nodes have a particular template assigned for compliance reasons)

The nodes i am looking for are sql servers. I need to find out which of these are missing a specific template. Sorry for being confusing

Here's what I'd do:

1. Identify the Application Template ID with the below query OR by editing the template and find the ID in the web URL.
   
   SWQL:
   
   

   SELECT
        a.Node.Caption AS [Node]
       ,a.Name AS [App Name]
       ,a.ApplicationID
       ,a.ApplicationTemplateID
   FROM Orion.APM.Application AS a

   
   
   Web: Go to "Settings -> All Settings -> SAM Settings -> Manage Templates" and search for your template, click the check box and click "Edit".
   
   The Application Template ID is here in the web URL of the address bar, mine below is 6 (see at the end of the URL in the address bar of the browser)
   
   
   
   
2. Now update the query below with your information for your environment (your custom property name containing the 'Prod' data you need and application template ID)
   
   Line 6: replace <CustomPropertyNameHere> with name of your custom property (I assumed it was a node custom property)
   Line 15: replace <APPIDHERE> with your Application Template ID
   Line 16: replace <CustomPropertyNameHere> with name of your custom property
   
   

   SELECT
        n.Caption AS [Node]
       ,n.DetailsUrl AS [Node Details Url]
       ,n.Status AS [Node Status]
       ,n.ChildStatus AS [Node Child Status]
       ,n.CustomProperties.<CustomPropertyNameHere>
       ,a.Name AS [App Name]
       ,a.Status AS [App Status]
       ,a.DetailsUrl AS [App Details Url]
       ,CASE WHEN a.ApplicationID IS NULL 
           THEN 'ERROR: No APP Assignment' 
           ELSE 'All Good Here...' 
        END AS [App Result]
   FROM Orion.Nodes AS n
   LEFT JOIN Orion.APM.Application AS a ON a.NodeID = n.NodeID AND a.ApplicationTemplateID = <APPTEMPLATEIDHERE>
   WHERE n.CustomProperties.<CustomPropertyNameHere> = 'Prod'

Now this is a start, and would only work (currently) if you're only concerned with a single application ID assignment, but could be adjusted to include more, but would need additional changes to the query to do so.

Hope this helps...