Posts

Have you heard about Get-WQLObject?

, , , , ,

Have you heard about Get- WQL Object?


WQL is a part of SCCM administration whether you like it or not. Over time you may become quite savvy with writing up WQL queries and appreciate that it mimics SQL in just enough of a way to allow some fancy collections. 

You can also use the ‘Query’ section under monitoring to view, develop, and run these WQL queries outside of collection evaluation. This can be useful for getting some quick data in a one-off fashion at a point in time. Commonly I will need to interact with this data though such as when I’m remediating some issues via a script. As much fun as copy pasting to excel and importing a CSV is… I wrote a script instead! 


The Meat:


What I’m doing at it’s core is a WMI query. That is the real meat here. So… here’s a basic function that takes in a WQL query and gives you the raw output. 


$Query = @"
select distinct s.Name,
sw.ProductName,
sw.ProductVersion,
cbs.CNIsOnline,
os.Caption
from  SMS_R_System s
inner join SMS_G_System_INSTALLED_SOFTWARE sw on sw.ResourceID = s.ResourceId
inner join SMS_CollectionMemberClientBaselineStatus cbs ON cbs.ResourceID = s.ResourceId
inner join SMS_G_System_Operating_System os ON os.ResourceID = s.ResourceID
where sw.ProductName like "%7-Zip%"
and sw.ProductVersion NOT IN ("18.05.00.0","18.05")
order by sw.ProductVersion
"@

function Get-WQLObject {
    param(
        # WQL formatted query to perform
        [Parameter(Mandatory = $true)]
        [string]$Query,
        # SMS Provider to query against
        [Parameter(Mandatory = $true)]
        [string]$SMSProvider
    )
    $SiteCode = (Get-WmiObject -Namespace "root\sms" -ClassName "__Namespace" -ComputerName $SMSProvider).Name.Substring(5, 3)
    $Namespace = [string]::Format("root\sms\site_{0}", $SiteCode)
    Get-WmiObject -ComputerName $SMSProvider -Namespace $Namespace -Query $Query
}

Get-WQLObject -SMSProvider 'SCCM.CONTOSO.COM' -Query $Query

