1 Powershell Script 2 SQL Tables

Question

I am having trouble populating my second table in my SQL Server Management Studio. My first table ServerList contains all information in the screenshot below. The second table ServerDrives should then contain all the information regarding hard drive specs (free, used, letter, and which server are the hard drives connected to).

Currently, I have my powershell script pull from the ServerList all the values in the column ServerName and push info into the powershell script which will then populate OS, RAM, and etc.

Issue: I am not sure how to have the same powershell script use the same primary key ServerID from table ServerList to populate my ServerDrives table. For I want each row in that table to be one for each server in ServerList. I have in the script currently a way to collect the hard drive information but I am not sure how to use that info to populate my empty table

Goal: To have ServerID (primary key in ServerList) and ServerDriveID (primary key in ServerDrives) be for the same server (QAGGAPP01, QAGGAPP03, QAGGAPP05, and etc). To have the variable $disks be pushed in the database table ServerDrives with the proper hard drives letters and specs. Each server has 1 or 2 hard drives.

Code:

    Write-Output " `n Start of Hal0 `n";

    #Start of Server Connection
    $connectionString = "Server=QAUTILITYDB01;Database=Hal0Test;Integrated Security=True;"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()
    $command = $connection.CreateCommand()

    $ServerArray = [System.Collections.ArrayList]@()
    $query = "SELECT ServerName FROM ServerList"
    $command.CommandText = $query
    $ServerNames = $command.ExecuteReader()

    $table = new-object “System.Data.DataTable”
    $table.Load($ServerNames)

    $ServerArray = $table | select -Expand ServerName

    $ServerArray | ForEach-Object {
        #$ServerArray returns each server name

        #Operating System
        $SystemInfo = Get-WmiObject -Class Win32_OperatingSystem -computername  $_
        $os = Get-WmiObject -Class Win32_OperatingSystem -Computer $_ 
        #Server's Memory (RAM) Usage Average
        $TotalRAM = [math]::round($SystemInfo.TotalVisibleMemorySize/1MB,4)
        $FreeRAM =  [math]::round($SystemInfo.FreePhysicalMemory/1MB,3)
        $UsedRAM = $TotalRAM - $FreeRAM
        $RAMPercentFree = [math]::round(($FreeRAM / $TotalRAM) * 100,3)
        $RAMPercentUsed = [math]::round(($UsedRAM / $TotalRAM) * 100,3)
        #Server's CPU (Proccess) Usage Average
        $cpuAVG = Get-WmiObject -computername $_ win32_processor |     Measure-Object -property LoadPercentage -Average | Select Average 

        #Server's Hard Drives (MB) Free/Used
        $disks = Get-WmiObject -Class Win32_LogicalDisk -Computer $_ |
             Where-Object {$_.DriveType -eq 3} |
             ForEach-Object {
                 '{0} {1:D} MB Free/{2:D} MB Used' -f $_.DeviceID,
                     [int]($_.FreeSpace/1MB), [int]($_.Size/1MB)
             }

        #Value Types being pushed to Server
        $command.CommandText = "UPDATE ServerList SET FQDN = '$_',
        TotalRAM= '$TotalRAM' ,
        FreeRAM= '$FreeRAM' ,
        UsedRAM= '$UsedRAM' ,
        RAMPercentFree = '$RAMPercentFree' ,
        RAMPercentUsed= '$RAMPercentUsed' ,
        OS = '$($os.Caption)' WHERE ServerName LIKE '$($os.PSComputerName)%';"
        $result = $command.ExecuteNonQuery()
}

    Write-Output "`n End of Hal0";

ServerDrives:

enter image description here

HTML Preview, I only have in my powershell code just to make it easier to see what is being pushed to the server without having to reopen the tables everytime:

enter image description here

Your second, unfinished, SQL script is an UPDATE but you are saying the ServerDrives table is empty. There's nothing to update then – instead, you want to INSERT data into it. On the other hand, if your PS script is supposed to be run more than once, you would need to either clear that table each time before repopulating it or merge the new info with the contents of ServerDrives, i.e. some of its rows would be updated, others inserted, while still others even deleted (if that is a possibility). If you clean up the table every time, ServerDriverID values will be generated every time anew. — Andriy M
– Andriy M, Commented Jul 7, 2015 at 15:04

Petr · Accepted Answer · 2015-07-15 01:38:58Z

Based on suggestion in another comment to get ServerID. You can change your code as follows:

Get both name and id using "SELECT ServerID, ServerName FROM ServerList"
Pipe resulting table and read $ServerName and $ServerID separately
Use this information when you iterate over your disks to insert/update $ServerID

Implementation:

