Inventory % for App-V and ARP

A SQL query that needs to run against the Configuration Manager database and present all software that is installed for a specific collection (App-V or traditionally registered software in Programs and Features)

Will present;

Software Name
Software Version
App-V or ProductCode
# installations
% av devices that have the software installed (in comparision to collection members)
# Collection Members

 

DECLARE @collection VARCHAR(50),
@DisplayName VARCHAR(50);

SET @collection = 'SS100517';
set @DisplayName = 'Adobe Audition%'

select arp.DisplayName0 as 'Name', arp.Version0 as 'Version', arp.ProdID0 as 'GlobalorProd', COUNT(*) as Total, count(distinct arp.resourceid) * 100/
(
SELECT count(distinct ws.resourceid)
from v_ClientCollectionMembers ws
where ws.CollectionID = @collection
) as '%',
(
SELECT count(distinct ws.resourceid)
from v_ClientCollectionMembers ws
where ws.CollectionID = @collection
) as 'Collection Members'
from v_Add_Remove_Programs as arp
inner join v_ClientCollectionMembers as coll on arp.ResourceID = coll.ResourceID
where arp.DisplayName0 LIKE @DisplayName
and  CollectionID = @collection
GROUP BY arp.DisplayName0, arp.Version0, arp.ProdID0
UNION
select appv.Name00 as 'Name', appv.Version00 as 'Version', 'AppV' as 'GlobalorProd', COUNT(*) as Total,
count(distinct appv.machineid) * 100/
(
SELECT count(distinct ws.resourceid)
from v_ClientCollectionMembers ws
where ws.CollectionID = @collection
) as '%',
(
SELECT count(distinct ws.resourceid)
from v_ClientCollectionMembers ws
where ws.CollectionID = @collection
) as 'Collection Members'

from APPV_CLIENT_PACKAGE_DATA as appv
inner join v_ClientCollectionMembers as coll on appv.MachineID = coll.ResourceID
where appv.Name00 LIKE @DisplayName
and  CollectionID = @collection
GROUP BY appv.Name00, appv.Version00, appv.PackageId00

Device, primary user and model

A simple query that will present the following information based on what ConfigMgr has inventoried

Computer-name
Primary User(Domain\username)
Manufacturer
Model

SQL;

select umr.MachineResourceName as 'Device name', umr.UniqueUserName as 'Username',
CASE
when CAST(umsr.SourceID as varchar(20))  = '1' then 'Software Catalog'
when CAST(umsr.SourceID  as varchar(20)) =  '2' then 'Administrator'
when CAST(umsr.SourceID as varchar(20)) =  '3'  then 'User'
when CAST(umsr.SourceID as varchar(20)) =  '4'  then 'Usage agent'
when CAST(umsr.SourceID as varchar(20)) =  '5'  then 'Device Management'
when CAST(umsr.SourceID as varchar(20)) =  '6'  then 'OSD'
when CAST(umsr.SourceID as varchar(20)) =  '7'  then 'Fast Install'
else CAST(umsr.SourceID as varchar(20)) END as 'Source',
cs.Manufacturer0 AS 'Manufacturer',
CASE
when cs.Manufacturer0 = 'Lenovo' Then hwdata.Version0
else cs.Model0
END as Model
from v_UserMachineRelationship as umr
inner join v_UserMachineSourceRelation as umsr on umr.RelationshipResourceID = umsr.RelationshipResourceID
inner JOIN v_GS_COMPUTER_SYSTEM AS cs ON umr.MachineResourceID = cs.ResourceID
left join v_GS_COMPUTER_SYSTEM_PRODUCT as hwdata on umr.MachineResourceID = hwdata.resourceid

Windows 7 x64 and TPM 2.0

If you are using Bitlocker, Configuration Manager 1610 or higher and get a piece of hardware that has TPM 2.0 there are a few more hurdles to get the device encrypted. Coretech has written notes from the field which states the two options going forward. Following their recommendation – lets go down the path of running a device with CSM (UEFI) enabled and installing Windows 7.

Make sure that the device is running in UEFI and is setup as expected. I will leave the actual configuration of BIOS / UEFI out of this – as any device with TPM 2.0 will most likely have a preconfigured settings that are optimal. If not, time to configure for UEFI (without Secure Boot).

UEFI Boot

