Creating WPF GUI Applications with Pure PowerShell

When creating a GUI application in PowerShell, I usually use Visual Studio, or Blend for Visual Studio, to design a WPF application, then copy and run the XAML code in PowerShell.  Designing in VS is generally easier and quicker and creates less code, but it is also perfectly possible to create a WPF GUI using pure PowerShell, which is more akin to the Windows Forms method of GUI creation.  For more complex applications that’s not the ideal way because it takes longer and creates a lot more code, but for simple applications, or if you are used to designing something in Windows Forms, why not give it a go?  You simply need to create a window from the [System.Windows] .Net namespace, then add some controls from the [System.Windows.Controls] namespace, and you’re away!

Here’s a simple example application that displays the running processes on your machine, the list of services, and a little game “Click the Fruit” as a bonus 😉

capture


# Add required assembly
Add-Type -AssemblyName PresentationFramework

# Create a Window
$Window = New-Object Windows.Window
$Window.Height = "670"
$Window.Width = "700"
$Window.Title = "PowerShell WPF Window"
$window.WindowStartupLocation="CenterScreen"

# Create a grid container with 2 rows, one for the buttons, one for the datagrid
$Grid =  New-Object Windows.Controls.Grid
$Row1 = New-Object Windows.Controls.RowDefinition
$Row2 = New-Object Windows.Controls.RowDefinition
$Row1.Height = "70"
$Row2.Height = "100*"
$grid.RowDefinitions.Add($Row1)
$grid.RowDefinitions.Add($Row2)

# Create a button to get running Processes
$Button_Processes = New-Object Windows.Controls.Button
$Button_Processes.SetValue([Windows.Controls.Grid]::RowProperty,0)
$Button_Processes.Height = "50"
$Button_Processes.Width = "150"
$Button_Processes.Margin = "10,10,10,10"
$Button_Processes.HorizontalAlignment = "Left"
$Button_Processes.VerticalAlignment = "Top"
$Button_Processes.Content = "Get Processes"
$Button_Processes.Background = "Aquamarine"

# Create a button to get services
$Button_Services = New-Object Windows.Controls.Button
$Button_Services.SetValue([Windows.Controls.Grid]::RowProperty,0)
$Button_Services.Height = "50"
$Button_Services.Width = "150"
$Button_Services.Margin = "180,10,10,10"
$Button_Services.HorizontalAlignment = "Left"
$Button_Services.VerticalAlignment = "Top"
$Button_Services.Content = "Get Services"
$Button_Services.Background = "Aquamarine"

# Create a button to play Click the fruit
$Button_Cool = New-Object Windows.Controls.Button
$Button_Cool.SetValue([Windows.Controls.Grid]::RowProperty,0)
$Button_Cool.Height = "50"
$Button_Cool.Width = "150"
$Button_Cool.Margin = "350,10,10,10"
$Button_Cool.HorizontalAlignment = "Left"
$Button_Cool.VerticalAlignment = "Top"
$Button_Cool.Content = "Play 'Click the Fruit'"
$Button_Cool.Background = "Aquamarine"

# Create a datagrid
$DataGrid = New-Object Windows.Controls.DataGrid
$DataGrid.SetValue([Windows.Controls.Grid]::RowProperty,1)
$DataGrid.MinHeight = "100"
$DataGrid.MinWidth = "100"
$DataGrid.Margin = "10,0,10,10"
$DataGrid.HorizontalAlignment = "Stretch"
$DataGrid.VerticalAlignment = "Stretch"
$DataGrid.VerticalScrollBarVisibility = "Auto"
$DataGrid.GridLinesVisibility = "none"
$DataGrid.IsReadOnly = $true

# Add the elements to the relevant parent control
$Grid.AddChild($DataGrid)
$grid.AddChild($Button_Processes)
$grid.AddChild($Button_Services)
$grid.AddChild($Button_Cool)
$window.Content = $Grid

# Add an event on the Get Processes button
$Button_Processes.Add_Click({
    $Processes = Get-Process | Select ProcessName,HandleCount,NonpagedSystemMemorySize,PrivateMemorySize,WorkingSet,UserProcessorTime,Id
    $DataGrid.ItemsSource = $Processes
    })