#Start of Server Connection
$ConnectionString = "<CONNECTION>"
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
$Command = $Connection.CreateCommand()
# Selects server id and name and puts it into the table
$ServerInfoQuery = "SELECT ServerID, ServerName FROM ServerList"
$Command.CommandText = $ServerInfoQuery
$ServerInfoReader = $Command.ExecuteReader()
$ServerInfo = New-Object System.Data.DataTable
$ServerInfo.Load($ServerInfoReader)    
$ServerInfo | ForEach-Object {
    # Read Name and ID from table
    $ServerName = $_.ServerName
    $ServerID = $_.ServerID
    # Operating System
    $SystemInfo = Get-WmiObject -Class Win32_OperatingSystem -computername  $ServerName
    $os = Get-WmiObject -Class Win32_OperatingSystem -Computer $ServerName        
    $TotalRAM = [math]::round($SystemInfo.TotalVisibleMemorySize/1MB,4)
    $FreeRAM =  [math]::round($SystemInfo.FreePhysicalMemory/1MB,3)
    $UsedRAM = $TotalRAM - $FreeRAM
    $RAMPercentFree = [math]::round(($FreeRAM / $TotalRAM) * 100,3)
    $RAMPercentUsed = [math]::round(($UsedRAM / $TotalRAM) * 100,3)
    # Server's CPU (Proccess) Usage Average
    $cpuAVG = Get-WmiObject -computername $ServerName win32_processor | Measure-Object -property LoadPercentage -Average | Select Average         
    # Value Types being pushed to Server
    $Command.CommandText = "
        UPDATE 
            ServerList 
        SET 
            FQDN = '$ServerName',
            TotalRAM= '$TotalRAM',
            FreeRAM= '$FreeRAM',
            UsedRAM= '$UsedRAM',
            RAMPercentFree = '$RAMPercentFree',
            RAMPercentUsed= '$RAMPercentUsed',
            OS = '$($os.Caption)' 
        WHERE 
            ServerID = $ServerID"       
    $result = $Command.ExecuteNonQuery()

    # Server's Hard Drives (MB) Free/Used
    $disks = Get-WmiObject -Class Win32_LogicalDisk -Computer $ServerName | Where-Object {$_.DriveType -eq 3} | ForEach-Object {
        $DriveLetter = $_.DeviceID
        $DiskFree = [Math]::Round($_.FreeSpace/1MB, 0)        
        $DiskUsed = [Math]::Round($_.Size/1MB, 0)
        $Command.CommandText = "
            IF EXISTS (SELECT * FROM ServerDrives WHERE ServerID = '$ServerID' AND DriveLetter = '$DriveLetter')
            BEGIN
                UPDATE
                    ServerDrives
                SET
                    DriveLetter = '$DriveLetter',
                    DiskFree = '$DiskFree',
                    DiskUsed = '$DiskUsed',
                    ServerID = '$ServerID'
                WHERE
                    ServerID = '$ServerID' AND DriveLetter = '$DriveLetter' 
            END
            ELSE
            BEGIN
                INSERT INTO 
                    ServerDrives (DriveLetter, DiskFree, DiskUsed, ServerID) 
                VALUES 
                    ('$DriveLetter','$DiskFree','$DiskUsed', '$ServerID')
            END"
        $command.ExecuteNonQuery()
    }
}

I am so sorry I just gave the wrong person the bounty... The above solution.

Bacon Bits · Accepted Answer · 2015-07-07 14:12:31Z

1

+50

Can't you just fetch the primary key when you ask for the server names?

$query = "SELECT ServerID, ServerName FROM ServerList"

Now you can use WHERE ServerID = $CurrentServerID or somesuch.

You're not running any INSERT statements. It's a SELECT followed by UPDATE. If you need the primary key, just ask the server for it.

answered Jul 7, 2015 at 14:12

Bacon Bits

32.5k6 gold badges63 silver badges78 bronze badges

4 Comments

Andriy M Over a year ago

Not as simple as that. The name list goes into $ServerNames, which is then expanded into an FQDN list stored in $ServerArray. It is $ServerArray that is iterated over, not $ServerNames. So likely other changes will be needed too. But I agree, it would seem easiest to get both IDs and names at the beginning: names for getting the information, IDs for referencing the tables' rows.

Bacon Bits Over a year ago

@AndriyM I'm not saying the changes are simple, just that the solution is pretty simple. Don't iterate over $ServerArray. Iterate over each record in the DataTable. Something like foreach ($row in $table) {....}. Then you have $row.ServerId and $row.ServerName.

Andriy M Over a year ago

Yes, I just thought it was worth pointing out in the answer, because the OP cannot just fetch the primary key – they need to restructure the script a little as well.

TheAce Over a year ago

Thank you guys for the feedback but the thought process is not my issue. I just need a push (start code) of how to accomplish this goal. Even if the push is the code for pushing the value of free space on C Drive to the database. I can figure out the rest like what I did for the first table. Coded answers would help GREATLY! Thank you already for the current reponses!

Collectives™ on Stack Overflow

1 Powershell Script 2 SQL Tables

2 Answers 2

Implementation:

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Implementation:

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related