PowerShell script for SQL Network Software Inventory

7 Jan

I wanted to populate a SQL database with all the software on all the machines on the network (This was before I started using SCCM, although this is still quite handy).

I could then see what had been installed each day and machine sure we were correctly licensed. I could also check for any unwanted software and any out of date software.

First was the get the SQL table created:

[crayon]
CREATE TABLE [dbo].[softwareinstalled](

[computername] [varchar](50) NULL,

[datetime] [datetime] NULL,

[name] [varchar](100) NULL,

[publisher] [varchar](100) NULL,

[installdate] [datetime] NULL,

[estimatedsize] [decimal](10, 2) NULL,

[version] [varchar](100) NULL

) ON [PRIMARY]
[/crayon]
Then I needed the PowerShell script to run each data to get the data

[crayon]
#=Functions====================================================

Function Get-InstalledSoftware{
Param([String[]]$Computers)
If (!$Computers) {$Computers = $ENV:ComputerName}
$Base = New-Object PSObject;
$Base | Add-Member Noteproperty ComputerName -Value $Null;
$Base | Add-Member Noteproperty Name -Value $Null;
$Base | Add-Member Noteproperty Publisher -Value $Null;
$Base | Add-Member Noteproperty InstallDate -Value $Null;
$Base | Add-Member Noteproperty EstimatedSize -Value $Null;
$Base | Add-Member Noteproperty Version -Value $Null;
$Base | Add-Member Noteproperty Wow6432Node -Value $Null;
$Results = New-Object System.Collections.Generic.List[System.Object];

ForEach ($ComputerName in $Computers){
$Registry = $Null;
Try{$Registry = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]::LocalMachine,$ComputerName);}
Catch{Write-Host -ForegroundColor Red “$($_.Exception.Message)”;}

If ($Registry){
$UninstallKeys = $Null;
$SubKey = $Null;
$UninstallKeys = $Registry.OpenSubKey(“Software\Microsoft\Windows\CurrentVersion\Uninstall”,$False);
$UninstallKeys.GetSubKeyNames()|%{
$SubKey = $UninstallKeys.OpenSubKey($_,$False);
$DisplayName = $SubKey.GetValue(“DisplayName”);
If ($DisplayName.Length -gt 0){
$Entry = $Base | Select-Object *
$Entry.ComputerName = $ComputerName;
$Entry.Name = $DisplayName.Trim();
$Entry.Publisher = $SubKey.GetValue(“Publisher”);
[ref]$ParsedInstallDate = Get-Date
If ([DateTime]::TryParseExact($SubKey.GetValue(“InstallDate”),”yyyyMMdd”,$Null,[System.Globalization.DateTimeStyles]::None,$ParsedInstallDate)){
$Entry.InstallDate = $ParsedInstallDate.Value
}
$Entry.EstimatedSize = [Math]::Round($SubKey.GetValue(“EstimatedSize”)/1KB,1);
$Entry.Version = $SubKey.GetValue(“DisplayVersion”);

$now=Get-Date
$computername = $Entry.ComputerName
$name = $Entry.Name
$publisher = $Entry.Publisher
$installdate = $Entry.InstallDate
$estimatedsize = $Entry.EstimatedSize
$version = $Entry.Version

# Write Values to SQL
$dbconn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=ServerName; Initial Catalog=DBName; Integrated Security=SSPI”)
$dbconn.Open()
$dbwrite = $dbconn.CreateCommand()
$dbwrite.CommandText = “INSERT INTO dbo.softwareinstalled (computername, datetime, name, publisher, installdate, estimatedsize, version) VALUES (‘$computername’,’$now’,’$name’,’$publisher’,’$installdate’,’$estimatedsize’,’$version’)”
$dbwrite.ExecuteNonQuery()
$dbconn.Close()

[Void]$Results.Add($Entry);
}
}

If ([IntPtr]::Size -eq 8){
$UninstallKeysWow6432Node = $Null;
$SubKeyWow6432Node = $Null;
$UninstallKeysWow6432Node = $Registry.OpenSubKey(“Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall”,$False);
If ($UninstallKeysWow6432Node) {
$UninstallKeysWow6432Node.GetSubKeyNames()|%{
$SubKeyWow6432Node = $UninstallKeysWow6432Node.OpenSubKey($_,$False);
$DisplayName = $SubKeyWow6432Node.GetValue(“DisplayName”);
If ($DisplayName.Length -gt 0){
$Entry = $Base | Select-Object *
$Entry.ComputerName = $ComputerName;
$Entry.Name = $DisplayName.Trim();
$Entry.Publisher = $SubKeyWow6432Node.GetValue(“Publisher”);
[ref]$ParsedInstallDate = Get-Date
If ([DateTime]::TryParseExact($SubKeyWow6432Node.GetValue(“InstallDate”),”yyyyMMdd”,$Null,[System.Globalization.DateTimeStyles]::None,$ParsedInstallDate)){
$Entry.InstallDate = $ParsedInstallDate.Value
}
$Entry.EstimatedSize = [Math]::Round($SubKeyWow6432Node.GetValue(“EstimatedSize”)/1KB,1);
$Entry.Version = $SubKeyWow6432Node.GetValue(“DisplayVersion”);
$Entry.Wow6432Node = $True;

$now=Get-Date
$computername = $Entry.ComputerName
$name = $Entry.Name
$publisher = $Entry.Publisher
$installdate = $Entry.InstallDate
$estimatedsize = $Entry.EstimatedSize
$version = $Entry.Version

# Write Values to SQL
$dbconn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI”)
$dbconn.Open()
$dbwrite = $dbconn.CreateCommand()
$dbwrite.CommandText = “INSERT INTO dbo.softwareinstalled (computername, datetime, name, publisher, installdate, estimatedsize, version) VALUES (‘$computername’,’$now’,’$name’,’$publisher’,’$installdate’,’$estimatedsize’,’$version’)”
$dbwrite.ExecuteNonQuery()
$dbconn.Close()

[Void]$Results.Add($Entry);
}
}
}
}
}
}
$Results
}

#=Variables====================================================

$now=Get-Date -format “yyyy-MM-dd”
$new_line = “`r`n”
$output = “”

#=Computers====================================================

$Computers = Get-ADComputer -Filter {Name -notlike ‘FilterHere*’ -and Name -notlike ‘FilterHere*’ -and Name -notlike ‘FiltersHere*’ -and Name -notlike ‘AnotherFilter*’ -and Name -notlike ‘FilterHere*’} | Sort Name | ForEach-Object {$_.Name}

#=Main program==================================================

foreach ($computer in $computers) {
Get-InstalledSoftware $computer
#$computer
}

#=End====================================================
[/crayon]

I then have a few SQL queries to return specific information:

[crayon]
–All software as of today today

select  computername, name, publisher, version from softwareinstalled where datetime >= DATEADD(d,0,DATEDIFF(d,0,GETDATE()))

–All previous software installed

select distinct computername, name, publisher, version from softwareinstalled where datetime < DATEADD(d,0,DATEDIFF(d,0,GETDATE()))

–All Java out of date

select  computername, name, publisher, version from softwareinstalled where datetime >= DATEADD(d,0,DATEDIFF(d,0,GETDATE()))

and name like ‘%Java%’ and name like ‘%update%’ and version like ‘7.%’ and version not like ‘7.0.250’

–Copies of office installed

select  computername, name, publisher, version from softwareinstalled where datetime >= DATEADD(d,0,DATEDIFF(d,0,GETDATE()))

and name like ‘%Microsoft Office Professional%’

–Remote Access Software

select  computername, name, publisher, version from softwareinstalled

where datetime >= DATEADD(d,0,DATEDIFF(d,0,GETDATE()))

and name like ‘%TeamViewer%Host%’ or name like ‘%LogMeIn%’

–Cute PDF Pro

select  computername, name, publisher, version from softwareinstalled

where datetime >= DATEADD(d,0,DATEDIFF(d,0,GETDATE()))

and name like ‘%CutePDF Professional%’
[/crayon]