SCCM 2012 - Endpoint Protection Reporting with Dynamic Date Range

Recently I setup our SCCM 2012 server with System Center Endpoint Protection 2012 (formerly ForeFront Endpoint Protection, or FEP) and was asked to setup a report that would be mailed out weekly to show the overall status of our new antimalware solution which we were slowly rolling out throughout the enterprise.

This is normally pretty easy to do, find the report you are interested in, create a subscription and you are on your way; however I hit a snag. I created the subscription, but did notice that the built in report (Antimalware Overall Status and History) for SCEP2012 wanted a static start and end date. I didn't think much of this as I thought that surely being a subscription that is meant to be provide a report on a set interval, it would be dynamic and those dates are just your initial report start and end dates that would dynamically update every time the subscription is ran. Well I soon found out that this is not the case.

The first week this was not evident as the report was mailed out the day I setup the subscription, however, the following week, the report still had the same charts as it did the week prior, without dynamically updating to show the last 7 days like I wanted it to.

I did a Google search and found a post on TechNet where someone was having the exact same issues as me, and to my relief, someone posted what looked like a solution. I found the report in SCCM, right clicked on it and went "Edit" which opened the report builder. I quickly went to the file many and saved it as a new report and made the changes suggested. I created another subscription, and set it to run daily so that I could see the result quickly. To my disappointment, there was no change, the report still was showing just the static dates. I left the problem for a while to concentrate on other issues and didn't revisit it until last week. I looked around at additional information online referring to dates and tried everything that I could find, but to no avail, the reports were still coming out with static dates. If you run the report manually it will return the proper 7 day window in the charts; however, whenever I created a subscription, it was like those dynamic dates were set in stone and became static.

Last night, out of nowhere, it hit me! When I followed the TechNet article, it mentioned that we have to set the date parameters as hidden. The parameters rely on a query and generate a dynamic date when the report is run manually, however, when a subscription is created, the dynamically generated dates are still parameters that the subscription uses, they are just hidden. So the day the subscription is created, the correct dates are automatically populated into the subscription and off you go, but those dates don't change after the fact. I thought that the way to get around this issue, is to get rid of the date parameters all together! If the date parameters are not there in any fashion, then the subscription will not use them (even when hidden) at all and will dynamically generate the date.

I opened the report builder again, went to Parameters and deleted the @StartDate and @EndDate parameters. I tried to save the report at this point which gave me an error that the Parameters are used in some query. Not being very proficient in SQL Server Report Builder, I went to the Datasets area and deleted the whole "DateRange" dataset. I tried to save again and still the same error. At this point I remembered that there was a query setup under EPHistory dataset filed called "StatusTime". When you view the properties of "StatusTime" field, and click on Query, you will see something like this:

select * from fn_rbac_EndpointProtectionHealthStatus_History(@UserSIDs)
where  CollectionID=@CollID and
             DATEADD(day, 0, DATEDIFF(day, 0, statustime)) between @StartDate and @EndDate

As you can see, this is the query that is referencing the @StartDate and @EndDate parameters and is what drives the charts. Since I deleted the parameters, I had to find a way to make this query work without the parameters. I dig into my previous attempts at getting all of this working and simply replaced the @ parameter values with actual date statements, and this was the result:

select * from fn_rbac_EndpointProtectionHealthStatus_History(@UserSIDs)
where  CollectionID=@CollID and
             DATEADD(day, 0, DATEDIFF(day, 0, statustime)) between DATEADD("d",-7,GetDate()) and DATEADD("d",-0,GetDate())

I saved the report at this point without error, created the subscription to run the report at 5 minutes past midnight, and stayed up until 12:05AM waiting for the e-mail. The e-mail arrived and the charts were correct!

This is a portion of the e-mail (using web archive format, as I found it preserves the layout best):

I spend a good chunk of time on this, so hopefully this information is useful to somebody! I now have to setup multiple subscriptions for various other things like MBAM 2.0, so hopefully this solution is more of less the same thing that I will have to do for any other report that will require static dates.

If I had more knowledge in Report Builder I am sure I could have figured it out right away, but I never used it before this time, so it took me some time.

Hope my first blog post isn't a disappointment. Sorry for the wall of text!