# Add an event on the Get Services button
$Button_Services.Add_Click({
    $Services = Get-Service | Select Name,ServiceName,Status,StartType
    $DataGrid.ItemsSource = $Services
    })

# Add an event to play Click the fruit
$Button_Cool.Add_Click({

    $Fruit = @{
        Apples = "Green"
        Bananas = "Yellow"
        Oranges = "Orange"
        Plums = "Maroon"
    }

    $Fruit.GetEnumerator() | Foreach {
        # Create a transparent window at a random location on the screen
        $NewWindow = New-Object Windows.Window
        $NewWindow.SizeToContent = "WidthAndHeight"
        $NewWindow.AllowsTransparency = $true
        $NewWindow.WindowStyle = "none"
        $NewWindow.Background = "Transparent"
        $NewWindow.WindowStartupLocation = "Manual"
        $Height = Get-Random -Maximum (([System.Windows.SystemParameters]::PrimaryScreenHeight) - 100)
        $Width = Get-Random -Maximum (([System.Windows.SystemParameters]::PrimaryScreenWidth) - 100)
        $NewWindow.Left = $Width
        $NewWindow.Top = $Height

        # Add a label control for the fruit
        $NewLabel = New-Object Windows.Controls.Label
        $NewLabel.Height = "150"
        $NewLabel.Width = "400"
        $NewLabel.Content = $_.Name
        $NewLabel.FontSize = "100"
        $NewLabel.FontWeight = "Bold"
        $NewLabel.Foreground = $_.Value
        $NewLabel.Background = "Transparent"
        $NewLabel.Opacity = "100"

        # Add an event to close the window when clicked
        $NewWindow.Add_MouseDown({
            $This.Close()
        })

        # Add an event to change the cursor to a hand when the mouse goes over the window
        $NewWindow.Add_MouseEnter({
        $this.Cursor = "Hand"
        })

        # Display the window
        $NewWindow.Content = $NewLabel
        $NewWindow.ShowDialog()
    }
})

# Show the window
if (!$psISE)
{
    # Hide PS console window
    $windowcode = '[DllImport("user32.dll")] public static extern bool ShowWindowAsync(IntPtr hWnd, int nCmdShow);'
    $asyncwindow = Add-Type -MemberDefinition $windowcode -name Win32ShowWindowAsync -namespace Win32Functions -PassThru
    $null = $asyncwindow::ShowWindowAsync((Get-Process -PID $pid).MainWindowHandle, 0) 

    # Run as an application in it's own context
    $app = [Windows.Application]::new()
    $app.Run($Window)
}
Else
{
    [void]$window.ShowDialog()
}

Temporarily Increasing the ConfigMgr Client Cache Size for a Large Application

Recently I had to deploy an application whose content files were larger than the default SCCM client cache size (5120 MB).  This will return an error in the Software Center, such as:

0x87D01201 (The content download cannot be performed because there is not enough available space in cache or the disk is full.)

I didn’t want to permanently increase the cache size, or require that user do it manually, so I investigated some options and came up with a couple of simple PowerShell scripts that can increase or decrease the cache size.  I put these scripts into a standard package and created a program for each script using a command-line like:

powershell.exe -ExecutionPolicy Bypass -WindowStyle Hidden -File .\Increase-CCMCacheSize

You then have various options for how you can run that.  For example, my application was being deployed using a task sequence as there are multiple steps, so I simply right-click the task sequence and on the Advanced tab, I check the option to Run another program first:

capture

This will increase the cache size before the task sequence starts to run, which means it will no longer give an error.

To restore the cache to it’s default size after the application install, I simply add an additional step in the task sequence at the end using the package I created:

capture

I haven’t tested it, but you could do something similar with the standard package model by right-clicking the package program, and setting the Run another program first option. The only issue there is that there is no option to run the script to restore the cache size after, unless you create a kind of dependency chain, ie:

Restore Cache size > (depends on) My Package > (depends on) Increase Cache size

For applications, you could also use the capability for a dependecy chain, but you would need to create the script as an application and use a detection method.

Increase-CCMCacheSize


# Increase SCCM Client cache size to 20000 MB (20GB)
$CCM = New-Object -com UIResource.UIResourceMGR
$CC = $CCM.GetCacheInfo()
$CacheSize = $CC.TotalSize

