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
  • 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]

  • ,

    Any idea on this to get the availability

  • SELECT
        SA.Node.Caption AS [Server]
        ,SA.InstanceName AS [SQL Instance]
        ,SA.Databases.DisplayName AS [Database]
        ,SA.Databases.StatusDescription AS [Status]
        ,B.Availability AS [AVG Availability last 7 days]
    FROM Orion.APM.SqlServerApplication AS SA
    INNER JOIN (
    SELECT
        A.DatabaseID
        ,AVG(A.Av*100) AS Availability
    --    ,DateTrunc('day',A.TimeStamp) as day  --Enable to get availability per day
    FROM(
    SELECT
        DS.DatabaseID AS [DatabaseID]
        ,DS.TimeStamp
        ,CASE
            WHEN DS.Status>5 THEN 0
            ELSE 1
        END AS Av
        
    FROM Orion.APM.SqlDatabaseStatus AS DS
    WHERE 
        DS.TimeStamp>ADDDAY(-7,GETUTCDATE())  --Set days back to calculate avg availability
    ) AS A
    GROUP BY A.DatabaseID --, DateTrunc('day',A.TimeStamp)  --Enable to get availability per day
    ) AS B ON SA.databases.DatabaseID=B.databaseid

    Hi , think above code will give you what you need. 

  • Perfect report working like a charm ..:) 

    Is that possible to remove unplug /offline thing also the time stamp filed needed ..

    Like daily ,weekly ,monthly ..how can i modify please provide ..

    This will help lot of people i say so many looking for this report ..but no luck

      

  • Remove "unplugged/Offline": Just add "WHERE SA.Databases.status!=10" at the end,making it look like this:

    
    
    SELECT
        SA.Node.Caption AS [Server]
        ,SA.InstanceName AS [SQL Instance]
        ,SA.Databases.DisplayName AS [Database]
        ,SA.Databases.StatusDescription AS [Status]
        ,B.Availability AS [AVG Availability last 7 days]
    FROM Orion.APM.SqlServerApplication AS SA
    INNER JOIN (
    SELECT
        A.DatabaseID
        ,AVG(A.Av*100) AS Availability
    --    ,DateTrunc('day',A.TimeStamp) as day  --Enable to get availability per day
    FROM(
    SELECT
        DS.DatabaseID AS [DatabaseID]
        ,DS.TimeStamp
        ,CASE
            WHEN DS.Status>5 THEN 0
            ELSE 1
        END AS Av
        
    FROM Orion.APM.SqlDatabaseStatus AS DS
    WHERE 
        DS.TimeStamp>ADDDAY(-7,GETUTCDATE())  --Set days back to calculate avg availability
    ) AS A
    GROUP BY A.DatabaseID --, DateTrunc('day',A.TimeStamp)  --Enable to get availability per day
    ) AS B ON SA.databases.DatabaseID=B.databaseid
    WHERE SA.Databases.status!=10

    What do you mean that you need the timestamp field? The availability is the average for the last 7 days. Not for any specific day or so.

    BUT if you want the availability Per day/per database you have it here:

    SELECT
        SA.Node.Caption AS [Server]
        ,SA.InstanceName AS [SQL Instance]
        ,SA.Databases.DisplayName AS [Database]
        ,SA.Databases.StatusDescription AS [Status]
        ,B.Availability AS [AVG Availability per day]
        ,B.[day] as [day] --Enable to get availability per day
    FROM Orion.APM.SqlServerApplication AS SA
    INNER JOIN (
    SELECT
        A.DatabaseID
        ,AVG(A.Av*100) AS Availability
        ,DateTrunc('day',A.TimeStamp) as day  --Enable to get availability per day
    FROM(
    SELECT
        DS.DatabaseID AS [DatabaseID]
        ,DS.TimeStamp
        ,CASE
            WHEN DS.Status>5 THEN 0
            ELSE 1
        END AS Av
        
    FROM Orion.APM.SqlDatabaseStatus AS DS
    WHERE 
        DS.TimeStamp>ADDDAY(-7,GETUTCDATE())  --Set days back to calculate avg availability
    ) AS A
    GROUP BY A.DatabaseID , DateTrunc('day',A.TimeStamp)  --Enable to get availability per day
    ) AS B ON SA.databases.DatabaseID=B.databaseid
    WHERE SA.Databases.status!=10

  • ,

    This is great !!!!

    when i generate daily if i mention -1 am getting yesterday and today data instead i need only yesterday 24 hours data ..

    And if i run last month in time stamp filed it should shows like may 22 instead all dates (how can i get last month complete data )

    in our out of box we have option yesterday it will give yesterdays time stamp alone 24 hours data ..

    Also if i select last month it will give the time stamp like May 22 in time stamp filed 

    just need above 2 format to finalize my report ...

  • Adjust below line in the query to get data from the dates you are after:

    DS.TimeStamp>ADDDAY(-7,GETUTCDATE())  --Set days back to calculate avg availability

    Currently its "from now and 7 days back". Including the hours that has passed today. You can also hardcode date and time there if you want. Example:

    DS.Timestamp>'2022-06-01 00:00' AND DS.TimeStamp<'2022-06-10 00:00'

  • Thanks you so much for your support and time ..

    Its work great .....

    once again thank for your support ..

  •  ,

    One more help ..The above condition i need an alert to be generated ..when the database up status change to down i need an alert bcs the availability shows less than 100 % ..Then there should be an alert if any instance down it should trigger alert  what condition need to pass in trigger condition any idea 

  • To alert if a database goes either "Down" or goes "critical", create an alert that looks something like this:

    Clicking on the arrow that I circled above you can then click on "Show SWQL". There you can verify that you got the same query as I got:

    SELECT E0.[Uri], E0.[DisplayName]
    FROM Orion.APM.SqlDatabase AS E0
    WHERE ( ( E0.[SqlDatabaseAlert].[Status] = '2' ) OR ( E0.[SqlDatabaseAlert].[Status] = '14' ) )

Reply Children
No Data