Why is my custom report displaying deleted objects or groups?

Here’s a fairly common stumbling block when creating custom reports, so I wanted to share this quick tip.

Discovered inventory will stay in the data warehouse for 400 days, by default, at which time these items will be deleted.  In the interim, these objects will stick around and often times the report author doesn’t realize they need to filter out objects that have been marked for deletion.  You'll need to make sure you're not selecting objects that are marked for deletion when creating your report dataset.

An example is to look at ManagedEntityManagementGroup table, ToDateTime column.  If this column is NULL, the object is valid (current).  If it has a date-time entry, the object is marked for deletion and you should filter these records from your dataset (unless you actually want to see deleted items in your report).  You can join on the RowId fields to include a filter in a WHERE clause (where ToDateTime is null).

Example: rows 1, 14 and 15 are marked for deletion – they are deleted from SCOM’s perspective.