Displaying Cause of Component Events

We had trouble with that too.  We changed all of our alerts from Alert on Application to Alert on Component.  Then we dug through the available variables to find more valuable statistics.  This is an example of an SQL Lock alert.  We put the desired information 3 places.  The alert that goes to the teams, the "Message displayed when the alert is triggered" field and also us the action to write the event to the NetPerMon event log.  This is part of the alert message text.

AppInsight for SQL Component Details:

Node Name:  ${N=SwisEntity;M=Application.Node.DisplayName}

Instance Name: ${N=SwisEntity;M=Application.ApplicationAlert.ApplicationName}

IP Address:  ${N=SwisEntity;M=Application.Node.IP_Address}

Component Name:  ${N=SwisEntity;M=ComponentAlert.ComponentName}

Current Value:  ${N=SwisEntity;M=ComponentAlert.StatisticData}

Current State:  ${N=SwisEntity;M=ComponentAlert.ApplicationAvailability}.

Component Description:  ${N=SwisEntity;M=ComponentAlert.UserDescription}

The component description is key for the AppInsight alerts for us as the descriptions for the component and the recommendations are all in here.  But the current value and state may be what you are asking about.  Just write them to the log and you can find them in message center.  Write them in the alert and it is right there as well.

Disclaimer:

When we do this, we have to specify which components we want to alert on in the trigger condition, but since our SQL team only wanted alerts on a subset of the components, and the ability to look at charts of the others, this worked for us.

This is the SQL custom variable I wrote a while back to try to tackle this situation in my alert messages, it works for most of the "standard" component types but I haven't gone back to have it cover all the edge cases with the appinsights or components with multiple statistics.  It could be more efficient but i didn't know sql as well at the time and haven't been motivated to re-write it since then.

The tricky bit is that depending on the type of component there are a LOT of reasons it might be showing as yellow/red and all those scenarios live on different tables and it gets tangly fast..

Node ${N=SwisEntity;M=Application.Node.Caption} Application ${N=SwisEntity;M=Application.ApplicationAlert.ApplicationName} Component ${N=SwisEntity;M=ComponentAlert.ComponentName} is ${N=SwisEntity;M=Status;F=Status}

The following Thresholds have been breached:

