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--
  • 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.

  • Hello cmartin and thanks for taking the time to look over the Code.

    Theres only one row that is generated or updated.

    It is like this.

    A User logs on his workstation
    The Script generates this Little Report and saves it to that Array @Report

    Then I look in the database for the hostname,
    if it exists, the row will be updated, if not, a new one will be created.
    Thats all, for now.

    My Problem is

    a) I want to make it as time efficient as possible
    b) I want to make the update process easier. Im enumerating all objects of my Report to update the SQL.
    Isnt it possible to say : ~ Update Inventory with @Report where Hostname = ... the same way I do it with INSERT INTO..?
    Or to Loop through my Report and update..

    How would you Insert or Update a Database with an Array ?
    Could you please give me an example?

    thanks in advance

     

  • Hello Sean,

    thanks for your time.

    Puh..you are so right. I Updated the Code to use the new Var $Data instead of $Report.

    Of Course . thanks

  • Is this what you mean?

    $sets = ($Data.Keys | ForEach-Object { "$_ = '" + $Data[$_] + "'" } ) -join ', '
    "Update {0} SET {1} WHERE Hostname = '{2}' " -f $table, $sets, $Env:Computername

     

  • this seems to get in the right direction and it is definitly what I mean.

    If I use your example it throws Errors:
    ~ Exception with call "ExecuteNonQuery" with 0 Arguments: illegal coloumn 'OU' or illegal Coloumn 'VirtualHost'

    It must have been something with this construct { "$_ = '"... }
    Remember my Insert construct was like this

    >

     

     

     

     

     

     

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

     

     

     

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

     

     

     

     

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

    Sorry, but I cannot Adopt your example to my needs, can you help me with this?

     

  • this seems to get in the right direction and it is definitly what I mean.

    If I use your example it throws Errors:
    ~ Exception with call "ExecuteNonQuery" with 0 Arguments: illegal coloumn 'OU' or illegal Coloumn 'VirtualHost'

    It must have been something with this construct { "$_ = '"... }
    Remember my Insert construct was like this

    >

     

     

     

     

     

     

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

     

     

     

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

     

     

     

     

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

    Sorry, but I cannot Adopt your example to my needs, can you help me with this?

     

  • sorry for the double post..dont know why..
    formatting is really hard in this forum

  • The error message is indicating that the target table in the database doesn't have columns named "OU" or "VirtualHost". Is that correct?

  • This is correct. These are only Variables from my $Report ( $Report.OU, $Report.VritualHost ect ) The coresponding database entry is Office and Hypervisor..
  • HA!. Ok understood. I renamed the Database Parts and now it ís working....cool
  • You said that I open a connection for every set to process but I think I dont...but correct me if Im wrong.

    - I´m creating my Report
    - Open a Connection in my function New-SQLCheck
    - Check if 'Hostname' exists
    - then update or insert my Report.

    I open the connection only one time..?