This almost shouldn’t even be a function, but it is because I put ‘function’ in front of it with a name and a {.

So, if you run this you will get some data back, though it is a bit ugly honestly. 

Ugly

But hey, this is PowerShell. It is object oriented. I can dig into this object. I can expand cbs (Founders Canadian Breakfast Stout anyone?) and then I can expand CNIsOnline and I’ll get the value I truly care about. Same for sw, and then ProductName, and ProductVersion. But that sounds tedious.


The Potatoes:


Right, so I technically have what I need. But I want more! Fatten things up a bit ya know? Couple scoops of potatoes. Disclaimer: This IS potatoes, it does slow things down a bit. Though to be fair, any large WQL query via PowerShell is going to be slow no matter what.


function Get-WQLObject {
    param(
        # WQL formatted query to perform
        [Parameter(Mandatory = $true)]
        [string]$Query,
        # SMS Provider to query against
        [Parameter(Mandatory = $true)]
        [string]$SMSProvider,
        # Optional PSCredential 
        [Parameter(Mandatory = $false)]
        [pscredential]$Credential
    )
    Begin {
        if ($PSBoundParameters.ContainsKey('Credential')) {
            $AddedDefaultParam = $true
            $PSDefaultParameterValues.Add("Get-WmiObject:Credential", $Credential)
        }
        $SiteCode = (Get-WmiObject -Namespace "root\sms" -ClassName "__Namespace" -ComputerName $SMSProvider).Name.Substring(5, 3)
        $Namespace = [string]::Format("root\sms\site_{0}", $SiteCode)
    }
    Process {
        $RawResults = Get-WmiObject -ComputerName $SMSProvider -Namespace $Namespace -Query $Query
        $PropertySelectors = $RawResults | Get-Member -MemberType Property | Where-Object { -not $_.Name.StartsWith('__') } | Select-Object -ExpandProperty name | ForEach-Object {
            $Class = $_
            $Properties = $RawResults.$Class | Get-Member -MemberType Property | Where-Object { -not $_.Name.StartsWith('__') } | Select-Object -ExpandProperty name
            foreach ($Property in $Properties) {
                [string]::Format("@{{Label='{1}.{0}';Expression = {{`$_.{1}.{0}}}}}", $Property, $Class)
            }
        }
    }
    end {
        if ($AddedDefaultParam) {
            $PSDefaultParameterValues.Remove("Get-WmiObject:Credential")
        }
        $PropertySelector = [scriptblock]::Create($($PropertySelectors -join ','))
        $RawResults | Select-Object -Property $(. $PropertySelector)
    }
}

Starting to look a lot more like a function! You know it should be a function when it starts doing weird things that you don’t feel like typing out over and over. 


$PropertySelectors = $RawResults | Get-Member -MemberType Property | Where-Object { -not $_.Name.StartsWith('__') } | Select-Object -ExpandProperty name | ForEach-Object {
    $Class = $_
    $Properties = $RawResults.$Class | Get-Member -MemberType Property | Where-Object { -not $_.Name.StartsWith('__') } | Select-Object -ExpandProperty name
    foreach ($Property in $Properties) {
        [string]::Format("@{{Label='{1}.{0}';Expression = {{`$_.{1}.{0}}}}}", $Property, $Class)
    }
}

Remember how I said we could ‘dig into’ the objects? This snippet above is generating a string (that we will join and turn into a scriptblock) to do just that. We are going to dynamically find our classes and properties by leveraging Get-Member and filtering out system properties. Those system properties don’t interest me in this context so I’m filtering them out with “Where-Object { -not $_.Name.StartsWith(‘__’) }” leaving me with the properties I care about.

Couple notes:

  •  I like [string]::Format. It is actually REALLY fast in terms of CPU time, and for me personally it makes things more ‘manageable.’ For some people it is just confusing. You can simply do $Potatoes = “$Var1″+’bacon’+”$Var2” or many other methods if you prefer. ($Var1 = ‘Cheese’ by the way)
  • In our generated string I am escaping $_ by writing out `$_. This is so that we don’t actually expand out $_ right now. I want to treat it is a string to be used later.

The output, based on the 7-Zip software query above, can be seen below and should look familiar if you’ve ever done some custom Select-Object with calculated properties.


@{Label='cbs.CNIsOnline';Expression = {$_.cbs.CNIsOnline}},
@{Label='os.Caption';Expression ={ $_.os.Caption}},
@{Label='s.Name';Expression = {$_.s.Name}},
@{Label='sw.ProductName';Expression ={$_.sw.ProductName}},
@{Label='sw.ProductVersion';Expression = {$_.sw.ProductVersion}}

To leverage this code in the way I am hoping to though, it cannot just be a string. It has to be a scriptblock that can be dot-sourced. Let’s do that!


 $PropertySelector = [scriptblock]::Create($($PropertySelectors -join ','))

Because we are going to be wanting to expand out from $_ in the context of $RawResults | Select-Object… we need to execute the scriptblock. Simply passing $PropertySelector won’t work, so instead we will dot-source the scriptblock.


$RawResults | Select-Object -Property $(. $PropertySelector)

This will ‘execute’ our $PropertySelector statement that we generated. So {$_.s.Name} will actually be {$<instance of $RawResults that we are piping>.s.Name} which is what we want!

And of course the output from the function now looks MUCH nicer.

Mmmm Potatoes!

The Gravy!


I know, I know. This has all been really dry. Enter… the gravy!

WQL… you’ve got that down right? You know a good 2-300 WMI Classes under the root\sms\site_<sitecode> namespace including their properties right? Yeah, me too. 

What if we could leverage all those queries you have under you ‘Queries’ node in monitoring?

$Gravy = ‘DynamicParam’


function Get-WQLObject {
    param(
        # WQL formatted query to perform
        [Parameter(Mandatory = $true, ParameterSetName = 'CustomQuery')]
        [string]
        $Query,
        # SMS Provider to query against
        [Parameter(Mandatory = $true)]
        [string]
        $SMSProvider,
        # Optional PSCredential (unfortunately I can't figure out how to use this cred in the DynamicParam WMI queries without providing info outside the function)
        [Parameter(Mandatory = $false, ParameterSetName = 'CustomQuery')]
        [pscredential]
        $Credential
    )
    DynamicParam {
        if (($SMSProvider = $PSBoundParameters['SMSProvider'])) {
            $ParameterName = 'SCCMQuery'
            $RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
            $AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
            $ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute
            $ParameterAttribute.Mandatory = $true
            $ParameterAttribute.ParameterSetName = 'ExistingQuery'
            $ParameterAttribute.HelpMessage = 'Specify the name of a query that already exists in your ConfigMgr environment'
            $AttributeCollection.Add($ParameterAttribute)
            $SiteCode = (Get-WmiObject -Namespace "root\sms" -ClassName "__Namespace" -ComputerName $SMSProvider).Name.Substring(5, 3)
            $Namespace = [string]::Format("root\sms\site_{0}", $SiteCode)
            $arrSet = Get-WmiObject -ComputerName $SMSProvider -Namespace $Namespace -Query "SELECT Name FROM SMS_Query WHERE Expression not like '%##PRM:%'" | Select-Object -ExpandProperty Name
            $ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($arrSet)
            $AttributeCollection.Add($ValidateSetAttribute)
            $RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
            $RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
            return $RuntimeParameterDictionary
        }
    }
    Begin {
        $SCCMQuery = $PsBoundParameters[$ParameterName]
        if ($PSBoundParameters.ContainsKey('Credential') -and -not $PSDefaultParameterValues.ContainsKey("Get-WmiObject:Credential")) {
            $AddedDefaultParam = $true
            $PSDefaultParameterValues.Add("Get-WmiObject:Credential", $Credential)
        }
        $SiteCode = (Get-WmiObject -Namespace "root\sms" -ClassName "__Namespace" -ComputerName $SMSProvider).Name.Substring(5, 3)
        $Namespace = [string]::Format("root\sms\site_{0}", $SiteCode)
        if ($PSCmdlet.ParameterSetName -eq 'ExistingQuery') {
            $Query = Get-WmiObject -ComputerName $SMSProvider -Namespace $Namespace -Query "SELECT Expression FROM SMS_Query WHERE Name ='$SCCMQuery'" | Select-Object -ExpandProperty Expression
        }
    }
    Process {
        $RawResults = Get-WmiObject -ComputerName $SMSProvider -Namespace $Namespace -Query $Query
        $PropertySelectors = $RawResults | Get-Member -MemberType Property | Where-Object { -not $_.Name.StartsWith('__') } | Select-Object -ExpandProperty name | ForEach-Object {
            $Class = $_
            $Properties = $RawResults.$Class | Get-Member -MemberType Property | Where-Object { -not $_.Name.StartsWith('__') } | Select-Object -ExpandProperty name
            foreach ($Property in $Properties) {
                [string]::Format("@{{Label='{1}.{0}';Expression = {{`$_.{1}.{0}}}}}", $Property, $Class)
            }
        }
    }
    end {
        if ($AddedDefaultParam) {
            $PSDefaultParameterValues.Remove("Get-WmiObject:Credential")
        }
        $PropertySelector = [scriptblock]::Create($($PropertySelectors -join ','))
        $RawResults | Select-Object -Property $(. $PropertySelector)
    }
}

