Configuring Relative Dates for Any Scheduled SCOM Report

One of my favorite features of Operations Manager has always been the ability to tap into the data it collects and run reports. It's like being able to travel back in time, and examine what was happening on our servers 3, 6, or 12 months ago. To me, it is a type of Business Intelligence, and just like BI data, it should be leveraged for the benefit of the organization generating it. One of the ways reports are most useful, is through the scheduling functionality provided by SQL Reporting Services. OM reports can be scheduled to run on a regular basis (daily, weekly, monthly, quarterly) with convenient delivery right to your mail inbox, or a file share, and configured in pretty much any file format you would want.

A major challenge for Operations Manager administrators when scheduling reports, however, is that some of the reports which come with Operations Manager out of the box, or which ship with management packs, are not configured to use relative dates but rather use fixed dates when they are executed through the Administration Console. The ACS reports, as well as the Exchange 2010 reports are prime examples of this problem:

The above example shows the Unsuccessful Logon Attempts report from the Operations Manager Audit Reports (ACS) library. Note how the Start Date and End Date are configured as specific dates. Although this is convenient when running the report manually, because it just "runs" immediately for a two day period without having to configure a date range or other parameters, it presents a problem when the report is scheduled.

If you schedule the report above, after having run it, and configure it to run say on a weekly basis on Friday afternoons, the report which is generated on Friday afternoons will ALWAYS have the same starting date (March 12, 2014) and ending date (March 14, 2014). Always getting the same report, with the same data, for the same window of time, week after week does not make for a very useful report.

The problem is that when scheduling the report, the scheduling wizard does not give us a way to pick relative dates for the execution of the report. By relative, we mean dates relative to the time when the report is executed:

 

Notice that the Start Date, and End Date settings above are hard-wired to specific dates, which completely ruins the scheduling feature. Compare that with a report which does allow relative dates, like the Most Common Alerts report from the Microsoft Generic Report Library:

 

Notice that in this report, the From and To date fields are not hard-wired, and instead allow for the use of "Friday previous week." When the report is run, as scheduled, it figures out what that date corresponds to and adjusts it in the report execution accordingly.

 

So, does that mean that there is no hope for reports in the Audit Reports (ACS) library or other reports like the Exchange 2010 reports? Fortunately the answer is no. We can modify these reports to provide us with the relative date functionality. To do so, we must first open the reports using SQL Reporting Service's Report Builder. You can access Report Builder from the SQL Reporting Services web page by using the Report Builder button at the top:

 

I am using Report Builder 3.0 from SQL Server 2008R2, but the functionality should be the same in other versions:

 

Once Report Builder launches, we need to find and open the report we want to modify by clicking on the Open option, and then browsing to the report you wish to schedule in the Open Report dialog box.

 

 

After clicking the Open button, the report opens up in the design view of Report Builder, and it should look something like this:

 

The first thing we should do is to save a copy of the report, so we are working on the copy and not altering the original. Click the circle in the top left corner of the Report Builder window, and select Save As. Save the report with a new name, perhaps by adding some text to the end of the report file name indicating the report window. For example, if I intend to run this report for the last 7 days using relative dates, I might save my report with the name: "Access_Violation_-_Unsuccessful_Logon_Attempts_RelativeDates_Last_7_Days" . With the report copy saved, we can now feel free to alter it, without fear of losing the original report, which might still be useful for manual execution in the future.

Now, note that the report data is configured using the Report Data pane on the left. If you expand the Datasets tree node, you will see the data set items which are being requested from the Operations Manager database, either the Data Warehouse or ACS databases. This particular report gets data from the ACS database, about the user, IP address, computer, logon process, authentication package, etc. The Parameters folder, meanwhile, contains the report parameters which are entered by the user at run-time. Here is where our problem is, and what we need to correct.

Start by right-clicking on the StartDate item under the Parameters folder, and select Parameter Properties. Click on the Default Values tab on the right, which shows the configuration for what are the default values used for this parameter when the report is run.

Click the "fx" button on the right side which will bring up the Expression dialog box. Here we can see how the default value for the StartDate Parameter is calculated. The formula highlighted at the top obtains Today's date, and uses the AddDays() function to add -1 days, in other words, move the date backward one day, starting today.

You should also explore the functions in the function category control, bottom left, and get familiar with the Date & Time functions. Of specific interest for a future step is the DateAdd function highlighted in the middle list control above. This function can be used to add or subtract time from a given date. The beauty of this function is that it can be used to add or subtract days, weeks, months, years, you name it. There is an excellent reference for this function, with a complete list of parameters and some sample uses, in TechNet: https://technet.microsoft.com/en-us/library/ms186819.aspx. For now, simply document the expression used for the StartDate Parameter, and do the same for the EndDate Parameter.

Armed with this knowledge, let's alter the report, so that instead of taking in input from a user. Start by right clicking on the "dataSet" item in the Datasets folder, and then click on the Parameters tab. Here we see that the dataSet gets its values for StartDate and EndDate from the Parameters we explored above.

We do not want to get the dates from the Parameters, so we will remove the link between the two. To do so, click on the "fx" button next to the StartDate, and we will have an Expression dialog box similar to the one we saw before in the Parameters Properties for StartDate, but here the expression formula is different. The expression formula here links up the dataSet's configuration to the parameters the user configures at execution. We are going to remove that link, and instead hard-wire the report to run for a fixed period of time relative to the current date. For this example we will configure the report to run for the period of the LAST 7 days, prior to today. We can use the DateAdd() function to add or subtract days as I mentioned previously. We can also use the Today() function to obtain the current date. Finally, we can next one function inside the other, so that today's date will be a parameter for the execution of the DateAdd() function, like this:

I won't get into every scheduling variation, but one other useful one is for when you want to run the report for the past month. In that scenario the value would be:

Click OK to complete the configuration of the StartDate parameter, and then configure the expression for the EndDate as follows:

 

After doing so, the Parameters tab of the Dataset properties looks like this:

 

Now we can delete the two parameters of the report, StartDate and EndDate, under the Parameters folder. Since we hard-wired the StartDate and EndDate values, the parameters are not needed. Simply right-click on each one, and select the Delete option:

 

 

Save the report, and then run it. If you schedule it from the SCOM admin console, you will notice that it does not prompt for StartDate and EndDate parameters any longer, and now when scheduled will use the relative dates you configured.

Comments

• Anonymous
  May 02, 2014
  Andres! I've traveled far beyond the wall to find you and ask a question. I was looking to receive repeat notifications until the issue was resolved. I opened a case with Microsoft and we set up a 2nd notification for the same monitor and set the delay for 60 minutes. I would have to create a new notification for each additional notification I would want. Is there a cleaner way to do this?
• Anonymous
  June 11, 2014
  Walter,
  Sorry for the long delay, I just saw your question. No, to the best of my knowledge unless you use something like System Center Orchestrator, there is no cleaner way to do this.
• Anonymous
  January 06, 2015
  what if, instead of the past 30 days, I want to run it from 00:00 on the first day of the previous month to 00:00 on the first day of this month?