Custom SQL for APM: Applications

This post is to highlight my displeasure with the direction of custom alert definitions. For years, I have used custom SQL to do the advanced filtering that my environment requires. This includes group membership, certain node and or application custom property values and others. It has always been a pet peeve that the SELECT box was hardcoded based on the type of alert chosen in the dropdown menu. But as long as it worked, I was fine with it.

That all changed when I upgraded to the latest version (Orion Platform 2018.4 HF3, NPM 12.4, SAM 6.8.0). I started to hear complaints of duplicate notifications for application alerts. When I look at my definitions I found that they were set to "APM: Component" types, instead of "APM: Application" types. I confirmed this on my second instance which is still on a previous version (Orion Platform 2018.2.1 SP1, NPM 12.3, SAM 6.6.1). Since these application monitors contain multiple components, when they trigger, they trigger for ALL of the components instead of the single application.

I thought this was a bug and opened a case. It was then I learned this was a purposeful change that not only broke my alert definitions, but would now force me to use the SWQL. I understand that many people like and use SWQL, and that is fine. However, my background in in SQL, all of my queries for alerts, reports even dynamic group memberships are all in SQL. To remove that feature in favor of the proprietary language is what has me upset. Technical Support suggested that I submit a Feature Request to raise this issue, but it is not a NEW feature I am looking for. I simply want to have the original feature returned.

Is there any way I can modify my install so that I can have that feature back? Or will I not only be forced to re-engineer a large number of queries into a proprietary language that I have never used?

Find more posts tagged with

custom sql

advanced alerting

Accepted answers

All comments

xaturbatu

You can either change it to SWQL or just join it with the APM_ApplicationAlertsData table, make the components distinct to trigger only once and adapt a little the old queries. The only thing you need to look at is the email variables to be set right. An example is below.

SELECT APM_AlertsAndReportsData.ComponentName, APM_AlertsAndReportsData.ComponentID FROM APM_AlertsAndReportsData ---<<<<This is selected by default in APM: Component

inner join APM_ApplicationAlertsData a on a.ApplicationID=APM_AlertsAndReportsData.ApplicationID

where APM_AlertsAndReportsData.ComponentID in (Select ard.componentid from APM_AlertsAndReportsData ard group by ard.ComponentID having count(ard.componentid)=1) ---<<< complete your other queries

inner join APM_Application a on a.ID=ard.ApplicationIDwhere ard.ComponentID in (Select ard.componentid from APM_AlertsAndReportsData ard group by ard.ComponentID having count(ard.componentid)=1)

mesverrum

I can relate to the frustration, I can hardly think of a reason to remove the feature in that way.

It's a pain to be forced to change something that was working fine, but on the plus side it's pretty easy to convert SQL to SWQL, in most cases I can convert back and forth in under 5 mins. The only places where it gets a bit painful are usually if you are relying on some more advanced sql functions like rank over or things on that level. Some functions have slight tweaks to their names or syntax, but its all pretty simple.

SWQL Functions · solarwinds/OrionSDK Wiki · GitHub

Detroiter

That sounds like a good idea, but your query does not work. You have an INNER JOIN after a WHERE, which SQL does not allow. Is that intended? If not, how should it have been formatted?