Version 1

    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.

     

     

    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