Report to list all settings within all assigned application monitors

Yes. I just pulled this one together this afternoon. IT's a SQL query based report, but it's do-able:

select Nodes.NodeID, Nodes.StatusLED, Nodes.Caption, Nodes.IP_Address, Nodes.DNS, Nodes.SysName,

APM_Application.ID as AppID, APM_Application.Name as AppName,

APM_Component.ID as CompID, APM_Component.Name as Component,

CPUCrit.Critical as CPUCrit, PMemCrit.Critical as PMEMCrit, VMemcrit.Critical as VMEMCrit,

[Credential].Name,

from [Credential]

join APM_ComponentSetting on [Credential].ID = APM_ComponentSetting.Value

left join APM_Component on APM_ComponentSetting.ComponentID = APM_Component.ID

join APM_Application on APM_Component.ApplicationID = APM_Application.ID

left join (select APM_Threshold.ID, APM_Threshold.Critical from APM_Threshold where APM_Threshold.ThresholdName = 'CPU') CPUcrit on APM_Component.ID = CPUcrit.ID

left join (select APM_Threshold.ID, APM_Threshold.Critical from APM_Threshold where APM_Threshold.ThresholdName = 'CPU') PMEMcrit on APM_Component.ID = PMEMcrit.ID

left join (select APM_Threshold.ID, APM_Threshold.Critical from APM_Threshold where APM_Threshold.ThresholdName = 'CPU') VMEMcrit on APM_Component.ID = VMEMcrit.ID

join Nodes on APM_Application.NodeID = Nodes.NodeID

where APM_ComponentSetting.[Key] = '__CredentialSetId'

Leon,

Thank you very much! This is a great start. I wasn't sure where to begin but this query helps tremendously. I will mark this as the correct answer and build on this query to get more details per component. Thanks again!

- Joe

Leon Adato

I wanted to let you know what I did so far. Using your example as a base, I pulled together this:

select

    Nodes.Caption 'NODE NAME',

    APM_Application.Name 'APM NAME',

    APM_ComponentDetails.Name 'COMPONENT NAME',

    APM_ComponentDefinition.name 'COMPONENT TYPE',

    APM_ComponentDetails."Key" 'VARIABLE',

    APM_ComponentDetails."Value" 'VARIABLE VALUE'

from

    APM_ComponentDefinition,

    APM_ComponentDetails

    join APM_Application on APM_ComponentDetails.ApplicationID = APM_Application.ID

    join Nodes on APM_Application.NodeID = Nodes.NodeID

where

    Nodes.Assigned_Group = 'eCommerce Operations' AND

    Nodes.MachineType NOT LIKE 'XP' AND

    APM_ComponentDefinition.ComponentType = APM_ComponentDetails.ComponentType and

    APM_ComponentDetails."Value" is not NULL and

    APM_ComponentDetails."Value" != ''

order by

    APM_ComponentDetails.Name,

    APM_Application.Name,

    Nodes.Caption,

    APM_ComponentDetails."Key"

There is another post in the forum that I used for this example too but I cannot find it so I cannot take all the credit for this query. This works for my situation and the report satisfies our auditors I have a large network with a lot of SAM monitors (somewhere around 5000) so this report takes a good 120 seconds to run. But at least it works.

- Joe

NICE work!

Guys - I am copying and pasting this into a report and each time it fails. I'm not sure if the formatting from the copy/paste is wreaking havoc but can someone post this XML report in a working state? i can't seem to get this one going.

Thanks in advance

adatole I can't get this working on NPM 12.1/SAM 6.4.  Did the database table names change since this was written?

It has almost certainly changed since 2014 when this post was first written. You've stumbled upon the #1 reason to transition from SQL to SWQL and the Orion API. While table and field names will undoubtedly change as SolarWinds adds new features and functions, SWQL will change very little. And the new web-based alerting tool will take both SQL and SWQL as input, so you can make a slow transition from one to the other.

However, in this case, "sooner" rather than "later" will be the key.

I'm going to bookmark this, and update the query to SWQL as time permits. Stay tuned for further developments.

Thanks Leon

Hi Leon, Any updates with the latest Query

I whipped this one up in SWQL, you can use it in a custom query resource and it gathers all the info from the original request and a few things that the SQL query didn't include.  Depending on your environment it might take a while to complete

--Nodes

Select  

'' as Sort

,tostring(n.nodeid) as _linkfor_Sort

,n.caption as Object

,(n.ipaddress + ' - ' + n.MachineType) as Description

FROM orion.nodes n  

where n.Applications.ApplicationID is not null

--Apps

UNION (  

Select

'' as Sort

,(tostring(a.nodeid) + ' ' + TOSTRING(a.ApplicationID))  as _linkfor_Sort

,(' - '+A.Name) as Object

,('Template: ' + isnull(a.Template.Name,'')) as Description

FROM Orion.APM.Application a)

--Components

UNION (  

SELECT

'' as Sort

,(tostring(c.Application.nodeid) + ' ' + TOSTRING(c.ApplicationID) + ' ' + tostring(c.componentid))  as _linkfor_Sort

,(' -  ' + c.Name) as Object

,('Type: ' + c.ComponentDefinition.Name) as Description

FROM Orion.APM.Component c)

