SCCM Query find All Server count with OS Name

SCCM Query find All Server count with OS Name

• Recently I was asked to count of servers by OS
• It was quite Challenging as we need to understand the SCCM Structure very well to do the SQL Queries.
• Following are the Queries to fin the Server by OS Name and the count of Servers

SELECT VR.operatingSystem0,COUNT(VR.operatingSystem0) FROM v_R_System AS VR

WHERE VR.operatingSystem0 LIKE '%Windows Server%'

GROUP BY VR.operatingSystem0

SQL query to get all Distributed Applications created by users in SCOM

SQL query to get all Distributed Applications created by users in SCOM

• Distributed Application are stored in SCOM in ManagedEntity Table
• All Distributed Applications created by users start with "Service_" in the FullName column
• If you want to query all your distributed application that are created for reporting purpose use the below query

***********

SELECT * FROM [OperationsManagerDB].[ManagedEntity] where (FullName like 'Service_%' AND Name is NULL)

*****************

SQL Query to get the Maintenance Mode History for Particular Server SCOM 2016

SQL Query to get the Maintenance Mode History for Particular Server SCOM 2016

• Many time you get questions from Management whether the particular server was in Maintenance mode and how long.
• It has always been a challenge for SCOM engineer to pull the Data, as there is now easy way to pull this information.
• You can use the below SQL query to get that information.
• What this query does is that it queries the "OperationsManagerDW" database in "ManagedEntity"  table "MaintenanceMode" Table and "MaintenacenModeHistory" table where the ManagetEntity Row ID matches Maintenance mode Manage entity RowID and Maintenance Mode RowId matches Maintenance Mode Manage Entity Row ID.
• Replace the "Servername" with server name you wan the report for and you will get the Maintenance mode History report for the server

********************
USE OperationsManagerDW
SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.*
FROM ManagedEntity WITH (NOLOCK) 
INNER JOIN
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId 
INNER JOIN
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId
where DisplayName Like '%servername%'

*******************

SQL query to find out if your SCOM 2012 R2 Grooming jobs are succeeding

SQL query to find out if your SCOM 2012 R2 Grooming jobs are succeeding:

• Grooming jobs are very important in SCOM.
• If grooming fails then your old data is not removed as per your grooming settings.
• This will cause your database to fillup and also cause slowness.
• You need to regularly monitor the Grooming jobs are will face a outage.
• Unfortunately, SCOM only raise alert if the grooming fails for atleast 6 days.
• So by this time depending on your environment there could be lot of issues  and grooming could never be completd
• So i came up with SQL query which you can use to daily check after 6:00 AM server time
• "SELECT * FROM INTERNAJOBHISTORY ORDER BY INTERALJOBHISTORYID DESC"
• Run the above query and check if the grooming jobs succeeds. You should do this daily to avoid our OperationsManager Database gets filled.