if ($CacheSize -lt 20000)
    {
        write-host "Setting cache size to 20000"
        try
        {
            $CC.TotalSize = 20000
        }
        Catch
        {
            $_
        }
}

Restore-CCMCacheSize


# Restore SCCM Client cache size to default (5120 MB)
$CCM = New-Object -com UIResource.UIResourceMGR
$CC = $CCM.GetCacheInfo()
$CacheSize = $CC.TotalSize

if ($CacheSize -gt 5120)
    {
        write-host "Setting cache size to 5120"
        try
        {
            $CC.TotalSize = 5120
        }
        Catch
        {
            $_
        }
}

Detection Method

For an application detection method, you could also use a PowerShell script, something like this:


# Detection method to check the SCCM Client cache size
$CCM = New-Object -com UIResource.UIResourceMGR
$CC = $CCm.GetCacheInfo()
$CacheSize = $CC.TotalSize

if ($CacheSize -eq 20000)
{
    write-host "Compliant"
}
Else
{
    write-host "Not-Compliant"
}

Prompting the End-User during ConfigMgr Application Installs

As a Configuration Manager administrator, from time to time I have to deploy an application where I need to notify the end-user of something before the installation begins. A recent example was a plugin for IE that would fail to install if Internet Explorer was running at the time. I can force-ably kill the running process of course, but that’s not necessarily a nice experience for the user – without warning their browser and any open tabs get closed. So better to notify them first, and give them a chance to close the application themselves and save any work. Rather than email each targeted user and warn them to close Internet Explorer before the plugin installs (which they probably ignore or forget anyway), I wanted the installation process to handle that by some kind of prompt.

I could create a script wrapper for the plugin but that would necessitate running in the user context to display interactively. An easier way is simply to install it using a task sequence with some additional steps that will prompt the user first, kill the process if necessary, then install the plugin. A task sequence also gives me better logging.

The problem with a task sequence is that it runs in the system context, so I cannot interact with the end user who is effectively working in a different session. This can be solved however by using the ServiceUI.exe that comes with MDT. Sometime ago I wrote a post about how to prompt for input during a task sequence, but in this case I don’t want input, I simply want to use a message box.  I also want something reusable – so I don’t have to create a new package for each custom prompt.

I have a nice PowerShell function that will create a message box for me using the Wscript.shell “popup” method, so I added this function to a script, where I have also defined the message parameters I want to use at the bottom.


function New-PopupMessage {
# Return values for reference (https://msdn.microsoft.com/en-us/library/x83z1d9f(v=vs.84).aspx)

# Decimal value    Description  
# -----------------------------
# -1               The user did not click a button before nSecondsToWait seconds elapsed.
# 1                OK button
# 2                Cancel button
# 3                Abort button
# 4                Retry button
# 5                Ignore button
# 6                Yes button
# 7                No button
# 10               Try Again button
# 11               Continue button

# Define Parameters
[CmdletBinding()]
    [OutputType([int])]
    Param
    (
        # The popup message
        [Parameter(Mandatory=$true,Position=0)]
        [string]$Message,

        # The number of seconds to wait before closing the popup.  Default is 0, which leaves the popup open until a button is clicked.
        [Parameter(Mandatory=$false,Position=1)]
        [int]$SecondsToWait = 0,

        # The window title
        [Parameter(Mandatory=$true,Position=2)]
        [string]$Title,

        # The buttons to add
        [Parameter(Mandatory=$true,Position=3)]
        [ValidateSet('Ok','Ok-Cancel','Abort-Retry-Ignore','Yes-No-Cancel','Yes-No','Retry-Cancel','Cancel-TryAgain-Continue')]
        [array]$ButtonType,

        # The icon type
        [Parameter(Mandatory=$true,Position=4)]
        [ValidateSet('Stop','Question','Exclamation','Information')]
        $IconType
    )

# Convert button types
switch($ButtonType)
    {
        "Ok" { $Button = 0 }
        "Ok-Cancel" { $Button = 1 }
        "Abort-Retry-Ignore" { $Button = 2 }
        "Yes-No-Cancel" { $Button = 3 }
        "Yes-No" { $Button = 4 }
        "Retry-Cancel" { $Button = 5 }
        "Cancel-TryAgain-Continue" { $Button = 6 }
    }

# Convert Icon types
Switch($IconType)
    {
        "Stop" { $Icon = 16 }
        "Question" { $Icon = 32 }
        "Exclamation" { $Icon = 48 }
        "Information" { $Icon = 64 }
    }

# Create the popup
(New-Object -ComObject Wscript.Shell).popup($Message,$SecondsToWait,$Title,$Button + $Icon)
}

