Tuesday, July 4, 2017

Windows 10 ConfigMgr Collections

I've been creating alot of Windows 10 focused collections in SCCM so thought I would gather what I have here. Mostly for me, but also to share with the world. I'll update as I add other ones and tweak these queries. If you have any share them with me!

The main one is to look for Windows 10 specifically. It should be pretty commonly known. I'm not one to use the 'All Systems' built-in collection so I have a parent called 'All Workstations' which contains all endpoints that are not Servers. I set the initial Windows 10 collection below to limit from that collection.

All Windows 10 Systems

 select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where OperatingSystemNameandVersion like '%Workstation 10.0%'  

Individual Versions. These all reference the above as the limiting collection. They are the same exception the version at the end.

All Windows 10 v1507 Workstations (10.0.10240)

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Build like '10.0.10240%'  

All Windows 10 v1511 Workstations (10.0.10586)

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Build like '10.0.10586%'  

All Windows 10 v1607 Workstations (10.0.14393)

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Build like '10.0.14393%'  

All Windows 10 v1703 Workstations (10.0.15063)

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Build like '10.0.15063%'  

These show the branches. These are the same except for SMS_R_System.OSBranch difference.

All Windows 10 Current Branch (CB)

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like '%Workstation 10.0%' and SMS_R_System.OSBranch = '0'  

All Windows 10 Current Branch for Business (CBB)

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like '%Workstation 10.0%' and SMS_R_System.OSBranch = '1'  

All Windows 10 Long Term Service Branch (LTSB)

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like '%Workstation 10.0%' and SMS_R_System.OSBranch = '2'  

These are neat ones. They show which ones that have expiring info around Servicing. They are all the same except SMS_WindowsServicingStates.State.

All Windows 10 Servicing Current

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System LEFT OUTER JOIN SMS_WindowsServicingStates ON SMS_WindowsServicingStates.Build = SMS_R_System.build01 AND SMS_WindowsServicingStates.branch = SMS_R_System.osbranch01 where SMS_WindowsServicingStates.State = '2'  

All Windows 10 Servicing Expiring Soon

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System LEFT OUTER JOIN SMS_WindowsServicingStates ON SMS_WindowsServicingStates.Build = SMS_R_System.build01 AND SMS_WindowsServicingStates.branch = SMS_R_System.osbranch01 where SMS_WindowsServicingStates.State = '3'  

All Windows 10 Servicing Expired


 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System LEFT OUTER JOIN SMS_WindowsServicingStates ON SMS_WindowsServicingStates.Build = SMS_R_System.build01 AND SMS_WindowsServicingStates.branch = SMS_R_System.osbranch01 where SMS_WindowsServicingStates.State = '4'  

For Editions you can use these to capture Pro vs Enterprise etc. I dont use Education but it should be easy to adapt also.

All Windows 10 Enterprise Edition

 select distinct SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows 10 Enterprise"  

All Windows 10 Pro Edition

 select distinct SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows 10 Pro"  

For the Insider Preview versions I'm still figuring out a nice query for it. In the mean time. I just created a collection that is limited to the initial Windows 10 collection. It in turn has an include for the same Windows 10 collection and excludes for the versions above. (1507, 1511, and 1607 currently). When the Creators Update is released its version would need to be added as an exclusion.

Everything above uses the initial Windows 10 collection as limiting. Once any useful ones are created , you can have all sorts of fun by taking the initial query above and using other limiting collections such as bit-level (64-bit vs 32-bit), or platforms like mobile vs desktop or Dell, Lenovo, and what not to isolate further.

Then of course there is Windows 2016

 select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where OperatingSystemNameandVersion like '%Server 10%'  



Monday, June 26, 2017

WSUS 2012 R2 Maintenance Automation

One of my most popular posts is WSUS automated maintenance, however it is centered on Server 2008 / 2008 R2. A friend asked me how I was doing it on Server 2012 R2 WSUS version 6.3 so I thought I would share that with the rest of the world. Server 2012 aka 6.2 should be no different.

I wont cover the reasons as they are explored in my above post as well as other places on the Internet, such as Jasons link below. This post is simply what I do to keep a 2012 R2 WSUS happy and fast. As before I perform 3 basic steps:

  • Decline Itanium Updates
  • Cleanup Wizard
  • Re-Index Database

Decline Itanium Updates