There we go! Now we are nice and bloated!

What have I done!!!

  • Added ParameterSets
  • Added DynamicParam
  • Justified creating a function by making this thing nice and ugly

We now have two ParameterSets, one of which is less obvious because it is introduced in the ‘DynamicParam’ block. 


$ParameterAttribute.ParameterSetName = 'ExistingQuery'

And then we do a bit of magic which allows us to tab-complete our existing Queries that you have in SCCM right now. (Note: I’m excluding those that require parameters because… yeah I don’t feel like writing in that logic right now)


$arrSet = Get-WmiObject -ComputerName $SMSProvider -Namespace $Namespace -Query "SELECT Name FROM SMS_Query WHERE Expression not like '%##PRM:%'" | Select-Object -ExpandProperty Name

So what does this do for me? 

Tab completion!

You now have tab completion based on the name of the queries in SCCM. You can execute all of your pre-existing queries, and even find them with tab-completion or by using ctrl+space.

Ohhh buddy!

I did mention this above, but I will say it again. A slow query is still a slow query, and our calculated properties isn’t going to make it faster. This function adds some overhead to your query as we are also piping it to a select-object after the fact. BUT it is pretty cool right? Get those gears turning on DynamicParams!

Some good-to-mentions about the function:

  • To use the -SCCMQuery parameter you need to supply -SMSProvider first. if (($SMSProvider = $PSBoundParameters[‘SMSProvider’]))
  • Unfortunately, no -Credential param when you are using -SCCMQuery because I wasn’t able to figure out how to use the credentials from the parameter inside the DynamicParam which would be needed to get our list of expressions. Let me know if you figure it out!
  • It is on GitHub
  • I might have made some gross oversights and overcomplicated this

 