# Close the Task Sequence Progress UI temporarily (if it is running) so the popup is not hidden behind
try
    {
        $TSProgressUI = New-Object -COMObject Microsoft.SMS.TSProgressUI
        $TSProgressUI.CloseProgressDialog()
    }
Catch {}

# Define the parameters.  View the function parameters above for other options.
$Params = @(
    "The software 'Custom IE Plugin' is being installed to your computer. Please close Internet Explorer then click OK to continue." # Popup message
    0                           # Seconds to wait till the popup window is closed
    "Contoso IT: Custom IE Plugin" # title
    "Ok"                        # Button type
    "Exclamation"               # Icon type
    )

# Run the function
New-PopupMessage @Params

I place this script in a network share that everyone can access, and then simply call it during the task sequence using ServiceUI.exe.

How to Do It

Firstly, I need to create a package in SCCM containing the ServiceUI.exe for x86 and x64 architectures.  This package has no program, but simply contains the exe files, which I have renamed per architecture.  You can find the ServiceUI.exe in the following locations in your MDT install:

C:\Program Files\Microsoft Deployment Toolkit\Templates\Distribution\Tools\x64, or
C:\Program Files\Microsoft Deployment Toolkit\Templates\Distribution\Tools\x86

Capture

Once I have created and distributed the package, I create a new task sequence and add two “Run command line” steps at the beginning where I will prompt the user, one for x86 OS and one for x64.

Capture

The following things are needed in this step:

  • Use the package you created that contains the ServiceUI executables
  • Call ServiceUI using a process that the end user is running.  This enables ServiceUI to detect the session of the end user and interact with it.  If you are using a task sequence deployment with the option “Show task sequence progress” enabled, then you can use the tsprogressui.exe process, however if you are hiding the task sequence progress from the user, then this process will not exist, so you can call Explorer.exe which is certain to be running in the user session.
    • Eg, ServiceUI_x86 -process:Explorer.exe
  • You must specify the full path to powershell.exe
    • Eg, %SYSTEMROOT%\System32\WindowsPowershell\v1.0\powershell.exe
  • Use the “-File” parameter to call the powershell script that displays the popup.
  • Do NOT use the “timeout” option in the step, as this will cause ServiceUI to give an access denied error.
  • On the Option tab of the step, I use a couple of WMI queries so that the step only runs if the correct OS architecture is detected, and the Internet Explorer process is actually running.  I don’t want to prompt the user to close IE if it’s not actually open.
    • Eg, Select * from win32_OperatingSystem where OSArchitecture = ’32-bit’
    • Select * from Win32_Process where Name = ‘iexplore.exe’

Capture

A couple of things to note:

  • You could include the PowerShell script in the package with the ServiceUI executables, then you can call it locally instead of from a network share.  But the advantage of keeping the script and the executables separate is that you don’t need to create a new package each time you want to add a prompt – you simply reuse the ServiceUI package and create a new PowerShell script in the network share by copying and updating and existing script.
  • If you are using the “Show task sequence progress” option, the script includes some code that will hide the progress UI temporarily while the popup is displayed, otherwise it may appear behind the progress UI.
  • Don’t try to pass parameters when calling the PowerShell script, ServiceUI doesn’t seem to like that.
  • The script function includes a “SecondsToWait” parameter – this is set to 0 by default, which means the popup will stay on the screen indefinitely until a button is clicked.  In some cases this may not be desirable, so you can set a value here such that the task sequence will continue if no button has been clicked for some time.

Next, in case the user ignored the prompt or it timed-out, we add another “Run command line” step to kill the process forcefully using taskkill, if it is still running.

  • Eg, cmd /c taskkill /F /IM iexplore.exe

Capture

Make sure to add the same WMI process query to this step:

Capture

Then in the last step, we install the application itself.

