SCCM2012 with SCUP 2011 - Move your database and make it shared

So as you can see, a lot of SCCM 2012 posts mainly due to the fact that I setup SCCM2012 from the begining to the end at my company, and experienced lots of challenges along the way, but it is all now running as a well oiled machine, with Endpoint Protection 2012, MBAM2.0 integration, Citrix Publisher Integration, WSUS, SCUP 2011 with Shavlik updates, pretty much the whole nine yards.

One of the requirements around here was to get SCUP 2011 working to be able to publish Adobe updates at the least. Since there are only a few live catalogue providers (Dell, HP and Adobe last I checked) this wasn't really a requirement per say, but in an effort to make the transition from SCCM 2007 to 2012 as seamless as possible, I took the challenge on to get this working.

As some of you may know, SCUP 2011 is a single user application by default. You set things up, everything is great, it works for you, but if anyone else wants to use it, then they have to set everything up as well. This includes having to add all update sources, WSUS and SCCM integration components ect. I didn't know this myself until I had a co-worker test out SCUP, and to my surprise it was completely empty, with nothing configured at all. I figured there has to be a way to make this all work, so I searched on Google and found a few very interesting posts which got me 90% there, but the other 10% took me a few more days to figure out.

As posted here, the database file that SCUP 2011 uses is actually stored under the user's profile, which can be moved to a different, shared location. In my case, the database file was located at this directory

C:\Users\EBLEND\AppData\Local\Microsoft\System Center Updates Publisher 2011\5.00.1727.0000\scupdb.sdf
 

I copied the file to the install directory where I installed SCUP 2011 as it is accessibly by all administrators, and modified the  Scup2011.exe.config file in the same directory to reflect the new location as described in that post I mentioned earlier.

    <applicationSettings>
        <Scup.Properties.Settings>
            <setting name="SSCEDataFile" serializeAs="String">
                <value>D:\Program Files (x86)\System Center Updates Publisher 2011\scupdb.sdf</value>
            </setting>
        </Scup.Properties.Settings>
    </applicationSettings>
 

Excellent I thought, this is now ready....but this was not the case. When a co-worker tried to open SCUP, he got an error "An error occured. Please refer to log file for details". I did a quick Google search and found this post where it explained that for whatever reason, a user or a group that is a member of a local administrator group, even if granted permissions directly to the SCUP folder, does not have access to access the database file. I looked at the permissions on the SCUP folder, and Administrators had full access, and I had my AD SCCM groups as part of the local Administrator's group, but it wouldn't work. The trick is to assign your AD SCCM administrator groups directly to the folder, after I did this, SCUP opened without issue.....I thought....more on this in a minute.

So as you can see, ConfigHelpDesk was granted full permissions on the SCUP install folder directly, even though it was already a member of the local Administrators group.

So all this work got me to about 90%...users were now able to login (one at a time) and see everything that is configured on this shared database. Great, so my work was done I thought. A few weeks later a co-worker was ready to publish something and the options were not there, so I was called in to take a look. Looked at the options, and sure enough, all the certificates, WSUS integration and SCCM integration is not even configured! So it looks as though the database holds some information, the configuration is stored somewhere else. I started to do some looking around, and stumbled upon this nicely named directory in my user profile:

C:\Users\EBLEND\AppData\Local\Microsoft\Scup2011.exe_StrongName_2wzdfznimh1kefuisr0pqsefwkw5k4tp\5.0.1727.0\user.config
 

