SCCM ADR Approval ala PowerApps and Flow

There are administrative activities that take place in every ConfigMgr/SCCM hierarchy that would be nice to be able to add some safety pre-checks before ConfigMgr takes off and does its thing. In the example I’m going to cover within, the scenario revolves around wanting to be notified when my ConfigMgr Automatic Deployment Rule (ADR) runs its usual scheduled download, distribution, and deployment process. I didn’t only want to know what is actually about to be deployed to my clients, but I want to know what the maximum runtimes are for each update, the start time and deadline of the deployment, some basic info about each update including a link to view more details and check for known issues, and any other interesting information you might want to include.

You could opt to only be notified of the new software updates using status filter rules to run a script which sends an e-mail from within the script. I decided to leverage Microsoft Flow’s native Send Email capability to handle the notification e-mail. One great thing many don’t realize is that a free Flow plan is included with every Office 365 tenant. Once you realize all of the automation capabilities that Flow has, you’ll probably soon realize it’s worth bumping up to Flow Plan 1 so you can begin taking advantage of the Flow Gateway. Fortunately, it’s a great deal @ $15/user per month (with no limit to the number of Flows each user can create), or a per Flow plan starting at $500/month for up to 5 Flows. For more details on Flow and it’s pricing, click here.

What if you want to take it to the next level, and not only be notified of the updates, but also click a link within the e-mail which allows you to review details of each update, as well as click an “Approve Deployment” button to actually go back into ConfigMgr and enable the deployment that the ADR created? This is also possible, and the possibilities are endless!

You must create your ADR, and when doing so, make sure to uncheck the “Enable the deployment after this rule is run” checkbox on the first screen of the ADR wizard. This is where creating a PowerApp can come in handy. PowerApps for Office 365 is free with every Office 365 tenant and includes over 200 standard connectors. If you want to store and consume your data in CDS, you’ll require a PowerApps P1 ($7 user/mo) subscription which includes Flow P1 along with being able to use premium connectors. The HTTP response trigger is now a premium connector so it’s best to have P1 for this specific use case.

Each time an ADR runs, a status message with a Message ID of 5800 is generated upon the successful creation of the new deployment of the associated Software Update group:

There are many automation opportunities available with status messages, as you can run a batch file, powershell, vbscript, etc each time this status message is generated in the hierarchy.

For the ADR review and approval, I created a Status Filter Rule, and selected the Run a program action.

The command-line I used is C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -executionpolicy bypass -noprofile -windowstyle hidden -file c:\scripts\Get-UpdateGroupContent.ps1 -Desc “%msgdesc”

%msgdesc equates to the Description within a status message, and to see what other variables exist, visit this TechNet article. In this example, it is equal to “CI Assignment Manager successfully processed new CI Assignment Monthly OS Updates 2019-07-29 20:31:50.”

In the Get-UpdateGroupContent.ps1 script, I’m triggering the e-mail notification Flow while also populating a CDS entity with the data I’m interested in originating from ConfigMgr using the native PowerShell cmdlets. CDS is Common Data Service. CDS isn’t the only option – you could also create a SQL database, an Excel Spreadsheet on OneDrive, Azure Table storage, etc.

You could alternatively use something like Send-MailMessage to send the e-mail in place of the Flow if you just want to use your own SMTP server without relying on Flow for that.

Get-UpdateGroupContent.ps1

[CmdletBinding()]
 
param(
    # Software Update Group
    [Parameter(Mandatory = $true, ValueFromPipeline=$true)]
    [String] $Desc
    )
 
 