Now, when the application is deployed to the end user’s machine, the first thing that happens is they get a popup on the screen warning them to close Internet Explorer.

Capture

Sweet 🙂

You could customise this further by adding some code to the script that will set a task sequence variable based on the exit code of the popup function, which will tell you what button was pressed, for example Yes, No, Ok, Cancel, Abort, Retry etc.  Then you could perform different activities in the task sequence based on the value of the variable.

Redistribute Failed Package Distributions in ConfigMgr with PowerShell

Here’s a little script I wrote based on one written by David O’Brien that allows you to redistribute failed package distributions in Configuration Manager by selecting which packages you want to redistribute.

First the script queries WMI to find packages that are not in the “installed” state, ie the distribution is not successfully completed.  It will then display these in PowerShell’s gridview so you can view details about the package and the distribution.

capture

Simply select which packages you wish to redistribute and click OK.

Using the “Distribution Point Job Queue Manager” available from the Configuration Manager toolkit is a great way to monitor the distributions:

capture

Invoke-PackageRedistribution

Enter your sitecode at the top of the script, and run in on your site server.


$SiteCode = "ABC"
$failures = Get-WmiObject -Namespace root\sms\site_$SiteCode -Query "SELECT * FROM SMS_PackageStatusDistPointsSummarizer WHERE State <> 0" |
    Select ServerNALPath,LastCopied,PackageType,State,PackageID,SummaryDate |
        ForEach-Object {
            $PKG = Get-WmiObject -NameSpace root\sms\site_$SiteCode -Class SMS_Packagebaseclass -Filter "PackageID = '$($_.PackageID)'" | Select Name,PackageSize
            $server = $_.ServerNALPath.Split("\\")[2]
            $size = $PKG.PackageSize / 1KB
            $State =  switch ($_.State)
                {
                    1 {"Install_Pending"}
                    2 {"Install_Retrying"}
                    3 {"Install_Failed"}
                    4 {"Removal_Pending"}
                    5 {"Removal_Retrying"}
                    6 {"Removal_Failed"}
                    7 {"Instal_Start_Pending"}
                    8 {"Content Validation Failed"}
                }
            $Type = switch ($_.PackageType)
                {
                    0 {"Standard Package"}
                    3 {"Driver Package"}
                    4 {"Task Sequence Package"}
                    5 {"Software Update Package"}
                    6 {"Device Setting Package"}
                    7 {"Virtual App Package"}
                    8 {"Application Package"}
                    257 {"OS Image Package"}
                    258 {"Boot Image Package"}
                    259 {"OS Install Package"}
                }
            $LastCopied = [System.Management.ManagementDateTimeconverter]::ToDateTime($_.LastCopied)
            $SummaryDate = [System.Management.ManagementDateTimeconverter]::ToDateTime($_.SummaryDate)
            New-Object psobject -Property @{
                Name = $PKG.Name
                'PackageSize (MB)' = $size
                PackageType = $Type
                PackageID = $_.PackageID
                State = $State
                StateCode = $_.State
                DistributionPoint = $server
                LastCopied = $LastCopied
                SummaryDate = $SummaryDate
                }
        } |
            Select Name,'PackageSize (MB)',PackageType,PackageID,State,StateCode,DistributionPoint,LastCopied,SummaryDate | Sort LastCopied -Descending |
                Out-GridView -Title "Select package/s to redistribute" -OutputMode Multiple |
                 ForEach-Object {
                    Get-WmiObject -Namespace root\sms\site_$SiteCode -Query "SELECT * FROM SMS_DistributionPoint WHERE PackageID='$($_.PackageID)' and ServerNALPath like '%$($_.DistributionPoint)%'" |
                        ForEach-Object {
                            $_.RefreshNow = $true
                            $_.Put()
                        }
                    }

Searching the Registry Uninstall Key with PowerShell

Here’s a little PowerShell function I wrote that searches the Uninstall key in the registry for DisplayNames and product code GUIDs.  I wrote it to help in finding the relevant uninstall key to use for the registry detection method when creating new applications in System Center Configuration Manager.  You can use it to output all the DisplayNames and GUIDs in the key, or search for a keyword to filter the results.  On 64-bit systems, it can also search the Wow6432Node.

UPDATE! – Oct-16-2015 – Script updated to include “DisplayVersion” key.

