AppInsight for SQL (Summary)

Hi All,

This script will display just the metrics you are concerned with for AppInsight for SQL. It can be altered for any other templates.

select DISTINCT
n.Caption as [Node]

,tmp1.data as [Free Stalls]
,round(tmp2.data,2) as [Pages Read/s]
,round(tmp3.data,2) as [Pages Write/s]
,round(tmp4.data,2) as [Page Life Expectancy]

from Nodes n
join APM_Application a on a.NodeID=n.NodeID
join APM_Component c on c.ApplicationID=a.ID

left join
(SELECT DISTINCT n.caption, n.nodeid, a.Name as [AppName]
,c.Name as [CompName], c.ID, c.ApplicationID, cs.ComponentStatisticData as [data]
from Nodes n
join APM_Application a on a.NodeID=n.NodeID
join APM_Component c on c.ApplicationID=a.ID
join APM_CurrentStatistics cs on cs.ComponentID=c.ID
where c.name like 'Free list stalls/sec') tmp1 on tmp1.applicationid=a.ID and tmp1.nodeid=n.nodeid

left join
(SELECT DISTINCT n.caption, n.nodeid, a.Name as [AppName]
,c.Name as [CompName], c.ID, c.ApplicationID, cs.ComponentStatisticData as [data]
from Nodes n
join APM_Application a on a.NodeID=n.NodeID
join APM_Component c on c.ApplicationID=a.ID
join APM_CurrentStatistics cs on cs.ComponentID=c.ID
where c.name like 'Page Reads/sec') tmp2 on tmp2.applicationid=a.ID and tmp2.nodeid=n.nodeid

left join
(SELECT DISTINCT n.caption, n.nodeid, a.Name as [AppName]
,c.Name as [CompName], c.ID, c.ApplicationID, cs.ComponentStatisticData as [data]
from Nodes n
join APM_Application a on a.NodeID=n.NodeID
join APM_Component c on c.ApplicationID=a.ID
join APM_CurrentStatistics cs on cs.ComponentID=c.ID
where c.name like 'Page Life Expectancy') tmp4 on tmp4.applicationid=a.ID and tmp4.nodeid=n.nodeid

where a.TemplateID in (SELECT distinct a.TemplateID from APM_Application a
left join APM_ApplicationTemplate t on t.ID=a.TemplateID where t.CustomApplicationType is not null and t.name like '%sql%')

A prettier one can be written in SWQL with links.

Thank you,

Amit

Loop1 Systems

Find more posts tagged with

template

sam

sql

Comments

spoofffed

Great query, Amit!! Do you have the SWQL code too?

cscoengineer

Yes I do

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

axg4437

Hi cscoengineer

Am using Appinsight sql for monitor the sql servers i need instance availability report the out of box database availability not giving the instance name .

Any sql query to achieve this SQL availability report