Once that is completed – create a new group that has the intention to identify if we (if possible) booted with a legacy option (legacy PXE-boot, legacy USB stick etc etc). Validate that its not UEFI-booted and that we have a TPM 2.0 chip (and add a check that we are also intending to install Windows 7 x64).

image

Only two steps are required in the group – format the drive as GPT and then a restart to the boot-image. Suggested format:

image

Preprovision

Once we have ensured that the device is running in UEFI, and not legacy…, and since we all are running a newer ADK then 1511 – we should add three registry keys per Microsoft

Above configuration of the algorithms for Windows 10 (build 1511) is kept as REG_DWORDs under:

HKLM\SOFTWARE\Policies\Microsoft\FVE

Operating System drives: EncryptionMethodWithXtsOs

Fixed Data drives: EncryptionMethodWithXtsFdv

Removable Data drives: EncryptionMethodWithXtsRdv

Hotfix

You can integrate the TPM 2.0 hotfix into the Windows 7, or simply install it after the WIM has been copied onto the machine. It should be installed before MBAM (or just bitlocker) is installed and starts handling the encryption

Task Sequence and shutdown (not reboot) a computer and continue

For some reason there is a requirement to do a computer shutdown (not restart) while running a task sequence, and once the computer starts again there is a need to continue running the task sequence where we left it.

How do you go about that? Let’st start…

image

We need two scripts, a task sequence with the ability to run one script and then to start a task sequence controlled restart.

For testing purposes a networkshare was used instead of leveraging a package, but in real-life and in production – all of the files can be placed in a package and executed from there.
This concept is tested within WinPE (using Winpeutil etc…), but you can most likely adapt it to a Windows installation.

Run Monitor

The ‘Run Monitor’ step will kick off a VB-Script that will start a powershell script – and then exit. Simple enough to start a script, and then allow the task sequence to continue with the next steps

image

VBScript
Runapp "powershell.exe","-noprofile -executionpolicy bypass -file " & GetScriptPatH() & "shutdown.ps1"

Private Function RunApp(AppPath,Switches)
Dim WShell
Dim RunString
Dim RetVal
Dim Success

On Error Resume Next

Set WShell=CreateObject("WScript.Shell")

RunString=Chr(34) &AppPath & Chr(34) & " " & Switches
Retval=WShell.Run(RunString,0,False)

RunApp=Retval

Set WShell=Nothing
End Function

Private Function GetScriptPath
GetScriptPath=Replace(WScript.ScriptFullName,WScript.ScriptName,"")
End Function

The powershell-script (shutdown.ps1) looks as follows;

  1. Create a TS Environment (so we can read variables)
  2. Verify if the variable _SMSTSBootStagePath is set
  3. If the drive-part is longer than a single-letter – we know that the boot-image is prepared and that the reboot countdown has started.
Powershell
$end =$true
write-output "start"

DO
{
start-sleep 2
Get-date
#Remove-Variable -name tsenv -Force -ErrorAction SilentlyContinue
if (!$tsenv) {
try  {
$tsenv = New-Object -COMObject Microsoft.SMS.TSEnvironment
}
catch {
write-output "No TS started yet"
}
}
try  {
$bootpath = $tsenv.Value("_SMSTSBootStagePath") -split ":"
$tsenv.Value("_SMSTSBootStagePath")
if ($bootpath[0].length -gt 1) {
write-output "SMSTSBootStagePath prepped for reboot"
$end = $false
}
}
catch {
write-output "variable not set"
}

} While ($end -eq $true)

start-sleep 5

wpeutil shutdown

Restart

The restart step is fairly generic and you can configure it as you need. A thing to note is that the time-out needs to be higher than the start-sleep within the Powershell-script. As the purpose is to continue within WinPE – the step is configured to start to the boot-image.

image

Lenovo and management of BIOS settings

Lenovo has published an excellent guide for management of their BIOS settings via scripts for the ThinkPad-series. It seems that it does apply for all different series (ThinkCentre, ThinkPad, ThinkStation) and therefore the same methods can be used regardless of the type of device.

However, there are numerous caveats to the documentation and some minor misalignments of naming standards between specific device types – even within the same series of devices.

Supervisor Password

Initial password

Password seems to be quite odd for Lenovo. First of all – password can’t be set the first time around via their WMI interface but requires that someone sets on the device. In addition – the experience has been that depending on the type of keyboard (validated with a Lenovo and an HP set of keyboards) the password might not be set as expected. In the end – we could only validate what the password was (and use the expected password) when set with an HP-keyboard.

