Hey All,
I came across a request to see all the problematic components when alerting for a SAM Application.
I managed to create a variable to be used on a email to show a list of components that are not "available" when sending the email for a SAM Alert.
The trigger condition for the alert I did is:
The SQL Variable used is below. This one will get all the components not up on the Application that triggered the alert and display the name of this components:
The full body of the email I used is:
----- Components not up for Application ${N=SwisEntity;M=ApplicationAlert.ApplicationName} on ${N=SwisEntity;M=Node.Caption} -----
${SQL: SELECT name+Char(10)
FROM [dbo].[APM_Component] as c left join [dbo].[APM_CurrentComponentStatus] as cs on c.id = cs.componentid
where c.applicationid = ${N=SwisEntity;M=ApplicationID} and availability <> 1
FOR XML PATH ('')}
----------
The result:
----- Components not up for Application Microsoft IIS on XXXXXX -----
Web Management Service
ASP.NET State Service
Request Execution Time
Request Wait Time
----------
I hope this one helps someone else 
