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]

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. 

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'

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.

SELECT
SA.Node.Caption AS [Server]
,SA.InstanceName AS [SQL Instance]
,SA.Databases.DisplayName AS [Database]
,SA.Node.CustomProperties.DeviceClassification AS [Classification]
,SA.Node.MachineType AS [Machine Type]
,SA.Databases.StatusDescription AS [Status]
,SA.Node.CustomProperties.Device_Scope AS [Scope]
,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< DATETRUNC('day',GETUTCDATE()) --Set days back to calculate avg availability
and DS.TimeStamp > addday(-1,DATETRUNC('day',GETUTCDATE()))
) 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 AND SA.Node.CustomProperties.DeviceClassification = 'Gold'
AND SA.Node.MachineType != 'Windows 2003 Server' AND SA.Node.MachineType != 'Windows 2008 Server' AND SA.Node.MachineType != 'Windows 2008 R2 Server'

see last 3 lines wheer yiu can add your custom property

Appinsight SQL when ever the server scheduled for maintenance and it get manage again the polling sql instance went to unknown state for 10 minutes and getting up .

This report shows less than 100 % for unknown state .

Any possible query to ignore this unknown state in this availability report  event the instance is unknown it should give us 100%

Appinsight SQL when ever the server scheduled for maintenance and it get manage again the polling sql instance went to unknown state for 10 minutes and getting up .

This report shows less than 100 % for unknown state .

Any possible query to ignore this unknown state in this availability report  event the instance is unknown it should give us 100%