$SiteCode = "PRI"
 
 
# Load ConfigMgr module if it isn't loaded already
If (-not(Get-Module -name ConfigurationManager)) 
   {
        Import-Module ($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager.psd1')
   }
 
# Change to site
    Push-Location
    Set-Location ${SiteCode}:
 
Function Send-RestAPI {
Param ($body, $cdsinfo)
 
# RESTful API Calls
$sendmail = Invoke-WebRequest -Uri $RestAPI.uriemail -Method Post -ContentType "application/json" -Body (Convertto-Json -InputObject $body)
$sendtocds = Invoke-WebRequest -Uri $RestAPI.uricds -Method Post -ContentType "application/json" -Body (Convertto-Json -InputObject $cdsinfo)
}
 
    
[regex]$regex="CI Assignment Manager successfully processed new CI Assignment\s+(?<Group>.*)\s\d{4}-\d{2}-\d{2}"
 
$Desc | Select-String -Pattern $regex -AllMatches | ForEach-Object { 
    
[string]$DeploymentName = $_.Matches.Groups[$regex.GroupNumberFromName("Group")].Value
}
 
If ($SUG = Get-CMSoftwareUpdateGroup -Name "$($Deploymentname)*" | Where-Object {(Get-Date $_.DateCreated -Format D) -eq (Get-Date -Format D)})
{
 
If ($DeploymentName -notlike "*Definition*")
 
    {
 
$Info = @()
 
$Deployment = Get-CMSoftwareUpdateDeployment | Where-Object {$_.AssignmentDescription -eq $DeploymentName -and ((Get-Date $_.CreationTime -Format D) -eq (Get-Date -Format D))}
 
 
    ForEach ($item1 in (Get-CMSoftwareUpdate -UpdateGroupID $SUG.CI_ID))
         {
         #Convert MaxExecutionTime from seconds to minutes
         $item1.MaxExecutionTime = $item1.MaxExecutionTime / 60
 
         $UpdateProperties1 = [ordered]@{ 
 
            KB = $item1.ArticleID
            Title = $item1.LocalizedDisplayName
            "Max RunTime (mins)" = $item1.MaxExecutionTime
  
         }
 
         $object = [pscustomobject]$UpdateProperties1
 
         $info+=$object
}
 
$cdsinfo= @()
 
  ForEach ($item2 in (Get-CMSoftwareUpdate -UpdateGroupID $SUG.CI_ID))
         {
         #Convert MaxExecutionTime from seconds to minutes   
         $item2.MaxExecutionTime = $item2.MaxExecutionTime / 60
 
         $UpdateProperties2 = @{ 
 
            KB = $item2.ArticleID
            Description = $item2.LocalizedDescription
            Title = $item2.LocalizedDisplayName
            URL = $item2.LocalizedInformativeURL
            Severity = $item2.SeverityName
            "Date Posted" = (Get-Date $item2.DatePosted -Format D)
            "Max RunTime (mins)" = $item2.MaxExecutionTime
            DeploymentID = $Deployment.AssignmentID
            UpdateGroup = $SUG.LocalizedDisplayName
            StartTime = (Get-Date $Deployment.StartTime -Format s)
            Deadline = (Get-Date $Deployment.EnforcementDeadline -Format s)
            AssignmentName = $Deployment.AssignmentName
         
         }
 
         $object2 = [pscustomobject]$UpdateProperties2
 
         $cdsinfo += $object2
         }
 
$html = $Info | ConvertTo-Html -Fragment | Out-String
 
[string]$UpdateGroup = $SUG.LocalizedDisplayName
 
$RestAPI = 
    @{
    uriemail = 'https://prod-109.westus.logic.azure.com:443/workflows/blahblahblahb34e76a2b73a3c527fa5b4/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=blahblahp7w1fEs0cMk5Z7yuo5F_JauR3HjnVjppU5'
    uricds = 'https://prod-115.westus.logic.azure.com:443/workflows/blahblahblahed13435a46be613d70801f8/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=blahblah-_oNWgE77sb5lDkUDkXLhbh2Fmwq0AbKgY'
     }
 
    $RestAPI["uriemail"]
    $RestAPI["uricds"]
 
$body = @{
     "to"= "russ@russrimmerman.com";
     "subject"="Software Update Group: $($SUG.LocalizedDisplayName)"
     "body"=$html
     "updategroup"=$SUG.LocalizedDisplayName
     "deployment"=$Deployment.AssignmentID
     "assignmentname"=$Deployment.AssignmentName
         }
 
    }
 
}
 
Send-RestAPI -body $body -cdsinfo $cdsinfo

Each time an ADR runs, the status filter rule runs the script passing the description from the status message ID 5800 as a parameter to the script. The script then creates two json parameters, one for the e-mail sending Flow, and one for populating CDS (or any other datastore you choose.)

First, the update group is created by the ADR. This is nothing magical, just standard ADR operation. Notice since the “Enable the deployment after the rule is run” checkbox was unchecked, the deployment is currently “Enabled:No”.

In the update group, 6 updates were added – including our friend the Servicing Stack. Speaking of – if you aren’t deploying Servicing Stacks in advance of your monthly software update deployments, I highly recommend doing so!

The PowerShell script first triggers the Flow which sends the e-mail.

The JSON for the HTTP request and Parse JSON steps are below. To send the e-mail along with a deep link to pass the update group, deployment, and assignment names, we need a parameter to facilitate each.

{
    "type": "object",
    "properties": {
        "to": {},
        "subject": {},
        "body": {},
        "updategroup": {},
        "deployment": {},
        "assignmentname": {}
    }
}

I added two “Compose” Flow actions following the Parse JSON step that I named “Replace spaces with underscores”. The reason for doing this is due to my Software Update Groups and assignment names in ConfigMgr having spaces in them. If I was going to do this over again, I would recommend not having any spaces as this made things a little tricky, as when passing these names as parameters within a hyperlink, the spaces are automatically encoded and replaced with plus (+) signs. I could’ve encoded these using percent encoding (%20=space) and then decoded them back to spaces, but decided it would be easier to just leave the +’s and replace them with underscores in the Flow.

“Replace spaces with underscores in UpdateGroup” is using the following expression in the Flow step: replace(body(‘Parse_JSON’)?[‘updategroup’],’ ‘,’_’)

“Replace spaces with underscores in AssignmentName” is doing the same but with the assignmentname parameter.

Then within my PowerApp, I had to do the reference, replacing the underscores with spaces to bring the strings back to their original state.

And viola, the ADR runs, the Software Update Group is created, and the e-mail arrives!

When clicking the link within the e-mail, a PowerApp I created to consume the data in CDS launches, displaying all the updates in the update group The reason my PowerApp knows what to display is because of the deep links within the URL in the e-mail. My deep link has &updategroup=Plan_2019-09-07_13:36:15&deployment=16780466&assignmentname=Plan_2019-09-07_13:36:25. This needs to be appended to the end of the PowerApp URL for launching the application.

The severity rating, title, and a link directly to the KB article is included, as this is what I collected with my PowerShell script that sent the data up to Flow. Clicking on the “i” icon brings up details about the update including a more detailed description, the maximum runtime currently configured for the update, the date the update was posted, as well as the start time and deadline of the update group’s deployment.

In this case, I created my PowerApp for mobile device size – so I could approve it on my phone – but of course it will also come up in a browser as well. This is the beauty of PowerApps – create once, run it from anywhere! Another cool plug while I’m at it – PowerBI tiles can easily be displayed within a PowerApp and a PowerApp can run nicely within a PowerBI dashboard. This is quite cool. You may have noticed another button within my app named “Update Compliance”. I added this to show this off. We have a Premier Support offering called Modern Workplace Compliance Reporting which shows a 6 month historical overview of your software update compliance, with drill downs that can show more granular compliance by collection, device, update as well. What’s really unique about this PowerBI based report is that it also allows customizable blacklisting of updates and update classifications, and returns software update scan error codes and common fixes for them to assist with expediting troubleshooting broken endpoints. Blacklisting is really a great thing, no more wasting time explaining why your compliance numbers are low due to updates that you’ve decided as a business not to deploy (preview updates and the likes)! I’ve taken a small sampling of just this month’s compliance from my small lab environment and pinned it within my ADR Deployment Reviewer so I can also monitor my compliance once I’ve enabled the deployment. One-stop shop for compliance = BIG time savings! If you’re interested in this offering, hit up your Technical Account Manager and ask for the “Modern Workplace Compliance Reporting” offering if this sounds interesting!

So, what does all this look like on my mobile device? Here it is – I couldn’t pass up the buttons 😊):

