Create small inventory and push to SQL after login to workstation

Hello,

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 

--snip--
Report:

#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
{

param
(
$Table,
$Data
)

$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
{

param
(
$Table,
$Data
)



"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
$conn.open()
$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
$cmd.ExecuteNonQuery()
Write-Host -ForegroundColor Yellow 'Update in SQL Database'

}

else # if not, create a new entry

{

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

}#End function NEW-SQLCheck

#endregion sql
--snip--
  • Could you use a different font for the script. I'm not seeing the problem.

  • Hi, I postet it with the forum Code MarkUp Tool

     

    is it ok now?

  • So the first problem I see in New-SQLUpdate is that you are referencing $Report inside the function instead of $Data. You are passing $Report in $Data but you are reaching outside of the function scope to use $Report instead of using the local $Data copy. You are getting lucky that $Report is global and your function works but that would not work if $Report were local to New-SQLCheck instead.

    But as for performance, there shouldn't be any noticeable difference. Is the INVENTORY table indexed by Hostname? If not, that would help.

  • Except for what Sean has point out, I don't see a problem. Databases are geared for set-based operations, but sometimes the set you have to work with is just one row. That's what it looks like you have to do here.

    As for the speed, how many rows are you inserting and how long is it taking?

  • I should have spent more time looking. It looks like you call New-SQLCheck each time you have a new set to process. That means that you have to open a new connection to the database each time, and that is a time consuming operation.

    If that's what is going on, try opening $conn just once and then pass it into New-SQLCheck as a parameter. That will streamline things a bit.