Posts

Configuration Manager Queries

One of the best uses of Configuration Manager is its ability to query and actually make use of the data in a dynamic and automated way.

This is just a quick post with a few Configuration Manager WQL queries I have created or collected that may be useful to someone.

Feel free to use them, change them to suit your needs and share your own!

If you don’t know how to use these – check the bottom of the post for links to TechNet.

Collection Based Dynamic Rules:

Get Windows 7 Enterprise x64 Devices matching a specific naming convention:
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 inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows 7 Enterprise" and SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" and SMS_G_System_SYSTEM.Name like "HOSTNAME%"
Devices in a specific OU:
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.SystemOUName = "Atlantis.local/Atlantis/WORKSTATIONS/PRODUCTION"
Devices that have a specific Hotfix installed:
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 inner join SMS_G_System_QUICK_FIX_ENGINEERING on SMS_G_System_QUICK_FIX_ENGINEERING.ResourceId = SMS_R_System.ResourceId where SMS_G_System_QUICK_FIX_ENGINEERING.HotFixID = "KB2520155"
Devices which are Bitlocker encrypted:
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 inner join SMS_G_System_BITLOCKER_DETAILS on SMS_G_System_BITLOCKER_DETAILS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_BITLOCKER_DETAILS.ProtectionStatus = 1
Devices which have ran a Software Metering Rule in the last x60 days:
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 inner join SMS_MonthlyUsageSummary on SMS_R_SYSTEM.ResourceID = SMS_MonthlyUsageSummary.ResourceID    INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFile.MeteredFileID    WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 60  AND SMS_MeteredFiles.RuleID = 16777421
Devices that have Visual Studio Premium 2013 installed:
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 inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Visual Studio Premium 2013" order by SMS_R_System.Name
Devices that are a member of a specific Active Directory group:
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.SecurityGroupName = 'ATLANTIS\\GROUPNAME'

Query Based Dynamic Rules:

Devices which are Bitlocker encrypted:
select SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_R_System.LastLogonUserName from  SMS_R_System inner join SMS_G_System_BITLOCKER_DETAILS on SMS_G_System_BITLOCKER_DETAILS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_BITLOCKER_DETAILS.ProtectionStatus = 1 order by SMS_R_System.Name
Get users Primary devices from User based group:
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, SMS_R_User.UniqueUserName
FROM SMS_R_System
JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName
JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
Where SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "ATLANTIS\\UsersGroupName")
Show Devices and Users of a limited Collection:
select distinct SMS_R_System.LastLogonUserName, SMS_R_System.Name, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonTimestamp, SMS_R_System.IPAddresses from  SMS_R_System order by SMS_R_System.IPAddresses
Get Identical MAC addresses:
select distinct *  from  SMS_R_System where SMS_R_System.MACAddresses = "50:1a:c5:ff:10:88"
Get a list of hardware Models that exist:
select distinct SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
Get a list of Webcam models:
select SMS_R_System.Name, SMS_G_System_USB_DEVICE.Caption from  SMS_R_System inner join SMS_G_System_USB_DEVICE on SMS_G_System_USB_DEVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_USB_DEVICE.Caption like "%CAM%" order by SMS_R_System.Name

Resources:

How to Create Queries in Configuration Manager – https://technet.microsoft.com/en-us/library/gg712323.aspx

How to Create Collections in Configuration Manager – https://technet.microsoft.com/en-us/library/gg712295.aspx

Configuration Manager – Does not have permission to update the prereq or state flag of the package

When attempting an upgrade of System Center Configuration Manager v1511 to 1606 I ran into issues relating to the permissions of the Security Scope in Configuration Manager under the account I was doing the upgrade under.

I also had issues enabling Automatic Client upgrade which was grayed out– after some research I found that it was due to the original Full Administrator account which was used by a contractor to do the SCCM environment installation had been deleted from Active Directory and had explicit ownership of All Scopes and Collections which was fixed by taking ownership of the Scope and Collections below.

ConfigMgr Error Object:instance of SMS_ExtendedStatus{ Description = “User \”DOMAIN\\User\” does not have permission to update the prereq or state flag of the package. “;               ErrorCode = 1112017920;      File = “e:\\nts_sccm_release\\sms\\siteserver\\sdk_provider\\smsprov\\sspupdatepackages.cpp”;           Line = 435;              ObjectInfo = “2”;     Operation = “ExecMethod”;        ParameterInfo = “SMS_CM_UpdatePackages.PackageGuid=\”0D256560-ED2C-45B5-8D75-4D38AB3F758C\””;           ProviderName = “WinMgmt”;          StatusCode = 2147749889;};