Updates settings with password

Once a password is set it becomes a requirement to pass this one when changing any setting, or setting a new password. To pass this one each updated setting requires the password, encoding and keyboard and in addition it is also required when saving the bios settings. One could find many more efficient methods, but this is the way togo about it. After lots of testing – the following methods have been succesful;

Changing a BIOS configuration

$wmi = Get-WmiObject -Class Lenovo_SetBiosSetting -Namespace root\wmi
$wmi.SetBiosSetting("TCG Security Feature,Active,password,ascii,us;")

Note that at the end there is a ;.
Ascii and us is the encoding and the language of the keyboard. This is the most common setup – so lets stick with it.

To save the settings the following command can be issued;

(Get-WmiObject -Class Lenovo_SaveBiosSettings -Namespace root\wmi).SaveBiosSettings(password,ascii,us;)

If you are unsure wether there is a password or not – we can always test and validate. If you configure all settings incorrectly and then try to save without the appropiate password (blank or with the correct password) – all settings are lost.

You can check if a password is set by using the following method;

$password = “,password,ascii,us”
$result = ((Get-WmiObject -Class Lenovo_SaveBiosSettings -Namespace root\wmi).SaveBiosSettings(password,ascii,us)).return
if (!($result –eq "Success")) {
#if the command isn’t successfull we set a blank password
$password = ""
}
if ($result –eq "Success") {
$nopass = $false
}

To avoid writing lots of code once we have identified if a password is in use – we can leverage the $password and append it to every settings.

$wmi.SetBiosSetting("TCG Security Feature,Active$password")

The $nopass can be used to choose decide how we save the settings

if ($nopass -eq $true) {
Get-WmiObject -Class Lenovo_SaveBiosSettings -Namespace root\wmi).SaveBiosSettings()
}
else {
(Get-WmiObject -Class Lenovo_SaveBiosSettings -Namespace root\wmi).SaveBiosSettings($passwordsave)
}
<pre>

Settings

Unlike the harmonized and common way to handle BIOS settings via the WMI interface – settings have a wide spread of possible names and setting options. Quite often similiar enough to cause frustration

An overview of TPM related settings and Secureboot

image

In addition – once these settings are enabled they can’t be disabled. Lenovo has taken a secure-by-default stance and will force someone to physically access the computer to decrease security. As far as their guide states today the following settings can’t be disabled – once they are enabled – via WMI.

SecureBoot
SecureRollbackPrevention
PhysicalPresneceForTpmClear
PhysicalPresenceForTpmProvision

ConfigMgr and “Who updated All Systems–again”?

As this questions shows up at all places for the same reason; Someone pressed the “Updated Membership” on a collection way up in hierarchy of collections. A simple right-click and press – and then accept.

image

The action is simple, however if the collection is ‘far up’ in the hierarchy and have all (or many) collections indirectly connected to it also start to update – an unforgiven workload. Delays, frustration and mayhem will ensue (perhaps a bit over the top here..)

If you are a type of organisation that blames people – well, here you go!

image

Choose to create a new Status Message Query and use the following query to filter the search based on time and only look for specific the refresh action of collections

Query;

select stat.*, ins.*, att1.*, stat.Time 
from SMS_StatusMessage as stat 
left join SMS_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID 
left join SMS_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID 
where stat.MessageType = 768 and 
stat.MessageID = 30104 and 
stat.Time >= ##PRM:SMS_StatusMessage.Time## 
order by stat.Time desc

image

End result – with the user account and collection to blame;

image

ConfigMgr, SQL Availability Groups and upgrades

If leveraging the new great features from SQL Server the last years – certainly the Availability Group have been a tempting way to increase availability for ConfigMgr (despite the fact that everyone prefers the local SQL Server running on the Primary Site server).  Now, as ConfigMgr TechPreview 1706 announced Site Server Role High Availability – separating the site server and the SQL database might be a new preferred method going forward in the future.

Microsoft provided a great step-by-step (2016-05-14) approach on all the steps required to migrate an existing database to a SQL Server Availability Group. As part of this a few specific SQL configurations are stated as a way to ensure an operational database;

We need to Set Trustworthy and enable CLR Integration. Run this against Primary Replica (You can confirm from the AlwaysOn dashboard)

