Part 6: Upgrading SCCM Current Branch

Now that you have ConfigMgr setup it’s time to upgrade it to the latest version. This is a relatively straight forward process and applies to all versions of current branch from 1511 onward. In the last post I installed 1606 so that’s what we’ll be using.

NB: You must have the Service Connection point installed and configured to upgrade.

At a glance:

  1. Confirm no operational issues with SCCM sites
  2. Review new SCCM version requirements, 1702 for example removes support for 2008 server. So you will need to upgrade these sites to 2012 or 2016 before upgrading.
  3. Patch, patch, patch!
  4. Uninstall any deprecated SCCM Sites system roles before upgrading
  5. Disable DB replicas on all primary sites (if you’re using them)
  6. Disable maintenance tasks
  7. Run Pre-req check for update
  8. Backup DBs (CAS and Primary)
  9. Test DB Backups
  10. Backup any custom .mof files
  11. Restart all Site Systems
  12. Upgrade
  13. Deploy new SCCM Admin Console
  14. Reconfigure DB Replicas
  15. Upgrade Clients
  16. Reconfigure clients

Continue reading

Advertisements

Part 3: Prep & Pre-reqs

In this post I’m going to setup all the prerequisites for SCCM and SQL. I’ll cover off on the install of SQL and configMgr in following articles though.

  1. Create a new Virtual Machine with the below
    • Name: SCCM-P01
    • Generation: 2
    • Startup Memory: 1024
    • Use Dynamic memory for this VM: Yes
    • Connection: vNet External
  2. I’ve allocated 8vCPUs to my SCCM VM.
  3. Add the following disks to the VM:
    • D:\ – SCCM (200GB)
    • E:\ – SQL Databases (50GB)
    • F:\ – SQL TempDB (50GB)
    • G:\ – SQL Logs (50GB)
  4. Install Windows Server 2016 Standard
  5. Set a static IP address, mines 192.168.0.110
  6. Give your server a name, mines SCCM-P01
  7. Join the lab domain
  8. Initialize all of the extra Hard Drives and format the SQL volumes with 64K allocation unit size
  9. Create the following Service Accounts
    • SA_SCCM_SQL
    • SA_SCCM_SQLReporting
    • SA_SCCM_NetworkAccess
    • SA_SCCM_Client
    • SA_SCCM_DomainJoin
  10. Create the following groups in AD
    • SCCM Server Admins
    • SCCM Servers
  11. Add your server to the newly created AD group ‘SCCM Servers’
  12. Delegate Full Control to the SYSTEM Container in Active Directory for the group ‘SCCM Servers’.
  13. Create a GPO for your SCCM server, mines in lab.local\Member Servers\SCCM
  14. Create the following Inbound Firewall rules in the GPO, Computer Configuration>Policies>Windows Settings>Security Settings> Windows Firewall with Advanced Security>Inbound.
    • Port (TCP) – 1433
    • Port (TCP) – 1434
    • Port (TCP) – 4022
    • Port (TCP) – 135
    • Port (TCP) – 2383
    • Port (TCP) – 2382
    • Port (TCP) – 80
    • Port (TCP) – 443
    • Port (TCP) – 1434
  15. Create a new file in sysvol called ‘No_sms_on_drive.sms’ and copy the file using GP Preferences to C:\ with the SCCM GPO.
  16. Extend AD Schema on DC01
    1. Login with an account with Schema Admins rights.
    2. Mount ConfigMgr ISO on
    3. Open PoerWhell as an adminstrator and run  .\SMSSETUP\BIN\X64\extadsch.exe
      2017-03-28_14-32-32.png
    4. Open log C:\Extadsch.log and confirm the schema has been successfully extended.
      2017-03-28_14-34-13.png
  17. Create System Management Container.
    1. Open ADSI Edit
    2. Right Click System container>New>Object
      2017-03-28_14-37-30.png
    3. Select Container and click Next.
      2017-03-28_14-38-18.png
    4. Enter ‘System Management’ exactly and click next.
      2017-03-28_14-39-31.png
    5. Right click on System Management Container and select Properties and then select the Security tab.
    6. Add your Primary SCCM Server and delgate full control.
      2017-03-28_14-46-01.png
    7. Click Advanced
    8. Select the site server and click Edit.
      2017-03-28_14-48-24.png
    9. Under applies to select This object and all descendant objects.
      2017-03-28_14-49-36.png
  18. On you SCCM Primary Site Server open PowerShell and run the following commands to install the prerequisite roles and features.
    Install-WindowsFeature Web-Windows-Auth
    Install-WindowsFeature Web-ISAPI-Ext
    Install-WindowsFeature Web-Metabase
    Install-WindowsFeature Web-WMI
    Install-WindowsFeature BITS
    Install-WindowsFeature RDC
    Install-WindowsFeature NET-Framework-Features -source \\yournetwork\yourshare\sxs
    Install-WindowsFeature Web-Asp-Net
    Install-WindowsFeature Web-Asp-Net45
    Install-WindowsFeature NET-HTTP-Activation
    Install-WindowsFeature NET-Non-HTTP-Activ

    2017-03-28_15-29-10.png

  19. Install Windows Update Service
  20. Install Windows ADK
    1. Download ADK from here
    2. Run ADKsetup.exe
    3. Change installation directory to your SCCM volume and click next.
      2017-04-23_20-05-29.png
    4. Select the below features and click Install.
      2017-04-23_20-07-33.png
    5. Once completed restart your server.

