SCOM 2019: require sql query

kumaravelu 106 Reputation points
2021-07-09T10:01:58.13+00:00

Hi All,

We are running SCOM 2019 and SQL 2019 we require to pull the report as shown in the screen shot.

SQL server has multiple databases (that is quite normal), that every database has its own row (like above, you see three different databases under the same EFG instance for server XYZ123.
In case that same SQL server has also multiple instances (which is also quite common), the list needs to be sorted like Server -> Instances -> Databases (so listing all databases per instance per server).

is it possible to get this information by using SQL query.

113322-sqledition.jpg

Regards,
Kumar

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,409 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. System Center guy 686 Reputation points
    2021-07-09T17:31:21.56+00:00

    The required SQL statement as

    use [OperationsManager]
    go
    select [server],[instance],[version],[edition],[Database],[Size (MB)]
    from
    (select
    dbe.NetbiosComputerName_2D8779BA_83A0_5B9C_39D1_4ACB8A542D1D [SERVER],
    dbe.InstanceName_2CC762EA_9008_8DED_CEEA_C0A731372EA8 [Instance],
    dbe.Version_FC1E2814_6896_5FFD_0D0C_0AA8F803FF07 [Version],
    dbe.Edition_0EBA9421_D2D6_9ED0_19A0_EF1D330C7E11 [Edition],
    db.DisplayName [Database],
    pcv.ManagedEntityId,SampleValue [Size (MB)], TimeSampled ,ROW_NUMBER() over (partition by pcv.managedentityid order by timesampled desc) rn
    from BaseManagedEntity bme
    inner join ManagedType mt
    on mt.ManagedTypeId=bme.BaseManagedTypeId
    inner join MT_Microsoft$SQLServer$Windows$DBEngine dbe
    on dbe.BaseManagedEntityId=bme.BaseManagedEntityId
    inner join
    ( select db.BaseManagedEntityId,bme.TopLevelHostEntityId,db.DisplayName from MT_Microsoft$SQLServer$Windows$Database db
    inner join BaseManagedEntity bme
    on db.BaseManagedEntityId=bme.BaseManagedEntityId) db
    on db.TopLevelHostEntityId=bme.BaseManagedEntityId
    inner join PerformanceCounterView pcv
    on pcv.ManagedEntityId=db.BaseManagedEntityId
    inner join PerformanceDataAllView pdv
    on pdv.PerformanceSourceInternalId=pcv.PerformanceSourceInternalId

    where mt.TypeName='Microsoft.SQLServer.Windows.DBEngine'
    --and pcv.ManagedEntityId='9DF7FCA2-42EF-ADA3-786F-12945F3B89E7'
    and pcv.CounterName='DB Allocated Space (MB)'
    ) dbinfo
    where rn=1

    Roger

    0 comments No comments

  2. XinGuo-MSFT 13,856 Reputation points
    2021-07-12T02:36:09.79+00:00

    Hi,

    Based on my understanding, the standard SQL queries are based on each instance.

    We need to write a script like this to collect these information.

    DECLARE @GetInstances TABLE
    ( Value nvarchar(100),
     InstanceNames nvarchar(100),
     Data nvarchar(100))
    
    Insert into @GetInstances
    EXECUTE xp_regread
      @rootkey = 'HKEY_LOCAL_MACHINE',
      @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
      @value_name = 'InstalledInstances'
    
    Select InstanceNames from @GetInstances 
    

    How can I determine installed SQL Server instances and their versions?

    Note:

    • It is easier to collect this table using Powershell.
      • Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice.
    0 comments No comments