@CodyMathis123

https://sccmf12twice.com/author/cmathis/

Add task sequence dependencies to DP group

, , , , , ,

We’re working on adding new distribution point that are used exclusively for imaging.  This motivated me to create a script to make sure all dependent packages are assigned to the proper distribution point group.  (You are using distribution point groups right?)

Change the site code in the script and it will prompt you for the task sequence and then prompt for the distribution point group.  After that, magic!  🙂

I wasn’t planning on posting this one yet as it could use a lot of polish but I’m sure it’ll evolve as time goes on and I’ll do my best to keep this updated.

On to the code…

 

 

$siteCode = "CM1"

Import-Module ConfigurationManager
Push-Location
Set-Location "$($siteCode):"

$site = (Get-CMSite -SiteCode $siteCode)
$sequences = Get-CMTaskSequence | Select-Object -Property Name | Out-GridView -Title "Select task sequences for DG" -PassThru
$dg = Get-CMDistributionPointGroup | Out-GridView -Title "Select a distribution point group." -PassThru

if($ConfirmPreference -eq 'Low') {$conf = @{Confirm = $true}}

foreach ($tsname in $sequences)
{
    $ts = Get-CMTaskSequence -Name $tsname.Name
    Write-Host "References $($ts.References.Count)"
    foreach ($ref in $ts.References)
    {
        $pkgContentServer = $null
        $pkgId = $null

        if ($ref.Type -eq 0)
        {
            $pkgContentServer = Get-WmiObject -ComputerName $site.ServerName -Namespace "rootSMSsite_$($site.SiteCode)" -Class SMS_PackageContentServerInfo -Filter "PackageID = '$($ref.Package)' AND ContentServerID = '$($dg.GroupID)'"
            $pkgId = $ref.Package
        }
        elseif ($ref.Type -eq 1)
        {
            $app = Get-CMApplication -ModelName $ref.Package
            $pkgContentServer = Get-WmiObject -ComputerName $site.ServerName -Namespace "rootSMSsite_$($site.SiteCode)" -Class SMS_PackageContentServerInfo -Filter "PackageID = '$($app.PackageID)' AND ContentServerID = '$($dg.GroupID)'"
            $pkgId = $app.PackageID
        }

        if ($pkgContentServer -eq $null)
        {
            Write-Host "Adding distribution point group $($dg.Name) to package $($ref.Package)."
            if ($PSCmdlet.ShouldProcess("$($ref.Package)", "Distribute package"))
            {
                if ($ref.Type -eq 0)
                {
                    $baseObject = Get-WmiObject -ComputerName $site.ServerName -Namespace "rootSMSsite_$($site.SiteCode)" -Class SMS_PackageBaseClass -Filter "PackageID = '$pkgId'"
                    Start-CMContentDistribution -InputObject ($baseObject | ConvertTo-CMIResultObject) -DistributionPointGroupName $dg.Name #-WhatIf:([bool]$WhatIfPreference.IsPresent) #@conf
                }
                elseif ($ref.Type -eq 1)
                {
                    Start-CMContentDistribution -ApplicationId $app.CI_ID -DistributionPointGroupName $dg.Name
                }
            }
        }
    }

    $bootImage = Get-CMBootImage -Id $ts.BootImageID
    $pkgContentServer = Get-WmiObject -ComputerName $site.ServerName -Namespace "rootSMSsite_$($site.SiteCode)" -Class SMS_PackageContentServerInfo -Filter "PackageID = '$($bootImage.PackageID)' AND ContentServerID = '$($dg.GroupID)'"
    if ($pkgContentServer -eq $null)
    {
        Write-Host "Adding distribution point group $($dg.Name) to package $($bootImage.PackageID)."
        if ($PSCmdlet.ShouldProcess("$($bootImage.PackageID)", "Distribute package"))
        {
            $baseObject = Get-WmiObject -ComputerName $site.ServerName -Namespace "rootSMSsite_$($site.SiteCode)" -Class SMS_PackageBaseClass -Filter "PackageID = '$($bootImage.PackageID)'"
            Start-CMContentDistribution -InputObject ($baseObject | ConvertTo-CMIResultObject) -DistributionPointGroupName $dg.Name
        }
    }
}
Pop-Location


