cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Report to list all settings within all assigned application monitors

Jump to solution

I received an insane request from some senior admins in my organization and I don't think it can be done using the report writer. But I'd like to confirm first.

What I need to do is export all of my current assigned SAM templates, where they are assigned to, how each app monitor is configured, and what protocol or credential is being used in the SAM template. The report has to look something like this:

  • Monitored Server name
    • Application Monitor name (SAM Template name) assigned to the server
    • Description of the app monitor
      • The components of the template
        • Every detailed settings and configuration in the template
        • Every threshold setting in each component
      • Description of each component
    • Credential \ Protocol used by the monitor

Can this be done?

1 Solution
Level 17

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 Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

View solution in original post

14 Replies
Level 17

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 Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

View solution in original post

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

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

2-20-2016 12-24-18 PM.jpg

0 Kudos

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

0 Kudos

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.

Leon Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

Hi Leon, Any updates with the latest Query

0 Kudos

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

- Marc Netterfield, Github

Thanks so much mesverrum​ for putting this together!! This is exactly what I've been looking for!

Just as an fyi, when I first ran the query it gave me a "Mismatched input '' expecting 'SELECT'". I guess the formatting when I copied the query created extra spaces in between "..)  as..." and certain other spacing and lines that SWQL didn't like. So if someone else tries to run this and gets a similar error, I've recopied mesverrum​'s query here with the spaces removed in hopes that it doesn't create the same errors in SWQL.

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

0 Kudos

Thanks,

The error you got wouldn't come from anything to do with spaces or quotes. Comparing what I have to what you pasted in I suspect that when you copied it initially you missed a character in the first line, --Nodes, so instead of having that line commented out it was trying to figure out what function "Nodes" was instead of the Select that it was expecting.

I just copied and pasted the query from here as I wrote it and it ran without issue, just need to make sure the copy is complete.

- Marc Netterfield, Github
0 Kudos

So I figured out what it was. When I copy your query and directly paste it into SWQL studio it runs just fine. But what I did was copied your query to OneNote to save it, and then copied from OneNote into SWQL studio which gave me the error. I just recopied to test it and it did the same thing.

I have no clue why, but either way, it's working! Thanks again for putting this together!!

0 Kudos

not working for me...i running sam 6.7

0 Kudos

Thanks Leon

0 Kudos

NICE work!

Leon Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

0 Kudos

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