Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

AppInsight tracking parent to child configuration changes.

Hi everyone

I'm interested in writing a query against the Orion database that can pull the default alert configuration values and if the child configuration has changed.

Looking at the number of tables, 3137 to be exact, this will take some time to track down.

For example, lets use Reads per second.

Lets say the default configuration is set to 90 before it alerts and this is shared across the entire enterprise.

After tuning the instance, Reads per second is set to 200 on the instance, no longer using the parent value of 90.

Has anyone written a query to do something like this and would share the code?

Thanks,

Find more posts tagged with

Accepted answers

xtraspecialj

Ahh yes, I think I have a SWQL Query somewhere. Give me some time and I can find it somewhere (assuming someone else doesn't reply with it first of course).

xtraspecialj

This isn't exactly what you need, but it's a start. This is a SWQL Query I have that will return the current thresholds set at the template level. You can change the template name to whatever you want, but I have it currently set at "AppInsight for SQL".

To make this show you the thresholds for individual components, it'll need to be joined to the Orion.APM.ThresholdsByComponent entity, which I can try and do if I get some time today.

Here's the code (by the way, you will need SWQL Studio installed on your computer. You can get that by downloading and installing the OrionSDK.msi from the Orion SDK Github page. If I can make a recommendation: Go to the All Releases section and scroll down and find version 2.6.0.238-Beta because any newer version than that has a problem with the Intellisense in SWQL Studio, which makes writing and working with SWQL queries quite difficult. Once SWQL Studio is installed, just point it towards your Primary Orion Polling Engine choose either Orion (v3) AD if you use AD login in Orion, or just Orion (v3) and put in your username and password).

Here is the SWQL Query (to insert this into a report, just create a web report and use the Custom Table object. In the Custom Table data source, change the Select Method to Advanced Database Query and make sure it's set to SWQL, then just copy and paste this in; once you've verified it works for you in SWQL Studio of course.):

SELECT
ISNULL(cc.Name, 'UNCATEGORIZED') AS CategoryName
,c.Name AS Component
,ct.Name AS ComponentTypeName
,ISNULL(t.ThresholdName, 'NO THRESHOLD AVAILABLE') AS ThresholdName
,CASE
	WHEN TOSTRING(t.ThresholdOperator) = 0 THEN 'Greater Than'
	WHEN TOSTRING(t.ThresholdOperator) = 1 THEN 'Greater Than or Equal to'
	WHEN TOSTRING(t.ThresholdOperator) = 2 THEN 'Equal to'
	WHEN TOSTRING(t.ThresholdOperator) = 3 THEN 'Less Than or Equal to'
	WHEN TOSTRING(t.ThresholdOperator) = 4 THEN 'Less Than'
	WHEN TOSTRING(t.ThresholdOperator) = 5 THEN 'Not Equal to'
	ELSE ISNULL((TOSTRING(t.ThresholdOperator)), 'No Threshold Set')
END AS ThresholdOperator
,CASE
	WHEN t.Warning > 1.797E+300 THEN NULL
	WHEN t.Warning < -1.797E-300 THEN NULL
	ELSE t.Warning
END AS WarningThreshold
,CASE
	WHEN t.Warning > 1.797E+300 THEN NULL
	WHEN t.Warning < -1.797E-300 THEN NULL
	WHEN t.WarningPolls = t.WarningPollsInterval THEN (TOSTRING(t.WarningPolls) + ' Consecutive Poll(s)')
	ELSE (TOSTRING(t.WarningPolls) + ' out of ' + TOSTRING(t.WarningPollsInterval) + ' Polls')
END AS WarningThresholdOccurrences
,CASE
	WHEN t.Critical > 1.797E+300 THEN NULL
	WHEN t.Critical < -1.797E-300 THEN NULL
	ELSE t.Critical
END AS ThresholdCritical
,CASE
	WHEN t.Critical > 1.797E+300 THEN NULL
	WHEN t.Critical < -1.797E-300 THEN NULL
	WHEN t.CriticalPolls = t.CriticalPollsInterval THEN (TOSTRING(t.CriticalPolls) + ' Consecutive Poll(s)')
	ELSE (TOSTRING(t.CriticalPolls) + ' out of ' + TOSTRING(t.CriticalPollsInterval) + ' Polls')
END AS CriticalThresholdOccurrences
FROM
Orion.APM.ComponentTemplate c
JOIN
Orion.APM.ApplicationTemplate at ON c.ApplicationTemplateID = at.ApplicationTemplateID
LEFT OUTER JOIN
Orion.APM.ComponentCategory cc ON c.ComponentCategoryID = cc.CategoryID
LEFT OUTER JOIN
Orion.APM.ComponentDefinition ct ON c.ComponentType = ct.ComponentType
LEFT OUTER JOIN
Orion.APM.Threshold t ON c.ID = t.ID
WHERE
at.Name = 'AppInsight for SQL'
AND ISNULL(t.ThresholdName, 'NONE') NOT IN ('Response', 'IOReadOperationsPerSec', 'IOTotalOperationsPerSec', 'IOWriteOperationsPerSec')
ORDER BY cc.Name, c.Name, t.ThresholdName

All comments

xtraspecialj

Are you talking about thresholds? Sorry, not completely clear on what you're asking about here.

jefff

Thanks for the response.

Yes, that is the correct term.

Maybe I can explain a bit better what we are trying to gather.

In the example, we want to change the "Page Reads/sec" threshold, which has a default value of lets say 150, which is inherited by all of the children objects as a default threshold value.

Someone change "Page Reads/sec" to 300 on Database object ABC, thus overwriting the default threshold value.

We want to make a query that will gather all the data of the AppInsight objects and have their default values (e.g.150)and changed child values (e.g. 300) displayed.

Does that make more sense?

xtraspecialj

Ahh yes, I think I have a SWQL Query somewhere. Give me some time and I can find it somewhere (assuming someone else doesn't reply with it first of course).

sum_giais

You would surely need to edit this query for your own purposes but I created it to look into specifically powershell script component monitors (and the actual script itself and any overridden scripts set).

Perhaps it may get you close to what you need with some edits, not sure if app insight utilizes different tables in anyway though... but it would likely be a different component type ID.

thwack.solarwinds.com/.../swql-query-for-looking-at-powershell-component-scripts

Hope this helps-

xtraspecialj

To make this show you the thresholds for individual components, it'll need to be joined to the Orion.APM.ThresholdsByComponent entity, which I can try and do if I get some time today.

SELECT
ISNULL(cc.Name, 'UNCATEGORIZED') AS CategoryName
,c.Name AS Component
,ct.Name AS ComponentTypeName
,ISNULL(t.ThresholdName, 'NO THRESHOLD AVAILABLE') AS ThresholdName
,CASE
	WHEN TOSTRING(t.ThresholdOperator) = 0 THEN 'Greater Than'
	WHEN TOSTRING(t.ThresholdOperator) = 1 THEN 'Greater Than or Equal to'
	WHEN TOSTRING(t.ThresholdOperator) = 2 THEN 'Equal to'
	WHEN TOSTRING(t.ThresholdOperator) = 3 THEN 'Less Than or Equal to'
	WHEN TOSTRING(t.ThresholdOperator) = 4 THEN 'Less Than'
	WHEN TOSTRING(t.ThresholdOperator) = 5 THEN 'Not Equal to'
	ELSE ISNULL((TOSTRING(t.ThresholdOperator)), 'No Threshold Set')
END AS ThresholdOperator
,CASE
	WHEN t.Warning > 1.797E+300 THEN NULL
	WHEN t.Warning < -1.797E-300 THEN NULL
	ELSE t.Warning
END AS WarningThreshold
,CASE
	WHEN t.Warning > 1.797E+300 THEN NULL
	WHEN t.Warning < -1.797E-300 THEN NULL
	WHEN t.WarningPolls = t.WarningPollsInterval THEN (TOSTRING(t.WarningPolls) + ' Consecutive Poll(s)')
	ELSE (TOSTRING(t.WarningPolls) + ' out of ' + TOSTRING(t.WarningPollsInterval) + ' Polls')
END AS WarningThresholdOccurrences
,CASE
	WHEN t.Critical > 1.797E+300 THEN NULL
	WHEN t.Critical < -1.797E-300 THEN NULL
	ELSE t.Critical
END AS ThresholdCritical
,CASE
	WHEN t.Critical > 1.797E+300 THEN NULL
	WHEN t.Critical < -1.797E-300 THEN NULL
	WHEN t.CriticalPolls = t.CriticalPollsInterval THEN (TOSTRING(t.CriticalPolls) + ' Consecutive Poll(s)')
	ELSE (TOSTRING(t.CriticalPolls) + ' out of ' + TOSTRING(t.CriticalPollsInterval) + ' Polls')
END AS CriticalThresholdOccurrences
FROM
Orion.APM.ComponentTemplate c
JOIN
Orion.APM.ApplicationTemplate at ON c.ApplicationTemplateID = at.ApplicationTemplateID
LEFT OUTER JOIN
Orion.APM.ComponentCategory cc ON c.ComponentCategoryID = cc.CategoryID
LEFT OUTER JOIN
Orion.APM.ComponentDefinition ct ON c.ComponentType = ct.ComponentType
LEFT OUTER JOIN
Orion.APM.Threshold t ON c.ID = t.ID
WHERE
at.Name = 'AppInsight for SQL'
AND ISNULL(t.ThresholdName, 'NONE') NOT IN ('Response', 'IOReadOperationsPerSec', 'IOTotalOperationsPerSec', 'IOWriteOperationsPerSec')
ORDER BY cc.Name, c.Name, t.ThresholdName

jefff

That script is perfect, just a few minor modifications to make it run.

I was digging around, trying to find where the default threshold values are stored, but not having much luck, probably because I'm not exactly sure how this all ties together.

Is there a different table that holds the default values?

xtraspecialj

I don't think so. For all intents and purposes, a template contains all of the "default" thresholds. If you modify a template you've effectively changed the default threshold. The only way to keep the true default would be to clone a template and edit the copy, however, with AppInsight monitors you can't copy the template, so once you edit an AppInsight template's threshold, the default threshold is gone.

Also, I realized that the JOIN clause for Orion.APM.Threshold in my query is missing a clause. You would need to add in this at the end of it:

at.IsTemplate = TRUE

This would make sure it is only returning App Template thresholds.

If you wanted to see app monitor thresholds you actually wouldn't need to join to the ThresholdsByComponent entity like I originally thought, you would just need to do another join line to the Orion.APM.Thresholds entity and include IsTemplate = FALSE in its JOIN clause.