ConfigMgr Error Object

In order to resolve this issue – I needed to make Changes to the Configuration Manager (SCCM) SQL database. I don’t take responsibility for any damage this may cause and of course highly recommend doing this in Test first, and of course making sure a SQL backup has been done before hand.

1. Open SQL Management Studio with an account that has sysadmin permissions on your SCCM database – and connect to it.

2. Expand Databases, and locate your SCCM database. Right click your database and select New Query

3. Type in or Copy the following Query and Execute it:

select *from RBAC_ADMINS
4. You should now get all the specified Administrators for SCCM role based Full Administrator group; this includes LogonName, DisplayName and their CreatedBy and Created Date

5. Now we need to set the correct permissions on the Security Scope – find the account you want to make Full Administrator (best practice would indicate – this should be an Active Directory group, which your account is then a member of to avoid future problems) and note down the AdminID of your account

6. Clear the Query box and type in:

Update v_securedScopePermissions Set categoryid=‘sms00all’ where adminid=<ADMINID>

7. Replace <ADMINID> with the number of your account, Admin ID without the <>.

8. When you are ready to make the change, click Execute

9. You have now successfully gained Full Administrator permissions over all Scopes and Permissions. Close the SCCM console and relaunch and you should now be able to do the upgrade or enable Automatic client upgrade.

Windows Update Task Sequence (Patching) – SCCM 2012 R2

With monthly server patching, the process is currently manual due to the number of clusters and very application specific servers that is patched – including an issue with failed updates caused by Trend Officescan – and an issue that has been done manually for months.

It was time to automate this process – and without Orchestrator or SMA I had to use what I already had – a SCCM 2012 R2 Infrastructure, and the use of the Task Sequence and PowerShell.

WinUpdate TS TS (Patching)

The Windows Update Task Sequence process goes like this (updates are deployed to the servers as Available): Disables Trend OfficeScan Start-up type to: Disabled, run a Scheduled Task on the server (this could be emailing a business user notifying their server is going down for patching or shutting down an application – this is intended to be Server SPECIFIC so the task sequence doesn’t need to be modified for every new server getting patched), Restart the computer (this is done to make sure OfficeScan is not running and make sure the server is in a clean state for patching), and begin the patching process (see more information on the steps below).

Task Sequence Patching Steps are as follows:

Disable – Trend Office scan Services

This calls a PowerShell script which changes the Startup Type of: Office Scan NT Real-time Scan and Office Scan NT Listener services to Disable. This is changed to prevent the Trend Antivirus solution from interfering with the download and installation of Software Updates. Note: Some servers encountered issues stopping the Trend service, the restart step after this stops the Trend service from starting

Get-Service tmlisten, ntrtscan | Set-Service -StartupType disabled

Run SCHTask

This step starts a Scheduled Task “PreShutdown” that has been setup on the deployed server. This scheduled task allows for server based automation (application shutdown, business communication etc) and is specific to the server. This is a Command Line Step.

schtasks /run /TN "\WinUpdate\PreShutdown"

Restart Computer

This step counts down for 60 seconds and notifies the user “This server is undergoing Windows patching. Please save your work and log off” before then Restarting the computer

Scan for Updates |

This step does a WMI call to do a Software Update re-evaluation to determine if there are any new Windows Updates that are required by the system

WMIC /namespace:\\root\ccm path sms_client CALL TriggerSchedule "{00000000-0000-0000-0000-000000000113}" /NOINTERACTIVE

Wait for the Scan to Finish |

This step is a PowerShell command to sleep the system for 30 seconds. This step is set to allow the Software Update re-evaluation cycle from the previous step to complete

Powershell.exe -command start-sleep 30

Install Software Updates

Installs all required and available Windows Updates on the Windows server

Restart Computer

This step Restarts the computer after the first batch of patches have been installed

Scan for Updates ||

This step does a WMI call to do a Software Update re-evaluation to determine if there are any new Windows Updates that are required by the system

WMIC /namespace:\\root\ccm path sms_client CALL TriggerSchedule "{00000000-0000-0000-0000-000000000113}" /NOINTERACTIVE

Wait for the Scan to Finish ||