Examples

Output all the DisplayNames and GUIDs in the 32-bit and 64-bit uninstall registry keys to GridView:

Search-RegistryUninstallkey -Wow6432Node | Out-GridView

Capture

Search for all products with “Apple” in the DisplayName (excluding the Wow6432Node):

Search-RegistryUninstallkey -SearchFor "Apple"

Capture

Search for all products with “Apple” in the DisplayName (including the Wow6432Node):

Search-RegistryUninstallkey -SearchFor "Apple" -Wow6432Node

Capture

Code

function Search-RegistryUninstallKey {
param($SearchFor,[switch]$Wow6432Node)
$results = @()
$keys = Get-ChildItem HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall | 
    foreach {
        $obj = New-Object psobject
        Add-Member -InputObject $obj -MemberType NoteProperty -Name GUID -Value $_.pschildname
        Add-Member -InputObject $obj -MemberType NoteProperty -Name DisplayName -Value $_.GetValue("DisplayName")
        Add-Member -InputObject $obj -MemberType NoteProperty -Name DisplayVersion -Value $_.GetValue("DisplayVersion")
        if ($Wow6432Node)
        {Add-Member -InputObject $obj -MemberType NoteProperty -Name Wow6432Node? -Value "No"}
        $results += $obj
        }

if ($Wow6432Node) {
$keys = Get-ChildItem HKLM:\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall | 
    foreach {
        $obj = New-Object psobject
        Add-Member -InputObject $obj -MemberType NoteProperty -Name GUID -Value $_.pschildname
        Add-Member -InputObject $obj -MemberType NoteProperty -Name DisplayName -Value $_.GetValue("DisplayName")
        Add-Member -InputObject $obj -MemberType NoteProperty -Name DisplayVersion -Value $_.GetValue("DisplayVersion")
        Add-Member -InputObject $obj -MemberType NoteProperty -Name Wow6432Node? -Value "Yes"
        $results += $obj
        }
    }
$results | sort DisplayName | where {$_.DisplayName -match $SearchFor}
} 

Retrieving Software Inventory for a ConfigMgr Site with PowerShell

In my last post, I demonstrated how we can retrieve software inventory information for a single ConfigMgr client or an array of clients, using PowerShell.  In this post, we will change the scope from the client to the entire site.  Using this script, you can query for all installations of a specific software/s in your ConfigMgr site, returning either the count, or the full list of machines with the software installed.

When I say “software inventory”, I’m actually referring to the “hardware inventory” process (strange but true) in Configuration Manager that collects data from WMI classes, including the installed software, and not to be confused with the “software inventory” process in ConfigMgr which is used to inventory file types.

As previously, you need ‘db_datareader’ permission to your ConfigMgr database, with your logged on account, and you need to add the sql server and database name in the script.

Examples

Find a count of machines that have “Microsoft Silverlight” installed


Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight%" -Count

capture1

Get the list of machines that have “Microsoft Silverlight 5” installed and output to Gridview


Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight 5%" | Out-GridView

Capture2

Get the list of machines that have “Microsoft Silverlight 5” installed and output to CSV

 Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight 5" -CSV 

Capture3

Get-CMInstalledSoftware


