WSUS Cleanup

Despite multiple articles of howto maintain a WSUS database for performance and scalability – there was always a performance issue dragging across which made all the cleanup jobs take forever. An earlier (not able to find it again) Technet Forum post clarified that this was due to all clean-up jobs beeing dependent on the Stored Procedure spDeleteUpdate, and this generates a temporary table that doesnt have an index for the appropiate columns. Unfortunately I werent able to find this post again – but lo, and behold – Microsoft confirmed the behaviour!

The fix is simply to alter the Stored Procedure spDeleteUpdate and append the following line during the creation of the temporary table;

image

EnableFastFirstSignin – howto set it up

EnableFastFirstSignin seems to be a semi-announced feature that is only possible to configure using a Provisioning Package. I think the official documentation states that it is in preview.

But, let’s not care about that

What is it?

This:

Is there  any documentation for this seemingly awesome black magic?

Yes, at Docs@Microsoft

How do I set it up?

Prerequisites

Windows 10 – 1809

Windows ADK – 1809 – Windows Imaging and Design Configuration

Brief overview of what you need todo;

· Configure Provisioning Package

· Generate Provisioning Package

· Install Provisioning Package

Configure / Generate package

Start Windows Imaging and Configuration Designer
Press to create Advanced Provisioning
Define the following settings:
Runtime Settings -> Policies -> Authentication ->

EnableFastFirstSignIn

Select Enabled

Runtime Settings -> SharedPC -> EnabledSharedPCMode

Select TRUE

Runtime Settings -> SharedPC -> AccountManagement -> AccountModel

Select Domain-joined only

Select Export -> Provisioning Package
Enter information regarding name of package, version. Information is arbitrarily set.

Owner is: IT Admin

Rank: 0 or 1

Press Next

Press Next
Select where to save the Provisioing Package

Press Next

Press Build
Press Finish

Install Package

Ensure you are using Windows 10 – 1809
Open an elevated Powershell prompt, using a local administrative account
NOTE: PackagePath is unique to the package name and environment you are working

Execute the following command to install:

Install-ProvisioningPackage -PackagePath “sample-name.ppkg” -QuietInstall

After this the awesome experience should be on whatever endpoint you installed this on. As far as I can tell all that remains is Group Policy Object processing.

What happens in the background?

What does this magical black box of awesomeness actually do in the background? Microsoft has little to reveal, however quite a few people have posted findings on Twitter so far

Trenteye seem to be digging into this further and this is what has been shared so far;

Lenovo and BIOS Settings

Lenovo has provided numerous series of devices, which apparently do not have an aligned standard of handling BIOS / UEFI settings or BIOS / UEFI upgrades. As part of this headache and to ease the burden of jumping between the different device types I started to dive in to harmonize our scripts. As previously written Lenovo has a few odd design decisions – you can for example not reduce security via script. To disable TPM or SecureBoot you would be required to alter this via a keyboard. However, setting the initial password (increasing the security) also requires that you do this by manually typing it into the settings. In addition to the above challenges I reached the following conclusion;

1. It is most likely only relevant to enable any setting.
2. Handling passwords stinks (setting them etc)
3.  Settings between different models in a series can vary by name and vary by name of the possible values to set
4. WMI-classes used differs between series (ThinkStation, ThinkPad and ThinkCentre)

For example, ThinkPad has the GetBIOSSelections class, which can present the different possibilities for a specific setting. However, ThinkCentre does not have this class, but instead present options via a [bracket] indicator in the output of the currentsetting.

How would one deal with all this mess?

Two functions are provided – one for figuring our howto enable a specific setting. The other one for setting each setting. Its ugly – as the more models, bios-versions and whatnot I get a hold of the uglier variations of this I find. The initial attempt was to keep it as generic as possible.The best way to explain what I have todo sometimes;

 

