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

Resource To Display All Your Components With Problems (SQL)

< BACK TO TO THE MAGIC OF SQL SCRIPTS SERIES LIST

Hi All,

This solution is going to be based on the previous work, which has been done for SWQL. Version 2 if you will... SQL based

Challenge:

  • Be able to see all your components with problems in one convenient report/resource with click-able URLs

End result:

003.PNG

Features:

  • Display your components with problems along-side with nodes, apps, message, etc in one SQL report
  • Direct click-able links for the objects
  • Status icons
  • Use this report as a resource on the page
  • Support for exclusions (mute) on any level - node, app, components itself and even template
  • Ensure that disabled components would not show up

Step-by-step:

(1)

To be able to mute you would need to have the following Custom Properties (obviously if you already have some for this purpose - please adopt SQL script as per your set-up):

  • n_mute_dashb Node's CS AS a date-type, where you can set date in the future until when to mute
  • a_mute_dashb App's CS AS a date-type, where you can set date in the future until when to mute

(2)

PLEASE USE BELOW SQL AND REMEMBER TO UPDATE DATABASE NAME

* For the URLs to work you would need to copy paste URLs (which you can find in the SQL script below as green comments, next to columns descriptions) into Web URL field of the Orion Report Writer, under Field Formatting tab. You can also hide ID fields as they are only used to build URLs). If you will download attached Report - this is all has been done for you already

SELECT 

   APM_AlertsAndReportsData.NodeId 

  ,APM_AlertsAndReportsData.ApplicationID 

  ,APM_AlertsAndReportsData.ComponentID 

  ,APM_AlertsAndReportsData.ComponentStatus+'.gif' AS 'ICON' 

  ,APM_AlertsAndReportsData.NodeName AS 'NODE' --/Orion/View.aspx?View=NodeDetails&NetObject=N:${NodeID} 

  ,APM_AlertsAndReportsData.ApplicationName AS 'APP' --/Orion/APM/ApplicationDetails.aspx?NetObject=AA:${ApplicationID} 

  ,APM_AlertsAndReportsData.ComponentName AS 'CMPNT' --/Orion/APM/MonitorDetails.aspx?NetObject=AM:${ComponentID} 

  ,Round(APM_AlertsAndReportsData.StatisticData, 2) AS 'STAT' 

  ,CASE 

    WHEN len(APM_AlertsAndReportsData.ComponentMessage)>100 

      THEN left(APM_AlertsAndReportsData.ComponentMessage,100) + ' [...]' 

    ELSE APM_AlertsAndReportsData.ComponentMessage 

   END AS 'MSG' --/Orion/APM/MonitorDetails.aspx?NetObject=AM:${ComponentID} 

FROM SolarWinds.dbo.APM_AlertsAndReportsData WITH (NOLOCK) 

 

INNER JOIN SolarWinds.dbo.Nodes n WITH (NOLOCK) ON n.NodeID = APM_AlertsAndReportsData.NodeId 

INNER JOIN SolarWinds.dbo.APM_Application a WITH (NOLOCK) ON a.ID = APM_AlertsAndReportsData.ApplicationID 

INNER JOIN SolarWinds.dbo.APM_ApplicationCustomProperties appcp WITH (NOLOCK) ON appcp.ApplicationID = APM_AlertsAndReportsData.ApplicationID 

INNER JOIN SolarWinds.dbo.APM_ApplicationTemplate at WITH (NOLOCK) ON at.ID = a.TemplateID 

INNER JOIN Solarwinds.dbo.APM_Component c WITH (NOLOCK) ON c.ID = APM_AlertsAndReportsData.ComponentID 

INNER JOIN Solarwinds.dbo.APM_ComponentTemplate ct WITH (NOLOCK) ON ct.ID = c.TemplateID 

 

WHERE 

 

  (n.n_mute_dashb IS NULL OR n.n_mute_dashb < n.LastSync) AND --not muted on Node level 

  (appcp.a_mute_dashb IS NULL OR appcp.a_mute_dashb < n.LastSync) AND --not muted on App level 

  APM_AlertsAndReportsData.UserNotes NOT LIKE '%__mute%' AND --not muted on Component level 

  at.Description NOT LIKE '%__mute%' AND --not muted on template level 

 

  n.Status IN ('1','3') AND --Node is either (1)Up or (3)Warning 

  n.UnManaged = 0 AND --Node is not unmanaged 

  APM_AlertsAndReportsData.ApplicationStatus <> 'Unmanaged' AND --App is not unmanaged 

  ( 

   (ct.IsDisabled = 0 AND ISNULL(c.IsDisabled,0) <> 1) OR --Component is enabled on template level and is not disabled on component level 

   (ct.IsDisabled = 1 AND ISNULL(c.IsDisabled,1) = 0) --Component is disabled on template level, but is overridden (enabled) on component level 

  ) AND 

 

  APM_AlertsAndReportsData.ComponentStatus <> 'Up' -- Component is not Up

