Author Posts

October 19, 2016 at 10:34 am

Hi guys! I am trying to write a small PS script that will gather some information from the local computer and send it to Microsoft Access DB. Here is my code:

Clear-Host

Function Check-Path($Db)
{
If(!(Test-Path -path (Split-Path -path $Db -parent)))
{
Throw "$(Split-Path -path $Db -parent) Does not Exist"
}
ELSE
{
If(!(Test-Path -Path $Db))
{
Throw "$db does not exist"
}
}
} #End Check-Path

Function Get-HDD
{
Get-WmiObject -Class Win32_LogicalDisk -Filter "DeviceID = 'C:'"
} #End Get-HDD

Function Connect-Database($Db, $Tables)
{
$OpenStatic = 3
$LockOptimistic = 3
$connection = New-Object -ComObject ADODB.Connection
$connection.Open("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=$Db" )
Update-Records($Tables)
} #End Connect-DataBase

Function Update-Records($Tables)
{
$RecordSet = new-object -ComObject ADODB.Recordset
ForEach($Table in $Tables)
{
$Query = "Select * from $Table"
$RecordSet.Open($Query, $Connection, $OpenStatic, $LockOptimistic)
Invoke-Expression "Update-$Table"
$RecordSet.Close()
}
$connection.Close()
} #End Update-Records

Function Update-Comp_Inventory
{
"Updating Comp_Inventory"
$computerSystem = Get-CimInstance CIM_ComputerSystem
$computerBIOS = Get-CimInstance CIM_BIOSElement
$computerOS = Get-CimInstance CIM_OperatingSystem
$computerCPU = Get-CimInstance CIM_Processor
$computerHDD = Get-HDD
$computerIP = Get-WmiObject Win32_NetworkAdapterConfiguration | Where {$_.IPAddress.length -gt 1}

$RecordSet.AddNew()
$RecordSet.Fields.Item("ComputerName").value = $computerSystem.Name
$RecordSet.Fields.Item("Manufacturer").value = $computerSystem.Manufacturer
$RecordSet.Fields.Item("Model").value = $computerSystem.Model
$RecordSet.Fields.Item("Serial_Number").value = $computerBIOS.SerialNumber
$RecordSet.Fields.Item("CPU").value = $computerCPU.Name
$RecordSet.Field.Item("UserName").value = $computerSystem.UserName
$RecordSet.Field.Item("HDD_Capacity").value = "{0:N2}" -f ($computerHDD.Size/1GB) + "GB"
$RecordSet.Field.Item("HDD_Space").value = "{0:P2}" -f ($computerHDD.FreeSpace/$computerHDD.Size) + " Free (" + "{0:N2}" -f ($computerHDD.FreeSpace/1GB) + "GB)"
$RecordSet.Field.Item("RAM").value = "{0:N2}" -f ($computerSystem.TotalPhysicalMemory/1GB) + "GB"
$RecordSet.Field.Item("OS").value = $computerOS.Caption
$RecordSet.Field.Item("ServicePack").value = $computerOS.ServicePackMajorVersion
$RecordSet.Field.Item("IP_Address").value = $computerIP.ipaddress[0]
$RecordSet.Field.Item("MAC_Address").value = $computerIP.MACAddress

$RecordSet.Field.Item("Last Reboot").value = $computerOS.LastBootUpTime
$RecordSet.Update()
} #End Update-Comp_Inventory

# *** Entry Point to Script ***

$Db = "C:\FSO\Comp_Inventory.mdb"
$Tables = "Comp_Inventory"
Check-Path -db $Db
Connect-DataBase -db $Db -tables $Tables

Now for some reason, some of the information was written down to the Access DB and most of the aren't. Now here are the error codes:

You cannot call a method on a null-valued expression.
At C:\Users\renelson.CHINAONLINE\Desktop\Desktop Items\dev\comp_inventory2.ps1:62 char:2
+ $RecordSet.Field.Item("UserName").value = $computerSystem.UserName
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Users\renelson.CHINAONLINE\Desktop\Desktop Items\dev\comp_inventory2.ps1:63 char:2
+ $RecordSet.Field.Item("HDD_Capacity").value = "{0:N2}" -f ($computerHDD.Size/1G ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Users\renelson.CHINAONLINE\Desktop\Desktop Items\dev\comp_inventory2.ps1:64 char:2
+ $RecordSet.Field.Item("HDD_Space").value = "{0:P2}" -f ($computerHDD.FreeSpace/ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Users\renelson.CHINAONLINE\Desktop\Desktop Items\dev\comp_inventory2.ps1:65 char:2
+ $RecordSet.Field.Item("RAM").value = "{0:N2}" -f ($computerSystem.TotalPhysical ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Users\renelson.CHINAONLINE\Desktop\Desktop Items\dev\comp_inventory2.ps1:66 char:2
+ $RecordSet.Field.Item("OS").value = $computerOS.Caption
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Users\renelson.CHINAONLINE\Desktop\Desktop Items\dev\comp_inventory2.ps1:67 char:2
+ $RecordSet.Field.Item("ServicePack").value = $computerOS.ServicePackMajorVersio ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Users\renelson.CHINAONLINE\Desktop\Desktop Items\dev\comp_inventory2.ps1:68 char:2
+ $RecordSet.Field.Item("IP_Address").value = $computerIP.ipaddress[0]
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Users\renelson.CHINAONLINE\Desktop\Desktop Items\dev\comp_inventory2.ps1:69 char:2
+ $RecordSet.Field.Item("MAC_Address").value = $computerIP.MACAddress
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Users\renelson.CHINAONLINE\Desktop\Desktop Items\dev\comp_inventory2.ps1:71 char:2
+ $RecordSet.Field.Item("Last Reboot").value = $computerOS.LastBootUpTime
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Can someone help me on this one?

October 19, 2016 at 11:53 am

After a couple of minutes staring on my script, I think I know what the problem is. Sorry for this one.