AppInsight for SQL Database Status (SWQL)

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]

Seashore,

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  

Seashore,

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 axg4437, think above code will give you what you need. 

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 axg4437, think above code will give you what you need. 

Seashore

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

Seashore ,

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'

Seashore

Thanks you so much for your support and time ..

Its work great .....

once again thank for your support ..

Seashore ,

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 

axg4437 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' ) )

Thank you

Seashore ,

I have some custom property in node eg : categories =gold i need to filter this report using this node custom property can you ple help on this 

Seashore :- first of all very perfect report so thanks for that but i need to sort this report via custom property so can you please add the script line for custom properties.