cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

AppInsight for SQL summary screen (SWQL)

For people who have a large amount of AppInsight for SQL - they might only want to see certain metrics.   This script shows the metrics across the servers.

The clickable links will take you into the node and components.

pastedImage_1.png

select DISTINCT

n.Caption as [Node]

,n.DetailsURL as [_LinkFor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]

,tmp.data as [Free Stalls]

,tmp.detailsurl as [_linkfor_Free Stalls]

,case when tmp.data>tmp.ThresholdStatisticCritical then '/Orion/images/StatusIcons/Small-Critical.gif'

      when tmp.data>tmp.ThresholdStatisticWarning then '/Orion/images/StatusIcons/Small-Warning.gif'

      else '/Orion/images/StatusIcons/Small-up.gif' end as [_Iconfor_Free Stalls]

,tmp1.data as [Pages Read/s]

,tmp1.detailsurl as [_linkfor_Pages Read/s]

,case when tmp1.data>tmp1.ThresholdStatisticCritical then '/Orion/images/StatusIcons/Small-Critical.gif'

      when tmp1.data>tmp1.ThresholdStatisticWarning then '/Orion/images/StatusIcons/Small-Warning.gif'

      else '/Orion/images/StatusIcons/Small-up.gif' end as [_Iconfor_Pages Read/s]

,tmp2.data as [Pages Write/s]

,tmp2.detailsurl as [_linkfor_Pages Write/s]

,case when tmp2.data>tmp2.ThresholdStatisticCritical then '/Orion/images/StatusIcons/Small-Critical.gif'

      when tmp2.data>tmp2.ThresholdStatisticWarning then '/Orion/images/StatusIcons/Small-Warning.gif'

      else '/Orion/images/StatusIcons/Small-up.gif' end as [_Iconfor_Pages Write/s]

,tmp3.data as [Page Life Expectancy]

,tmp3.detailsurl as [_linkfor_Page Life Expectancy]

,case when tmp3.data<tmp3.ThresholdStatisticCritical then '/Orion/images/StatusIcons/Small-Critical.gif'

      when tmp3.data<tmp3.ThresholdStatisticWarning then '/Orion/images/StatusIcons/Small-Warning.gif'

      else '/Orion/images/StatusIcons/Small-up.gif' end as [_Iconfor_Page Life Expectancy]

from Orion.nodes n

join Orion.APM.Application a on a.NodeID=n.NodeID

join orion.apm.Component c on c.ApplicationID=a.ApplicationID

left  join

(SELECT DISTINCT n.caption, n.nodeid, n.Applications.Name, c.Name, c.ComponentID, c.ApplicationID ,c.CurrentStatistics.ComponentStatisticData as [data]

,c.DetailsUrl,ct.ThresholdStatisticWarning, ct.ThresholdStatisticCritical

from Orion.Nodes n join Orion.APM.Component c on c.ApplicationID=n.Applications.ApplicationID

join orion.APM.ComponentAlertThresholds ct on ct.ComponentID=c.ComponentID

where c.name like  'Free list stalls/sec') tmp on tmp.applicationid=a.ApplicationID and tmp.nodeid=n.nodeid

left  join

(SELECT distinct n.caption, n.nodeid, n.Applications.Name, c.Name, c.ComponentID, c.ApplicationID ,round(c.CurrentStatistics.ComponentStatisticData,0) as [data]

,c.DetailsUrl,ct.ThresholdStatisticWarning, ct.ThresholdStatisticCritical

from Orion.Nodes n join Orion.APM.Component c on c.ApplicationID=n.Applications.ApplicationID

join orion.APM.ComponentAlertThresholds ct on ct.ComponentID=c.ComponentID

where c.name like  'Page Reads/sec') tmp1 on tmp1.applicationid=a.ApplicationID and tmp1.nodeid=n.nodeid

left join

(SELECT DISTINCT  n.caption, n.nodeid, n.Applications.Name, c.Name, c.ComponentID, c.ApplicationID , round(c.CurrentStatistics.ComponentStatisticData,0) as [data]

,c.DetailsUrl,ct.ThresholdStatisticWarning, ct.ThresholdStatisticCritical

from Orion.Nodes n join Orion.APM.Component c on c.ApplicationID=n.Applications.ApplicationID

join orion.APM.ComponentAlertThresholds ct on ct.ComponentID=c.ComponentID

where c.name like  'Page Writes/sec') tmp2 on tmp2.applicationid=a.ApplicationID and tmp2.nodeid=n.nodeid

left join

(SELECT DISTINCT  n.caption, n.nodeid, n.Applications.Name, c.Name, c.ComponentID, c.ApplicationID ,c.CurrentStatistics.ComponentStatisticData as [data]

,c.DetailsUrl,ct.ThresholdStatisticWarning, ct.ThresholdStatisticCritical

from Orion.Nodes n join Orion.APM.Component c on c.ApplicationID=n.Applications.ApplicationID

join orion.APM.ComponentAlertThresholds ct on ct.ComponentID=c.ComponentID

where c.name like  'Page Life Expectancy') tmp3 on tmp3.applicationid=a.ApplicationID and tmp3.nodeid=n.nodeid

where a.ApplicationTemplateID in (SELECT distinct a.ApplicationTemplateID from Orion.APM.Application a

left join Orion.APM.ApplicationTemplate t on t.ApplicationTemplateID=a.ApplicationTemplateID where t.CustomApplicationType is not null and t.name like '%sql%')

order by tmp3.data asc, tmp2.data desc, tmp1.data desc

Thank you,

Amit

Version history
Revision #:
1 of 1
Last update:
‎01-21-2019 02:10 PM
Updated by: