Create small inventory and push to SQL after login to workstation


im writing an a Login Script that gets some Computer Information and pushes the Information to a SQL Express Database, every time a user logs on to the System

So I can grab the Information with Excel or another frontend and sort it by Computername or OU or something else.

- At first, I generate a small Report

- Check if the Hostname is already in my Database  (function New-SQLCheck)
  If it is, update (function New-SQLUpdate)     This is where I need help.
  if not create a new entry. (function New-SQLInsert)

This is working at the Moment, but the script takes very long to process and I think it s not really professional coded
( I´m no programmer, but I learn fast )

Is it possible that you look over my Code and give me advise specially for the part where I UPDATE a hostname?
UPDATE INVENTORY SET OSName='OBject1" 'Object2' 'Object3' ect.. 

I want to update the complete array of my [email protected]() instead of updating every single Object, ´cause in the feature there are coming more and more objects that we want to push.


I hope you see what I mean

Here are the neccessary parts of my code 


#region report

# Creating the Report

$Report = @{}

if (Test-Path ("HKLM:\SOFTWARE\Microsoft\Virtual Machine\Guest\Parameters")){(
$Report.VirtualHost = get-item "HKLM:\SOFTWARE\Microsoft\Virtual Machine\Guest\Parameters").GetValue("HostName")}else{
$Report.VirtualHost = '.'}

$Report.Hostname = $Windows.CSName #$systeminfo.Hostname
$Report.OSName = $Windows.caption
$Report.Username = $env:USERNAME # + '@' + $env:USERDOMAIN
$Report.LogonTime = get-date -Format 'HH:mm:ss' #$Systeminfo.Systemstartzeit.Remove(0,12)
$Report.LogonDate = (get-date).ToShortDateString() #$Systeminfo.Systemstartzeit.Remove(10)
$Report.Logonserver = ((Get-WmiObject -Class win32_ntdomain -Filter "DomainName = 'ghvedvedi'" -ComputerName localhost).DomainControllerName).trim('\', ' ')#$systeminfo.'Anmeldeserver'.TrimStart('\\')
$Report.Biosversion = ($Bios.manufacturer + " " + $Bios.SMBIOSBIOSVersion).substring(0,29) #$systeminfo.'BIOS-Version'.Substring(0,31)
$Report.Domain = $Environment.Domain
$Report.IPAddress = $Net.IPAddress | % {($_ | Out-String).trim() -replace '@\{|\}'}
$Report.DHCPServer = $Net.DHCPServer
$Report.Gateway = $Net.DefaultIPGateway | % {($_ | Out-String).trim() -replace '@\{|\}'}
$Report.MacAddress = $Net.MACAddress
$Report.SerialNumber = (Get-WmiObject win32_bios).serialnumber
$Report.OSVersion = $Windows.Version
$Report.IEVersion = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Internet Explorer').svcVersion
$Report.SystemModel = $Environment.Model
$Report.SystemType = $Environment.SystemType
$Report.SystemManufact = $Environment.Manufaturer
$Report.OU = $BaseOU
#endregion Report

And here the sql part

#region sql
function NEW-SQLInsert


$keys = ($Data.Keys | ForEach-Object {$_}) -join ','
$data = ($Data.Values | ForEach-Object { "'$_'" }) -join ','

'Insert Into {0} ({1}) VALUES ({2})' -f $table, $keys, $data

function NEW-SQLUpdate


"UPDATE INVENTORY SET OSName='"+ $Report.OSName +"',Username='"+ $Report.Username +"',Domain='"+ $Report.Domain +"',LogonTime='"+ $Report.LogonTime +"',LogonDate='"+ $Report.LogonDate +"',Logonserver='"+ $Report.Logonserver +"',IPAddress='"+ $Report.IPAddress +"',DHCPServer='"+ $Report.DHCPServer +"',Gateway='"+ $Report.Gateway +"',Serialnumber='"+ $Report.SerialNumber +"',OSVersion='"+ $Report.OSVersion +"',IEVersion='"+ $Report.IEVersion +"',MACAddress='"+ $Report.MacAddress +"',BiosVersion='"+ $Report.Biosversion +"',SystemModel='"+ $Report.SystemModel +"',SystemType='"+ $Report.SystemType +"',SystemManufact='"+ $Report.SystemManufact +"',Hypervisor='" + $Report.VirtualHost +"',Office='" + $Report.OU + "' WHERE Hostname='$Env:Computername' "


function New-SQLCheck
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = 'Data Source=SQLServer.ourDomain\RDM;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OurDatabase;'

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn
$cmd.CommandText = "SELECT * FROM INVENTORY WHERE [Hostname]='$env:computername'" # Check if Hostname is already in Database

$Global:Scalar = $cmd.ExecuteScalar()

if ($Global:Scalar) # if it is..only update

$cmd.CommandText = NEW-SQLUpdate -Table INVENTORY -Data $Report
Write-Host -ForegroundColor Yellow 'Update in SQL Database'


else # if not, create a new entry


$cmd.CommandText = NEW-SQLInsert -Table INVENTORY -Data $Report
Write-Host -ForegroundColor Yellow 'New Entry in Database'

}#End function NEW-SQLCheck

#endregion sql
Parents Reply Children
No Data