Function Get-LenovoBIOSSettingValue {
    param(
        [Alias("BIOSSetting")]
        [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
        [Array]$Setting
        
    ) 

    begin {
            $complex = $null
            if(Get-WmiObject -namespace root\wmi -List | where { $_.Name -eq "Lenovo_GetBiosSelections"})
            {
                      $complex = $false
                      Write-verbose "Lenovo GetBiosSelections does exist"
            }
            else
            {
                      $complex = $true
                      Write-verbose "Lenovo GetBiosSelections does not exist"
            }
    }

    process {
            foreach ($s in $setting) {
                $value = $null
                $selections = $null
                $findsetting = $null
        
                If ($complex -eq $true) {
                    $findsetting = gwmi -class Lenovo_BiosSetting -namespace root\wmi | Where-Object {$_.CurrentSetting.split(",",[StringSplitOptions]::RemoveEmptyEntries) -like $s} | Select-Object CurrentSetting
                    try {
                        $value = $findsetting.CurrentSetting.split(",",[StringSplitOptions]::RemoveEmptyEntries)[0]
                    }
                    catch {
                       # write-output "Unable to convert $s to value"
                    }                        
                    $r = [regex] '(?<=\[).+?(?=\])'
                    try {

                         [string]$checkvalue = $findsetting.CurrentSetting.split(",",[StringSplitOptions]::RemoveEmptyEntries)[1].split(";",[StringSplitOptions]::RemoveEmptyEntries)[0]
                         $appliedselection = $checkvalue
                        if ([string]$($findsetting.CurrentSetting.split(";",[StringSplitOptions]::RemoveEmptyEntries)[1]) -Match 'Status:ShowOnly') {


                            if($checkvalue -like  "Active*" -or $checkvalue -like  "Enabl*"  -or $checkvalue -like 'Discrete*TPM*' ) 
                            {
                                    if ($value -like 'Security*Chip*') {
                                            $value = $null
                                            $selections = $null
                                            $findsetting = $null
                                    }
                                
                            }
                            else {
                                $value = $null
                                $selections = $null
                                $findsetting = $null
                            }
                        }
                    }
                    catch {
                        #noerror
                    }
                    $selections = ($r.match($findsetting.currentsetting).value).split(":").split(",") | Where-Object {$_ -like "Active*" -or $_ -like "Enabl*" -or $_ -like 'Discrete*TPM*' }
                    
                    if ($selections -is [array] -and $selections -contains 'Enable') {
                        $selections = $selections| Where-Object { $_ -like "Enabl*" }
                    }
    
                }
                else {

                    
                    try {
                        $findsetting = Get-WmiObject -Class Lenovo_BiosSetting -Namespace root\WMI | Where-Object {$_.CurrentSetting -match $s} | Select-Object CurrentSetting
                        $value = $findsetting.currentsetting.split(",")[0]
                        $appliedselection = $findsetting.currentsetting.split(",")[1]
                    }
                    catch {
                        #write-output "Unable to convert $s to value"
                    }

                    $selections = ((gwmi –class Lenovo_GetBiosSelections –namespace root\wmi).GetBiosSelections($value).selections).split(",") | Where-Object {$_ -like "Active*" -or $_ -like "Enabl*" }
                    if ($selections -is [array] -and $selections -contains 'Enable') {
                        $selections = $selections| Where-Object { $_ -like "Enabl*" }
                    }
                }
                if ($value -ne $null -and $selections -ne $null) {
                    $object = New-Object –TypeName PSObject
                    $object | Add-Member –MemberType NoteProperty –Name Setting –Value $($value)
                    $object | Add-Member –MemberType NoteProperty –Name Current –Value $($appliedselection)
                    $object | Add-Member –MemberType NoteProperty –Name Selection –Value $($selections) 
                    $object

                    #write-output "$s provided $value, which can be set to $selections"
                }
            }
    }
}

Function Set-LenovoBIOSSetting {
    param(
        [Alias("BIOSSetting")]
        [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
        [Array]$Setting,
        [Parameter(Position = 1, Mandatory = $false)]
        [String]$Password
        
    )

    begin {
        $wmi = Get-WmiObject -Class Lenovo_SetBiosSetting -Namespace root\wmi -ErrorAction Stop
        $object = $null
        if ($object) {
            Remove-Variable -Name $object
        }
        #$setting
    }

    process {
    
     
        foreach ($s in $setting) {
        #$s
            $result = $null
                 if ($s.Current -ne $s.Selection) {
                       $result = ($wmi.SetBiosSetting("$($s.setting),$($s.selection)$password")).return
                        $object = New-Object –TypeName PSObject
                        $object | Add-Member –MemberType NoteProperty –Name Setting –Value $($s.setting)
                        $object | Add-Member –MemberType NoteProperty –Name Current –Value $($s.selection)
                        $object | Add-Member –MemberType NoteProperty –Name Result –Value $result
                        $object
                 }
                 else {

                        $object = New-Object –TypeName PSObject
                        $object | Add-Member –MemberType NoteProperty –Name Setting –Value $($s.setting)
                        $object | Add-Member –MemberType NoteProperty –Name Current –Value $($s.Current)
                        $object | Add-Member –MemberType NoteProperty –Name Result –Value 'Not updated'
                        $object
                }
         }
    }

}

Supersedence and how it is not a feature

Image result for configuration manager logoSupersedence was supposedly a great new feature for ConfigMgr 2012 (released in… you guessed it..) that was part of the Application Model. App-model held so many promises, however it was actually quite broken until a few service packs and fixes down the road. There are still quite a few defects that just are “known” by people and adds to the general feeling of ConfigMgr beeing complex.

After a twitter-rant on how Microsoft view their support organisation (and anyone paying / using it) as a third-class citizien with no rights – apart from beeing the therapist for whatever sysadmin is on the other end of the call – I became sort of frustrated that the sixth most top voted Uservoice item, something that I previously raised support calls within their organisation aswell as spent better part of a week (at Microsoft Seattle campus) ranting about this specific defect to just about anyone who potentially could reach the actual Product-Caretaker to fix the issue. Microsoft may spring out shiny new things – but it honestly sucks at fixing its products. If its broken – it’s still just plain broken. And even when its fixed – noone tells you its fixed apart from during watercooler talk.

How long has supersedence been broken? Since 2012

Update 2017-12-18:
ConfigMgr TechPreview 1712 was released with a specific fix that would potentially aim to resolve test 1 and 2. If you are on a support agreement it seems that potentially you could drop it and only relay requests via UserVoice.

What is the scenario?

So, a few things to get the setup of what the actual defect is. Supersedence is the intent to connect two Applications within ConfigMgr and inform the system how it should replace the older one/ones. For the defect to show itself the following has to be inplace;

  • Application has to be deployed (directly / indirectly) as Available
  • Application can be targeted to users / computers
  • Application made Available has to replace a previous old version that is installed on the given impacted client

FoxDeploy (Stephen Owen) wrote a great article that sets up the premise for the defect, however as far as I understand his target environment deals only with users (or user collection targeted deployments).  Apart from really sharing a few thoughts, creating a very popular UserVoice-item there are somethings that needs to be retested (previous extensive confirmation of this defect was in the ConfigMgr 2012 era – both for myself and FoxDeploy) with the newer versions of ConfigMgr.

There is no release statement from Microsoft that this has been altered, fixed or improved upon as far as the eye can see.

Based on the above we can also conclude that this is not relevant if;

  • All deployments are Required
  • Supersedence is not used
  • Older versions, now beeing replaced, are not installed on any endpoints

Tests

We will retry the following tests on a ConfigMgr 1702 site. Neither 1706 and 1710 has introduced anything remotely closing to fixing this behaviour and all rumours that people have heard about previous fixes have been pre-CurrentBranch.

Applications used while testing

image

Test 1

  • Application (Adobe Reader DC 18) will supersed an older version (Adobe Reader DC 17)  before creating the deployment
  • A deployment will be created that targets our specific computer that has Adobe Reader DC 17 installed
  • Deployment will target a computer collection
  • Deployment will be set to be made available

Result

When creating the deployment the following new check-boxes will become painfully obvious for the administrator. Pay attention, and ConfigMgr will tell you exactly how you are about to wreak havoc within the environment. Despite that you are saying – please just offer this as a nice-to-have-thingy – there is a greyed out check-box that indicates that a required deployment is technically beeing created.

image

When setting the date/time the deployment will be available – this becomes even more painfully obvious that you are infact creating a required deployment

image

As you might have guessed – as soon as the client receives this new policy – Adobe Acrobat Reader DC 17 is upgraded to Adobe Acrobat Reader DC 18.

Consistent with ConfigMgr 2012 in 2012

Test 2

  • Application (Adobe Reader DC 18) will supersed an older version (Adobe
    Reader DC 17)  after creating the deployment
  • A deployment will be created that targets our specific computer that
    has Adobe Reader DC 17 installed
  • Deployment will target a computer collection
  • Deployment will be set to be made available

Result

As you can imagine the wizard while creating the deployment as Available does not present any information regarding supersedence (as that relationship between the two applications does not exist yet)

image

After creating the supersedence relationship and then opening the properties for the specific Available deployment the following reveals itself. Same as when Foxdeploy concluded the testing early 2016.

image

The scheduling leaves no schedule set for when supersedence should be run.

image

For a brief moment on the client the two versions will reveal themselves

image

Unlike Foxdeploys testing (that were targeted for user collections) – the upgrade will still occur and now its running on the schedule that you can not control. At the latest of the next Application Evaluation cycle – the upgrade will take place.

Consistent with ConfigMgr 2012 in 2012

Test 3

  • Application (Adobe Reader DC 18) will supersed an older version (Adobe
    Reader DC 17) before deployment is created
  • The application themselves will not be deployed, but a Task Sequence containing one step installing Adobe Reader DC 18 will be made available.
  • A deployment (for the task sequence) will be created that targets our specific computer that
    has Adobe Reader DC 17 installed
  • Deployment will target a computer collection
  • Deployment will be set to be made available

Contents of Task Sequence

image

Result

As the task sequence only contains the step to install the one specific application there is no possibility to select that this would be available for PXE-only or other options than the ConfigMgr-client. The Task Sequence is still set to only be available. Per my experience it is also recommended to set the Available time back 1h (or 1 day) to get fairly instant results, otherwise you need to wait a bit before the client start processing the information.

image

As per the hallway chatter this seems to be resolved and the Adobe Acrobat Reader DC 18 does not cause an automatic upgrade.

Not consistent with ConfigMgr 2012 in 2012

Workaround

What have we learnt? Well – anything that depends on supersedence will have a required deployment instead of an available deployment. There are of course three options to deal with this and they are as follows

  • Script it. Powershell Application Deployment Toolkit offers great ways todo this with control.
  • Set up the supersedence relationship, and then create the available deployment and alter the time for the supersedence from ASAP to a date far far into the future. Perhaps so far that you will not be handling the site any more.
  • Follow FoxDeploys guidance for user deployments and create the deployment, and then the supersedence relationship.

 

 

 

Apple iTunes 12.7 and Software License Agreement registry key

Image result for itunesiTunes is the ugly step-child (or the Meg, or the bastard) of the Apple family. As iPhone still remains the phone of choice within my family and quite a few corporations there is still a need from time to time to deal with this excuse of a software.

As a revisit to the previous post where I did track down howto eliminate the end-user requirement to accept the Software License Agreement on corporate installations – there has been some changes in the years past. As the software has decided the progress updates are evil and show no indication of pretty much anything moving this will be how time is spent to track the latest version.

Previously to identify the necessary parts to avoid presenting the SLA for the end-user we required two parts. The first is an identifier for the SLA, and the second was where to put this identifier in the registry.

Registry

Second things first. The registry key still remains the same. The below is the registry key necessary for a Windows 10 x64 installation of Apple iTunes 12.7.

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Apple Computer, Inc.\iTunes]
"SLA"="EA1511"

 

SLA value

To retrieve the value (EA1511) we need a slightly different process than previously used. The value was stored in iTunesPrefs.xml in the earlier versions of iTunes, however that file doesn’t exist anymore and instead we can see the following files

image

Opening all of them will reveal binary or hexadecimal-files and neither will allow us to decipher anything we need. As the SLA is most likely located within the installation folder we can poke around and see if the actual SLA will provide us with anything.

Locating the RTF-file License within en.lproj (for english users) could potentially contain something useful.

image

Opening the document and scrolling around it will reveal the two last lines at the bottom of the document

image

And there it is!

End result

image

ConfigMgr – Howto generate an inbox-backlog

Ever wondered how someone can generate an enormous inbox-backlog for a Configuration Manager site? Apart from not scaling the environment correctly based on the performance of the infrastructure and the number of clients and features its servicing – here are a two methods to bring pretty much everything to a halt.

Server Groups

Server Groups is a feature not yet released for production. It became available in ConfigMgr 1606 and has been worked on since. As one can check it is required to be enabled from Updates and Servicing under Administration in the Feature list, however its been toggled ‘On’ in a number of sites where noone has actively thought about using this feature. Perhaps its toggled on per default?

image

There are a great number of write-ups by independt specialists aswell as an extensive Microsoft documentation.

The Server Group is defined per collection, and there are a few safeguards in place to avoid harm – for example you can’t se this on the generic ConfigMgr collections – such as the All systems

image

Any other collection, regardless of how many members it has, can toggle this on.

image

Locating these collections that are enabled for Server Groups is fairly easy by simplying recursively searching for all collections that has Server Groups to Yes – check the Server Group search criteria.

image

image

Create the backlog

The intent is to control Software Updates and in what order they are applied. If Server Group is enabled for a collection with all (or many devices) and these devices have deadline for a Software Update set – things will happen directly after these deadlines. Each client will effectively send a status update regarding patching status roughly once every 5 minutes (or each minute?)

Now, watch that backlog go…

Relationships – Dependency or Supersedence

Deeply burried with a side-note on howto create Dependencies within a Configuration Manager application is the following limit specified;

In some cases, a deployment type is dependent on a deployment type that also contains dependencies. In this scenario, where a chain of dependencies exists, the maximum number of supported dependencies in the chain is five.
Source: Technet¨

After effective testing the following is concluded – this isn’t a hard-limit that measures the limit of 5 level deep dependency chain – but rather a complete halt of so many things when its to  ‘complex’ and ‘to large’. Or – its not the exact number of 5 that is the limit but rather that depending on loads of factors (complexity of application object, how many, how deep etc)  this will eventually generate a failure of the handling within the Configuration Client or simply to many status messages sent. Also – the limit is very applicable for supersedence.

A chain of 5 dependencies or supersedences is a very good estimate for when things will function, and beyond that you are at the mercy of luck and testing to see if things work.

What adds payload and therefore risk of failure?

  • Requirements
  • Detections
  • Conditions
  • Dependencies
  • Many levels of dependency
  • Supersedence
  • Many levels of supersedence

The more you have of the above the more has to be evaluated and processed. As a consultant I have learned the hardway to avoid using the ‘always’, ‘never’ etc. Usually time is saved if dependencies and supersedence is limited in use. Wrap it in whatever preferred scripting language you have.

Now, if you have neatly packaged and deployed all versions of Java or Adobe for the last 2-3 years – there should be quite a few versions available within ConfigMgr. Using supersedence this is a great way to start generating that backlog;

Create the backlog

Create the latest version and ensure you replace about 8 previous versions in the first level. After the first level there should be roughly 7 deeper levels for each one in the first level. Something in the lines of

image

Aim to deploy the application to any collection that contains many devices and watch that backlog start creeping up…

Why create this havoc?

The intent is not to teach novice users howto wreak havoc within an environment, but rather what potentially could cause a backlog in the ConfigMgr processing and howto identify ill advised practices within a company.

 

Windows 10–1709 and OneDrive UX

OneDrive receives some well deserved attention in every upgrade of Windows 10. The road to an exciting user experience is paved with some hiccups, but once 1709 came around – most of the quirks were sorted out.

OneDrive is still seen in the system tray as two clouds (blue for OneDrive for Business and white for the personal edition). Spotting them in the task manager reveals a different confusion still.

image

image

One of the advocated new improvements is the ability to maintain sparse-files, or pointers, or – well, the point beeing; the file is not on disk and will only be retrieved from the cloud when needed (or requrested). The ability needs toggling under the Settings for each cloud (business or personal) and named Files On-Demand (or this can be enabled via group policy per machine)

image

Technically Files On-Demand is an attribute set for each file. The state is called Pinned or Unpinned and can be toggled via the “attrib” command. No idea why they chose to maintain an older command rather than enabling this via Powershell only.
To make files available offline; attrib -U +P /s

image

The end-user isn’t required to know this and can toggle the options from  right-click option when selecting any file or folder. The names aren’t the same, but rather offer a more end-user friendly name.

image

Applying the Free up space option will clearly show the progress of altering the attribute by saying Applying properties….

image

As a way to educate the user and offer a clear view of the state of any folder (offline, online, issues or syncing) there is a new column added (as opposed to the previous overlay on the icons) named Status. Personally this seems to be an improvement (allows for sorting for example).

image

Remember the system tray icons? A bit more useful this time around – if you left-click them once the following status will be shown.

OneDrive for Business

image

OneDrive

image

The reason for this write-up is based on the experience of migrating all OneDrive content to OneDrive for Business. Odd thing once I was completed. (1) is the OneDrive for Business and (2) is OneDrive. For some reason the consumption of data differs with just above 100gb. Where did this go? The below is from settings of the OneDrive-client.

image

Comparing the folder on disk looks like this shows that both are roughly 237gb – so this seems odd. Verifying online via the Manage Storage button shows some correct numbers and verifiying basic folder structure from the web-interface provides some additional confidence. Accessing the OneDrive for Business via the admin-center for Office 365 and spinning up the Reports for usage – shows that 8mb is currently consumed?

Are there any more ways to confirm how much data I got?

image

As OneDrive for Business is essentially a glorified interface for Sharepoint – there are certain limitations defined. There are restrictions on files both in OneDrive and OneDrive for Business, however OneDrive for Business has far more annoying limitations. Sample output to resolve the 181 conflicts – a number far lower than expected to be honest.

image

Get-FileInfo

Had to retrieve information if a filed was locked and who owned it – so wrote the below Powershell function. Ways to improve would perhaps be to provide parsing of multiple-files.

<?Function Get-FileInfo {
<#
.SYNOPSIS
Retrieves file-information, such as size, name and locks
.DESCRIPTION
Outputs an object with Path, Size, Created on, Last Write Time,
Owner and if the file is locked
.EXAMPLE
Get-FileInfo -File c:\windows\regedit.exe
#>
[CmdletBinding()]
param(
[Parameter(mandatory=$true)]
[string]$File
)
Begin
{
write-verbose "------------------------"
write-verbose "Start of Get-FileInfo"
write-verbose "Computername:  $($env:computername)"
write-verbose "Username: $($env:USERNAME)"
Write-verbose "Validate file $($file)"

if (Test-Path  $($file))
{
Write-Verbose "File exists"
}
else
{
throw-error "File does not exist"
}

}
Process
{

#Retrieve file object
$objfile = Get-ChildItem $file

#check file lock
try { [IO.File]::OpenWrite($objfile).close();$lock = $false }
catch {$lock = $true}

#output object
New-Object PSObject -Property @{
Path = $objFile.fullname
Size = "{0:N2} MB" -f ( $objFile.Length / 1mb )
'Created on' = $objFile.CreationTime
'Last Write Time' = $objFile.LastWriteTime
Owner = (Get-Acl $File).Owner
Lock = $lock

}

}
End
{
write-verbose "End of Get-FileInfo"
write-verbose "------------------------"
}
}

Note to self: WMI does not adhere to RPC-standards

Every single project…. every single firewall guy, and every single requirement list that I had to dig into…

Windows Management Traffic leverages the same ports as RPC traffic (TCP 135 for initial connection, and after that a random port within a defined port-range), however it does not adhere to the RPC specification and will therefore not be correctly identified by any firewall (yes, any firewall) as RPC traffic. Most firewalls tries to dynamically identify the specific port for the session within the dynamic range, however this requires that lots of things are RPC and not MSRPC.

Cisco wrote it pretty clearly;

As Microsoft switched from using pure RPC to use DCOM (ORPC) calls, those non-epm calls will be used more and more. Windows RPC/DCOM services use the RPC Endpoint Mapper to accept initial communications on port 135 and then dynamically transition to ports for the service.

Just open all the high-ports.

Checkpoint statement

Cisco statement

Troubleshoot:

Testing RPC ports with PowerShell (and yes, it’s as much fun as it sounds)

Wireshark-article if you ever need to troubleshoot

 

Software Metering–Checks and balances

Software Metering is a very interesting concept, however after been hit with quite a few issues of ensuring that we can depend on the reliable information within Configuration Manager – here comes an appendix to a great blog-post series provided by Microsoft

Software Metering Deep Dive and Automation Part 1: Use It Or Lose It – The Basics

Software Metering Deep Dive and Automation Part 2: Use It Or Lose It – The Collections

What is the issue?

For many reasons a few perfect storms essentially gave clients that from a far looked OK, but we could not consider their metering data accurate. Either it was blank (not the same as NULL), or it wasn’t good enough for only specific periods which caused them to inaccurately fall in-scope of not using a software at all. All of the reasons are of course part of the ongoing operations to ensure client health. However, users still had software removed and felt the frustration.

Checks and balances

We felt very confident in removing software, deploying accurately to devices which hadn’t use the software – apart from users being impacted by general issues of reporting metering data.

A check is to verify that there is metering data for the period that we want to ensure is software is used within. The following benefits are identified;

  • New devices wouldn’t be targeted until they have metering for the entire period
  • Devices which have black-out periods and fail to report any metering data for an extended period of time are excluded
  • Devices which are plain broken are excluded

Collections

Structure is to create a collection for each time period (month based) that you want to check. The specific example will use the following sample scenario;

  • A device will have the software removed if not used for 3 months (90 days)
  • We will verify that the device have metering data for period 0-30 days, 30-60 days and 60-90 days
  • The most efficient way to run the collection update is actually to have three separate collections
  • Each collection can be limited to the previous one
  • The collection with the deployment targeted can be limited to the final collection

Collection queries;

Metering check day 0 – 30

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
WHERE (DATEDIFF(day,SMS_MonthlyUsageSummary.LastUsage, GETDATE()) < 30
and DATEDIFF(day,SMS_MonthlyUsageSummary.LastUsage, GETDATE()) > 0)

Metering check day 30 – 60

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
WHERE (DATEDIFF(day,SMS_MonthlyUsageSummary.LastUsage, GETDATE()) < 60
and DATEDIFF(day,SMS_MonthlyUsageSummary.LastUsage, GETDATE()) > 30)

Metering check day 60 – 90

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
WHERE (DATEDIFF(day,SMS_MonthlyUsageSummary.LastUsage, GETDATE()) < 90
and DATEDIFF(day,SMS_MonthlyUsageSummary.LastUsage, GETDATE()) > 60)