Otto Helweg - Management Matters

Management, monitoring, automation, and instrumentation topics for the IT-Pro.

Quick and Dirty Software Inventory with PsInfo and PowerShell

Quick and Dirty Software Inventory with PsInfo and PowerShell

  • Comments 14
  • Likes

PsInfo is great for gathering asset information from Windows computers, both locally and remotely. PowerShell is great for automation and cleaning up output (among other things) as well as working with database driven data.

The following examples show how to gather an itemized list of the installed software on remote machines, process the data, then either display it to the screen or store it in a database. It's worth noting that PsInfo can also work on multiple remote computers from its native command line, or even read a list of computers from a file (check out the PsInfo site for more info). Since the final example seeks to show PsInfo in a database driven envoriment, PowerShell comes in very handy.

Note: In order for this example to work the necessary network connectivity and credentials will need to be in place.

Consider the following examples:

1 - The output is merely displayed on the screen. With this method the output can be redirected to a file and imported into an application like Excel for further analysis or record keeping.

2 - A database is used to drive the computers polled as well as store the output. The database table is very flat (one table) with 2 fields: 'Computer' and 'Software'. For large amounts of data, this will need to be normalized.

With the following output (imported into Excel):

 

Example 1: Standard Screen Output

The following PowerShell script gathers a software inventory from 3 remote computers ('happyhour', 'shaken', and 'extradry'). Presumably, your computer names will be different. After gathering and parsing the data, it's then displayed on the screen for all machines successfully queried.

Before running this script, test your connectivity and credentials with a single PsInfo command:

PsInfo -s Applications \\somecomputer

Example PowerShell script:

$computersToQuery = ("happyhour","shaken","extradry")

