SystemCenter

All about System Center Articles and Tutorials

Maintenance Mode History in SCOM

Posted by on Mar 4, 2017 in SCOM, SCOM 2012, SCOM 2016, SQL

Maintenance Mode History in SCOM

Recently we got a question from our server support team whether it’s possible to get the Maintenance Mode history of a particular server in SCOM or there’s any built in report available in SCOM to fetch this information.

I believe sometimes you would also be getting the same question from management whether the specific server was in Maintenance Mode during specific time interval as SCOM doesn’t have any view or native report which allows to quickly view the Maintenance Mode history of a specific server.

However, we can easily get the Maintenance Mode history of a specific server through OperationsManagerDW database. Below is query through which we can get Maintenance Mode history of a specific server, we just need to specify server and the date range:

use OperationsManagerDW
SELECT ManagedEntity.DisplayName, DBLastModifiedDateTime, ScheduledEndDateTime, UserId, MaintenanceModeHistory.PlannedMaintenanceInd, MaintenanceModeHistory.ReasonCode, MaintenanceModeHistory.Comment
FROM ManagedEntity WITH (NOLOCK)
INNER JOIN
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId
INNER JOIN
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId
WHERE ManagedEntity.DisplayName = ‘scmvptest.test.com’ AND ScheduledEndDateTime BETWEEN ‘2017-02-01 00:00:00’ AND ‘2017-02-23 13:00:00’

1

It’s simple however only SCOM administrator or the admins having access on SCOM DW can fetch this information.

So, better will be if we create a custom report to get Maintenance Mode history using same query and the server owners or management can also fetch this information for their respective servers.

Let’s create the report…

Below is the query we are going to use for our report:

SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.DBLastModifiedDateTime,
MaintenanceModeHistory.ScheduledEndDateTime,  MaintenanceModeHistory.UserId,
MaintenanceModeHistory.PlannedMaintenanceInd,
MaintenanceModeHistory.ReasonCode, MaintenanceModeHistory.Comment
FROM ManagedEntity WITH (NOLOCK)
INNER JOIN
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId
INNER JOIN
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId
WHERE ManagedEntity.DisplayName = @DName AND MaintenanceModeHistory.ScheduledEndDateTime BETWEEN @DTfrom AND @DTto

1: Login on to Report Server.

2: Open Report URL and click on Report Builder.

2

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.

3

After closing Getting Started wizard, you will get Home tab of the Report Builder interface.

4

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.

5

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.

6

9: Click on Test Connection button to check if connection is created successfully.

10: Click OK to close the Data Source Properties window.

7

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.

8

Now we need the fields which should be displayed in our report.

16: Click on Insert tab and select Table Wizard.

9

17: Select the Dataset which we create and click Next to continue.

10

18: Select the fields which you want to show in your report and drag it to Values box.

11

After dragging the required fields under Values box, you may get few fields starting from Sum.

19: We don’t need these fields, so remove all such fields by clicking dropdown option against those fields and unchecking the Sum option.

12

20: Click Next to continue.

13

21: Layout preview will appear, click Next to continue.

14

22: Select the Style and click on Finish button.

15

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.

16

Now it’s time to do some cosmetic changes.

23: Right click @ DName parameter and select Parameter Properties.

24: Under Prompt field, type Provide Server Name.

This prompt will be displayed in your report where we will provide the exact alert name for which we need the alert history.

25: Click OK to close the Parameter Properties box.

17

Same way we need to make changes for other parameters.

Since rest two fields are Date parameters, we need to make below changes.

26: Right click @ DTfrom parameter and select Parameter Properties.

27: Under Prompt option, type Date From.

28: Under Data type, select Date/Time.

Same way, make the changes for @ DTto parameter.

18

Now it’s time to test the report before we save it.

29: Click on Run button.

19

Provide Server Name, Date duration and click on View Report button.

It will show you the same results which we were getting by running the SQL query in OperationsMangerDW database.

20

Finally it’s time to save the report, so we can run the same in SCOM.

30: Provide your report a suitable name and click on Save button.

The report file will have .RDL extension.

21

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.

22

4: Provide the Server Name, Dates duration and click Run button.

You will get your desired report.

23

That’s it.

Hope this helps.

Submit a Comment

Your email address will not be published. Required fields are marked *