Category Archives: SQL Queries

Cleaning out unwanted event collection data from ops database

Most large SCOM management groups have lots of management packs and lots of monitored computers. Invariably this means that there are lots of event collection rules collecting event data and storing it in the Ops database and the datawarehouse. It … Continue reading

Posted in SQL Queries | Tagged , , , , , , , , , , | Leave a comment

SQL query to display database sizes

You can ran this SQL query against the Ops DB and the Ops DW to display database size information……. select ‘server’ = @@servername , ‘total size in megabytes’= convert(decimal(10,2),(sum(size * 8.00) / 1024.00 )) , ‘total size in gigabytes’ = … Continue reading

Posted in SQL Queries | Tagged , , , , , , , , , | Leave a comment

Cleaning stale data out of datawarehouse database

Prior to SCOM 2012 R2 UR7, there was an issue whereby SCOM did not groom out old data from the Event Parameter and Event Rule tables in the DW database.This will show up as these tables growing quite large, especially … Continue reading

Posted in SQL Queries | Tagged , , , , , , , , , , , , , , , , , , , , , , , , | Leave a comment

Missing UNIX/Linux Run As Accounts

Came across this scenario today whereby my UNIX/Linux Run As accounts were missing from my console view, but the monitoring was working as expected for my linux servers After some investigation it appeared that the reason for this was that … Continue reading

Posted in Misc Stuff, SQL Queries | Tagged , , , , , , , , , , , , , , , , | Leave a comment

Maintenance mode history SQL query

Here is a good SQL query that you can run against your SCOM datawarehouse database to get the maintenance mode history for a particular computer:- Replace ‘%myserver%’ with the hostname of your server USE OperationsManagerDW SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.* FROM ManagedEntity … Continue reading

Posted in SQL Queries | Tagged , , , , , , | Leave a comment

Datawarehouse Database Cleanup SQL query

IMPORTANT: Always perform a FULL Backup of the database before doing anything to it !!! This article applies to SCOM 2007 and SCOM 2012 Somtimes you may have event storms where you end up having old entries in the Data … Continue reading

Posted in SQL Queries | Tagged , , , , , , , , , , , , , | 1 Comment

SQL Query to report on how many times a particular alert has triggered

Here is a very useful SQL Query that you can use to determine how many times a particular alert has fired, when it fired and what host it fired on. Open up Microsoft SQL Server Management Studio on your Datawarehouse … Continue reading

Posted in SQL Queries | Tagged , , , , , , , , , , , | Leave a comment

Check last user to modify SCOM user roles

Attached is a simple guide that shows you how to run a SQL query against your Operations Database to see which one of your SCOM Admins made the most recent change to the SCOM user roles. Check last user to modify … Continue reading

Posted in Misc Stuff, SQL Queries | Tagged , , , , , , , , , | Leave a comment

Addressing database bloat

Database grooming is a well documented topic when it comes to working with SCOM. Only old state change data for monitors that are currently in a HEALTHY state, AT THE TIME that grooming runs are groomed out.  OLD state change events for monitors that … Continue reading

Posted in SQL Queries | Tagged , , , , , , , | Leave a comment

SQL queries for Operations Database

NOTE:-This post covers SQL queries for the Operations Manager Database not the Datawarehouse. A good and quick way to get information about your SCOM environment is to run SQL queries against the databases. Connect to SQL Server Management Studio on your … Continue reading

Posted in SQL Queries | Tagged , | Leave a comment