10 Replies Latest reply on Jul 25, 2018 5:29 PM by mesverrum

    Report to list all settings within all assigned application monitors

    joedissmeyer

      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?

        • Re: Report to list all settings within all assigned application monitors
          Leon Adato

          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'

          3 of 3 people found this helpful
            • Re: Report to list all settings within all assigned application monitors
              joedissmeyer

              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

              • Re: Report to list all settings within all assigned application monitors
                joedissmeyer

                adatole

                 

                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

                  • Re: Report to list all settings within all assigned application monitors
                    orioncrack

                    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

                      • Re: Report to list all settings within all assigned application monitors
                        bharris

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

                          • Re: Report to list all settings within all assigned application monitors
                            Leon Adato

                            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.

                            1 of 1 people found this helpful
                                • Re: Report to list all settings within all assigned application monitors
                                  mesverrum

                                  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