[CmdletBinding(SupportsShouldProcess=$True)]
    param
        (
        [Parameter(Mandatory=$False,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
            [string]$SoftwareName = "%",
        [Parameter(Mandatory=$False)]
            [switch]$Count,
        [Parameter(Mandatory=$False)]
            [switch]$CSV,
        [Parameter(Mandatory=$False)]
            [string]$SQLServer = “mysqlserver\INST_SCCM”, # eg, <sqlserver>, or <sqlserver>\<instance>
        [Parameter(Mandatory=$False)]
            [string]$Database = “CM_ABC”
        )
 
# Open a connection
$connectionString = “Server=$SQLServer;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
 
# Set queries
if ($count)
    {
        $query = "
        SELECT Count(sof.NormalizedName) AS 'Count',
        sof.NormalizedName, sof.NormalizedVersion, sof.NormalizedPublisher, sof.FamilyName, sof.CategoryName
        FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED sof
        where sof.NormalizedName like '$SoftwareName'
        GROUP BY sof.NormalizedName, sof.NormalizedVersion, sof.NormalizedPublisher, sof.FamilyName, sof.CategoryName
        ORDER BY 'Count' DESC, sof.NormalizedName, sof.NormalizedVersion
        "
    }
else
    {
        $query = "select Name0 as 'Computer Name',
        User_Name0 as 'Last Logged-On User',
        NormalizedName as 'Software Name',
        NormalizedPublisher as Publisher,
        NormalizedVersion as Version,
        FamilyName as 'Software Family',
        CategoryName as 'Software Category',
        InstallDate0 as 'Install Date',
        RegisteredUser0 as 'Registered User',
        InstalledLocation0 as 'Install Location',
        InstallSource0 as 'Source Location',
        UninstallString0 as 'Uninstall String',
        TimeStamp as 'Inventory Time'
        from v_GS_INSTALLED_SOFTWARE_CATEGORIZED sof
        inner join v_R_System sys on sof.ResourceID = sys.ResourceID
        where sof.NormalizedName like '$SoftwareName' order by Name0
        "
    }
    
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)

# Output results
if ($CSV)
    {
        $Path = "$env:TEMP\SoftwareQuery-$(Get-date -format hh-mm).csv"
        $table | Export-Csv -Path $Path -Force -NoTypeInformation
        Invoke-Item $Path
    }
Else {$table}

# Close the connection
$connection.Close()

Instant Client Software Inventory with ConfigMgr and PowerShell

Here’s a simple but handy PowerShell script I wrote that uses the ConfigMgr database to retrieve software inventory information for any client.  You can return the entire inventory for the client, or search for specific software.  You can also pass the computer name and/or software name along the pipeline to the script, so you can search multiple computers or multiple software titles.

You need ‘db_datareader’ access to your ConfigMgr database with your logged-on account, and you also need to add the ‘Installed Software‘ class to your hardware inventory classes, in your ConfigMgr Client Settings.

Examples

Search for software with “Apple” in the title for a specific client:


Get-CMClientInstalledSoftware -ComputerName mypc-tj8 -SoftwareName %Apple%

capture3

Retrieve the entire software inventory for a client, output to GridView


Get-CMClientInstalledSoftware -ComputerName mypc-tj8 | Out-GridView

capture2Search for “Cisco Webex Meeting Center for FireFox” on an array of clients

Uses PowerShell 4.0’s ForEach method


($computers = @("mypc-tj","mypc-tj8").ForEach({Get-CMClientInstalledSoftware -ComputerName $psitem -SoftwareName "%Cisco%FireFox%"}))

captureGet-CMClientInstalledSoftware

Update the $SQLServer and $Database parameters with your ConfigMgr SQL server (and instance if applicable) and database name.


[CmdletBinding(SupportsShouldProcess=$True)]
    param
        (
        [Parameter(Mandatory=$True,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
            [string]$ComputerName,
        [Parameter(Mandatory=$False,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
            [string]$SoftwareName = "%",
        [Parameter(Mandatory=$False)]
            [string]$SQLServer = “mysqlserver\INST_SCCM”, # eg <mysqlserver>, or <mysqlserver\instance>
        [Parameter(Mandatory=$False)]
            [string]$Database = “CM_ABC”
        )

# Open a SQL connection
$connectionString = “Server=$SQLServer;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Set query
$query = "select Name0 as 'Computer Name',
User_Name0 as 'Last Logged-On User',
NormalizedName as 'Software Name',
NormalizedPublisher as Publisher,
NormalizedVersion as Version,
FamilyName as 'Software Family',
CategoryName as 'Software Category',
InstallDate0 as 'Install Date',
RegisteredUser0 as 'Registered User',
InstalledLocation0 as 'Install Location',
InstallSource0 as 'Source Location',
UninstallString0 as 'Uninstall String',
TimeStamp as 'Inventory Time'
from v_GS_INSTALLED_SOFTWARE_CATEGORIZED sof
inner join v_R_System sys on sof.ResourceID = sys.ResourceID
where sys.Name0 = '$ComputerName'
and sof.NormalizedName like '$SoftwareName'
order by 'Software Name'"

# Execute query
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

# Load results
$table = new-object “System.Data.DataTable”
$table.Load($result)

# Output results
$table

# Close the connection
$connection.Close()