Getting nodes with specific application

Hello All!

I am trying to build a report that show what nodes are missing a certain application.

For Example I want any server that contains 'SQL'  and want to know if it has the application 'services'

  • 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...