--Component descriptions

UNION (  

SELECT

'' as Sort

,(tostring(c.Application.nodeid) + ' ' + TOSTRING(c.ApplicationID) + ' ' + tostring(c.componentid) + ' 1')  as _linkfor_Sort

,(' -   Component Description') as Object

,(c.UserDescription) as Description

FROM Orion.APM.Component c)

--Component thresholds

UNION (  

SELECT

'' as Sort

,(tostring(c.Application.nodeid) + ' ' + TOSTRING(c.ApplicationID) + ' ' + tostring(c.componentid) + ' 2')  as _linkfor_Sort

,(' -   Thresholds') as Object

,(cat.thresholds) as Description

FROM Orion.APM.Component c

join (SELECT

componentid

, 'Statistic ' + isnull(tostring(ThresholdStatisticWarning),'-') + '/' + isnull(tostring(ThresholdStatisticCritical) + ' ','-') +

' Response ' + isnull(tostring(ThresholdResponseTimeWarning),'-') + '/' + isnull(tostring(ThresholdResponseTimeCritical) + ' ','-') +

' CPU ' + isnull(tostring(ThresholdCPUWarning),'-') + '/' + isnull(tostring(ThresholdCPUCritical) + ' ','-') +

' Mem ' + isnull(tostring(ThresholdPhysicalMemoryWarning),'-') + '/' + isnull(tostring(ThresholdPhysicalMemoryCritical) + ' ','-') +

' V Mem ' + isnull(tostring(ThresholdVirtualMemoryWarning),'-') + '/' + isnull(tostring(ThresholdVirtualMemoryCritical) + ' ','-') +

' IO R ' + isnull(tostring(ThresholdIOReadOperationsPerSecWarning),'-') + '/' + isnull(tostring(ThresholdIOReadOperationsPerSecCritical) + ' ','-') +

' IO W ' + isnull(tostring(ThresholdIOWriteOperationsPerSecWarning),'-') + '/' + isnull(tostring(ThresholdIOWriteOperationsPerSecCritical) + ' ','-') +

' IO Total ' + isnull(tostring(ThresholdIOTotalOperationsPerSecWarning),'-') + '/' + isnull(tostring(ThresholdIOTotalOperationsPerSecCritical) + ' ','-')   as Thresholds

from Orion.APM.ComponentAlertThresholds

where ThresholdCPUCritical is not null

or ThresholdCPUWarning  is not null

or ThresholdIOReadOperationsPerSecCritical is not null

or ThresholdIOReadOperationsPerSecWarning is not null

or ThresholdIOTotalOperationsPerSecCritical is not null

or ThresholdIOTotalOperationsPerSecWarning is not null

or ThresholdIOWriteOperationsPerSecCritical is not null

or ThresholdIOWriteOperationsPerSecWarning is not null

or ThresholdPhysicalMemoryCritical is not null

or ThresholdPhysicalMemoryWarning is not null

or ThresholdResponseTimeCritical is not null

or ThresholdResponseTimeWarning is not null

or ThresholdStatisticCritical is not null

or ThresholdStatisticWarning is not null

or ThresholdVirtualMemoryCritical is not null

or ThresholdVirtualMemoryWarning is not null) cat on cat.ComponentID=c.ComponentID

)

--Component settings

UNION (

SELECT

'' as Sort

,(tostring(c.Application.nodeid) + ' ' + TOSTRING(c.ApplicationID) + ' ' + tostring(c.componentid) + ' 4')  as _linkfor_Sort

,(' -   Template Settings') as Object

,cts.key + ' - ' + (case when cts.key = '__CredentialSetId' and cts.value not like '-3' and cts.value not like '-4' then (select cred.Name from orion.Credential cred where cred.ID = cts.value)

when cts.key = '__CredentialSetId' and cts.value = '-3' then 'Inherit from Node'

when cts.key = '__CredentialSetId' and cts.value = '-4' then 'Inherit from Template'

else tostring(cts.value) end) as [Value]

FROM Orion.APM.Component c

left join Orion.APM.ComponentSetting cs on cs.ComponentID=c.ComponentID

left join Orion.APM.ComponentTemplateSetting cts on cts.ComponentTemplateID=c.TemplateID and cts.key != cs.key

where cts.value not like ''

union (

SELECT

'' as Sort

,(tostring(c.Application.nodeid) + ' ' + TOSTRING(c.ApplicationID) + ' ' + tostring(c.componentid) + ' 3')  as _linkfor_Sort

,(' -   Settings Override') as Object

, cs.Key + ' - ' + (case when cs.key = '__CredentialSetId' and cs.value not like '-3' and cs.value not like '-4' then (select cred.Name from orion.Credential cred where cred.ID = cs.value)

when cs.key = '__CredentialSetId' and cs.value = '-3' then 'Inherit from Node'

when cs.key = '__CredentialSetId' and cs.value = '-4' then 'Inherit from Template'

else tostring(cs.value) end) as [Value]

FROM Orion.APM.Component c

left join Orion.APM.ComponentSetting cs on cs.ComponentID=c.ComponentID

where cs.value not like ''

))

ORDER BY _linkfor_Sort, Object desc

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems