Jaa

Quick and Dirty Software Inventory with PsInfo and PowerShell

  • Artikkeli

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

  • Anonymous
    January 01, 2003
    Sweet! Check out this post from Otto Helweg on using PsInfo and PowerShell (I <3 you, PowerShell,

  • Anonymous
    January 01, 2003
    Besides creating a custom WMI report, you could use PSInfo to do something similar I believe. Combine

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    Tonight I stumbled across this blog and thought it was interesting. [link] I like it so much I think

  • Anonymous
    January 01, 2003
    Last Friday Quest software made GA the RC of the ActiveRoles ADcmdlets. This, to me, is another significant

  • Anonymous
    January 01, 2003
    Hey Bernd, 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. Thanks for your feedback, Otto

  • Anonymous
    January 01, 2003
    Hello Ray, 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). Hope that helps, Otto

  • Anonymous
    March 12, 2007
    Hi, nice thing. But what about removed software? That should be marked as deleted. I did myself something similar but not with Powershell. I remebered the last output from psinfo -s and diff´ed this with the actual output, so i get all those software which has been deleted from the last run..... Regards, Bernd.

  • Anonymous
    March 19, 2007
    The comment has been removed

  • Anonymous
    March 22, 2007
    This is great! Thanks for this post!

  • Anonymous
    April 13, 2007
    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? 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. Thank you in advance.

  • Anonymous
    April 01, 2008
    The comment has been removed

  • Anonymous
    June 03, 2008
    This can be done natively without pstools. It's all available in wmi. get-wmiobject -class "win32_Product" -namespace "rootcimv2" -computername sirvine | sort Name | select-object Name,Vendor,Version,Caption | export-csv sirvineaudit.csv

  • Anonymous
    June 13, 2008
    The comment has been removed