I opened up this file  and sure enough, it seems like all of the configution for Certificates, WSUS and SCCM integration is stored here, and looks something like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <userSettings>
        <Scup.Properties.Settings>
            <setting name="EnableWSUSPublish" serializeAs="String">
                <value>True</value>
            </setting>
            <setting name="LastKnownCertificateIssuer" serializeAs="String">
                <value>CN=certauthsa1, DC=auc, DC=ab, DC=ca</value>
            </setting>
            <setting name="LastKnownCertificateExpire" serializeAs="String">
                <value>3/21/2015 10:01:07 AM</value>
            </setting>
            <setting name="IsTimestampEnabled" serializeAs="String">
                <value>False</value>
            </setting>
            <setting name="SigningCertSectionEnabled" serializeAs="String">
                <value>True</value>
            </setting>
            <setting name="EnableCMIntegration" serializeAs="String">
                <value>True</value>
            </setting>
            <setting name="LocalSourcePublishingFolder" serializeAs="String">
                <value>I:\AdobeUpdates</value>
            </setting>
            <setting name="UseCustomLocalSourceFolder" serializeAs="String">
                <value>False</value>
            </setting>
            <setting name="Height" serializeAs="String">
                <value>720</value>
            </setting>
            <setting name="Width" serializeAs="String">
                <value>1000</value>
            </setting>
            <setting name="Left" serializeAs="String">
                <value>412</value>
            </setting>
            <setting name="Top" serializeAs="String">
                <value>368</value>
            </setting>
            <setting name="TrustedPublishers" serializeAs="Xml">
                <value>
                    <ArrayOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                        <string>MIIFkTCCBHmgAwIVyaXNpZ24uY29tL3Jw...........9/cD+1zUzqr</string>
                    </ArrayOfString>
                </value>
            </setting>
        </Scup.Properties.Settings>
    </userSettings>
</configuration>

I copied everything between <userSettings> and </userSettings> from the config file above and pasted it into the same Scup2011.exe.config file that we have previously modified with a new database location, replacing the empty fields with the same labels. Once I did this, every admin user could publish and all of the certificate, WSUS and SCCM integration configuration was there, in a truly shared way. Keep in mind that you still have to run SCUP 2011 with "Run as Administrator", otherwise it will not publish.

In addition, for every update category that you publish, that category or vendor name will need to be checked of as a "product" in SCCM 2012 console under Administraton > Site Configuration > Sites > Right click to Configure Site Components > System Update Point.

You will have to run "Synchronize Software Updates" function twice from within SCCM 2012 console to first get the update to show up in the list above to be checked off for synchronization, and then again to actually get the updates to show up within your update list in SCCM.

Somewhere along the line, I learned that we actually have an active Shavlik subscription, so I attached our SCUPUpdates catalogue from Shavlik into SCUP and can easily publish all 3rd party updates directly into our SCCM 2012 server. Here is a picture of the 3rd party products that Shavlik can patch via SCUPUpdates with SCUP 2011 and SCCM 2012

I didn't see any reference to this complete solution, so hopefully this is of use to someone.

SCCM 2012 - Endpoint Protection Antimalware Activitiy Report Subscription with Dynamic Dates

Over the weekend I decided to work on some additional subscriptions for reports that I wanted out of SCCM 2012, and although the "Antimalware Activity Report" looks much like the "Antimalware Overall Status and History" I discussed in my previous blog post, it it somewhat different in it's execution.

I opened the report in SQL Server Report Builder and noticed that it had the same @StartDate and @EndDate parameters as well as the DateRange dataset as the "Antimalware Overall Status and History" report, so I figured this would be easy. I saved the report with an appended name (usually add -last 7 days) and tried to figure out how to make it work automatically. I deleted the @StartDate and @EndDate parameters and tried to save, which gave me an error that a subreport is using these parameters somewhere. It seems that a report won't save if there are errors, and if there are errors, it tells you where they are specifically, so this has become my new meathod of tinkering.

Subreport, well that's news to me, what are these? I looked over to the right hand side of report builder and noticed these gray boxes:

Right clicking on each individual box will show give you an option for "Subreport Properties"....so I guess I found the sub reports which are referencing my date variables. Go into the Subreport Properties on each gray box , navigate to Parameters and you should see something like this:

Click on the little "fx" icon next to StartDate and replace the expression within with this:

=DATEADD("d",-7,Today())

Repeat the same thing with the EndDate and replace the expression with this:

=DATEADD("d",-0,Today())
 

 Repeat this for every Subreport.  Once this is done, you will be able to delete the DateRange Dataset as well. Save your report and create your subscription. The subscription will automatically update the dates everytime it executes and you will get your dynamic report showing Antimalware activity for the last 7 days.

Why Microsoft didn't make this a default behavious is anyone's guess. On the bright side, the MBAM 2.0 report doesn't require any dates and just shows the state of your BitLocker encryption at the time the report is ran.

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!