Once I’m satisfied with the updates that are going to be deployed, I just tap or click on the big green Accept button. The OnSelect for this simply runs my Flow and passes the parameters to it and then does an UpdateContext to display the success window after. SoftwareUpdateDeploymentApproval.Run(param1, param3);UpdateContext({ShowSuccess:true})

This initiates the following next Flow, which takes the same two parameters passed to the PowerApp within the deep link hyperlink inside the e-mail, and appends the parameters to a text file I happen to have sitting in C:\Temp\Watchme called “flow.txt” (pls don’t use C:\Temp – I like living on the edge). I first initialize the two parameters I need to pass to the script which will enable the deployment. To make sure my Flow know that it will be passed these parameters from my PowerApp, I used the “Ask in Powerapps” dynamic contentwithin my Initialize Variable steps to gather up the two parameters.

When finished, my Flow looks like this:

Now, the flow.txt file will end up looking a bit like this each time you tap/click on the Accept button in the app. Each time I tap it, it appends another line with the parameters for each applicable software update group name and deployment. This typically will be a monthly exercise for most.

Notice that again, the underscores are still in place of where there are normally spaces. I’ll cover converting the underscores back to spaces in a bit.

On any server with the ConfigMgr Admin Console installed on it (so that it has access to the ConfigurationManager.psd1 module), I had to install the Flow On-premises Gateway. On the gateway machine, I also put the various Powershell scripts I created above. The reason I needed the Flow gateway is because I’m having Flow take action on-prem (writing to the text file to trigger the script execution). I found it easiest to do this on my Primary Site server but it could really go anywhere.

