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:

• Get PsInfo bits and info: http://www.microsoft.com/technet/sysinternals/utilities/psinfo.mspx
• Get PowerShell bits and info: http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx
• The PowerShell blog: http://blogs.msdn.com/powershell/

Enjoy!