SQL query to extract SCOM Agent attributes

Question

We have a requirement to extract the below attributes of a SCOM agent and output to CSV file. Please assist in providing the SQL query to use to query the OperationsManager DB.

Name = Device Hostname

Class = Windows or Unix

Vendor = ‘BLOGGS – EUCS’

Serial Number =

Asset tag = If available

FITS Code = Needed

Manufacturer = Needed

Model ID = Manufacturer + Model

Status = In Use | In Maintenance (offline)

Comments = Free Text

Description = Free Text (value comments)

FQDN = Where possible

Virtual = Needed

Environment = Production

Operating = device OS

Much appreciated. Thanks.

Answer 1

Hi,

Below is an example of a SQL query that mines a specified attribute from an OperationsManager database, then we can join the tables with the master key BaseManagedEntityId and output them to a CSV file.

select * from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like '%Maintenance%'

select * from INFORMATION_SCHEMA.COLUMNS
Where COLUMN_NAME like '%Maintenance%'


select DisplayName,PrincipalName,* from MTV_Microsoft$Windows$OperatingSystem

select IsVirtualMachine,* from MT_Computer

select * from MT_Microsoft$Unix$Computer

select * from MaintenanceMode