Current Branch – Modern Cloud Management

SCCM Managed Office 365 Clients, Versioning

A topic I’ve been asked about often the last few weeks has been around Office 365 Client versioning, more specifically when being managed with SCCM. Ensuring Office 365 Clients are properly set to be managed via SCCM is important for consistent versioning results.

A few cross checks first, make sure your site is set to pull Office 365 Client updates down:

Make sure you have the highlighted field below set to “Yes”, that will ensure Configuration manager is able to update the clients as defined (hopefully) in their configuration when deployed\installed. This is assuming those clients are set to use SCCM as their point of contact for updates which we’ll cover in this blog.

When you’re creating the configuration.xml for your Office 365 deployment, make sure it contains the below highlighted segment, this will ensure the client knows who to talk to for updates.

Another note, if you want your users to see that there are updates pending, add the <Updates Enabled=”True” /> portion. As you can imagine, when set to False, clients still receive updates from SCCM, but end users will not see any pending update notifications.

See https://docs.microsoft.com/end-user-update-notifications-for-office-365-proplus for more information on update notifications.

Requirements for SCCM to manage Office 365 Client Updates:

System Center Configuration Manager, update 1602 or later
An Office 365 client – Office 365 ProPlus, Visio Pro for Office 365, Project Online Desktop Client, or Office 365 Business
Supported channel version for Office 365 client. For more details, see Release information for updates to Office 365 ProPlus
Windows Server Update Services (WSUS) 4.0 You can’t use WSUS by itself to deploy these updates. You need to use WSUS in conjunction with Configuration Manager
The hierarchy’s top level WSUS server and the top-level Configuration Manager site server must have internet access.
On the computers that have the Office 365 client installed, the Office COM object is enabled.

Let’s get some perspective on client versioning in an environment now. Check under ”Software Library > Office 365 Client Management” to see where your versioning stands.

A lot of what’s expected here should be dependent on what testing you may have in place. Do you have a test group on monthly? Who might be on Semi-annual? Don’t be caught off-guard to see something like what’s depicted further below.

If the expectation would be for all clients to be on “Semi-annual Chanel”, and all version numbers to be some variant of 1803 which would be build 9126, or 16.0.9126.XXXX, then we have some things to dig into. (Office Pro Plus Versions by Date)

Here is the expected behavior when devices are correctly set to be managed by SCCM:

“When Microsoft publishes a new Office 365 client update to the Office Content Delivery Network (CDN), Microsoft simultaneously publishes an update package to Windows Server Update Services (WSUS). Then, Configuration Manager synchronizes the Office 365 client update from the WSUS catalog to the site server. Configuration Manager can then download the update and distribute it to distribution points selected by the administrator. The Configuration Manager desktop client then tells the Office client where to get the update and when to start the update installation process.”

I stress the above bolded area as it is vital to ensure the clients are set to use SCCM properly.

If you feel that most of what has been covered so far remains true for you, the most likely culprit is probably still some mixture of inconsistent settings. Good news though, there are a few easy ways we can make sure we have the client settings set correctly where needed.

The Microsoft preferred method for computers that have already had Office Pro Plus deployed, would be the Group Policy option.

GPO Option – https://docs.microsoft.com/en-us/deployoffice/configure-update-settings-for-office-365-proplus#use-group-policy-to-configure-update-settings-for-office-365-proplus

The other option, and frankly the “Cooler” one, is the PowerShell option. See the following for the PowerShell Option – https://blogs.technet.microsoft.com/odsupport/2017/05/10/how-to-switch-channels-for-office-2016-proplus/

That should be a great start and hopefully answers a lot of the basic questions around getting Office 365 Client versioning in order. Please comment with any comments, questions, or anything I may have missed! After all, that is the purpose of being a community : )

Hyper-V Baseline Change – AutomaticStopAction

Was recently asked by a customer to make a global Hyper-V change on VM’s having the wrong AutomaticStopAction setting, across many HV Hosts. My tool of choice, SCCM Baseline! (Yes, SCVMM is a better option here, but that’s not an option in their environment…)

To familiarize everyone, this is what we are changing:

Let’s hop over to PowerShell and get familiar with the cmdlets needed for this. Let’s check the value first:

$(Get-VM -Name HYD-CLIENT1).AutomaticStopAction

For what’s it worth, running just $(Get-VM).AutomaticStopAction will return the state for all VM’s on the host.

Good source for Hyper-V cmdlets : https://docs.microsoft.com/powershell/module/hyper-v

Create and name the baseline, we’ll come back to it later:

Go ahead and create a new configuration item, name it, platforms you want to limit it to, etc. We’ll focus primarily on the “Settings” and “Compliance Rules”:

For “Setting type” and “Data type”, we’ll choose Script and String as shown below:

Since we know what value we want and how to grab that, we’ll use that for our “Discovery Script”:

And as you may have guessed, we’ll use the Set version of that cmdlet to ensure the desired value is set:

Get-VM | Stop-VM;Get-VM | Set-VM -AutomaticStopAction ShutDown;Get-VM | Start-VM

Now choose the “Compliance Rules” tab, this can also be accessed from the main properties window shown below this. Double click or highlight and:

Here we are defining the conditions for a machine to report as compliant when measured against this baseline, make sure you check the box to “Run the specified remediation script when this setting is noncompliant”:

That’s it for the configuration item!

Last step, head back to the Configuration Baseline and add the configuration item we just created, by adding it to the “Evaluation Conditions” as shown below:

