Database Reference
In-Depth Information
How to do it...
In this recipe, we will create a chart report that shows all the events by event level from the
previous month. Perform the following steps:
1. Start Report Builder (use the Report Manager website or standalone option).
2. Under
New Report
, select
Chart Wizard
.
3. Select
Create a dataset
and click on
Next
.
4. Select the
OperationsManagerDW
data source connection. If it is not in the
list, click on
Browse…
to browse to the data source in SQL Server Reporting Ser-
vices. Click on
Next
.
5. If prompted for data source credentials, select
Use the current Windows user
and
then click on
OK
.
6. Click on
Edit as Text
and type the following SQL query in the query window:
SELECT
CONVERT(varchar(7), E.DateTime, 126) AS MonthRaised,
ELe.EventLevelTitle,
COUNT(*) AS EventCount
FROM
Event.vEvent E
LEFT OUTER JOIN vEventCategory ECa ON
E.EventCategoryRowId = ECa.EventCategoryId
LEFT OUTER JOIN vEventChannel ECh ON
E.EventChannelRowId = ECh.EventChannelRowId
LEFT OUTER JOIN vEventLevel ELe ON
E.EventLevelId = ELe.EventLevelId
LEFT OUTER JOIN vEventLoggingComputer ELc ON
E.LoggingComputerRowId =
ELc.EventLoggingComputerRowId
LEFT OUTER JOIN vEventPublisher EPu ON
E.EventPublisherRowId = EPu.EventPublisherRowId
LEFT OUTER JOIN vEventUserName EUn ON
E.UserNameRowId = EUn.EventUserNameRowId
GROUP BY
CONVERT(varchar(7), E.DateTime, 126),
ELe.EventLevelTitle
ORDER BY
1
