SCOM 2019: require sql query

Question

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.

Regards,
Kumar

Answer 1

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

Answer 2

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.