Tag Archives: powershell

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…


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


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


Set WShell=Nothing
End Function

Private Function GetScriptPath
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.
$end =$true
write-output "start"

start-sleep 2
#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 ":"
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


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.


Copy a ConfigMgr Application DeploymentType

A small function inspired by Fred Bainbridges post on howto append an OS requirement to a deployment type. The purpose of the function is to copy the Deploymenttype within an application, but if someone feels like a spending a few hours to rewrite it to copy between different applications that could possible work aswell.


function Copy-CMAppDT {
Copy a single Deployment Type within an application
This will create a copy of a DeploymentType, with the lowest priority and the name specified
Copy-CMAppDT -appName "PingKing 2.0.0" -DeploymentType "PingKing 2.0.0" -newDTname "PingKing Updated" -siteCode P01 -siteServer CM01
This is the name of the configmgr application that has the deployment type. This accepts input from pipeline.
.PARAMETER DeploymentType
This is the name of the Deployment Type that you want to copy.
This is the name of the new DeploymentType.
This the ConfigMgr site code you are working with. Defaults to LAB
.PARAMETER siteServer
This the site server you are going to working with.  WMI calls are made to this server.  It is most likely your primary site server.
param (
$siteCode = "LAB",
$siteServer = "cm01.cm.lab"
begin {
write-verbose "Import module"
import-module 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1' -force #make this work for you
write-verbose "Connect to Provider and change location"
if ((get-psdrive $sitecode -erroraction SilentlyContinue | measure).Count -ne 1) {
new-psdrive -Name $SiteCode -PSProvider "AdminUI.PS.Provider\CMSite" -Root $SiteServer
write-verbose "Connect to the default scope"
try {
$connectionManager = New-Object Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlConnectionManager
$connectionManager.Connect($siteServer) | Out-Null
[Microsoft.ConfigurationManagement.ApplicationManagement.NamedObject]::DefaultScope = [Microsoft.ConfigurationManagement.AdminConsole.AppManFoundation.ApplicationFactory]::GetAuthoringScope($connectionManager)
catch {
throw-error "$error[0]"
write-verbose "Set location $sitecode"
set-location $sitecode`:


process {
write-verbose "Get Application $appName"
try {
$Appdt = Get-CMApplication -Name $appName
catch {
throw "Unable to get $appName - $error[0]"

$xml = [Microsoft.ConfigurationManagement.ApplicationManagement.Serialization.SccmSerializer]::DeserializeFromString($appdt.SDMPackageXML,$True)

$numDTS = $xml.DeploymentTypes.count
write-verbose "Number of DT: $numDTS"
$dts = $xml.DeploymentTypes

foreach ($dt in $dts)
if ($dt.title -eq $DeploymentType ) {
write-verbose "Found DT $deploymenttype"
$newDeploymentType = $dt.Copy()
write-verbose "Set new DT name $newDTname"
$newDeploymentType.Title = $newDTname

if ($newDeploymentType.GetType().name -eq 'DeploymentType') {

write-verbose "New DT created"

write-verbose "Commit to AppObject"
$UpdatedXML = [Microsoft.ConfigurationManagement.ApplicationManagement.Serialization.SccmSerializer]::SerializeToString($XML, $True)
$appdt.SDMPackageXML = $UpdatedXML
Set-CMApplication -InputObject $appDT
else {
write-error "No DeploymentType $newDTname located"

write-verbose "Return to c:"
set-location c:

ConfigMgr and a backlog in distributions


Do you have a primary site and a few secondary sites in ConfigMgr 2012+?

Do you schedule the legacy Package format to update on a schedule?


Do you have a backlog in the distribution manager?

Well, so far this is known (by Microsoft) defect that apparently is yet to be fixed (until 1606 – nothing confirmed beyond that)


If you review the database where ConfigMgr resides you can see that there is a constant growing amount of DistributionJobs. Sample query to get an overview;

use <database>
select COUNT(*) from distributionjobs

The problem grows the more packages you have set to update on a schedule. The frequency of the schedule is not relevant, the package will loop into a forever updating loop. Most likely the primary site will handle this efficiently, however the sending to secondary sites will cause a backlog that is not just an annoyance but causing severe problems as the backlog will continue to grow.

Repeating this: The frequency of the schedule is not relevant. Just check the above checkbox and the issue will occur.

SQL query to locate relevant packages

use <database>
select pkg.PkgID, pkg.Manufacturer, pkg.Name, pkg.Version, pkg.Language, pkg.RefreshSchedule from SMSPackages as pkg
where datalength(pkg.RefreshSchedule) !=0


Easy – uncheck all these check-boxes that updates packages. If you still want to update packages on a schedule use a powershell script to trigger the update and use the task scheduler to run the update.

Run the command-line;

powershell -executionpolicy bypass -file SCCM.UpdatePkg.ps1 -packageid <PACKAGEID>

(I honestly don’t know if I have stolen / copied this from somewhere – if I have give me a ping and I will remove this)

# Created on: 2014-10-28 15:06
# Created by: Nicke Källén
# Organization: Applepie.se
# Filename: SCCM.UpdatePkg.ps1

Function Invoke-CMPackageUpdate
[Parameter(Mandatory=$True,HelpMessage="Please Enter Primary Server Site code")]
[Parameter(Mandatory=$True,HelpMessage="Please Enter Primary Server Name")]
[Parameter(Mandatory=$True,HelpMessage="Please Enter Package/Application ID")]

$PackageClass = [wmiclass] "\\$($siteserver)\root\sms\site_$($sitecode):SMS_Package"
$newPackage = $PackageClass.CreateInstance()

$newPackage.PackageID = $PackageID



Invoke-CMPackageUpdate -SiteCode <SITECODE> -SiteServer <SERVER> -PackageID $packageid

Parallels Software Update Point–selfsigned certificate

As a continuation of the previous post on howto setup the Parallels Software Update Point (introduced in Parallels Mac Management for SCCM 4.5) – here comes a minor hack howto enable WSUS for selfsigned certificates and leverage this within Parallels SUP

Step 1.

Enable Selfsigned certificates for WSUS

Set the following registry key

HKLM\Software\Microsoft\Update Services\Server\Setup
DWORD: EnableSelfSignedCertificates – 1

Step 2

Open certmgr.msc where WSUS is installed and export the WSUS selfsigned certificate

Export the WSUS Publishers Self-signed certificate from Trusted publishers to a file. Remember to choose to export the private key…


…and all the extended properties…


… and set a password…


Step 3

Run some code provided by Parallels to set the certificate you just exported as the signing certificate. Replace CERTFILE and CERTPW


$updateServer = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()

$config = $updateServer.GetConfiguration()

$config.SetSigningCertificate("CERTFILE", "CERTPW")

Step 4.

Complete the setup wizard. As you already followed all the previous steps.


App-V 5 and publishing error code: 040000002C.

A minor defect that causes a publishing failure for any packages (only tested for publishing towards a user though. The error code looks like this;

Publish-AppvClientPackage : Application Virtualization Service failed to
complete requested operation.
Operation attempted: Publish AppV Package.
AppV Error Code: 040000002C.
Error module: Virtualization Manager. Internal error detail: 4FC086040000002C.

There seems to already be a few discussions online that assists in resolving the with a few different methods – one seems to suggest to delete a registry key and there is a one that contains a more granular approach by resetting the registry values under LocalVFSSecuredFolders.

A correct view is that each SID under this registry key references the %USERPROFILE%.


and incorrect (and the cause of the error) references the Default-user profile


A quick script (which you can wrap in a Compliance Item or a script – or whatever the preference is..) to remediate this. The actual fix (Set-ItemProperty) is prefixed with # – please test it before you deploy it.

$return = 0
$users = ($k = gi HKLM:\SOFTWARE\Microsoft\AppV\client\Virtualization\LocalVFSSecuredUsers).GetValueNames() | % {

New-Object PSObject -Property @{

Name = $_

Type = $k.GetValueKind($_)

Value = $k.GetValue($_)

} | select Name, Type, Value


foreach ($u in $users) {
if ($u.value -eq 'c:\users\Default\AppData\Local\Microsoft\AppV\Client\VFS') {
$return = 1
#Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\AppV\client\Virtualization\LocalVFSSecuredUsers' -Name $($u.Name) -value '%USERPROFILE%\AppData\Local\Microsoft\AppV\Client\VFS'



Per the above forum post this should be resolved within App-V 5.0 SP3, however I have still seen minor occurances for later releases – so I wouldn’t call that a  confirmed fix.

Office 365 and its import service

Office 365 is the cloud service with a major adoption. One part of this is getting the on-premises Exchange-servers to be removed, and instead leveraging the Outlook Online provided service. The typically increase in allowed mailbox size is a big selling point, but additional benefits are added every day.

Migration of PST

The increased mailbox size does start the discussion of howto eliminate all PST-files spread among all the local client harddrives and the file-servers in an organization. Microsoft has offered the PST Capture tool (scan all devices, locate all PSTs and import them), and as of last year (2015) the Import PST Files to Office 365 was a way to allow sysadmins to perform a more controlled (batch) upload of files.   As always, the end-users can migrate the data via Outlook.

None of these ways are “great”. The PST Capture Tool has a great process of collecting files and dumping them, but its essentially a gather tool that will without any intelligence of what the user wants and then dump anything it can find into a mailbox.

The newly arrived Import PST file-service is a batch-management tool that seems to target the admins that has a bunch of files at a single time (or potentially a few times) to upload. Options are either to upload this into an Office 365 managed Azure Storage Space or simply ship a hard-drive with a collection of files.
There are a few people who have explained that the Office 365 Import service has a powershell interface, unfortunately its not documented and Microsoft support does not acknowledge that it exists.

In addition to the the above options provided by Microsoft there are a few third-party options – such as MessageOps.

End-user driven migration

To make something that is end-user friendly a bit of automation is required of the above tools. Currently a dropbox (a folder where users can dump the PST-files) has been designed, however inorder to get that working there quite a few hurdles that someone has to overcome inorder to produce any type of favourable result. The below notes are for my own memory…

Office 365 Import Service

As the Office 365 Import Service is a Microsoft supported tool it was it was considered the most reliable option of all of the above. Requiring Outlook for end-users to migrate the data seemed to be a high-cost and not very friendly solution. The PST Capture tool were most likely to migrate data which wasn’t relevant to the user, and the risk was of course that something was missed in the process. Third-party options inccured additional licensing cost (ontop of any Office 365 licensing) and was therefore discarded early in the process.

Account requirements

To initiate anything for the Office 365 PST Import service you are required to have certain permissions, aswell as only leveraging simple authentication if there is an intent to automate the process. If any type of multifactor authentication is enabled the ability to connect to the Office 365 via Powershell session is disabled.image


A service account has to be setup that has the role Mailbox Import Export assigned to it. This isn’t directly granted any set of permissions so its recommended to create a new group, assign the role to it and make the service-account a member of the group.

It also seems that to be able to access the Office 365 PST Import Service from the portal one has to be a Global Admin aswell. Powershell cmdlets are only available once the Role Mailbox Import Export has been assigned.

Storage (Azure)

Office 365 Import Service offers to setup a Azure Storage Space for the tenant, and will provide the Shared Access Signature required to upload files (if using the network upload) or a storage key if using the option to shipping a harddrive. To leverage any kind of automation the only accessible path for the Office 365 PST Import service is the blob located on an Azure Storage Space. It seems that sometime in March (2016) the previous method of using the storage key to generate a Shared Access Signature (SAS) to allow for read-operations for the Import Service (technically this is performed by the Mailbox Replication Service provided by Office 365) was discontinued. One can find a storage key for the option to send in a hard-drive, however that seems to not leverage the same upload space as the network upload and therefore the storage key can’t be used.

Fortunately enough the option is only requiring an Azure Storage Space which can be provided via a normal Azure-subscription. Setup a Blob in an Azure Storage Space, and immediately you have access to the storage space. Once the Storage Space is setup you can retrieve the storage key by locating the key-icon


There will be two keys which you can leverage.


To generate a Shared Access Signature (needed for the automation part) you can download the Azure Storage Explorer 6. The tool allows a quick and easy way to view whats in a Blob on the Storage Space, aswell as generating the SAS-key.

Once you start the tool choose to add your storage space with the storage key above. Remember to check HTTPS.


Once you have connected to your Storage Space, choose to create a new Blob (with no anonymous access). Once this is created you can press Security to start generating the SAS-key.


Generate the signature by selecting a start-date (keep track of what timezone you are in) and the end-date. These dates will set the validity for the period of your SAS-key. Remember to define the actions you want to allow. To upload files you need to allow write, and to use the SAS-key for importing the files you need to allow read. There is the possibility to generate multiple SAS-keys and use them for different parts of the process.


A SAS-key are built of multiple parts – here comes a brief explanation;

#sv = storage services version; 2014-02-14
#sr  = storage resource; b (blob), c (container)
#sig = signature
#st = start time; 2016-02-01T13%3A30%3A00Z
#se = expiration time; 2016-02-09T13%3A30%3A00Z
#sp = permissions; rw (read,write)





Copy files to Azure Storage Space

There are multiple ways to copy files to any Azure Storage Space. You can use the Azure Storage Explorer 6 that was used to generate the SAS-key. Someone has provided a GUI for AZCopy command-line tool, but for automation the command-line usage for AZCopy is the route to go. Microsoft has written an excellent guide for this which doesn’t need any additional information.

Connecting to Office 365

Managing Office 365 for any type of automated manage is performed via a PSSession (PowerShell Session). A PSSession will import all available cmdlets from Office 365. As you can imagine quite a few are similar to Exchange, and it may therefore provide some overlap. To avoid confusion the recommended approach is to append a prefix for all cmdlets from the session which can be defined when the session is imported. This is a sample script that will provide the username and password that is required to connect to, configure the proxy-options for the Powershell session and setup the session with O365.

$password = ConvertTo-SecureString "password" -AsPlainText -Force
$userid = "name-admin@company.onmicrosoft.com" 
$cred = New-Object System.Management.Automation.PSCredential $userid,$password 
$proxyOptions = New-PSSessionOption -ProxyAccessType IEConfig -ProxyAuthentication Negotiate -OperationTimeout 360000
$global:session365 = New-PSSession -configurationname Microsoft.Exchange -connectionuri https://ps.outlook.com/powershell/ -credential $cred -authentication Basic -AllowRedirection -SessionOption $proxyOptions
Import-PSSession $global:session365 -Prefix  O365


Once the session is started the modules are imported with the prefix O365, as an example commands go from:





Using the Import-service via Powershell

As noticed the Office 365 Import service is a GUI only approach that is not supported for automation. That beeing said there are options to start this via Powershell. Multiple blog-posts are documenting the New-MailboxImportRequest cmdlet (and with the prefix its now: New-O365MailboxImportRequest), however Microsoft support will barely acknowledge its existance.

As long as you have the previous stated account permissions assigned (Mailbox Import Export Role) the cmdlet will be available and can be used.

For Office 365 the only supported source is an Azure Storage Space. The import-service is creating one for you, however today (2016-05-12) we are unable to create the Shared Access Signature to allow the automation part use that Storage Space. January 2016 this doesn’t seem to be the case and therefore we can assume that potentially this will change in the future.

Below command-line will allow you to start an import. If you receive the error 404 most likely there is an bad path to the file, and a result of 403 most likely is a bad SAS-key.

Remember: The O365 is the prefix we choose to use when running Import-PSSession. The actual cmdlet is New-MailboxImportRequest

New-o365MailboxImportRequest -Mailbox user@mailbox.com -AzureBlobStorageAccountUri https://yourstorage.blob.core.windows.net/folder/User/test.pst -BadItemLimit unlimited -AcceptLargeDataLoss –AzureSharedAccessSignatureToken “?sv=2012-02-12&se=9999-12-31T23%3A59%3A59Z&sr=c&si=IngestionSasForAzCopy201601121920498117&sig=Vt5S4hVzlzMcBkuH8bH711atBffdrOS72TlV1mNdORg%3D" -TargetRootFolder Nameoffolderinmailbox


Retrieving statistics

Once the import is started it fires off and actually goes through pretty quickly. As you can imagine the results can be retrieved by using Get-O365MailboxImportRequest and Get-O365MailboxImportRequestStatistics. One oddity was that the pipe of passing on Get-O365MailboxImportRequest to the Get-O365MailboxImportRequestStatistics didn’t work as expected. Apparently the required identifier is named Identity and it actually requests the RequestGuid.

Sample loop;

$mbxreqs = Get-O365MailboxImportRequest
foreach ($mbx in $mbxreqs) {
$mbxstat = Get-O365MailboxImportRequestStatistics -Identity $mbx.RequestGuid
$mbxstat | Select-Object TargetAlias,Name,targetrootfolder, estimatedtransfersize,status, azureblobstorageaccounturi,StartTimeStamp,CompletionTimeStamp,FailureTimeStamp, identity

The above data are things which was useful for a brief overview. Sometimes you can manage with the Get-O365MailboxImportRequest.

Cleanup of Azure Storage Space

What does not happen automatically (well, nothing in this process happens automatically) is the removal of the PST-files uploaded to the Azure Storage Space. Having the users PST-files located in a Storage Space will consume resources (and money), aswell as the user might be a bit uncomfortable about it. As always the attempt is to automate this process. To retrieve the cmdlets for managing the Azure Storage Space (remember, multiple ways to handle this. AZCopy is a single-purpose tool) you need to download Azure Powershell. Microsoft again has an excellent guide howto get started. What would be even faster is if all these services could provide a common approach of management. For Office 365 you import a session, but for Azure you download and install cmdlets?

Once the Azure Powershell cmdlets are installed you can easily create a cleanup job that will delete any file older than 15 days. First a time is defined. Secondaly we setup a connection to the Azure Storage Space (New-AzureStorageContext), and then we retrieve all files in our specific blob, filter based on our time-limit and then start removing them.

Good to know: Remove-AzureStorageBlob does accept –Whatif. However, –Whatif will still execute the remove. Test your code carefully… Most likely this is true for many other cmdlets.

[datetime]$limit = (Get-Date).AddDays(-15)
$context = New-AzureStorageContext -StorageAccountName $straccountname -StorageAccountKey $straccountkey -ErrorAction Stop

Get-AzureStorageBlob -Container $strblob -blob *.pst -Context $context | Where-Object { $_.LastModified -lt $limit } | ForEach-Object {Remove-AzureStorageBlob -Blob $_.Name -Container $strblob -Context $context}



A long rant that haven’t given anything to you. To be honest – this is memory notes for myself. The parts that are involved in creating an automated workflow requires a lot of moving bits and pieces that utilizes what a common-man would define as the cloud. The cloud is several messy parts that aren’t polished, not well documented, always in preview (technical preview, beta, early release, not launched..) and constantly changing.

All of the above are things that provided a bit of struggle. Most likely the struggle is due to lack of insight into a few of the technologies, and as more insight was gained the right questions were asked. If you read all of the links above carefully you will most likely see a few comments from me.

ConfigMgr: Match client address to IP-Range Boundaries

Despite the Microsoft recommendation, primarily due to additional workload that it causes, to not leverage IP-Ranges we have noticed a far greater significant accuracy of where clients retrieve content from based on our IP-ranges. So yes, we have our boundaries, with few exceptions, setup using IP-ranges.

We also have clients spread around the globe, new networks beeing spun-up, networks that aren’t supposed to be used for servers and clients and much more to actually be used for these type of things. The issue at hand is to understand where clients are actually connecting from, and what locations we know about.

To get some type of insight of where ConfigMgr clients are actually connecting from we started polling our database. In the end – this turned into two SQL-queries that would get all the IP-range boundaries, and a summary of how many clients we support on each network. As lazy as one can be – this ended up gathering enough information to present to other teams to present where clients are connecting from, how many there are and that we don’t previously didn’t know about this location.

To list how many clients you have per a /24-subnet. This may of course not necessarily be the exact size of a subnet, but it allows for an easy count-up of clients.

select SUBSTRING(ip.IPAddress0, 1, 
LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) + ".1" As IP,
 COUNT(*) as Devices
 from v_Network_DATA_Serialized as ip 
where ip.IPAddress0 IS NOT NULL and ip.IPSubnet0 != "64"
and ip.DNSDomain0 like "%yourdomain.com"
and ip.TimeStamp > DATEADD(day, -10, GETDATE())
GROUP BY  SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0)))

A list of all boundaries where we split the start and end IP-address of a specific range

select bound.DisplayName,
SUBSTRING(bound.value,1,CHARINDEX('-',bound.value) -1) AS LEFTHALF,
SUBSTRING(bound.value,CHARINDEX('-',bound.value) +1 ,100) AS RIGHTHALF
from vSMS_Boundary as bound
where bound.BoundaryType = "3"
and bound.DisplayName != "some boundary to exclude"

Information about the clients within a specific range that we do not know about

select DNSHostName0,
from v_Network_DATA_Serialized as ip
where ip.IPAddress0 IS NOT NULL
and ip.IPSubnet0 != '64'
and ip.DNSDomain0 like '%yourdomain.com'
and ip.TimeStamp > DATEADD(day, -10, GETDATE())
and ip.IPaddress0 like 'XXX.YYY.ZZZ.%'


To join all of this information together some basic, crude, logic was built in powershell to match up networks that clients are in and that we know about. The function to perform the actual IP-range lookup is from stackoverflow-reply. Sample output first:2015-11-22 16_07_41-Clipboard




function IsIpAddressInRange {
 [string] $ipAddress,
 [string] $fromAddress,
 [string] $toAddress

 $ip = [system.net.ipaddress]::Parse($ipAddress).GetAddressBytes()
 $ip = [system.BitConverter]::ToUInt32($ip, 0)

 $from = [system.net.ipaddress]::Parse($fromAddress).GetAddressBytes()
 $from = [system.BitConverter]::ToUInt32($from, 0)

 $to = [system.net.ipaddress]::Parse($toAddress).GetAddressBytes()
 $to = [system.BitConverter]::ToUInt32($to, 0)

 $from -le $ip -and $ip -le $to

$ErrorActionPreference = "silentlycontinue"
$database = "ConfigMgrServer"
$datasource = "ConfigMgrDB"

$netquery = "select SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) + '.1' As IP, COUNT(*) as Devices from v_Network_DATA_Serialized as ip where ip.IPAddress0 IS NOT NULL and ip.IPSubnet0 != '64' and ip.DNSDomain0 like '%yourdomain.com' and ip.TimeStamp > DATEADD(day, -10, GETDATE()) GROUP BY SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) ORDER BY Devices DESC"

$networks= Invoke-Sqlcmd -Query $netquery -server $datasource -Database $database

$query = "select bound.DisplayName, SUBSTRING(bound.value,1,CHARINDEX('-',bound.value) -1) AS LEFTHALF,SUBSTRING(bound.value,CHARINDEX('-',bound.value) +1 ,100) AS RIGHTHALF from vSMS_Boundary as bound where bound.BoundaryType = '3' and bound.DisplayName != 'exclusion boundary'"

$iprange = Invoke-Sqlcmd -Query $query -server $datasource -Database $database

foreach ($net in $networks) {
 if (!($net.ip -eq '')) {
 $i = 0
 $J = $iprange.count
 $boundaryfound = $false
 do {
 if (IsIpAddressInRange $net.ip $iprange[$i].LEFTHALF $iprange[$i].RIGHTHALF)

 $boundaryfound = $true
 } until ($i -gt $j)
 if ($boundaryfound -eq $false) 
 write-host "Network: $($net.ip) - Devices: $($net.Devices)"

 #$($($net.ip) -replace ".$")
 $devquery = "select DNSHostName0,DNSDomain0,IPAddress0,IPSubnet0,DefaultIPGateway0,DHCPServer0 from v_Network_DATA_Serialized as ip
 where ip.IPAddress0 IS NOT NULL
 and ip.IPSubnet0 != '64'
 and ip.DNSDomain0 like '%yourdomain.com'
 and ip.TimeStamp > DATEADD(day, -10, GETDATE())
 and ip.IPaddress0 like '$($($net.ip) -replace ".$")%'"
 $devices= Invoke-Sqlcmd -Query $devquery -server $datasource -Database $database



Working on some minor details to retrieve loads of data (we will see where this stuff ends-up..)

Here comes a minor Powershell function to retrieve all VMDK filenames from a VMware VMX-file. Output is the VMX-filename (so you know where it came from), the VMDK-files and the location of the VMX-file (so you know where to look for it).

Sample usage:

Get-VMDKFileNamefromVMX -VMX C:\VMs\VM1\vm1.vmx,c:\VMS\VM2\vm2.vmx

A sample output object;

function Get-VMDKFileNamefromVMX {
 Parses a VMX-file for all VMDK-filenames
 Outputs an object with all VMDK-filenames
 Get-VMDKFileNamefromVMX -VMX C:\VMs\VM1\vm1.vmx,c:\VMS\VM2\vm2.vmx
 [Parameter(Mandatory=$False, ValueFromPipeline=$true,
 HelpMessage="Location of VMX-File")]
 $vmx = $vmx.split(",")
 write-verbose "------------------------"
 write-verbose "Start of Get-VMDKFileNamefromVMX"
 Write-Verbose "VMX-files: $($vmx.count)"

foreach ($file in $vmx)
 write-verbose "Search for VMDK in $($file)"
 $vmdkfiles = Select-String -Path $($file) -Pattern vmdk
 write-error "Failed to retrieve $($file)"
 write-verbose "Parsing results for VMDK"
 write-verbose "Found $($vmdkfiles.count) matches of VMDK"
 foreach ($vmdk in $vmdkfiles)

write-verbose "Found: $($vmdk.line)"
 $vmdkfilename = ($vmdk.line).split("=")[1]
 $vmdkfilename = $vmdkfilename.Replace("`"","")
 $vmdkfilename = $vmdkfilename.trim()
 $object = New-Object –TypeName PSObject
 $object | Add-Member –MemberType NoteProperty –Name VMX –Value $($file)
 $object | Add-Member –MemberType NoteProperty –Name VMDK –Value $($vmdkfilename)
 $object | Add-Member –MemberType NoteProperty –Name Location –Value $(Split-Path $file)

 write-verbose "End of Get-VMDKFileNamefromVMX"
 write-verbose "------------------------"


A need arose to determine the latency to a few different nodes and act on that matter. Someone on the internet had almost already written all the Powershell code I wanted. However the code was primarily focused on outputting the results in a CSV-file and not actually using the result in the code afterwards.

Therefore I have re-written this function to output an object instead.


## Based on Ping-Latency
## Rewritten by Nicke Källén
## nicke dot kallen at applepie dot se
## Original header:
## http://kunaludapi.blogspot.com
## Version: 1
## Tested this script on
##  1) Powershell v3
##  2) Windows 7
function Test-Latency {
 Uses Test-Connection and determines latency to a host
 Outputs each node with Hostname, IP-Address, Latency (ms) and Date
 Test-Latency -ComputerNames,google.com

 [Parameter(Mandatory=$False, ValueFromPipeline=$true,
 HelpMessage="Hostnames or IP-Address seperated by commas")]
 [string[]]$ComputerNames = $env:COMPUTERNAME
 Begin {}

 $ComputerNames = $ComputerNames.split(",")
 foreach ($Computer in $ComputerNames)
 $Response = Test-Connection -ComputerName $computer -Count 1 -ErrorAction SilentlyContinue
 if ($Response -eq $null)
 $object = New-Object –TypeName PSObject
 $object | Add-Member –MemberType NoteProperty –Name Hostname –Value $Computer
 $object | Add-Member –MemberType NoteProperty –Name IPaddress –Value "Unreachable"
 $object | Add-Member –MemberType NoteProperty –Name Latency –Value "No response"
 $object | Add-Member –MemberType NoteProperty –Name Date –Value $(Get-Date)
 $object = New-Object –TypeName PSObject
 $object | Add-Member –MemberType NoteProperty –Name Hostname –Value $($Computer)
 $object | Add-Member –MemberType NoteProperty –Name IPAddress –Value $($Response.IPV4Address)
 $object | Add-Member –MemberType NoteProperty –Name Latency –Value $($Response.ResponseTime)
 $object | Add-Member –MemberType NoteProperty –Name Date –Value $(Get-Date)

 End {}

Uninstall Software

Based on the previous post handling the removal of the Ask software (the beloved add-on that everyone joyfully installs along with Java) a more developed script took form to handle any type of software.

Its based on the following borrowed pieces of code,

Get-LHSInstInstalledApp has been extended to also output the installationdate. Apart from that everything is as is from the original function

Convert-DateString has been used to convert the InstallationDate string to a date that can be used for calculations

ExitWithCode is a function that is simply used to end the script with an accumulated Exit Code from all uninstallations.

The script will accept the following parameters;

ApplicationName – a wild card search for the applications we want to remove.

PublisherName – we can validate that the right publisher have installed the application

InstallDateOlder – amount of days since the application was installed for us to remove it. Standard is 30

IgnoreInstallDate – True / False – we can choose to completely ignore when the application was installed

If the application is something other than an MSI – it will just report that a productcode is missing and not attempt the installation.

A log-file will be created in %WINDIR%\TEMP\APP_(yourappname)_Removal.LOG

Each uninstall will have a log-file written in %WINDIR%\TEMP with AP_UNINSTALL as prefix.



Running the script requires admin permissions


# Created with: PowerShell ISE
# Created on: 2015-02-21 23:32
# Created by: Nicke Källén
# Organization: Applepie.se
# Filename: SCCM_Uninstall_Unused_Application
# Comment: Uninstalls an application (msi support only) based
# on Display Name in ARP, Publisher and how long ago
# it was installed
# Convert-DateString function
# http://www.powershellmagazine.com/2013/07/08/pstip-
# converting-a-string-to-a-system-datetime-object/
# Get-LHSInstalledApp - appended InstallDate to output
# https://gallery.technet.microsoft.com/scriptcenter/
# Get-Installed-Application-615fa73a
# Exit function
# http://weblogs.asp.net/soever/returning-an-exit-
# code-from-a-powershell-script
param (
 [string]$ApplicationName = "",
 [string]$PublisherName = "",
 [int]$InstallDateOlder = "30",

 function Convert-DateString ([String]$Date, [String[]]$Format)
 $result = New-Object DateTime

 $convertible = [DateTime]::TryParseExact(

 if ($convertible) { $result }

Function Get-LHSInstalledApp {
 List installed applications for local or remote computers.

 List installed applications for local or remote computers.

 List both 32-bit and 64-bit applications. Note that
 dotNet 4.0 Support for Powershell 2.0 needed.

 Output looks like this:
 ComputerName : N104100
 AppID : {90120000-001A-0407-0000-0000000FF1CE}
 AppName : Microsoft Office Outlook MUI (German) 2007
 Publisher : Microsoft Corporation
 Version : 12.0.6612.1000
 Architecture : 32bit
 UninstallString : MsiExec.exe /X{90120000-001A-0407-0000-0000000FF1CE} 

.PARAMETER ComputerName
 Outputs applications for the named computer(s).
 If you omit this parameter, the local computer is assumed.

 Outputs applications with the specified application ID.
 An application's appID is equivalent to its subkey name underneath the Uninstall registry key.
 For Windows Installer-based applications, this is the application's product code GUID
 (e.g. {3248F0A8-6813-11D6-A77B-00B0D0160060}). Wildcards are permitted.

 Outputs applications with the specified application name.
 The AppName is the application's name as it appears in the
 Add/Remove Programs list. Wildcards are permitted.

.PARAMETER Publisher
 Outputs applications with the specified publisher name.
 Wildcards are permitted

 Outputs applications with the specified version.
 Wildcards are permitted.

 PS C:\> Get-LHSInstalledApp

 This command outputs installed applications on the current computer.

 PS C:\> Get-LHSInstalledApp | Select-Object AppName,Version | Sort-Object AppName

 This command outputs a sorted list of applications on the current computer.

 PS C:\> Get-LHSInstalledApp wks1,wks2 -Publisher "*microsoft*"

 This command outputs all installed Microsoft applications on the named computers.
 * regular expression to match any characters.

 PS C:\> Get-LHSInstalledApp wks1,wks2 -AppName "*Office 97*" 

 This command outputs any Application Name that match "Office 97" on the named computers.
 * regular expression to match any characters.

 PS C:\> Get-Content ComputerList.txt | Get-LHSInstalledApp -AppID "{1A97CF67-FEBB-436E-BD64-431FFEF72EB8}" | Select-Object ComputerName

 This command outputs the computer names named in ComputerList.txt that have the specified application installed.

 Get-LHSInstalledApp | Where-Object {-not ( $_.AppID -like "KB*") } |
 ConvertTo-CSV -Delimiter ';' -NoTypeInformation | Out-File -FilePath C:\temp\AppsInfo.csv
 Invoke-Item C:\temp\AppsInfo.csv

 Outputs all installed application except KB fixes to an CSV file and opens in Excel

 System.String, you can pipe ComputerNames to this Function

 PSObjects containing the following properties:

 ComputerName - computer where the application is installed
 AppID - the application's AppID
 AppName - the application's name
 Publisher - the application's publisher
 Version - the application's version
 Architecture - the application's architecture (32-bit or 64-bit)
 UninstallString - the application uninstall String

 More Info:
 Why not using Get-WmiObject
 * Win32_Product
 At first glance, Win32_Product would appear to be one of those best solutions.
 The Win32_product class is not query optimized.
 Queries such as “select * from Win32_Product where (name like 'Sniffer%')”
 require WMI to use the MSI provider to enumerate all of the installed
 products and then parse the full list sequentially to handle the “where” clause:,

 * This process initiates a consistency check of packages installed,
 and then verifying and repairing the installations.
 * If you have an application that makes use of the Win32_Product class,
 you should contact the vendor to get an updated version that does not use this class.

 On Windows Server 2003, Windows Vista, and newer operating systems, querying Win32_Product
 will trigger Windows Installer to perform a consistency check to verify the health of the
 application. This consistency check could cause a repair installation to occur. You can
 confirm this by checking the Windows Application Event log. You will see the following
 events each time the class is queried and for each product installed:

 Event ID: 1035
 Description: Windows Installer reconfigured the product. Product Name: <ProductName>.
 Product Version: <VersionNumber>. Product Language: <languageID>.
 Reconfiguration success or error status: 0.

 Event ID: 7035/7036
 Description: The Windows Installer service entered the running state.

 I would not recommend querying Win32_Product in your production environment unless you are in a maintenance window.

 * Win32Reg_AddRemovePrograms
 Win32Reg_AddRemovePrograms is not a standard Windows class.
 This WMI class is only loaded during the installation of an SMS/SCCM client.

 What is great about Win32Reg_AddRemovePrograms is that it contains similar properties and
 returns results noticeably quicker than Win32_Product.

 Using Registry:
 By default, if your process is running as a 32 bit process you will end up accessing the 32 bit "reflection" of
 the remote system. Therefore, registry keys like HKLM\Software will actually be mapped to HKLM\Software\Wow6432Node
 which gets very frustrating! You can access the 64 bit "reflection" via WMI, but personally I find that quite painful.

 Fortunately, in .NET 4, the registry class had some extra features added to it which allowed for a new
 overload "RegistryView". Therefore, you can now specify exactly which "reflection" of the registry
 you want to access and manipulate! No more headaches!

 In order to use this function, the Powershell instance must support .Net 4.0 or greater, which is fairly straightforward if you follow these instructions.
 1. Open notepad and copy the below text exactly as shown into the document.

<?xml version="1.0"?>
<configuration> <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0.30319"/> <supportedRuntime version="v2.0.50727"/> </startup>

 2. Save this document as c:\windows\System32\WindowsPowerhsell\v1.0\Powershell.exe.config
 (and/or c:\windows\System32\WindowsPowerhsell\v1.0\Powershell_ise.exe.config)
 (in addition for the 32bit Powershell on a 64bit Windows C:\Windows\SysWOW64\WindowsPowerShell\v1.0\*.config)
 3. Reload powershell and type the following command: $PsVersionTable.clrVersion (It should show Major version 4 if .Net 4 is supported.)

 NAME: Get-LHSInatalledApp.ps1
 AUTHOR: u104018
 LASTEDIT: 02/06/2012 16:01:40
 KEYWORDS: Registry Redirection, Installed software, Registry64, WOW6432Node,Accessing Remote x64 Registry From an x86/x32 OS Computer


#Requires -Version 2.0

[cmdletbinding(DefaultParameterSetName = 'Default', ConfirmImpact = 'low')] 


 [Parameter(ParameterSetName='AppID', Position=0,Mandatory=$False,ValueFromPipeline=$True)]
 [Parameter(ParameterSetName='Default', Position=0,Mandatory=$False,ValueFromPipeline=$True)]
 [string[]] $ComputerName=$ENV:COMPUTERNAME,

 [Parameter(ParameterSetName='AppID', Position=1)]
 [String] $AppID = "*",

 [Parameter(ParameterSetName='Default', Position=1)]
 [String] $AppName = "*",

 [Parameter(ParameterSetName='Default', Position=2)]
 [String] $Publisher = "*",

 [Parameter(ParameterSetName='Default', Position=3)]
 [String] $Version = "*"


 ${CmdletName} = $Pscmdlet.MyInvocation.MyCommand.Name

 If (!($PsVersionTable.clrVersion.Major -ge 4)) {Write-Error "Requires .Net 4.0 support for Powershell 2.0"; Return} 

} # end BEGIN

 #Write-Verbose -Message "${CmdletName}: Starting Process Block"
 ForEach ($Computer in $ComputerName) {
 Write-Verbose "`$Computer contains $Computer"
 IF (Test-Connection -ComputerName $Computer -Count 2 -Quiet) {
 try { 

 Write-Verbose "Get Architechture Type of the system"
 $OSArch = (Get-WMIObject -ComputerName $Computer win32_operatingSystem -ErrorAction Stop).OSArchitecture
 if ($OSArch -like "*64*") {$Architectures = @("32bit","64bit")}
 else {$Architectures = @("32bit")}
 #Create an array to capture program objects.
 $arApplications = @()
 foreach ($Architecture in $Architectures){
 #We have a 64bit machine, get the 32 bit software.
 if ($Architecture -like "*64*"){
 #Define the entry point to the registry.
 $strSubKey = "SOFTWARE\\WOW6432Node\\Microsoft\\Windows\\CurrentVersion\\Uninstall"
 $SoftArchitecture = "32bit"
 $RegViewEnum = [Microsoft.Win32.RegistryView]::Registry64
 #We have a 32bit machine, use the 32bit registry provider.
 elseif ($Architectures -notcontains "64bit"){
 #Define the entry point to the registry.
 $strSubKey = "SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall"
 $SoftArchitecture = "32bit"
 $RegViewEnum = [Microsoft.Win32.RegistryView]::Registry32
 #We have "64bit" in our array, capture the 64bit software.
 #Define the entry point to the registry.
 $strSubKey = "SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall"
 $SoftArchitecture = "64bit"
 $RegViewEnum = [Microsoft.Win32.RegistryView]::Registry64

 Write-Verbose "Create a remote registry connection to the Computer."
 $Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Computer, $RegViewEnum)
 $RegKey = $Reg.OpenSubKey($strSubKey)

 Write-Verbose "Get all subkeys that exist in the entry point."
 $RegSubKeys = $RegKey.GetSubKeyNames() 

 Write-Debug "Architecture : $Architecture"
 Write-Debug "SoftArchitecture : $SoftArchitecture"
 Write-Verbose "Enumerate the subkeys."
 foreach ($SubKey in $RegSubKeys)
 Write-Debug "`$SubKey : $SubKey"
 $Program = $Reg.OpenSubKey("$strSubKey\\$SubKey")
 $strDisplayName = $Program.GetValue("DisplayName")
 if ($strDisplayName -eq $NULL) { continue } # skip entry if empty display name

 switch ($PsCmdlet.ParameterSetName)

 "AppID" { if ((split-path $SubKey -leaf) -like $AppID)
 $RegKey = ("HKLM\$strSubKey\$SubKey").replace("\\","\")

 $output = new-object PSObject
 $output | add-member NoteProperty "ComputerName" -value $computer
 $output | add-member NoteProperty "RegKey" -value ($RegKey) # useful when debugging
 $output | add-member NoteProperty "AppID" -value (split-path $SubKey -leaf)
 $output | add-member NoteProperty "AppName" -value $strDisplayName
 $output | add-member NoteProperty "Publisher" -value $Program.GetValue("Publisher")
 $output | add-member NoteProperty "Version" -value $Program.GetValue("DisplayVersion")
 $output | add-member NoteProperty "Architecture" -value $SoftArchitecture
 $output | add-member NoteProperty "UninstallString" -value $Program.GetValue("UninstallString")
 $output | add-member NoteProperty "InstallDate" -value $Program.GetValue("InstallDate")

 } #end if
 } #end "AppID"

 "Default" { If (( $strDisplayName -like $AppName ) -and (
 $Program.GetValue("Publisher") -like $Publisher ) -and (
 $Program.GetValue("DisplayVersion") -like $Version ))
 $RegKey = ("HKLM\$strSubKey\$SubKey").replace("\\","\")

 $output = new-object PSObject
 $output | add-member NoteProperty "ComputerName" -value $computer
 $output | add-member NoteProperty "RegKey" -value ($RegKey) # useful when debugging
 $output | add-member NoteProperty "AppID" -value (split-path $SubKey -leaf)
 $output | add-member NoteProperty "AppName" -value $strDisplayName
 $output | add-member NoteProperty "Publisher" -value $Program.GetValue("Publisher")
 $output | add-member NoteProperty "Version" -value $Program.GetValue("DisplayVersion")
 $output | add-member NoteProperty "Architecture" -value $SoftArchitecture
 $output | add-member NoteProperty "UninstallString" -value $Program.GetValue("UninstallString")
 $output | add-member NoteProperty "InstallDate" -value $Program.GetValue("InstallDate")

 } #end if
 } #end "Default"
 } #end switch

 } # end foreach ($SubKey in $RegSubKeys)
 } # end foreach ($Architecture in $Architectures)
 } Catch {
 write-error $_
 } Else {
 Write-Warning "\\$Computer DO NOT reply to ping"
 } # end IF (Test-Connection -ComputerName $Computer -count 2 -quiet)
 } # end ForEach ($Computer in $computerName)

} # end PROCESS

END { Write-Verbose "Function ${CmdletName} finished." }

} # end Function Get-LHSInatalledApp

function Log
 param (
 Write-Debug 'Logging starting'
 Write-Debug "Filename: $($filename)"
 foreach ($txt in $text)
 Out-File $filename -append -noclobber -inputobject $txt -encoding ASCII
 Write-Verbose $txt


 Write-Debug 'Logging ending'


function ExitWithCode

 Write-Verbose "Ending with $($ExitCode)"

function Remove-InstalledMSI
 param (
 [string]$ProductCode = $null,

 Write-Verbose 'Start of Remove-InstallMSI'
 $exitcode = $null


 foreach ($pc in $ProductCode)
 Write-Verbose "Uninstall ProductCode: $($pc)"
 $AppName = $((Get-LHSInstalledApp -AppID $pc).AppName)
 Write-Verbose "AppName: $($AppName)"
 if ($LogFilePath -ne $null)
 $LogFilePath = "c:\windows\TEMP\AP_UNINSTALL_$($AppName).log"
 Write-verbose "LogFilePath: $LogFilePath"
 $argumentlist = "/x $pc /qn REBOOT=ReallySuppress /lv `"$($LogFilePath)`" "
 $argumentlist += $Property
 Write-Verbose "Argument List: $($argumentlist)" 

 $exitcode = (Start-Process -filepath "msiexec.exe" -ArgumentList $argumentlist -Wait -PassThru).ExitCode
 Write-Verbose "Exit Code: $($exitcode)"

 $output = new-object PSObject
 $output | add-member NoteProperty "ProductCode" -value $pc
 $output | add-member NoteProperty "AppName" -value $AppName
 $output | add-member NoteProperty "ExitCode" -value $($ExitCode)
 $output | add-member NoteProperty "LogFilePath" -value $LogFilePath



 Write-Verbose 'End of Remove-InstallMSI'


$logfile = "$env:windir\temp\AP_$($ApplicationName)_Removal.log"
Write-Verbose "Logfile: $($logfile)"
try { Remove-Item $logfile -force -ErrorAction SilentlyContinue }
catch { Write-Warning $_ }

log $logfile '--------------------------------------------'
log $logfile "$(get-date) - $($ApplicationName) - Removal Started"
log $logfile "$(get-date) - $($ApplicationName) - Searching for $($PublisherName) $($ApplicationName)"
 if ($IgnoreInstallDate -eq $true)
 Write-Verbose "IgnoreInstallDate set $($IgnoreInstallDate)"
 log $logfile "$(get-date) - $($ApplicationName) - Ignoring installation date"
 Write-Verbose "IgnoreInstallDate set $($IgnoreInstallDate)"
 log $logfile "$(get-date) - $($ApplicationName) - Removal only if install date is more than $($InstallDateOlder) days ago"

if ($PublisherName)

 $applist = Get-LHSInstalledApp -AppName "*$($ApplicationName)*" -Publisher "*$($PublisherName)*"
 $applist = Get-LHSInstalledApp -AppName "*$($ApplicationName)*"

log $logfile "$(get-date) - $($ApplicationName) - Found $($applist.Count) $($ApplicationName) installations"
log $logfile '--------------------------------------------'
$applist | foreach { write-verbose "AppName: $($_.AppName)"}
$ReturnValue = 0
Write-Verbose "Current Exit Code: $($ReturnValue)"

$applist | where {$_.appid -notmatch ('\{.+\}') } | foreach { log $logfile "$(get-date) - $($_.AppName) has no productcode" }
$applist = $applist | where {$_.appid -match ('\{.+\}') } 

if ($IgnoreInstallDate -eq $true)
 $uninstall = $applist | Remove-InstalledMSI
 $uninstall = $applist | where-object { ($_.InstallDate -notin ($null,'')) -and`
 ( ((get-date) - (Convert-DateString -Date $($_.InstallDate) -Format 'yyyyMMdd')).days -gt $InstallDateOlder ) }`
 | Remove-InstalledMSI

$uninstall | foreach { log $logfile "$(get-date) - $($_.AppName) - Exit Code: $($_.ExitCode)" ; $returnvalue += $_.exitcode }

log $logfile '--------------------------------------------'
log $logfile "$(get-date) - $($ApplicationName) - Removal Finished"