Creating SCOM Reports through Report Builder
There are scenarios wherein people ask us to provide us few reports however we don’t have those reports configured in SCOM.
We do have the SQL queries through which we can fetch the data from SQL however we can’t give the query to everyone so they can generate the report from SCOM DW whenever they need it.
So, in such scenarios it’s good to create a report in SCOM using those SQL queries and provide them access on same so the team can simply login to SCOM console and can generate the report whenever they require.
Many people think that Report authoring in SCOM is quite a difficult task. In fact, it is not.
So, let’s start..
I have below query which will fetch the history of a specific alert. I have mentioned this query in my previous post SPECIFIC ALERT HISTORY IN SCOM and made few changes here so can use the same in our report.
vManagedEntity.Path FROM Alert.vAlertResolutionState AS ars INNER JOIN
Alert.vAlertDetail as adt on ars.AlertGuid=adt.AlertGuid INNER JOIN
Alert.vAlert AS alt ON ars.AlertGuid=alt.AlertGuid INNER JOIN
vManagedEntity ON alt.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId
alt.RaisedDateTime between @dtFrom and @dtTo
Below is the process to create a report through Report Builder:
1: Login on to Report Server.
2: Open Report URL and click on Report Builder.
Getting Started box will appear. The dialog box provides several options for creating a new report or opening an existing one. We won’t be using this Getting Started wizard for creating our report.
3: Close the box by clicking the red Close button in top-right corner.
After closing Getting Started wizard, you will get Home tab of the Report Builder interface.
Connect to Data Sources
4: Right click Data Sources and click Add Data Source…
5: Select Use a connection embedded in my report option and click on Build… button.
6: Under Server name field, provide SQL Server/ SQL Cluster name which holds SCOM DW.
7: Select database name as OperationsManagerDW
8: Click OK to close the connection wizard.
9: Click on Test Connection button to check if connection is created successfully.
10: Click OK to close the Data Source Properties window.
Now we need to add Datasets to our report. Here we need to provide the SQL query which will fetch the data for our report.
11: Right click Datasets and click Add Dataset.
12: Select option Use a dataset embedded in my report.
13: Select the Data source which we created.
14: Select Text as Query type and past the SQL query here.
The SQL query is the core of this report, it will fetch the data from SCOM DW and present the same in our report.
15: Click OK to close the Dataset Properties.
Now we need the fields which should be displayed in our report.
16: Click on Insert tab and select Table Wizard.
17: Select the Dataset which we create and click Next to continue.
18: Select the fields which you want to show in your report and drag it to Values box.
After dragging the required fields under Values box, you may get few fields starting from Sum.
We don’t need these fields, so remove all such fields by clicking dropdown option against those fields and unchecking the Sum option.
19: Click Next to continue.
20: Layout preview will appear, click Next to continue.
21: Select the Style and click on Finish button.
The Report Builder Interface will show you all the created Data Sources, Datasets, Parameters used and the Fields which we want to show in our report.
Now it’s time to do some cosmetic changes.
22: Right click @ alert parameter and select Parameter Properties.
23: Under Prompt field, type Provide Alert Name.
This prompt will be displayed in your report where we will provide the exact alert name for which we need the alert history.
24: Click OK to close the Parameter Properties box.
Same way we need to make changes for other parameters.
Since rest two fields are Date parameters, we need to make below changes.
25: Right click @ dtFrom parameter and select Parameter Properties.
26: Under Prompt option, type Date From.
27: Under Data type, select Date/Time.
Same way, make the changes for @ dtTo parameter.
Now it’s time to test the report before we save it.
28: Click on Run button.
Provide Alert name, Date duration and click on View Report button.
Bingo!!! the report works as expected.
Finally it’s time to save the report, so we can run the same in SCOM.
29: Provide your report a suitable name and click on Save button.
The report file will have .RDL extension.
Once the report is saved, it’s time to view the same in SCOM.
1: Open SCOM Console.
2: Click on Reporting option, you will get the report under Authored Reports.
3: Double click the report we created.
4: Provide Alert Name, Dates duration and click Run button.
You will get your desired report.
The same way we can create reports in SCOM through Report Builder and make things simple.
Hope you will like it.