This step is a PowerShell command to sleep the system for 30 seconds. This step is set to allow the Software Update re-evaluation cycle from the previous step to complete

Powershell.exe -command start-sleep 30

Install Software Updates

Installs all required and available Windows Updates on the Windows server

Restart Computer

This step Restarts the computer after the first batch of patches have been installed

Scan for Updates |||

This step does a WMI call to do a Software Update re-evaluation to determine if there are any new Windows Updates that are required by the system

WMIC /namespace:\\root\ccm path sms_client CALL TriggerSchedule "{00000000-0000-0000-0000-000000000113}" /NOINTERACTIVE

Wait for the Scan to Finish |||

This step is a PowerShell command to sleep the system for 30 seconds. This step is set to allow the Software Update re-evaluation cycle from the previous step to complete

Powershell.exe -command start-sleep 30

Install Software Updates

Installs all required and available Windows Updates on the Windows server

Restart Computer

This step Restarts the computer after the first batch of patches have been installed

Scan for Updates |V

This step does a WMI call to do a Software Update re-evaluation to determine if there are any new Windows Updates that are required by the system

WMIC /namespace:\\root\ccm path sms_client CALL TriggerSchedule "{00000000-0000-0000-0000-000000000113}" /NOINTERACTIVE

Wait for the Scan to Finish |V

This step is a PowerShell command to sleep the system for 30 seconds. This step is set to allow the Software Update re-evaluation cycle from the previous step to complete

Powershell.exe -command start-sleep 30

Install Software Updates

Installs all required and available Windows Updates on the Windows server

Restart Computer

This step Restarts the computer after the first batch of patches have been installed

Software Update Deployment Re-Eval

This step forces the SCCM agent to “check in” and run a Compliance check on the Software Update deployment allowing for SCCM have accurate Compliance data at the end of the Task Sequence

WMIC /namespace:\\root\ccm path sms_client CALL TriggerSchedule "{00000000-0000-0000-0000-000000000114}" /NOINTERACTIVE

Enable – Trend Office scan Services

This calls a PowerShell script which changes the Startup Type of: Office Scan NT Real-time Scan and Office Scan NT Listener services to Enable. This PowerShell script also Starts the services.

Get-Service tmlisten, ntrtscan |
Set-Service -StartupType automatic -PassThru |
Start-Service

Importing drivers into System Center Configuration Manager 2012 R2

Importing drivers into System Center Configuration Manager R2 (SCCM) is a task I do at least once a month as new models are introduced into my work environment.

In this guide I am going to guide you through a step by step method of exporting drivers from an already existing build (ie vendor supplied, OEM) or from a driver package supplied for operating system deployment purposes from a vendor, such as Hewlett Packard.

Read more

.NET Framework not installing through SCCM

  1. Click Start
  2. Click Run and type in: services.msc
  3. Stop the Automatic Update service
  4. Navigate to: C:\Windows
  5. Delete the: SoftwareDistribution folder
  6. Start the Automatic Update service
  7. Double click on the SCCM Update icon in the notification tray and select Install & Install now.

Remove unused user profiles on a remote Windows workstation

Note: The intended guide for this audience is a Help Desk or Service Desk – this is intended for a domain setup with the user having local administration rights on the workstation.

  1. First, you need to download tool called: Delprof2
    (this is a remake of the Microsoft Delprof utility that Microsoft had dropped support & updates for).
  2. Once downloaded open My Computer/Computer and navigate to c:\Windows\System32 on your local machine.
  3. Extract the delprof2 zip file & folder and copy DelProf2.exe to the folder you opened earlier – c:\Windows\System32
  4. Now – open command prompt – click Start, Run and type in cmd and press Enter (For Windows 7 workstations – you can type Command in the search field or press the Windows Key + R to open the run dialog box).
  5. REMEMBER to make sure the profiles are backed up first – this will completely remove the local cache – use the syntax: delprof2 /u /r /c:computername and press Enter. This will connect to the remote workstation you specified in the “computername” field and remove all locally cached profiles that are not being used. It will also clear up remote registry entries making this a useful & easy tool for clearing up Roaming Profiles in Windows 7-10.

You can also run: delprof2 /u /d:30 /c:computername

Note: What I have done in the past is create a batch script that will clear up remote profiles from workstations & add this to a scheduled task – in conjunction with SCCM disk reporting this is useful for keeping on top of workstation’s HDD space.