CREATE TABLE [dbo].[tblHardware_Populated](
[ComputerName] [nvarchar](30) NOT NULL,
[SerialNumber] [nvarchar](50) NULL,
[PhysicalMemory] [nvarchar](10) NOT NULL,
[DiskSpace] [nvarchar](max) NOT NULL,
[Domain] [nvarchar](50) NOT NULL,
[Manufacturer] [nvarchar](100) NULL,
[Model] [nvarchar](100) NULL,
[PrimaryOwnerName] [nvarchar](50) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[JobName] [nvarchar](256) NULL,
[ActionsPath] [nvarchar](256) NULL,
[LastRunTime] [datetime] NULL,
[NextRunTime] [datetime] NULL,
[Actions] [nvarchar](256) NULL,
CONSTRAINT [PK_tblHardware_Populated] PRIMARY KEY CLUSTERED
(
[ComputerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSoftware_Populated](
[SoftwareID] [int] IDENTITY(1,1) NOT NULL,
[ComputerName] [nvarchar](30) NOT NULL,
[PackageName] [nvarchar](200) NOT NULL,
[InstallDate] [date] NULL,
[InstallLocation] [nvarchar](256) NULL,
[PackageCache] [nvarchar](50) NULL,
[Vendor] [nvarchar](200) NULL,
[Version] [nvarchar](30) NULL,
CONSTRAINT [PK_tblSoftware_Populated] PRIMARY KEY CLUSTERED
(
[SoftwareID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO ALTER TABLE [dbo].[tblSoftware_Populated] WITH NOCHECK ADD CONSTRAINT [FK_tblSoftware_Populated_tblHardware_Populated] FOREIGN KEY([ComputerName])
REFERENCES [dbo].[tblHardware_Populated] ([ComputerName])
GO
ALTER TABLE [dbo].[tblSoftware_Populated] NOCHECK CONSTRAINT [FK_tblSoftware_Populated_tblHardware_Populated]
GO
Application Folder Structure
The Universal Data Link file dbConnection.udl provides connection to SQL Server database and Powershell script should be located in .\InstalledSoftware\Script\ folder. Output data and scan.log (process log) file will be created in .\InstalledSoftware\Output\ directory. Here is the script listing:
#This script scans installed software and log it to text file and ConfigManag database
function datetime{
$year = (Get-Date).Year;
$month = Get-Date -format MM;
$day = Get-Date -format dd;
$hour = Get-Date -format hh;
$minute = Get-Date -format mm;
$second = Get-Date -format ss;
return "$day/$month/$year $hour`:$minute`:$second";
}
function getTasks($path) {
$out = @()
# Get root tasks
$schedule.GetFolder($path).GetTasks(0) | % {
$xml = [xml]$_.xml
$out += New-Object psobject -Property @{
"Name" = $_.Name
"Path" = $_.Path
"LastRunTime" = $_.LastRunTime
"NextRunTime" = $_.NextRunTime
"Actions" = ($xml.Task.Actions.Exec | % { "$($_.Command) $($_.Arguments)" }) -join "`n"
}
}
# Get tasks from subfolders
$schedule.GetFolder($path).GetFolders(0) | % {
$out += getTasks($_.Path)
}
#Output
$out
}
$tasks = @()
$schedule = New-Object -ComObject "Schedule.Service"
$schedule.Connect()
# Start inventory
$tasks += getTasks("\")
# Close com
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($schedule) | Out-Null
Remove-Variable schedule
# Output all tasks
#$tasks
CLEAR-HOST
[void][reflection.assembly]::LoadWithPartialName("microsoft.visualbasic")
[console]::ForegroundColor = "yellow"
$date = datetime;
"** Start process $date **";
write-host "`r";
$dt = Get-Date -format "yyyy_MM_dd"
$a = $ErrorActionPreference
$ErrorActionPreference = "SilentlyContinue"
try{
$file = "Scan_" + $dt + ".txt"
$JobName = "Scan Software Installed"
$folder = ".\InstalledSoftware\Output\"
$filePath = $folder + $file
$outlog = $folder+ "\scan.log"
$($date) | out-file $outlog -append
$testFileExists = Test-Path $outlog
if ($testFileExists -eq $false) {
"" | out-file $outlog
}
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
$myconnectionstring = "File Name=" + $scriptPath + "\dbConnection.udl"
$adOpenStatic = 3
$adLockOptimistic = 3
$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset
$objConnection.Open($myconnectionstring)
if($objConnection.state -eq 0){
"Could not establish connection" | out-file $outlog -append
exit
}
$cmd = Get-ChildItem $folder -force | select name
$testFileExists = $false
$filesNo = 0
foreach ($str in $cmd){
$test = $str.name
if ($str.name -eq $file) {
$testFileExists = $true
break
} else {
$StartsWith = $test -match "Scan"
if ($StartsWith -eq $true){
$filesNo = $filesNo + 1
}
}
}
#Delete existing data if ($testFileExists -eq $false) {
if ($filesNo -gt 0){
Get-ChildItem $folder | Remove-Item -r -force
}
New-Item $filePath -type file
$strSQL = "TRUNCATE TABLE dbo.tblSoftware_Populated"
$retval = $objConnection.Execute($strSQL)
$strSQL = "ALTER TABLE dbo.tblSoftware_Populated DROP CONSTRAINT FK_tblSoftware_Populated_tblHardware_Populated"
$retval = $objConnection.Execute($strSQL)
$strSQL = "TRUNCATE TABLE dbo.tblHardware_Populated"
$retval = $objConnection.Execute($strSQL)
$strSQL = "ALTER TABLE dbo.tblSoftware_Populated WITH NOCHECK ADD CONSTRAINT FK_tblSoftware_Populated_tblHardware_Populated FOREIGN KEY(ComputerName)"
$strSQL = $strSQL + "REFERENCES dbo.tblHardware_Populated (ComputerName)"
$retval = $objConnection.Execute($strSQL)
$strSQL = "ALTER TABLE dbo.tblSoftware_Populated NOCHECK CONSTRAINT FK_tblSoftware_Populated_tblHardware_Populated"
$retval = $objConnection.Execute($strSQL)
}
Add-Content $filePath $log
$log = "*" * 50
Add-Content $filePath $log #Check if it is already scanned $strSQL = "SELECT COUNT(*) counter FROM dbo.tblHardware_Populated WHERE"
$strSQL = $strSQL + " ComputerName = '" + $($env:computername) + "'"
$objRecordset.Open($strSQL,$objConnection,$adOpenStatic,$adLockOptimistic)
if($objRecordset.EOF -eq $True){
"No Data found" | out-file $outlog -append
exit
}
$counter = $objRecordset.Fields.Item("counter").Value
$objRecordset.Close() #Populate Hardware if ($counter -eq 0){
$strSQL = "INSERT INTO dbo.tblHardware_Populated("
$strSQL = $strSQL + "ComputerName"
$strSQL = $strSQL + ",SerialNumber"
$strSQL = $strSQL + ",PhysicalMemory"
$strSQL = $strSQL + ",DiskSpace"
$strSQL = $strSQL + ",Domain"
$strSQL = $strSQL + ",Manufacturer"
$strSQL = $strSQL + ",Model"
$strSQL = $strSQL + ",PrimaryOwnerName"
$strSQL = $strSQL + ",DateCreated"
$strSQL = $strSQL + ",JobName"
$strSQL = $strSQL + ",ActionsPath"
$strSQL = $strSQL + ",LastRunTime"
$strSQL = $strSQL + ",NextRunTime"
$strSQL = $strSQL + ",Actions"
$strSQL = $strSQL + " )VALUES("
$strSQL = $strSQL + "'" + $($env:computername) + "'"
$log = "Computer Name: " +$env:computername
Add-Content $filePath $log
$log = get-wmiobject win32_bios | select-object serialnumber
foreach ($str in $log){
$out = "Serial Number: " + $str.serialnumber
$strSQL = $strSQL + ",'" + $($str.serialnumber) + "'"
Add-Content $filePath $out
}
$txt = Get-WMIObject -class Win32_PhysicalMemory |
Measure-Object -Property capacity -Sum |
select @{N="~"; E={[math]::round(($_.Sum / 1GB),2)}}
$log = $txt -replace "@{",""
$log = $log -replace "}",""
$log = $log -replace "=",""
$log = $log -replace "~",""
$strSQL = $strSQL + ",'" + $log + "'"
$log = "Total Physical Ram: " + $log
Add-Content $filePath $log
$txt = ""
$log = gwmi win32_volume -Filter 'drivetype = 3' |
select driveletter, label, @{LABEL='GBfreespace';EXPRESSION={"{0:N2}" -f ($_.freespace/1GB)} }
foreach ($str in $log){
$out = "Driveletter : " + $str.driveletter + "`t"
$txt = $txt + $out;
#Add-Content $filePath $out
$out = "Label : " + $str.label + "`t"
$txt = $txt + $out;
#Add-Content $filePath $out
$out = "GBfreespace : " + $str.GBfreespace + "`t"
$txt = $txt + $out;
#Add-Content $filePath $out
}
$strSQL = $strSQL + ",'" + $txt + "'"
Add-Content $filePath $txt
$log = get-wmiobject win32_computersystem
foreach ($str in $log){
$out = "Domain : " + $str.Domain
$strSQL = $strSQL + ",'" + $($str.Domain) + "'"
Add-Content $filePath $out
$out = "Manufacturer : " + $str.Manufacturer
$strSQL = $strSQL + ",'" + $($str.Manufacturer) + "'"
Add-Content $filePath $out
$out = "Model : " + $str.Model
$strSQL = $strSQL + ",'" + $($str.Model) + "'"
Add-Content $filePath $out
$out = "PrimaryOwnerName : " + $str.PrimaryOwnerName
$strSQL = $strSQL + ",'" + $($str.PrimaryOwnerName) + "'"
Add-Content $filePath $out
}
$strSQL = $strSQL + ",CONVERT(datetime,'" + $date + "',103)"
$out = ""
foreach ($str in $tasks){
if ($str.Name -eq $JobName){
$out = "Name: " + $str.Name
$strSQL = $strSQL + ",'" + $($str.Name) + "'"
Add-Content $filePath $out
$out = "Path: " + $str.Path
$strSQL = $strSQL + ",'" + $($str.Path) + "'"
Add-Content $filePath $out
$out = "LastRunTime: " + $str.LastRunTime
$strSQL = $strSQL + ", CONVERT(datetime,'" + $($str.LastRunTime) + "',101)"
Add-Content $filePath $out
$out = "NextRunTime: " + $str.NextRunTime
$strSQL = $strSQL + ", CONVERT(datetime,'" + $($str.NextRunTime) + "',101)"
Add-Content $filePath $out
$out = "Actions: " + $str.Actions
$strSQL = $strSQL + ",'" + $($str.Actions) + "'"
Add-Content $filePath $out
Break
}
}
if ($out -eq ""){
$out = "Name: "
$strSQL = $strSQL + ",Null"
Add-Content $filePath $out
$out = "Path: "
$strSQL = $strSQL + ",Null"
Add-Content $filePath $out
$out = "LastRunTime: "
$strSQL = $strSQL + ",Null"
Add-Content $filePath $out
$out = "NextRunTime: "
$strSQL = $strSQL + ",Null"
Add-Content $filePath $out
$out = "Actions: "
$strSQL = $strSQL + ",Null"
Add-Content $filePath $out
}
$strSQL = $strSQL + ")"
$retval = $objConnection.Execute($strSQL)
$log = "*" * 50
Add-Content $filePath $log
Add-Content $filePath ""
$log = " " * 15 + "Software Installed"
Add-Content $filePath $log
#Populate Software Add-Content $filePath ""
$log = Get-WmiObject -Class Win32_Product -ComputerName . |
select Name,InstallDate,InstallLocation,PackageCache,Vendor,Version,IdentifyingNumber
foreach ($str in $log){
$strSQL = "INSERT INTO dbo.tblSoftware_Populated("
$strSQL = $strSQL + "ComputerName"
$strSQL = $strSQL + ",PackageName"
$strSQL = $strSQL + ",InstallDate"
$strSQL = $strSQL + ",InstallLocation"
$strSQL = $strSQL + ",PackageCache"
$strSQL = $strSQL + ",Vendor"
$strSQL = $strSQL + ",Version"
$strSQL = $strSQL + " )VALUES("
$strSQL = $strSQL + "'" + $($env:computername) + "'"
$out = "Name: " + $str.Name
$strSQL = $strSQL + ",'" + $($str.Name) + "'"
Add-Content $filePath $out
$dt = $str.InstallDate
$dtd = ",null"
if ($dt) {
$dta = $dt.Substring(6,2) + "/" + $dt.Substring(4,2) + "/" + $dt.Substring(0,4)
$dtd = ",'" + $dt + "'"
}
$out = "InstallDate: " + $dta
$strSQL = $strSQL + $dtd
Add-Content $filePath $out
$loc = ",null"
if ($str.InstallLocation) {
$loc = ",'" + $str.InstallLocation + "'"
}
$out = "InstallLocation: " + $str.InstallLocation
$strSQL = $strSQL + $loc
Add-Content $filePath $out
$out = "PackageCache: " + $str.PackageCache
$strSQL = $strSQL + ",'" + $($str.PackageCache) + "'"
Add-Content $filePath $out
$out = "Vendor: " + $str.Vendor
$strSQL = $strSQL + ",'" + $($str.Vendor) + "'"
Add-Content $filePath $out
$out = "Version: " + $str.Version
$strSQL = $strSQL + ",'" + $($str.Version) + "'"
Add-Content $filePath $out
$strSQL = $strSQL + ")"
Add-Content $filePath ""
#Write-Output $strSQL
$retval = $objConnection.Execute($strSQL)
}
$JobStatus = "OK"
}
} catch [Exception] {
"$($_.InvocationInfo.ScriptName)($($_.InvocationInfo.ScriptLineNumber)): $($_.InvocationInfo.Line)" | out-file $outlog -append
$("Error catched: " + $_.Exception.GetType().FullName) | out-file $outlog -append
$("Error catched: " + $_.Exception.Message) | out-file $outlog -append
$JobStatus = "not OK"
continue;
}
$ErrorActionPreference = $a
$objConnection.Close()
$date = datetime;
"** End of process $date **"; The process can be scheduled according your requirements.
If you find this script useful please donate generously.