ALSO CHECK : Post OSD Scheduled Task

Post OSD Scheduled Task

, ,

Post OSD Scheduled Task


Every organization handles OSD differently. Currently at our organization we do have some apps that have been ‘baked into the task sequence’  as an ‘Install Application’ step for a very long time and are needed on every single imaged machine. These work perfectly, install consistently and generally there are no ‘exceptions’ to a PC having the software.

This isn’t the case with all of our widely distributed applications, and rather than build the logic into the task sequence we let the collections do the work after imaging. After a period of time when the appropriate collections are updated on their various schedules these new machines happily receive their software and baselines and go on their merry way as we know all healthy SCCM clients do! In the day-to-day SCCM world this works perfectly fine. Machines are added to collections through whatever method your organization uses, whether it be Direct Membership, AD Group/OU Queries, name based queries, hardware inventory based queries and there is a general understanding that machines will pop into the collection and receive their Applications/Updates/CIs in good time.

When our techs image a machine it can be helpful for it to temporarily have some expedited policy refresh rates for a period of time to speed up those after-the-fact deployments. We had tried a few collection queries to catch these ‘new machines’ so that we could deploy some aggressive Client Settings to them, but generally there is never a perfect query. Usually you catch not just new machines but instead those risen from the dead pit of being stale, or they were rejoined to the domain, maybe the client was reinstalled or some form of in place upgrade happened.  As an alternative to the collection query I wrote up a Powershell script.


What Does It Do?


It creates a scheduled task! 

The script can be ran from a ‘Run Command Line’ or ‘Run Powershell Script’ step during OSD (Typically near/at the end) with various parameter options. You’ll have to toss it into a package to serve up to the task sequence of course. It will create a scheduled task that runs specified SCCM Client Policy requests at whatever interval you want for as long as you want. Also, the task deletes itself shortly after the duration has passed. I didn’t quite include all the of schedule types because there is a very long list. But most of the key ones are there and any others can be easily added. 


How Do I Use It?


New-ClientActionScheduledTask.ps1 -Schedule MachinePol -Interval 5