Disabling OneDrive for Office365

Recently I had a requirement to remove access to OneDrive for users that had Office365, unfortunately after a bit of research the communities stance was that basically this was not possible without impacting Office activation.

We spent a substantial amount of time testing multiple ways to achieve this and ultimately landed on the below options. Depending on your configuration you should ultimately be able to remove this access quite easily.

To clarify the functionality we wanted to remove was the OneDrive access within Office apps as per below:

2017-04-13_15-21-18.png

Stop OneDrive from being created

  1. Navigate to SharePoint admin Center and open User profiles
  2. Under People, click Manage Users Permissions
  3. Under everyone except external users untick – Create Personal site.

4-current-permission.png

This option is quick and easy if you haven’t migrated anyone yet and you want to blanket stop users from accessing the OneDrive feature.

Revoke Permission to the users OneDrive

  1. Navigate to SharePoint admin Center and open User profiles
  2. Under People, click Manage User Profiles
  3. Search for the user account you want to remove access for
  4. Click on the user name and, from the drop-down menu, choose Manage site collection owners
  5. Remove their account

This is great if you want to only remove access for specific users on a by request basis but not ideal for bulk removal. This can also apply even after the OneDrive has been created so it can be combined with the first option above.

Tenant Level Permissions (Microsoft need to do this)

Microsoft can at the tenant level apply configuration which allows you to remove access completely to OneDrive via URL, Office apps and sync where SharePoint license is removed from the plan.

To do this all you need to do is raise a premier support ticket with your default site collection URL – https://domain.sharepoint.com. This was ideal for us as it allowed us to programmatically remove it for all users and reintroduce access as we introduce OneDrive to the business.

Once applied the plan needs to be modified, you can automate this via PowerShell scripts so it can be managed by AD groups – Office 365: Assign licenses based on groups using PowerShell

Block Sign-in to Online Content (MSI Office only)

Where you have used the MSI version of Office (not click-to-run) you can disable access to online content by modifying a registry key on the client.

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\SignIn

Name: SignInOptions
Type: DWORD
Value: 4

 

 

Obviously the version  …\Office\16.0\Common can be changed to the specific version of office being used.

 

Getting started with PowerBI

thumbnail

PowerBI has been around for a while now, it’s being constantly being developed by a great team over at Microsoft. It also proves to be a great tool for visualising large sets of data which makes it ideal for carving up everything in your SCCM DB.

I’ve built a number of dashboards for presenting dynamic data of your environment on 55″ LCD TVs. It’s a great way to present data and gain visibility of any problems you might be having in your environment

First thing you’re going to want to do is go and pick up the latest copy of PowerBI from here. Then you can simply create queries like you would for a normal SQL report, make sure that the account you’re connecting with has read rights to your CM DB though.