${SQL: select isnull((

SELECT cast(concat(tbc.thresholdname, ' '

, case

when tbc.thresholdoperator = 0 then 'greater than '

when tbc.thresholdoperator = 1 then 'greater than or equal to '

when tbc.thresholdoperator = 2 then 'equal to '

when tbc.thresholdoperator = 3 then 'less than or equal to '

when tbc.thresholdoperator = 4 then 'less than '

when tbc.thresholdoperator = 5 then 'not equal to '

end

, cast(tbc.critical as varchar)

, ' for '

, isnull(isnull(ovr.criticalpolls,t.criticalpolls),1)

, case when isnull(ovr.criticalpolls,t.criticalpolls) != isnull(ovr.criticalpollsinterval,t.criticalpollsinterval) then concat(' of ',isnull(ovr.criticalpollsinterval,t.criticalpollsinterval)) else '' end

,case when isnull(ovr.criticalpolls,t.criticalpolls) > 1 then ' polls' else ' poll' end, CHAR(13)

) as xml) as CriticalDescription

--, *

FROM [dbo].[APM_ThresholdsByComponent] tbc

left join [dbo].[APM_Threshold] t on tbc.componentid=t.id and t.thresholdname=tbc.thresholdname and t.istemplate=1

left join [dbo].[APM_Threshold] ovr on tbc.componentid=ovr.id and ovr.thresholdname=tbc.thresholdname and ovr.istemplate=0

join [dbo].[APM_CurrentStatistics] cs on cs.componentid=tbc.componentid

where tbc.componentid=${N=SwisEntity;M=ComponentID}  and

(tbc.critical != '1.7976931348623157E+308')

and (

(tbc.thresholdname = 'StatisticData' and (

(tbc.thresholdoperator = 0 and (cs.componentstatisticdata > statisticcritical)) or

(tbc.thresholdoperator = 1 and (cs.componentstatisticdata >= statisticcritical)) or

(tbc.thresholdoperator = 2 and (cs.componentstatisticdata = statisticcritical)) or

(tbc.thresholdoperator = 3 and (cs.componentstatisticdata <= statisticcritical)) or

(tbc.thresholdoperator = 4 and (cs.componentstatisticdata < statisticcritical)) or

(tbc.thresholdoperator = 5 and (cs.componentstatisticdata != statisticcritical))))

or

(tbc.thresholdname = 'Response' and (

(tbc.thresholdoperator = 0 and (cs.componentresponcetime > responsetimecritical)) or

(tbc.thresholdoperator = 1 and (cs.componentresponcetime >= responsetimecritical)) or

(tbc.thresholdoperator = 2 and (cs.componentresponcetime = responsetimecritical)) or

(tbc.thresholdoperator = 3 and (cs.componentresponcetime <= responsetimecritical)) or

(tbc.thresholdoperator = 4 and (cs.componentresponcetime < responsetimecritical)) or

(tbc.thresholdoperator = 5 and (cs.componentresponcetime != responsetimecritical))))

or

(tbc.thresholdname = 'CPU' and (

(tbc.thresholdoperator = 0 and (cs.componentpercentcpu > cpucritical)) or

(tbc.thresholdoperator = 1 and (cs.componentpercentcpu >= cpucritical)) or

(tbc.thresholdoperator = 2 and (cs.componentpercentcpu = cpucritical)) or

(tbc.thresholdoperator = 3 and (cs.componentpercentcpu <= cpucritical)) or

(tbc.thresholdoperator = 4 and (cs.componentpercentcpu < cpucritical)) or

(tbc.thresholdoperator = 5 and (cs.componentpercentcpu != cpucritical))))

or

(tbc.thresholdname = 'PMem' and (

(tbc.thresholdoperator = 0 and (cs.componentpercentmemory > physicalmemorycritical)) or

(tbc.thresholdoperator = 1 and (cs.componentpercentmemory >= physicalmemorycritical)) or

(tbc.thresholdoperator = 2 and (cs.componentpercentmemory = physicalmemorycritical)) or

(tbc.thresholdoperator = 3 and (cs.componentpercentmemory <= physicalmemorycritical)) or

(tbc.thresholdoperator = 4 and (cs.componentpercentmemory < physicalmemorycritical)) or

(tbc.thresholdoperator = 5 and (cs.componentpercentmemory != physicalmemorycritical))))

or

(tbc.thresholdname = 'VMem' and (

(tbc.thresholdoperator = 0 and (cs.componentpercentvirtualmemory > virtualmemorycritical)) or

(tbc.thresholdoperator = 1 and (cs.componentpercentvirtualmemory >= virtualmemorycritical)) or

(tbc.thresholdoperator = 2 and (cs.componentpercentvirtualmemory = virtualmemorycritical)) or

(tbc.thresholdoperator = 3 and (cs.componentpercentvirtualmemory <= virtualmemorycritical)) or

(tbc.thresholdoperator = 4 and (cs.componentpercentvirtualmemory < virtualmemorycritical)) or

(tbc.thresholdoperator = 5 and (cs.componentpercentvirtualmemory != virtualmemorycritical))))

or

(tbc.thresholdname = 'IOReadOperationsPerSec' and (

(tbc.thresholdoperator = 0 and (cs.componentioreadoperationspersec > ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 1 and (cs.componentioreadoperationspersec >= ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 2 and (cs.componentioreadoperationspersec = ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 3 and (cs.componentioreadoperationspersec <= ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 4 and (cs.componentioreadoperationspersec < ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 5 and (cs.componentioreadoperationspersec != ioreadoperationsperseccritical))))

or

(tbc.thresholdname = 'IOWriteOperationsPerSec' and (

(tbc.thresholdoperator = 0 and (cs.componentiowriteoperationspersec > iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 1 and (cs.componentiowriteoperationspersec >= iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 2 and (cs.componentiowriteoperationspersec = iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 3 and (cs.componentiowriteoperationspersec <= iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 4 and (cs.componentiowriteoperationspersec < iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 5 and (cs.componentiowriteoperationspersec != iowriteoperationsperseccritical))))

or

(tbc.thresholdname = 'IOTotalOperationsPerSec' and (

(tbc.thresholdoperator = 0 and (cs.componentiototaloperationspersec > iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 1 and (cs.componentiototaloperationspersec >= iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 2 and (cs.componentiototaloperationspersec = iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 3 and (cs.componentiototaloperationspersec <= iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 4 and (cs.componentiototaloperationspersec < iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 5 and (cs.componentiototaloperationspersec != iototaloperationsperseccritical))))

)

FOR XML PATH('') ),'Appinsight/Other');  }

This is the SQL custom variable I wrote a while back to try to tackle this situation in my alert messages, it works for most of the "standard" component types but I haven't gone back to have it cover all the edge cases with the appinsights or components with multiple statistics.  It could be more efficient but i didn't know sql as well at the time and haven't been motivated to re-write it since then.

The tricky bit is that depending on the type of component there are a LOT of reasons it might be showing as yellow/red and all those scenarios live on different tables and it gets tangly fast..

Node ${N=SwisEntity;M=Application.Node.Caption} Application ${N=SwisEntity;M=Application.ApplicationAlert.ApplicationName} Component ${N=SwisEntity;M=ComponentAlert.ComponentName} is ${N=SwisEntity;M=Status;F=Status}

The following Thresholds have been breached:

${SQL: select isnull((

SELECT cast(concat(tbc.thresholdname, ' '

, case

when tbc.thresholdoperator = 0 then 'greater than '

when tbc.thresholdoperator = 1 then 'greater than or equal to '

when tbc.thresholdoperator = 2 then 'equal to '

when tbc.thresholdoperator = 3 then 'less than or equal to '

when tbc.thresholdoperator = 4 then 'less than '

when tbc.thresholdoperator = 5 then 'not equal to '

end

, cast(tbc.critical as varchar)

, ' for '

, isnull(isnull(ovr.criticalpolls,t.criticalpolls),1)

, case when isnull(ovr.criticalpolls,t.criticalpolls) != isnull(ovr.criticalpollsinterval,t.criticalpollsinterval) then concat(' of ',isnull(ovr.criticalpollsinterval,t.criticalpollsinterval)) else '' end

,case when isnull(ovr.criticalpolls,t.criticalpolls) > 1 then ' polls' else ' poll' end, CHAR(13)

) as xml) as CriticalDescription

--, *

FROM [dbo].[APM_ThresholdsByComponent] tbc

left join [dbo].[APM_Threshold] t on tbc.componentid=t.id and t.thresholdname=tbc.thresholdname and t.istemplate=1

left join [dbo].[APM_Threshold] ovr on tbc.componentid=ovr.id and ovr.thresholdname=tbc.thresholdname and ovr.istemplate=0

join [dbo].[APM_CurrentStatistics] cs on cs.componentid=tbc.componentid

where tbc.componentid=${N=SwisEntity;M=ComponentID}  and

(tbc.critical != '1.7976931348623157E+308')

and (

(tbc.thresholdname = 'StatisticData' and (

(tbc.thresholdoperator = 0 and (cs.componentstatisticdata > statisticcritical)) or

(tbc.thresholdoperator = 1 and (cs.componentstatisticdata >= statisticcritical)) or

(tbc.thresholdoperator = 2 and (cs.componentstatisticdata = statisticcritical)) or

(tbc.thresholdoperator = 3 and (cs.componentstatisticdata <= statisticcritical)) or

(tbc.thresholdoperator = 4 and (cs.componentstatisticdata < statisticcritical)) or

(tbc.thresholdoperator = 5 and (cs.componentstatisticdata != statisticcritical))))

or

(tbc.thresholdname = 'Response' and (

(tbc.thresholdoperator = 0 and (cs.componentresponcetime > responsetimecritical)) or

(tbc.thresholdoperator = 1 and (cs.componentresponcetime >= responsetimecritical)) or

(tbc.thresholdoperator = 2 and (cs.componentresponcetime = responsetimecritical)) or

(tbc.thresholdoperator = 3 and (cs.componentresponcetime <= responsetimecritical)) or

(tbc.thresholdoperator = 4 and (cs.componentresponcetime < responsetimecritical)) or

(tbc.thresholdoperator = 5 and (cs.componentresponcetime != responsetimecritical))))

or

(tbc.thresholdname = 'CPU' and (

(tbc.thresholdoperator = 0 and (cs.componentpercentcpu > cpucritical)) or

(tbc.thresholdoperator = 1 and (cs.componentpercentcpu >= cpucritical)) or

(tbc.thresholdoperator = 2 and (cs.componentpercentcpu = cpucritical)) or

(tbc.thresholdoperator = 3 and (cs.componentpercentcpu <= cpucritical)) or

(tbc.thresholdoperator = 4 and (cs.componentpercentcpu < cpucritical)) or

(tbc.thresholdoperator = 5 and (cs.componentpercentcpu != cpucritical))))

or

(tbc.thresholdname = 'PMem' and (

(tbc.thresholdoperator = 0 and (cs.componentpercentmemory > physicalmemorycritical)) or

(tbc.thresholdoperator = 1 and (cs.componentpercentmemory >= physicalmemorycritical)) or

(tbc.thresholdoperator = 2 and (cs.componentpercentmemory = physicalmemorycritical)) or

(tbc.thresholdoperator = 3 and (cs.componentpercentmemory <= physicalmemorycritical)) or

(tbc.thresholdoperator = 4 and (cs.componentpercentmemory < physicalmemorycritical)) or

(tbc.thresholdoperator = 5 and (cs.componentpercentmemory != physicalmemorycritical))))

or

(tbc.thresholdname = 'VMem' and (

(tbc.thresholdoperator = 0 and (cs.componentpercentvirtualmemory > virtualmemorycritical)) or

(tbc.thresholdoperator = 1 and (cs.componentpercentvirtualmemory >= virtualmemorycritical)) or

(tbc.thresholdoperator = 2 and (cs.componentpercentvirtualmemory = virtualmemorycritical)) or

(tbc.thresholdoperator = 3 and (cs.componentpercentvirtualmemory <= virtualmemorycritical)) or

(tbc.thresholdoperator = 4 and (cs.componentpercentvirtualmemory < virtualmemorycritical)) or

(tbc.thresholdoperator = 5 and (cs.componentpercentvirtualmemory != virtualmemorycritical))))

or

(tbc.thresholdname = 'IOReadOperationsPerSec' and (

(tbc.thresholdoperator = 0 and (cs.componentioreadoperationspersec > ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 1 and (cs.componentioreadoperationspersec >= ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 2 and (cs.componentioreadoperationspersec = ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 3 and (cs.componentioreadoperationspersec <= ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 4 and (cs.componentioreadoperationspersec < ioreadoperationsperseccritical)) or

(tbc.thresholdoperator = 5 and (cs.componentioreadoperationspersec != ioreadoperationsperseccritical))))

or

(tbc.thresholdname = 'IOWriteOperationsPerSec' and (

(tbc.thresholdoperator = 0 and (cs.componentiowriteoperationspersec > iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 1 and (cs.componentiowriteoperationspersec >= iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 2 and (cs.componentiowriteoperationspersec = iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 3 and (cs.componentiowriteoperationspersec <= iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 4 and (cs.componentiowriteoperationspersec < iowriteoperationsperseccritical)) or

(tbc.thresholdoperator = 5 and (cs.componentiowriteoperationspersec != iowriteoperationsperseccritical))))

or

(tbc.thresholdname = 'IOTotalOperationsPerSec' and (

(tbc.thresholdoperator = 0 and (cs.componentiototaloperationspersec > iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 1 and (cs.componentiototaloperationspersec >= iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 2 and (cs.componentiototaloperationspersec = iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 3 and (cs.componentiototaloperationspersec <= iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 4 and (cs.componentiototaloperationspersec < iototaloperationsperseccritical)) or

(tbc.thresholdoperator = 5 and (cs.componentiototaloperationspersec != iototaloperationsperseccritical))))

)

FOR XML PATH('') ),'Appinsight/Other');  }