(3)

Once you have configured and saved you report in Report Writer (or you can download attached) - you can then add "Report from Orion Writer" resource to any page you like (usually it would be your summary or NOC page)

001.PNG

Select this new report from the resource:

002.PNG

You are done!

How to mute

  • To mute on node level: set n_mute_dashb to any date in the future you would like to mute until
  • To mute on application level: set a_mute_dashb to any date in the future you would like to mute until
  • To mute on a component level: add "__mute" string into User Notes field of your component
  • To mute on a template level: add "__mute" string into Template description field

Tips

  • Usually, when I introduce any new monitoring application - I would mute it on template level. In this case our team don't receive any alerts whilst I am testing it.
  • I do use same SQL script (with just few minor adjustments) to fire email alerts. Therefore - all flagged issues can be muted by simply acknowledging an alert for a particular component. This particular check is not included in the above SQL, but please feel free to let me know if you would like to implement this and need help
  • My original script (which I use in production) has few additional checks, such as Environemnt and Impact. It will only trigger if Environment is set to PROD and impact either HIGH or MEDIUM. I will discuss this solution later, as it is a very robust and flexible approach for managing scopes in the environment of any size, which deserves a separate article

Have fun

To Your Success,

Alex Soul

www.pixace.com

Attachments
Comments

Alex, I know this is from a while back, but can you provide SQL for the APM_AlertsAndReportData view?

I was able to map out most of the tables you have included in your custom view with the exception of ComponentStatus and StatisticsData.

I wasn't able to find anywhere an icon/gif was referenced in the APM tables, or find a solid table for Statistics.

Also, I was not able to get the status icons for application/components working as shown in the previous post before this one- not sure if this is still possible.

If you could provide information as to how to create the specific view you are referencing, that would be an amazing help.

Thank you! - J

Hi J,

APM_AlertsAndReportData is a standard view in SolarWinds. I didn't create it myself - it was there already. What SAM version do you have?

If you have problems with finding actual gif icon, you can usually construct your own. For example:

SELECT

   n.StatusLED AS 'nLED'

  ,'PIX-'+ard.ApplicationStatus+'.gif' AS 'aLED'

  ,'PIX-'+ard.ComponentStatus+'.gif' AS 'cLED'

FROM APM_AlertsAndReportsData ard WITH(NOLOCK)

INNER JOIN Nodes n WITH(NOLOCK) ON n.NodeID = ard.NodeID

pastedImage_4.png

Notice a couple things here:

  • First, I have a bunch of my own icons that I want to use. So, I distinguish them with "PIX-" prefix. Often, especially if you use Status as a bases for your icon name (like with nLED column), this icon will already be there.
  • "Small-" prefix is used by design by SolarWinds. So, whatever you have as a result of your select statement above will have a prefix of "Small-" as an actual icon reference when you use it in RESOURCE
  • Also, you need to ensure that whatever you have as a final result as your icon is also stored in the below location:

pastedImage_2.png

And this is how icon column MUST be configured within your resource so that actual icon is displayed on page

pastedImage_9.png

Hope helps

--

Alex Soul

Alex, I can't tell you how much this helped (the part with the icons at least). I am super appreciative for the level of detail you went through to explain this.

I am kind of lost on SQL views as I assumed they were a part of the db, and not really sure how to access them - I'm relatively new on the SQL scene so to speak -

Although trying to backwards engineer and use your code to experiment has actually helped me learn a lot so far. I have the latest version of SAM.

Thanks again for the help and for all the code you have posted here on thwack -

And you are right - Hope does help. Thanks for giving some hope. haha    - J

Glad

This is what I am here for - to receive this kind of replies from my fellow SolarWinders Thank You!

As for the SQL part - as I have mentioned, view is already in SQL by default - so, you don't need to create anything. I think I know what is you problem - you simply copy-paste SQL script and didn't change database name... Notice Step # 2 above has the following "PLEASE USE BELOW SQL AND REMEMBER TO UPDATE DATABASE NAME"

All you need to do is to replace every instance of "SolarWinds.dbo" with "<You_Database_Name>.dbo" and run it again - try it - I think this will get you going

Version history
Revision #:
1 of 1
Last update:
‎12-22-2014 06:56 AM
Updated by: