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
  • Am using Appinsight sql for monitor the sql servers i need sql instance availability report the out of box database availability not giving the instance name .

    Any sql query to achieve this  SQL availability report will be great

  • Hi

    How about below SWQL (SolarWinds Query Language) query? Can be used as a datasource in a report etc. Gives you availability for last 7 days for each instance using AppInsight for SQL:

    SELECT 
        S.Node.Caption AS [ServerName]
        ,S.InstanceName
        ,S.ProductVersion
        ,ROUND(AVG(S.ApplicationStatus.PercentAvailability),1) AS [Availability]
        ,DateTrunc('day',S.ApplicationStatus.TimeStamp) as day
    FROM Orion.APM.SqlServerApplication AS S
    WHERE S.ApplicationStatus.TimeStamp>AddDay(-7,Getutcdate())
    GROUP BY 
        S.Node.Caption,S.InstanceName, S.ProductVersion, DateTrunc('Day',S.ApplicationStatus.TimeStamp)
    ORDER by 
        S.Node.Caption, S.InstanceName,[Day]

  • ,

    Thanks for the reply ..

    Am getting the instance name as below mssqlserver instead of sql instance name .Also the availability is depends on other Appinsight components as well ..i just need instance availability alone like if it goes down the availability will be less..any suggestion or idea ..

  • It is possible to bring the availability column in the below one report both are different table 

  • If it says "MSSQLSERVER" it's a default instance. Not a named instance. 

    Availability can be debated for long, available for who and with what performance and functionality. What is availability for you? What component under AppInsight for SQL do you want to measure on? Just if the service is running?

  • Do you want availability per database? Not per instance?

    In that report we might be able to add a column named for example "Availability last 7 days".

    But there we have the following statuses, which ones are considered "available"? 

  • Hi seashore  ,

    Thanks for the update i need report per database bcs if i take per instance it is considering all components under appinsight if any one component are critical or warning it impact the availability of instance but it should consider only instance up and down stats not all components .

    Either instance or per database availability without considering all components will be good   ,only the database offline or instance down should be come under availability .

    if it is possible we are good ..if required i will assign a separate component for availability but i need SAM sql component  query and report query too