Get Office add-ins/plugins from infrastructure using SCCM and PowerShell

While this is not a new topic, we all know that getting all Office add-ins installed on the machines of an infrastructure is a tricky topic. Microsoft released a new add-in inventory report for Configuration Manager back in 2017, but I didn’t wanted to use a mof file in my SCCM, nor I didn’t want my databse to be altered (it’s already cluttered as it is). If you want to use this method, check out the github page for the script and SCCM report here.

You have an alternative option of using Exchange PowerShell and create a one-Liner that is needed to create a csv-export of all the apps that your users are using, but that method was not what I was searching for either.

The script

I just wanted something easy that I can deploy in the infrastructure and put everything in a CSV on a local share that everybody has access, and then I decided to create a PowerShell script. The code is as follows:

function Check-FileOpen {

    param (

          [parameter(Mandatory=$true)]

          [string]$Path

      )

    $oFile = New-Object System.IO.FileInfo $Path

    if ((Test-Path -Path $Path) -eq $false)

    {

      $false

      return

    }

    try

    {

        $oStream = $oFile.Open([System.IO.FileMode]::Open, [System.IO.FileAccess]::ReadWrite, [System.IO.FileShare]::None)

        if ($oStream)

        {

          $oStream.Close()

        }

        $false

    }

    catch

    {

      # file is locked by a process.

      $true

    }

  }

$searchScopes = "HKLM:\SOFTWARE\Microsoft\Office\Outlook\Addins","HKCU:\SOFTWARE\Microsoft\Office\Outlook\Addins","HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\Outlook\Addins", "HKLM:\SOFTWARE\Microsoft\Office\Word\Addins","HKCU:\SOFTWARE\Microsoft\Office\Word\Addins", "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\Word\Addins", "HKLM:\SOFTWARE\Microsoft\Office\Excel\Addins","HKCU:\SOFTWARE\Microsoft\Office\Excel\Addins", "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\Excel\Addins", "HKLM:\SOFTWARE\Microsoft\Office\MS Project\Addins", "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\MS Project\Addins", "HKCU:\SOFTWARE\Microsoft\Office\PowerPoint\Addins", "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\PowerPoint\Addins"

$test = $searchScopes | % {Get-ChildItem -Path $_ | % {Get-ItemProperty -Path $_.PSPath} | Select-Object @{n="Name";e={Split-Path $_.PSPath -leaf}},FriendlyName,Description} | Sort-Object -Unique -Property name



foreach ($tst in $test){

$test | Add-Member -Name 'Computer Name' -Type NoteProperty -Value $env:COMPUTERNAME

}

write-host $test

while ((Check-FileOpen -Path "C:\Users\YOURUSER\Desktop\test.csv")){

Start-Sleep -s 15

   Write-Host "File in Use"

}

 Write-Host "File Not in Use"

$test | export-csv -Path C:\Users\YOURUSER\Desktop\test.csv -NoTypeInformation -Append

Reviewing the script

Ok, now let’s understand what the heck this script is doing. The first function called Check-FileOpen is what I discussed in this article. We are checking if the file is opened/in use, because when we will start the script in the infrastructure, there is no way of controlling how the script is executed. Basically two or more users could try to write in the file at the same time, and unfortunately this will lead in an error in PowerShell, and data from a certain user will be lost. I placed a 15 seconds delay because it’s more than enough to gather all the Office Add-ins.
$searchScopes = "HKLM:\SOFTWARE\Microsoft\Office\Outlook\Addins","HKCU:\SOFTWARE\Microsoft\Office\Outlook\Addins","HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\Outlook\Addins", "HKLM:\SOFTWARE\Microsoft\Office\Word\Addins","HKCU:\SOFTWARE\Microsoft\Office\Word\Addins", "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\Word\Addins", "HKLM:\SOFTWARE\Microsoft\Office\Excel\Addins","HKCU:\SOFTWARE\Microsoft\Office\Excel\Addins", "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\Excel\Addins", "HKLM:\SOFTWARE\Microsoft\Office\MS Project\Addins", "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\MS Project\Addins", "HKCU:\SOFTWARE\Microsoft\Office\PowerPoint\Addins", "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Office\PowerPoint\Addins"

$test = $searchScopes | % {Get-ChildItem -Path $_ | % {Get-ItemProperty -Path $_.PSPath} | Select-Object @{n="Name";e={Split-Path $_.PSPath -leaf}},FriendlyName,Description} | Sort-Object -Unique -Property name
This next part of the script checks all the registry keys where Office Add-Ins might be mentioned. When you install an Office plugin, this is automatically placed in one of the registry keys (HKCU or HKLM) of the respective Office App for which the plugin is intended. For example, if you have a Word plugin that is designed to be installed per-user, this will be mentioned in this registry key:
HKCU:\SOFTWARE\Microsoft\Office\Word\Addins
We are placing the name, friendly name and description of the found add-ins in a PowerShell object so that it’s easier to output the result in a CSV.
foreach ($tst in $test){

$test | Add-Member -Name 'Computer Name' -Type NoteProperty -Value $env:COMPUTERNAME

}
Next, I want to add some extra information in the object where we store the Office Add-Ins, and that is the computer name. I am adding this because at the end, I want to filter the results on a specific machine in Excel.
while ((Check-FileOpen -Path "C:\Users\YOURUSER\Desktop\test.csv")){

Start-Sleep -s 15

   Write-Host "File in Use"

}

 Write-Host "File Not in Use"

$test | export-csv -Path C:\Users\YOURUSER\Desktop\test.csv -NoTypeInformation -Append
The last part of the script checks if the file is opened/in use, and if it’s not, we append the information to it.
As a disclaimer, change the paths in the script to reflect your share. This is just an example which outputs the CSV on my desktop. Also, make sure that the location you create/append the CSV data is accessible by everybody.

Running the script with SCCM

The last thing we need to do is actually push the script in the infrastructure and run it on each machine (in the user context). Now, there are a tons of ways in which you can execute PowerShell scripts in an infrastructure, and this is not limited to SCCM. You can deploy it via a GPO, Intune, 3rd party infrastructure management tools, etc.
With SCCM you have 3 options:
1. Create a Package and a Program, then deploy it as required for all users
2. Create an Application, then deploy it as required for all users
3. Deploy it using Scripts
The choice is yours, but at the end, you should have a CSV on a share of your choice something like this:

Sharing is caring!

Leave a comment

Your email address will not be published. Required fields are marked *

17 + fourteen =