Filter the list to find the newly created item, select it and click “Add”:

Choose “OK” and you’re done!

Please comment with any questions about this procedure or any general baseline questions, we’re here to help!

Custom Reports 101 – An Example from the Real World – Report on Software Updates in a Software Update Group

Custom Reports 101 – An Example from the Real World – Report on Software Updates in a Software Update Group.

Good day everyone, my name is Trevor Stuart. I am one of the authors and operational leads of moderncloudmanagement.com. Today I wanted to share a post on a real-world client request that came my way. To shed a little background, I am a typical Tech Guy– specializing in SCCM (12+ years), Windows 10 (RTM +), Azure (5+ years), and Cyber Security (learning and loving it). I work alongside Joe Anich (the original author and operational lead of moderncloudmanagement.com) daily for this customer.

Scenario:

Whilst I was on my way out for the evening, the manager I report to for this particular customer asked if I was able to create a custom report that quickly showed all software updates that were in a particular software update group – group it, and highlight the ones that have been modified/added since the last time the software update group had been deployed. Sounds simple right? Let’s dive right in to see if that’s true or not!

Technical Deep Dive:

First thing that must be accomplished is to identify where in the SCCM DB the data for this request is stored. Now I am not going to sit here and assume everyone reading this understands the SCCM DB Schema to an expert level, so I want to share a common trick I teach customers during training on custom report creation. There is a component in all hierarchies called SMS Provider, the provider essentially takes what you do in the SCCM admin console, which is normally executed in WQL, translates that to SQL and executes it against the DB. So, how does this help? Big time!

Go to: Software Library – Software Updates – Software Update Groups – Double click your SUG to show the members of it.

This is the content you’re looking to identify, now quickly go to your log directory on you site server <installation directory>\Program Files\Microsoft Configuration Manager\Logs – within that directory you will see SMSProv.log – open it.

You can see from the highlighted line the precise SQL command that SMS Provider is executing against your DB to bring you what you visually see in the console. Here we are seeing all the columns it’s selecting and most importantly where it’s selecting this information from within the DB. Keep this log up on that line – we will need it later.

Moving on, you will need to open up SQL Server Management Studio and make a connection to your Site DB. Once connected go ahead and open up a new query window. I normally start off by doing a few “Select *” statements from the views I see within the SMSProv.log execution like below:

Text:

select * from vSMS_SoftwareUpdatesPackage_List

select * from vSMS_CIRelation

select * from v_AuthListInfo

select * from v_CIToContent

Returned Results:

As you can see here most of our information comes from these four views. Now what we must do is start pruning out what we do not need vs what we are trying to obtain in order to start forming this custom query to then build a report from. What you can learn from these select statements is your Name, CI_ID, etc. all of which will be crucial to the success of this report. I was quickly able to identify the CI_ID that I wanted to target along with sorting by date released to meet the other requirement from the customer.

Text:

select distinct

upd.articleid,

upd.bulletinid,

upd.DisplayName,

upd.severity,

upd.DateCreated,

upd.IsSuperseded,

upd.IsExpired,

upd.DateLastModified,

al.Title,

al.ci_id

from vSMS_CIRelation as cr

inner join fn_listupdatecis(1033) upd on upd.ci_id = cr.tociid and cr.relationtype =1

inner join v_CIToContent cc on cc.CI_ID=upd.CI_ID

inner join v_AuthListInfo al on al.ci_id=cr.FromCIID

where al.CI_ID = 1473

order by upd.DateCreated desc

Results (461 Rows Returned which match what we visually saw in the SCCM Console):

The above results provided me with everything I needed from a requirement perspective. So, at this point I know that I’m clear to move onto Report Builder, merely copying the query and taking it with me.

Next up we will open Report Builder (don’t worry, Report Builder isn’t all that difficult after all you’ll shortly see!), and follow the Wizard below:

1. Within “New Report” – Click “Table or Matrix Wizard”, that will bring up the following window:

2. Allow “Create a dataset” to remain selected and click Next. This will bring the following window:

3. It should automatically identify the data source that’s mapped to your report server, at this point click “Next” and that will bring you to the following window:

4. Now we are getting somewhere! At this point you will select “Edit as Text” which will bring you to the following window:

5. Remember that query we created in SQL? Let’s go ahead and copy and paste that into this window like the following:

6. After successfully pasting, please click “Next” and you’ll be presented with the following window:

7. You will see numerous available fields in which you will be able to drag and drop into Columns, Rows, or Values. For basic configurations I normally just drag the appropriate fields into the “Values” box like below:

8. Once the desired fields are in “Values”, click Next and you’ll see the following screen:

This will provide you a high-level look at what you’re table will look like once completed – go ahead and click “Next”, then “Finish” on the following screen.

9. You’ll now be brought into the Report Designer window where you will see the beginnings of your report!

10. From this point on you can customize the look and feel of the report as you deem fit. I normally expand the size so that all of the information is presented in a readable manner, I change the fonts, alignment, and add custom branding per each customer. At the end – the results look like the following (Customer Scrubbed Out):

11. Finally, go ahead and save the report where you’d like within your report server and you’re officially complete!

I was able to provide the URL where this report can be run to the manager of this customer, with all requirements met, and accepted! I have various other custom SQL queries that I will be posting at a later date, but feel free to request custom SCCM reports here: Custom SCCM Report Requests & Discussion – and we will do our best to provide the SQL statements for the data you’re trying to obtain.

Thanks for your time! Like, and share! See you all soon.

Trevor