If you’re not too crash hot on building SQL queries or want something up and running pretty quick then you can’t go past this solution template for System Center Configuration Manager

 

SCCM SQL Queries 101

Here’s a collection of useful SQL queries you can use these in reports or PowerBI. Make sure you’re charting them as best you can, a picture paints a thousand words especially when you’re talking to management.

Total Clients

SELECT
COUNT(dbo.v_R_System.ResourceID)
FROM
dbo.v_R_System

Total Users

SELECT
COUNT(dbo.v_R_User.ResourceID)
FROM
dbo.v_R_User

By Domain

SELECT
       dbo.v_R_System.Full_Domain_Name0,
       COUNT(dbo.v_R_System.Name0)
FROM
       dbo.v_R_System
GROUP BY
       dbo.v_R_System.Full_Domain_Name0
ORDER BY
       dbo.v_R_System.Full_Domain_Name0

By Architecture

SELECT
dbo.v_R_System.Full_Domain_Name0,
COUNT(dbo.v_R_System.Full_Domain_Name0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Full_Domain_Name0

By Operating System

SELECT
dbo.v_R_System.operatingSystem0,
COUNT(dbo.v_R_System.operatingSystem0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.operatingSystem0

By Chassis Type

SELECT
CASE dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
WHEN '1' THEN 'Other'
WHEN '2' THEN 'Unknown'
WHEN '3' THEN 'Desktop'
WHEN '4' THEN 'Low Profile Desktop'
WHEN '5' THEN 'Pizza Box'
WHEN '6' THEN 'Mini Tower'
WHEN '7' THEN 'Tower'
WHEN '8' THEN 'Portable'
WHEN '9' THEN 'Laptop'
WHEN '10' THEN 'Notebook'
WHEN '11' THEN 'Hand Held'
WHEN '12' THEN 'Docking Station'
WHEN '13' THEN 'All in One'
WHEN '14' THEN 'Sub Notebook'
WHEN '15' THEN 'Space-Saving'
WHEN '16' THEN 'Lunch Box'
WHEN '17' THEN 'Main System Chassis'
WHEN '18' THEN 'Expansion Chassis'
WHEN '19' THEN 'SubChassis'
WHEN '20' THEN 'Bus Expansion Chassis'
WHEN '21' THEN 'Peripheral Chassis'
WHEN '22' THEN 'Storage Chassis'
WHEN '23' THEN 'Rack Mount Chassis'
WHEN '24' THEN 'Sealed-Case PC'
ELSE 'Undefinded' END AS 'Chassis',
COUNT(dbo.v_R_System.Name0) AS [# Devices]

FROM
dbo.v_R_System
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID

GROUP BY
dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0

ORDER BY
1

By Virtual/Physical

SELECT
CASE
WHEN dbo.v_R_System.Is_Virtual_Machine0 = '0' THEN 'Physical'
WHEN dbo.v_R_System.Is_Virtual_Machine0 = '1' THEN 'Virtual'
ELSE 'Unknown'
END AS [Physical / Virtual],
COUNT(dbo.v_R_System.Name0) [# Devices]
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Is_Virtual_Machine0
ORDER BY
dbo.v_R_System.Is_Virtual_Machine0

 

By CCM Client Version

SELECT
dbo.v_R_System.Client_Version0,
COUNT(dbo.v_R_System.Client_Version0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Client_Version0
ORDER BY
dbo.v_R_System.Client_Version0

By Client Health

--Variables
DECLARE @Now DateTime = GetDate()

--Add Data to Temp DB Table
IF object_id('tempdb..#TMP_ClientsHealthSum') IS NOT NULL
DROP TABLE #TMP_ClientsHealthSum

Select Distinct
Name0,
ResourceID,
LastOnline,
case
When DATEDIFF(dd,LastOnline,@Now) between 0 and 7 then 'Past Week'
When DATEDIFF(dd,LastOnline,@Now) between 8 and 14 then 'Last 2 Weeks'
When DATEDIFF(dd,LastOnline,@Now) between 15 and 21 then 'Last 3 Weeks'
When DATEDIFF(dd,LastOnline,@Now) between 22 and 29 then 'Last 4 Weeks'
When DATEDIFF(dd,LastOnline,@Now) between 30 and 60 then 'Last 2 Months'
When DATEDIFF(dd,LastOnline,@Now) between 61 and 89 then 'Last 3 Months'
When DATEDIFF(dd,LastOnline,@Now) >= 90 then 'Over 3 Months'
Else 'Never'
End As 'DaysSinceLastOnline',
--LastMPServerName,
LastHealthEvaluation,
case
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 0 and 7 then 'Past Week'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 8 and 14 then 'Last 2 Weeks'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 15 and 21 then 'Last 3 Weeks'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 22 and 29 then 'Last 4 Weeks'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 30 and 60 then 'Last 2 Months'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 61 and 89 then 'Last 3 Months'
When DATEDIFF(dd,LastHealthEvaluation,@Now) >= 90 then 'Over 3 Months'
Else 'Never'
End As 'DaysSinceLastHealthEval',
LastHealthEvaluationResult,
IsActivePolicyRequest,
LastEvaluationHealthy,
ClientActiveStatus,
ClientState,
ClientStateDescription
INTO #TMP_ClientsHealthSum
FROM vSMS_R_System SYS
LEFT JOIN v_CH_ClientSummary vCS ON SYS.ItemKey = vCS.ResourceID
WHERE SYS.Obsolete0 = 0

Select * From #TMP_ClientsHealthSum

By Client Type

SELECT
CASE WHEN dbo.v_R_System.Client_Type0 = 0 THEN 'Legacy Client'
WHEN dbo.v_R_System.Client_Type0 = 1 THEN 'Computer'
WHEN dbo.v_R_System.Client_Type0 = 3 THEN 'Mobile'
ELSE 'Unknown' END AS [Client Type],
COUNT(dbo.v_R_System.Client_Type0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Client_Type0
ORDER BY
dbo.v_R_System.Client_Type0

 

 

 

 

 

If you want to only report on workstations or servers respectively you can append most of the above queries with the below WHERE statements.

WHERE
(dbo.v_R_System.operatingSystem0 NOT LIKE '%SERVER%') --Workstations
       (dbo.v_R_System.operatingSystem0 LIKE '%SERVER%') --Servers

SCCM Compliance – PowerShell version

One of the first things I always like to setup in compliance is a CI which checks for a minimum version of PowerShell. This is especially helpful when you’re writing advanced compliance scripts in PowerShell and they’re not running as expected across some of your environment. In my experience this is usually because some of the devices have an old version of PowerShell.

Compliance Settings:

Setting Type:

 Script

Data Type:

 Integer

Discovery script:

$PSVersionTable.PSVersion.Major

Remediation script:

I’m not using a Remediation script, better to just deploy Windows Management Framework across you’re environment and use compliance as validation.

Make sure that Run scripts by using the logged on user credentials is ticked.

Compliance Rules:

Rule Type:

Value

The value returned by the specified script:

Greater than or equal to

The following values:

 3

Report noncompliance if this setting instance is not found

Yes

Noncompliance severity for reports

 Critical

SCCM Collection: Dynamically Identifying Users that have had their mailbox migrated to O365

One of the challenges I’ve recently had is identifying users that have had their mailbox migrated to Office365. This becomes quite a task if like me you’re using InTune integrated with SCCM but need separate mail profiles for mobile users. Now this isn’t an issue if you’re happy to manually maintain an AD group which can then be referenced by SCCM, however I want this to dynamically be updated and not rely on Humans.

John Bailey, has written an excellent article on some key AD attributes which can be used to identify whether the mailbox is on-prem or in O365. For my environment I can simply extend my Active Directory User Discovery to include the attribute ‘msExchRecipientDisplayType’ and then use a WQL query to identify the users that have been migrated.

Common values:

Value

Object Type

1

User Mailbox

2

Linked Mailbox

4

Shared Mailbox

2048

Dynamic Distribution Group

2147483648

Remote Mailbox (O365)

The query for where the mailbox has been migrated to Office365:

SELECT *
FROM
SMS_R_User
WHERE
SMS_R_User.msExchRecipientTypeDetails = 2147483648