AppInsight for SQL Database Status (SWQL)

There was a need to view the status of all databases monitored by AppInsight for SQL.  Here a query which will do just that:

cscoengineer_0-1585265884490.png

SELECT
a.Node.Caption as [Node]
,concat('/Orion/images/StatusIcons/Small-',a.node.StatusIcon) AS [_IconFor_Node]
,a.node.DetailsUrl as [_LinkFor_Node]
,a.name as [Instance]
,concat('/Orion/images/StatusIcons/Small-',a.StatusDescription,'.gif') AS [_IconFor_Instance]
,a.DetailsUrl as [_LinkFor_Instance]
,sqldb.Name as [dB Name]
,concat('/Orion/images/StatusIcons/Small-',sqldb.StatusDescription,'.gif') AS [_IconFor_dB Name]
,sqldb.DetailsUrl as [_LinkFor_dB Name]
from Orion.APM.SqlServerApplication sql
join Orion.APM.Application a on a.id=sql.ApplicationID
join Orion.APM.SqlDatabase sqldb on sqldb.applicationid=a.id
order by sqldb.status desc

There was also a need to unmanage a bunch of databases on a regular basis.  This can be done using SQL and a SQL job.

Update APM_SqlBbDatabase
set UnmanagedStatus=1
from APM_SqlBbDatabase sqldb
join APM_SqlBbApplication sql on sql.ApplicationID=sqldb.ApplicationID
where sql.InstanceName like 'ORION'
and sqldb.name like 'EOC'

Thank you
Amit

email: ashah@loop1.com

Loop1 Systems - SolarWinds Training and Professional Services

Parents Reply Children
No Data