Reviewing the official documentation (2017-05-26) there are however a few additional configurations detailed;

  • CLR Integration must be enabled
  • Max text repl size must be 2147483647
  • The database owner must be the SA account
  • TRUSTWORTY must be ON
  • Service Broker must be enabled

As a great benefit to that article there is a check for all the configuration options that clearly states wether they are correct or not. All of the above, but one, are very much a requirement for basic functionality. The Max text repl size was not stated in the step-by-step guide and is not a requirement for a functional database for normal operations, however during an upgrade it will be immediately detected as a deviation. What does ConfigMgr do with deviations? Well, it tries to fix it of course (don’t mention inbox backlogs or… or…).

An upgrade with settings configured according to the above requirements

image

An upgrade where on setting deviates from the requirements and the SQL database is part of an availability group;

image

The operation cannot be performed on database [CM_P01] because it is invovled in a database mirroring…
ALTER DATABASE [CM_P01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Gives the response: Failed to set database [CM_P01] to SINGLE_USER mode

As ConfigMgr tries to fix the deviation it also tries to set the ConfigMgr database in SINGLE_USER mode, unfortunately that is not possible as long as the database is part of the Availability Group and mirrored. Only way is to configure according to the Microsoft recommend settings outside of ConfigMgr and then retry the upgrade.

Office 365, App-V and Windows 10 – 1607

Oh, the joy of reusing technology. Office 365 leverages the App-V technology, however slightly modified and primarily used as a delivery method with no isolation. App-V in comparision is used more for its isolation and to be honest – not so much for its delivery mechanism.

In addition – since Windows 10 – 1607 the App-V client is now a Windows Feature.

Now, for fun – lets try these steps;

  1. Install Windows 10 – 1607
  2. Install any version of Office 365 ProPlus

Find a Visio drawing and open it. Most likely it will be opened with the Visio Viewer – which is a component part of Office 365 ProPlus. Visio Viewer is just an ActiveX component and will therefore load the drawing inside Internet Explorer 11.

image

let’s add a third-step to this process….

3. Open an elevated Powershell prompt and type Enable-AppV. Press the enter-key
image

Once you attempt to re-open the Visio-drawing you will most likely see a blank Internet Explorer-page instead of the Visio Viewer ActiveX component. The below is from a minimal window.

image

All Microsoft components and suddenly we have a break scenario? The details are actually clarified in a knowledgebase-article relating to Skype for Business, Internet Explorer and App-V;

By default, Explorer.exe and Internet Explorer are listed there. Therefore, when the iexplore.exe process runs, it has the APPVEntsubsystem32.dllloaded in Internet Explorer. When Office C2R detects that APPVEntsubsystem32.dll is loaded into Internet Explorer, it does not load jitv.dll or APPVISVSubsystem32.dll into Internet Explorer. As a result, there is no registry redirection support.
When the JavaScript that’s running in Internet Explorer tries to create any ActiveX objects that are part of the Office C2R package, that operation fails and the Office C2R and SharePoint integration are broken. Office C2R and App-V dynamic virtualization aren’t designed to co-exist; therefore, Office C2R disables Dynamic Virtualization when it detects that AppV client binaries are loaded in Internet Explorer to prevent double hooking.

The article unfortunately provides no fix for a Windows 10 – 1607 unless someone is wanting to decrease the feature set for App-V. Browsing the web allows a little gem to be spotted in the comment section of Dan Gough’s amazing blog – the comment is from an Andrew G:

and also there is a registry key suggested by MS:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ClickToRun\OverRide
keyname=AllowJitvInAppvVirtualizedProcess (reg_dword)
keyvalue=1

Searching for this registry key on google presents only a few results (one spiceworks thread) are found. A hidden gem, I presume?

Add the registry key and immediately the Visio drawing can be opened without issues

image

PXE and notes to self

The joy of supporting PXE-boot in a number of different environments have created a mental note-to-selflist when operating PXE with Configuration Manager. These are my notes, and if they can be of use to anyone else – thats great.

Overview

PXE-boot and its dependencies on operational and a managable network is key and therefore a basic understanding is vital. Coretech has published a great overview explaining PXE and its relationship with DHCP.

Basic setup

Some minor tweaks to get started are of course required even for the not-so-complex environment and 4SysOps has a great guide that details the generic troubleshooting steps on basic configuration.

In essence:

  • An operational Configuration Manager environment is key
      • A Distribution Point for the Configuration Manager site that is setup to handle PXE
      • A possibility to handle the broadcast requests when pressing F12 on the client

The last part (handle broadcast requests) is normally something that is managed by a network operations-team. There are of course (why should there be one?) many different ways to configure this and normally the discussion stands between DHCP Options (not supported by Microsoft) or IP-helpers. TechThoughts has an overview with detailed pros and cons, however clearly states that IP-helpers is the way to go forward.

Challenges

Once you get everything setup – here comes a list in order of likely hood to cause you issues.

What does a client see when pressing F12?

Well, you see on the screen that you pressed F12 and potentially some sort of diagnostic information is presented to you. Is this information useful? Complete? Of debugging-quality?

Most likely not! There are two tools available – one from Citrix (PXEChecker) and one from 2pint (powershell) – which provides great insight into what shows up at your client.

Test TFTP download

Wondering if you have a responsive PXE-server? Follow the previous guide howto test the download of a file

DNS and PXE

As PXE and its reliance on just ip-addresses is heavy – the first question that comes to mind is why DNS is a thing? Well, legacy servers (still supported for the role of Distribution Point) that run Windows Server 2008 R2 may exhaust the available ports if DNS and PXE-role is hosted on the same system.

Resolve the issue by configuring Windows Deployment Services to  dynamically request and allocate ports that are available.

Registry:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\WDSServer\Parameters
DWORD: UDPPortPolicy
Value: 0

PXE and DHCP

If the Distribution Point (with PXE enabled) and the DHCP-server is configured on the same server a few technical configurations are required. Normally these are set up properly, however it can always be a good thing to double-check.

Configure WDS to not use the same ports as DHCP
Registry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WDSServer\Providers\WDSPXE
DWORD: UseDHCPPorts
Value: 0

Configure the DHCP-server to set option 60. Option 60 is intended for the client to know that in addition to the server providing an answer to the request for IP-address, a PXE-server also operates on the same server.

Command-line:
WDSUTIL /Set-Server /UseDHCPPorts:No /DHCPOption60:Yes

It’s slow

ConfigMgr has gradually provided improved ways to make PXE-boot go faster. CCMExec wrote article on howto tweak the configuration for boot-times (RamDiskTFTPBlocksize and RamDiskTFTPWindowSize). The sweetspot seems to be at 16384 / 8

Registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\DP
DWORD: RamDiskTFTPWindowSize
Value: 8
DWORD: RamDiskTFTPBlockSize
Value: 16384 (1456 allows for higher compatibility)

It starts to download but freezes (0xC0000001)

If the error is prevalent and not related to a specific device or model – most likely this relates to issues with something between the server and the client not beeing able to handle the default size of 1456 in block size. Windows Server 2003 was set to use a block size of 512, and since Windows Server 2008 this was bumped to 1456. If this fails – set it to 512 and verify compatibility. Previously the possibility was tested with 1432 and some success has been confirmed.

Registry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\WDSServer\Providers\WDSTFTP
DWORD: MaximumBlockSize:
Value: 512 (or 1432)

Windows 10–1607–LinkedConnections

So, lots of things have to fall into place for this to take effect, but here we go….

If you have UAC enabled (in some form or another) and have users that are able to run in two different contexts (elevated and non-elevated) with the same user account – it means that they are local administrators on the device. It also means…

.. that if they map a network drive in one context it will not be visible in the other context by default.

Microsoft created the great Linked Connection which will automatically allow the mapping of a network drive to be visible in both contexts.

Well, since roughly October 2016 this stopped working if you were running Windows 10 – 1607 with the October 2016 patch, and the user was local administrator on the device and mapped a drive in one context and also expected the EnableLinkedConnections to provide a smooth user experience – it simply stopped working.

To add insult to injury – if you map a drive within a login-script; the default context is elevated so the non-elevated process (such as explorer.exe) will not show the mapped drive.

As far as I can tell this is not listed as a known defect. Therefore its not listed as a fixed defect in the March 2017 patch for Windows (so, october.. november… december.. 6 months in total in a non-working state). According to Google (oh, what a reliable source for anything named Microsoft) Windows 10 – 1607 was noted as Business Ready (Current Business Branch) since 29 November 2016.

image

Ready for business, eh? Well – at least one step further….