SCOM: Reporting queries for performance counters

When you want to start making reports in your SCOM environment, you'd first have to learn how the data is stored in the SCOM DataWarehouse and which tables, views or stored procedure you can use.

I use SQL Server Business Intelligence Development Studio. You can use Report Builder, but it is less featured.
After you create a Shared DataSource and a new report, you can create your query for your Data Set.

Below is a query to get performance data from the aggregated view 'PerfDaily' for a computer from counter name 'Working Set'.

-- BEGIN QUERY
SELECT Perf.vPerfDaily.DateTime, Perf.vPerfDaily.AverageValue, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName,
vManagedEntity.FullName, vPerformanceRuleInstance.InstanceName
FROM vPerformanceRuleInstance INNER JOIN
Perf.vPerfDaily ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE (vPerformanceRule.CounterName = 'Working Set') AND (vManagedEntity.Path = @ComputerName)
ORDER BY Perf.vPerfDaily.DateTime

--END QUERY

@ComputerName is a parameter which can be entered by a user. Another option is to fill this value with server names based on another query. Like "give me all servers from a specific group".

Such a query could be:

-- BEGIN QUERY
SELECT dbo.vManagedEntity.Name
FROM dbo.vManagedEntity INNER JOIN
dbo.vRelationship On dbo.vManagedEntity.ManagedEntityRowId = dbo.vRelationship.TargetManagedEntityRowId INNER JOIN
dbo.vManagedEntity As CompGroup On dbo.vRelationship.SourcemanagedEntityRowId = CompGroup.ManagedEntityRowId
WHERE CompGroup.DisplayName = 'MyServers'
-- END QUERY