SCOM SQL Agnostic MP - Availability Groups

Dashboard Server
, , ,
1

For this new SQL MP there is a way to create groups based on SQL Version for the Database Replica and Availability Replica, but i can not see a way to link Availability Groups back to the SQL Version. Is there a way?

the SQL Team like to look at environments by SQL Version, did not have to worry about this in the Legacy MP since they were version based.

2

I realise this is a year late, but this is the tsql I use in my dashboard for SQL instance versions. It doesn’t show if the instance has databases that are a member of a AG but if your DBA team are just looking to inventory DB engine instance version this might do?

SELECT [MachineName_EA844CA4_B2CD_69E3_D8A9_C2E4489C53C7] AS 'Server Name'
    , [InstanceName_2CC762EA_9008_8DED_CEEA_C0A731372EA8] AS 'Instance Name'
    , [Account_DD780DD9_3E2D_48ED_D895_B1B25E8BA63B] AS 'DB Service Account'
    , [AuthenticationMode_968A62ED_B22A_EC1C_207F_020D7DAB9A52] AS 'Authentication Mode'
    , [Edition_0EBA9421_D2D6_9ED0_19A0_EF1D330C7E11] AS 'SQL Edition'
    , CASE
        WHEN [Version_FC1E2814_6896_5FFD_0D0C_0AA8F803FF07] LIKE '10.0%'
            THEN '2008'
        WHEN [Version_FC1E2814_6896_5FFD_0D0C_0AA8F803FF07] LIKE '10.5%'
            THEN '2008 R2'
        WHEN [Version_FC1E2814_6896_5FFD_0D0C_0AA8F803FF07] LIKE '11%'
            THEN '2012'
        WHEN [Version_FC1E2814_6896_5FFD_0D0C_0AA8F803FF07] LIKE '12%'
            THEN '2014'
        WHEN [Version_FC1E2814_6896_5FFD_0D0C_0AA8F803FF07] LIKE '13%'
            THEN '2016'
        WHEN [Version_FC1E2814_6896_5FFD_0D0C_0AA8F803FF07] LIKE '14%'
            THEN '2017'
        WHEN [Version_FC1E2814_6896_5FFD_0D0C_0AA8F803FF07] LIKE '15%'
            THEN '2019'
    END AS 'SQL Version'
    , [Version_FC1E2814_6896_5FFD_0D0C_0AA8F803FF07] AS 'SQL Version Number'
    , [ServicePackVersion_D2A3557C_0895_BDC6_30EE_0B2960240A46] AS 'ServicePack Version Number'
FROM [OperationsManager].[dbo].[MTV_Microsoft$SQLServer$Windows$DBEngine]
ORDER BY 'SQL Version';

edit: i just realised those field names might be unique to my env given the fact that it has a guid suffix, if this query doesnt work OOB try a select * and find the right field names for your env.