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

  • Hello Team,

    Do you have update on this post. i was looking database status of the instance.

    Can you please share the feedback if you have answer

    Thanks

    Suresh

  • Nice idéa!

    I had several databases "unplugged/offline" and there is no icon for that so I had to add a case part for that:

    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]
    ,CASE WHEN
        sqldb.StatusDescription='Unplugged / Offline' THEN '/Orion/images/StatusIcons/Small-Unknown.gif'
        ELSE concat('/Orion/images/StatusIcons/Small-',sqldb.StatusDescription,'.gif') 
    END 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

    And I also wanted a search function, the best part of a custom query. To be able to search for any server, instance or database name! 

    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]
    ,CASE WHEN
        sqldb.StatusDescription='Unplugged / Offline' THEN '/Orion/images/StatusIcons/Small-Unknown.gif'
        ELSE concat('/Orion/images/StatusIcons/Small-',sqldb.StatusDescription,'.gif') 
    END 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
    WHERE
        a.node.Caption LIKE '%${SEARCH_STRING}%' OR 
        a.name LIKE '%${SEARCH_STRING}%' OR
        sqldb.name LIKE '%${SEARCH_STRING}%'
    order by a.node.Caption, a.name, sqldb.name