The first script, I called FileWatcher.ps1, I set to run automatically on computer startup using a scheduled task.

My scheduled task was created using the following settings and command-line:

Command line:

powershell.exe -noexit -executionpolicy bypass -noprofile -file c:\scripts\filewatcher.ps1

General:

Run whether user is logged in or not

When running the task, use the following user account:

Select an account that has permissions to run the PowerShell cmdlets needed to enumerate software update groups, enable deployments, etc.

Trigger:

At system startup (make sure you uncheck the option to stop if it’s been running for 3 days which is automatically checked by default or it will only work for 3 days following each reboot!)

FileWatcher.ps1 is using the System.IO.FileSystemWatcher .NET class to detect a change in the file Flow.txt. When Flow appends a the new line to the Flow.txt file (containing the parameters for my update group deployment), it reads in only the last line, turns them into parameters (I called them $param1 and $param2), and passes them to my “Enable-Deployment.ps1” script which handles the final step – the enablement of the deployment.

FileWatcher.ps1

$FileSystemWatcher = New-Object System.IO.FileSystemWatcher
$FileSystemWatcher.path = "C:\temp\WatchMe"
$FileSystemWatcher.Filter = "Flow.txt"
$FileSystemWatcher.EnableRaisingEvents = $true
 
Register-ObjectEvent $FileSystemWatcher "Changed" -Action {
$content =  get-content C:\temp\WatchMe\Flow.txt | select-object -last 1
$content=$content.Replace("_"," ")
$Args=$content.Split(",")
$param1 = $Args[0]
$param2 = $Args[1]
[String]::Format("{0}, {1}", $param1, $param2)
 
cmd /c powershell.exe -executionpolicy bypass -noprofile -file c:\scripts\enable-deployment.ps1 $param1 $param2
$LogOutput = "Enable-Deployment.ps1 launched at $(Get-Date)"
 
$LogOutput | Out-File "c:\temp\watchme\log.txt" -append
} 

Enable-Deployment.ps1

[CmdletBinding()]
 
param(
    # Software Update Group
    [Parameter(Mandatory = $true, ValueFromPipeline=$true, Position=0)]
    [String] $UpdateGroup,
    [Parameter(Mandatory = $true, ValueFromPipeline=$true, Position=1)]
    [String] $AssignmentName
    )
 
 
$SiteCode = "PRI"
 
 
# Load ConfigMgr module if it isn't loaded already
if (-not(Get-Module -name ConfigurationManager)) 
   {
        Import-Module ($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager.psd1')
   }
 
# Change to site
    Push-Location
    Set-Location ${SiteCode}:
 
 
Set-CMSoftwareUpdateDeployment -SoftwareUpdateGroupName $UpdateGroup -DeploymentName $AssignmentName -Enable $true
 
$LogOutput = "The software update group $($UpdateGroup) with deployment name $($Assignmentname) was enabled at $(Get-Date)"
$LogOutput | Out-File "c:\temp\watchme\log.txt" -Append 

Enable-Deployment.ps1 uses the cmdlet Set-CMSoftwareUpdateDeployment cmdlet to actually enable the deployment.

Optionally storing the data in CDS

If you decide to store your data in CDS, you’ll first need to create a new entity in CDS. For each field, you’ll need to define the data type that correlates to the type of data that will be stored in each field. For any that include a combination of numeric and letter values, I recommend using a data type of “Text”. For any that are always numeric such as MaxRunTime, I used “Decimal number”, and for any that are Date and time, I used the type “Date and Time”. KBArticleID could’ve probably also been a decimal type.

To populate your CDS entity with the data queried by the script, create a Flow like this one. First, the PowerShell hits the automatically generated RestAPI URL provided automatically when creating the “HTTP Response” Flow trigger.

The JSON schema I ended up with is below.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "KB": {},
            "To": {},
            "Title": {},
            "URL": {},
            "Severity": {},
            "Date Posted": {},
            "Max RunTime (mins)": {},
            "DeploymentID": {},
            "UpdateGroup": {},
            "StartTime": {},
            "Deadline": {},
            "Description": {},
            "AssignmentName": {}
        }
    }
}

 Once the PowerShell script is initiated by the Message ID 5800 status message, you’ll be able to view and validate that the data made it to CDS.

And that’s it! It may seem like a lot of work just to enable a deployment, but this simple exercise opens the door to all kinds of automation using Flow, and viewing and interacting with the data within a PowerApp.