• State Not Answered
  • Locked Locked
  • Replies 10 replies
  • Subscribers 34 subscribers
  • Views 1583 views
  • Users 0 members are here
Options
Related
This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Getting nodes with specific application

SolarWindNoob

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'

Parents
  • 0

    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? 

Reply
  • 0

    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? 

Children
  • 0 in reply to jm_sysadmin

    Maybe I didn't put a detail

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

  • 0 in reply to jm_sysadmin

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

  • 0 in reply to SolarWindNoob

    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%'

  • 0 in reply to jm_sysadmin

    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. 

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification Media Vault Link Accounts
About THWACK Blogs For Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2024 SolarWinds Worldwide, LLC. All Rights Reserved.