This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL table of specific component threshold

Hi,

I'm searching for the SQL (not SWQL) table where I can find the threshold of component from an assigned application.

I was able to find the right one for the application templates, but if I overwrite the threshold value of the template setting this data has also to be stored in the database. 

Greetings

Dominik

  • When you create an application monitoring template and assign it to a node, you end up with two key items (no pun intended) in the database. There will be an ApplicationID for each instance of that assigned template (remember that in Orion, template+node=application) and there will be a ComponentID for each monitored component within the application (e.g. a monitored process = one unique component).

    There may be a more direct approach, but in my customizations I learned by trial and error. The Orion SWIS Query page (https://OrionServerName/Orion/Admin/Swis.aspx) lets you test queries against your Orion DB. Scrolling through the tables and views, you'll see Orion.APM.Application___ and Orion.APM.Component___ entities. The ComponentAlertThresholds one should have the data that you are looking for once you identify which row contains your desired template.

    sturdyerde_0-1590090953746.png

  •   Thank you for your explanation. I will try this

  • Hello Dominik

    I believe the tables you are looking for may be either :

    • dbo.APM_Thresold 
      • This table stores the threshold values for the Templates
    • dbo.APM_ComponenetThresholdCache
      • This table stores the individual component critical and warning values

    If you would like to go even further you can use the following select statement to calculate the baselines for all of your components based on SolarWinds 95th percentile recommendation:

    /*Calculate the Min / Max and 95th percentile for all component monitors based on a rolling time period. (SolarWinds recommends 7 days).
    ThresholdOperator is used in the case statemnt for either setting the MIN or MAX value for the Critical Thresholds
    ThresholdOperator Values: 0 = Greater Than, 4 = Less Than.  Also remember to change the dates in the CTE*/

    With cte_Stats (ComponentID, Max_Critical_Alert, MAX_Warning_Alert, MIN_Critical_Alert, MIN_Warning_Alert)
    AS (
    select distinct
    ComponentID
    ,Round(Max(AvgStatisticData) Over (Partition By ComponentID),2) as Max_Critical_Alert
    ,Round(Percentile_disc(.95) Within Group (Order By AvgStatisticData) Over (Partition By ComponentID),2) as MAX_Warning_Alert
    ,Round(Min(AvgStatisticData) Over (Partition By ComponentID),2) as MIN_Critical_Alert
    ,Round(Percentile_disc(.05) Within Group (Order By AvgStatisticData) Over (Partition By ComponentID),2) as MIN_Warning_Alert
    from DBO.APM_StatisticsUsage
    join APM_Component as c on APM_StatisticsUsage.ComponentID = c.ID
    where Convert(date, DateTime) between 'Start Date' and 'End Date'

    )
    Select

    cte.ComponentID
    , Case
    When ThresholdOperator = 0 Then cte.Max_Critical_Alert
    Else
    Case
    When ThresholdOperator = 4 then cte.MIN_Critical_Alert
    End
    End as StatisticDataCritical

    ,Case
    When ThresholdOperator = 0 Then cte.Max_Warning_Alert
    Else
    Case
    When ThresholdOperator = 4 then cte.MIN_Warning_Alert
    End
    End as StatisticDataWarning
    From cte_stats as cte
    left join APM_ComponentThresholdCache as tc on cte.ComponentID = tc.ComponentID
    join APM_Component as c on cte.ComponentID = c.id
    left join APM_Threshold as t on c.TemplateID = t.ID
    where c.ApplicationID in (Comma Separated List Of Your Application ID's)

    You can find a list of all of your application ID's by using the following query:

    select
    n.caption as Node_Name
    ,app.Name as Application_Name
    ,app.ID as App_ID
    from DBO.APM_Application as app
    join dbo.nodes as n on app.NodeID = n.NodeID

    **Please use caution when running the calculation statement as it can take several minutes to complete which may have a negative performance impact on a production environment**

    Best Regards

    Jason