The first bullet is handled by a Powershell script Jason Sandys wrote. You just pass all the options to the script.

 powershell.exe "C:\Scripts\WSUSServerCleanup\Decline-OtherUpdates.ps1 -UpdateServer YourWSUSServer -Port 8530 -DeclineBeta -DeclineItanium"  

Cleanup Wizard


Second is a cleanup wizard script. I have moved to this one by Trevor Jones as my previous one didn't support 2012 R2 WSUS all that well. Jasons Script can do much of this, however Trevor's generates a nice HTML based email that you can send to yourself to see what it did.

You configure settings within the PS1 file for servers, contact email and SMTP smarthost and whatnot. I have single WSUS servers that I manage in this example howver several WSUS instances email me so I added the servername to the subject line.


  $WSUSServers = @(  
   "YOURWSUSSERVER"  
   )  
 # Mail settings  
 $smtpserver = "smtp.yourdomain.com"  
 $MailSubject = "YOURWSUSERVER WSUS Cleanup Report"  
 $MailRecipients = "ITHelpDesk@yourdomain.com"  
 $FromAddress = "YourWSUSServer@yourdomain.com"  

Then just run it:

 powershell.exe "c:\Scripts\WSUSServerCleanup\WSUSServerCleanupReport.PS1"




Re-Index Database


Same as before I use the Scripting Guys Cleanup Script. You can find the WsusDBMaintenance script here, however doing a Re-Index is a little more complex and needs to be ran on each WSUS server from the parent on down. If you use Windows Internal Database (default for WSUS) then this applies.

Firstly, you have to install some prereqs as the sqlcmd called in the re-index script needs to be present. Microsoft provides it separately so you do not have to install a full edition of MS SQL to get it. Install MS SQL Server Native Client and then install the sqlcmd tool to your server. Below are links for version 13.1 that works on 2012 R2. Install with defaults. Note the versions must match.



then run this. Note the -S switch changed for 2012R2 WSUS from 2008 R2.

 sqlcmd -E -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i "C:\Scripts\WSUSServerCleanupReport\WsusDBMaintenance.sql"  

For 2008 R2 I would get an email of the output of sqlcmd. I stopped as I looked at it once and never again. My previous post has details around this if you want to do it.


Schedule



As far as schedule, I now just run a single batch file on the WSUS server with all three steps as compared to running separately. I run it as a scheduled task the first Tue of the month so things are clean when Update Tuesday rolls around. 



Closing


Just as I wish Microsoft would split out Itanium from X86/AMD64, I also wish that WSUS would list Windows 10 versions separately instead of all together. As of right now if you choose Windows 10 updates you get 1511, 1607, and 1703 versions. Say for an environment that no longer has 1511 in it, why are we keeping updates for it? I have been thinking of using Jasons script to also decline all the Windows 10 version 1511 cumulatives in this example as he has a switch for '-DeclineOther' that takes a string so should do this.

Next up is Server 2016.

-Kevin





Tuesday, May 30, 2017

Tabulate OS Counts from Active Directory

For a couple smaller environments without ConfigMgr I wanted to know the versions of Windows, specifically the build # of Windows 10 so I knew counts of each version to target updates via WSUS or manually.

Over on the TechNet Gallery I found something that was real close written by Brian Arnold. The script will look at AD and count all OS versions and email it. I have this running on a monthly basis. Good "Executive Overview".

CountName
4Windows 10 Pro, 10.0 (10586)
3Windows 10 Pro, 10.0 (14393)
1Windows 2000 Professional, 5.0 (2195)
1Windows 7 Professional, 6.1 (7601)
1Windows 7 Ultimate, 6.1 (7601)
3Windows Server 2012 R2 Standard, 6.3 (9600)

I ended up modifying it a little as shown above. The bordering was not working right and I wanted to also get the version for Windows 10 in addition the friendly name. The script will spit to the console as well as email. I'm not that good with PS1 scripts so someone can do way better then my attempt. All credit to Brian.

The scheduled task is pretty simple:

 powershell.exe -ExecutionPolicy Bypass "Path\To\\CountOS\Get-OSCounts.ps1"  


Download

This script is provided as-is, no warranty is provided or implied.The author is NOT responsible for any damages or data loss that may occur through the use of this script.  Always test, test, test before rolling anything into a production environment.

You can obtain my modified version here.