This will create a schedule task that runs every 5 minutes for 24 hours. I did say aggressive at least once up there right? The task will receive a generated name based on the requested schedules. The above would produce a task named –
SCCM Action Scheduler – [MachinePol]
Which calls a file (Start-CMClientAction.ps1)  that is generated and stored in c:\windows\temp

Both the task name and the file name can be specified as parameters to the script aptly named… -FileName and -TaskName


New-ClientActionScheduledTask.ps1 -Schedule AppEval, HardwareInv, UpdateEval, UpdateScan -Interval 30 -Duration 12
Task Sequence Example

Similar results with this execution of the line above. A task is created, but with it running every 30 minutes for a 12 hour period. The task will appear in task scheduler with a title –
SCCM Action Scheduler – [AppEval,HardwareInv,UpdateEval,UpdateScan]
Which is based on the actions provided.


Where Do I Git It?


GitHub! I intend to continue adding to this GitHub repository.

https://github.com/CodyMathis123/CM-Ramblings/blob/master/New-ClientActionScheduledTask.ps1


Neat Stuff:


I’ve also used a couple of bits of code you might find interesting to create the scheduled task. Maybe you’ve seen it, maybe you haven’t.


${Function:Invoke-CCMClientAction}

This will write-out the contents of many functions (note: function, not cmdlet). I am leveraging this to generate a .ps1 file that can be easily invoked by the task sequence.

Neat right? I wrote that function though… but what is more interesting is you can do this with some built in functions! Try ${function:Clear-Host} or ${function:Get-Verb} and you can see some of the magic behind at least some commands you’ve used. Many are compiled cmdlets and are simply not expandable like this but can be dug into in other ways.


$TaskDefinition.Settings.DeleteExpiredTaskAfter = "PT0S"

https://docs.microsoft.com/en-us/windows/desktop/taskschd/tasksettings-deleteexpiredtaskafter

While this can be a bit odd to work with, the above piece of code allows our scheduled task to ‘delete itself’ after it expires. Specifically… zero seconds after it expires. T is just a delimiter between date and time (Eg. days vs hours/minutes/seconds). You will need to specify an ‘EndBoundary’ for this to function, which is what our ‘Duration’ is in this.


function New-ScheduledTaskTimeString {
    param(
        [Parameter(Mandatory = $false)]
        [int]$Hours = 0,
        [Parameter(Mandatory = $false)]
        [int]$Minutes = 0
    )
    $TimeSpan = New-TimeSpan -Hours $Hours -Minutes $Minutes
    $TimeSpanDays = $TimeSpan | Select-Object -ExpandProperty Days
    $TimeSpanHours = $TimeSpan | Select-Object -ExpandProperty Hours
    $TimeSpanMinutes = $TimeSpan | Select-Object -ExpandProperty Minutes

    if ($TimeSpanDays -gt 0) {
        $OutputDays = [string]::Format("{0}D", $TimeSpanDays)
    }

    if ($TimeSpanHours -gt 0 -or $TimeSpanMinutes -gt 0) {
        $Delimiter = 'T'
        if ($TimeSpanHours -gt 0) {
            $OutputHours = [string]::Format("{0}H", $TimeSpanHours)
        }

        if ($TimeSpanMinutes -gt 0) {
            $OutputMinutes = [string]::Format("{0}M", $TimeSpanMinutes)
        }
    }

    [string]::Format("P{0}{1}{2}{3}", $OutputDays, $Delimiter, $OutputHours, $OutputMinutes)

}

You give me hours and minutes, I give you a PnDTnHnMnS string to use for a scheduled task.
Just a quick function I wrote for the purposes of dumping out usable strings for time intervals that task scheduler understands.


I opted to not use the *-ScheduledTask* commands available post-Win 7. You could ‘simplify’ the code a bit for the task creation if you don’t mind being incompatible with Windows 7 by using these.

Is this the right way to do it? Who knows! I’m sure with some very careful inspection and categorization of our collections and their refresh schedules we could help the situation in other ways. Still a neat bit of code.

@CodyMathis123

https://sccmf12twice.com/author/cmathis/