$softwareInventory = @{}
foreach ($computer in $computersToQuery) {
   $psinfoOutput = ./psinfo.exe -s Applications \\$computer

   $foundSoftwareInventory = 0
   $computerName = ""
   foreach ($item in $psinfoOutput) {
      if ($foundSoftwareInventory -eq 1) {
         # Force the results to a string
         # Remove any single quotes which interfere with T-SQL statements
         # Load the result into a hash whereby removing any duplicates
         [string]$softwareInventory[$computerName][$item.Replace("'","")] = ""
      }

      if ($item -like "System information for *") {
         $computerName = $item.Split("\")[2].TrimEnd(":")
      } elseif ($item -eq "Applications:") {
         $foundSoftwareInventory = 1
         $softwareInventory[$computerName] = @{}
      }
   }
}

foreach ($computer in $softwareInventory.Keys) {
   foreach ($softwareItem in $softwareInventory[$computer].Keys) {
      $computer + ":" + $softwareItem
   }
}

Your output should look something like:

Example 2: Save Output to a Database

This example is additive to the first in that it adds the following 3 items:

  • 1- Pulls the list of computer to query from a database table
  • 2- Adds the current data and time to the result
  • 3- Records the audit results into a database

The following is the database schema for this example:

Example PowerShell script:

# Open the database connection
$dbConn = new-object System.Data.SqlClient.SqlConnection "server=kcdb;database=Inventory;Integrated Security=sspi"
$dbConn.Open()
$sqlQuery = $dbConn.CreateCommand()

# Get all known computers
$sqlQuery.CommandText = "select * from Inventory..Computers"
$reader = $sqlQuery.ExecuteReader()
$computersToQuery = @()
while ($reader.Read()) {
   $computersToQuery += $reader["Computer"]
}

# Close the database connection
$dbConn.Close()

$softwareInventory = @{}
foreach ($computer in $computersToQuery) {
   $psinfoOutput = ./psinfo.exe -s Applications \\$computer

   $foundSoftwareInventory = 0
   $computerName = ""
   foreach ($item in $psinfoOutput) {
      if ($foundSoftwareInventory -eq 1) {
         # Force the results to a string
         # Remove any single quotes which interfere with T-SQL statements
         # Load the result into a hash whereby removing any duplicates
         [string]$softwareInventory[$computerName][$item.Replace("'","")] = ""
      }

      if ($item -like "System information for *") {
         $computerName = $item.Split("\")[2].TrimEnd(":")
      } elseif ($item -eq "Applications:") {
         $foundSoftwareInventory = 1
         $softwareInventory[$computerName] = @{}
      }
   }
}

$dbConn = new-object System.Data.SqlClient.SqlConnection "server=kcdb;database=Inventory;Integrated Security=sspi"
$dbConn.Open()
$sqlQuery = $dbConn.CreateCommand()

foreach ($computer in $softwareInventory.Keys) {
   foreach ($softwareItem in $softwareInventory[$computer].Keys) {
      "Loading-" + $computer + ":" + $softwareItem

      # Try an Insert than an Update
      trap {
         $sqlQuery.CommandText = "update Inventory..SoftwareInventory set AuditDate = getdate() where  Computer = '" + $computer + "' and Software = '" + $softwareItem + "'"
         $result = $sqlQuery.ExecuteNonQuery()
         continue
      }

      $sqlQuery.CommandText = "insert into Inventory..SoftwareInventory (Computer,Software,AuditDate) values ('" + $computer + "','" + $softwareItem + "',getdate())"
      $result = $sqlQuery.ExecuteNonQuery()
   }
}

$dbConn.Close()

For more information:

Comments
  • <p>Hi, </p> <p>nice thing. But what about removed software? That should be marked as deleted. I did myself something similar but not with Powershell. </p> <p>I remebered the last output from psinfo -s and diff&#180;ed this with the actual output, so i get all those software which has been deleted from the last run.....</p> <p>Regards, Bernd.</p>

  • <p>Hey Bernd,</p> <p>That's a good point. I looked that the above database enabled script to see what modifications would need to be made to allow removed applications to be trackable and realized that it will work as is (although I admit, that was not my intention). Every time the script is run, it will add/overwrite entries for the applications with a new time stamp, but it won't delete previous entries for a computer. Therefore one can query for specific computer and see the application audit history.</p> <p>Thanks for your feedback,</p> <p>Otto</p>

  • <p>Sweet! Check out this post from Otto Helweg on using PsInfo and PowerShell (I &amp;lt;3 you, PowerShell,</p>

  • <p>I could not run the sample scripts:</p> <p>error:</p> <p>PS C:\temp\Pstoolsv2.43&gt; c:\temp\pstoolsv2.43softwareinventory.ps1</p> <p>File C:\temp\pstoolsv2.43\softwareinventory.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see &quot;get-help about_signing&quot; for more details.</p> <p>At line:1 char:42 + c:\temp\pstoolsv2.43\softwareinventory.ps1 &lt;&lt;&lt;&lt;</p> <p>PS C:\temp\Pstoolsv2.43&gt;</p>

  • <p>Hello Ramon,</p> <p>The default PowerShell execution policy is to only run &quot;signed&quot; scripts (makes it less likely to be a vehicle for worms). In order to change this, run the following command in PowerShell:</p> <p>set-executionpolicy remotesigned</p> <p>Otto</p>

  • <p>This is great! Thanks for this post!</p>

  • <p>I found this info helpful but will show my ignorance on one point. You reference a database in part 2. What did you use to create this database? We are at a small, private college and have SQL Server Express installed there, as well as SQL Server itself. Would the express edition be sufficient for databases of information that would be from or used with Powershell applications?</p> <p>Also, I am assuming that the database was created beforehand. If the script creates it, which it does not look like it does, how does it do this? If the script does not create the tables or database, but merely updates an existing database, then please ignore this second question.</p> <p>Thank you in advance.</p>

  • <p>Hello Ray,</p> <p>No, the script does not create the database. It was created beforehand. Since I'm using SQL 2005, I use 'SQL Server Management Studio' to create my database(s) and table(s). This should install with the Client Tools (but might not be available for the 'express' version - never tried that one).</p> <p>Hope that helps,</p> <p>Otto</p>

  • <p>Tonight I stumbled across this blog and thought it was interesting. [link] I like it so much I think</p>

  • <p>Last Friday Quest software made GA the RC of the ActiveRoles ADcmdlets. This, to me, is another significant</p>

  • <p>I have tried the script on a group of 100 servers, but what i find is that &nbsp;while on the powershell screen it loads many applications, i find only a handful actually appear in the database. Could the strip duplicates actually strip duplicates from other servers?</p>

  • <p>This can be done natively without pstools. It's all available in wmi.</p> <p>get-wmiobject -class &quot;win32_Product&quot; -namespace &quot;root\cimv2&quot; -computername sirvine | sort Name | select-object Name,Vendor,Version,Caption | export-csv sirvineaudit.csv</p>

  • <p>The script does not work for me. &nbsp;It does not recognize the psinfo call made be the script. &nbsp;Below is the error output.</p> <p>The term './psinfo.exe' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.</p> <p>At line 5, position 35</p> <p> &nbsp; $psinfoOutput = ./psinfo.exe -s Applications \\$computer</p>

  • <p>Besides creating a custom WMI report, you could use PSInfo to do something similar I believe. Combine</p>

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment