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 Report@() 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--
Parents
No Data
Reply
  • 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?

Children
No Data