Writing the results from an Intune executed Powershell script to Azure Table Storage

Edit 23/05/20: The script in this post omits a check for the presence of the required Powershell modules required to run certain cmdlets, please see new accompanying post for details on how to check for the presence, and install required modules remotely using Intune.

Anyone who has worked as a SysAdmin over the years managing either server or client estates, or both, and likely remotely, will have undoubtedly had to hack up a script of varying complexity to push out some sort of workaround or fix, be it adding a registry key or deleting old profiles to recoup some disk space.

That was all straight forward in ‘traditional’ Windows-based server-client environments were connectivity to the source was over a reliable LAN or WAN and the hardest part was remembering the correct syntax for PSEXEC, but that is no longer the case in the more modern of workplaces were devices are Azure AD joined and use the internet as their default communication medium.

The is where Intune (Endpoint Manager) comes in, the ability to remotely execute Powershell scripts to enrolled devices isn’t new and I’ve used it increasingly over the months to bridge a few small gaps, usually where a particular policy setting I needed wasn’t natively available, such as setting the properties of a local account that was created using an Intune policy, such as ‘password does not expire’ but I’ll cover that end-to-end in another post as it’s handy to know.

So, as mentioned, the actual pushing out of a script in Intune is pretty straight forward however centrally capturing the output isn’t. The Intune portal provides a very basic reporting function that displays whether the script was successfully deployed or not, that is to say, did it exit with defined exit code, but you do not get anything remotely verbose in the portal, for example, it does not capture any directed output.

So, in a decentralised environment with no common LAN or server infrastructure to write results too what are the options?

I did once unsuccessfully experiment trying to write to Azure Blob Storage and have contemplated similar using Azure Files and mapping drives within the script but for now, after consulting the MDM community on Twitter I came across a post by Travis Roberts that provided a great article on writing the output of a Powershell script to Azure Table Storage.

Note, the script that follows is directly adapted from Travis Roberts’ original blog post, so please check that out. Thanks, Travis.

Right, so let’s quickly cover the actual use-case here, why do I need to write a Powershell script and push it out via Intune and why am I so concerned with capturing the results?

The answer is I was contacted by a customer who as part of a wider Microsoft 365 adoption we deployed Intune for and was asked whether we could use Intune to quickly report on whether a particular Microsoft hotfix had been installed on all of their Windows 10 devices in response to a recent threat – the short answer was no we couldn’t.  Yes, we were using Intune to define Windows Updates but again the reporting was not detailed enough.

The result was the below, this script when executed searches for the presence of a defined hotfix by the KB it was wrapped in and writes the results to a very simple Table in Azure which I could use Storage Explorer to monitor and report from.

In readiness to utilise this script you must have already created a Table within an Azure Storage Account and generated a SAS key for a defined window.

I’ve used a generic Partition Key of KBCheck to satisfy the data integrity constraints.

# Step 1, use Start-Transcript to capture the execution to a text file and set variables for connecting to Azure Table

Start-Transcript -Path "C:\TempLogs\KB4541338Check-$(((get-date).ToUniversalTime()).ToString("yyyyMMddThhmmssZ")).log"

$storageAccountName = 'StorageAccountName'
$tableName = 'TableName'
$sasToken = 'SASKeyHere' 
$dateTime = get-date
$partitionKey = 'KBCheck'

# Step 2, Connect to Azure Table Storage
$storageCtx = New-AzureStorageContext -StorageAccountName $storageAccountName -SasToken $sasToken
$table = (Get-AzureStorageTable -Name $tableName -Context $storageCtx).CloudTable

# Step 3, Check for presence of hotfix
Write-Host "Checking for KB4541338"

if (get-hotfix -Id KB4541338) {
$PatchCheck = "Installed"
Write-Host "KB4541338 Installed"
} 
else {
$PatchCheck = "Not Installed"
Write-Host "KB4541338 Missing"
}
# Step 4, Write data to Table Storage and end transcript.

Write-Host "Writing to Azure Table $table"

Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ([guid]::NewGuid().tostring()) -property @{
'LocalHostname' = $env:computername
'PatchStatus' = $PatchCheck
} | Out-Null

Stop-Transcript

The output of the script when viewed in Azure Storage Explorer is very simple, it shows the device hostname and either ‘Installed’ or ‘Not Installed’ depending on whether the KB was found.

This script can easily be used as a framework for capturing other simple text-based outputs using Intune and Powershell, simply update the body of the script in step 3.

Thanks again to Travis Roberts for the original script.