Trouble with multiple Device ID's (Local Hard drives)

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 6 years and 2 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
mtartaglia
Posts: 101
Last visit: Mon Dec 19, 2022 11:45 am

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by mtartaglia »

Oh I think I see where your confusion is.

The PRIMARY KEY is the ID field in the image I sent. However, now that I looked at the database design I never assigned it to be the primary key. DOH!

It is set as one now.

The start block will happen at 12:01AM every morning and the end will happen at 11:59PM every night. So daily for both.

The view I created in SQL allows us to know which records are in the queue to have END dates, times and freespace added to the records.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by jvierra »

The ID (identity) is designed to automatically increment according to the rules you have set. It is automatically a key which gurantees uniqueness. It does not identify a row. Only a true primary key can identify a single row. An "identity" field should not be part of the primary key. That defeats the purpose of the primary key.

What fields uniquely identify an entry. SystemName + DeviceID as a minimum if there is only one of these per system/device.

You have to define the data integrity rules before you can identify the primary key. You have not done this anywhere.
User avatar
mtartaglia
Posts: 101
Last visit: Mon Dec 19, 2022 11:45 am

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by mtartaglia »

there can be more than one row with SystemName + DeviceID being the same. What about including the ID field with the systemane + DeviceID. Would that work or should I not add the ID field as you mentioned before?
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by jvierra »

How do you know which of these rows matches the end update? Is it date? You must describe the process and from process you can decide how to define the primary key.
User avatar
mtartaglia
Posts: 101
Last visit: Mon Dec 19, 2022 11:45 am

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by mtartaglia »

The process is
1. Search the view and select all rows with their ID field.
2. Take the list of ID's and modify each row with the respective EndDate, EndTime, and EndSpace.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by jvierra »

Are you are saying that you want to update ALL records with the same DeviceID and ServerName? What is your key? What primary key are you using to match the original record with the added fields? You have not defined that anywhere in your process.

To do all that match as you have defined it above:

"UPDATE <table or view> Set field=value, ... WHERE Server]'$servername', DeviceId='$DeviceID'"

This will change the values on all records that match server and DeviceID.

As I noted much earlier you can only use "ID" if you save that ID and use it in the update statement.
User avatar
mtartaglia
Posts: 101
Last visit: Mon Dec 19, 2022 11:45 am

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by mtartaglia »

I took your advice and thought the process through the END switch again.

Here is the code (just the end switch part) that now works 100% Please make any suggestions you see fit, but I wanted to at least post what I came up with so it may help others. Thanks for all your help.

if ($end)
{ $creds = Get-Credential
$servers = get-content servers.txt
foreach ($server in $servers)
{
$searchQuery = "SELECT IDNumber, SystemName, DeviceID FROM NoEndDate WHERE [SystemName] = '$server'"
$results = Invoke-Sqlcmd -ServerInstance $sqlserver -Database $database -Username $sqluser -Password $sqlpass -Query $searchQuery
foreach ($result in $results)
{
$obj = Get-WmiObject -Class Win32_LogicalDisk -Filter 'DriveType=3' -ComputerName $server -Credential $creds | where DeviceID -EQ $result.DeviceID
$endSpace = $obj.FreeSpace
$endDate = Get-Date -UFormat "%m/%d/%y"
$endTime = Get-date -UFormat "%r"
[int]$ID = $result.IDNumber
$updateQuery = "UPDATE dbo.NoEndDate SET EndDate = '$endDate', EndTime = '$endTime', EndSpace = '$endSpace' WHERE [IDNumber] = '$ID'"

Invoke-Sqlcmd -ServerInstance $sqlserver -Database $database -Username $sqluser -Password $sqlpass -Query $updateQuery
}

}
}
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by jvierra »

That will update all records for a server with the last update which is your issue. You still haven't defined a primary key. Without a PK this is what will happen.

You have to change your schema. It must contain a PK that allows you to target the record you need to update. It must contain at least the server and device plus one more item that tells you which record you need to update.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by jvierra »

Start again by stating what you are trying to do. No code or database. Just state the problem you are trying to solve. What is the purpose for this script?
User avatar
mtartaglia
Posts: 101
Last visit: Mon Dec 19, 2022 11:45 am

Re: Trouble with multiple Device ID's (Local Hard drives)

Post by mtartaglia »

jvierra,

I have tested inside and out and this code works now. It has been re-rewritten to fit my needs. For now, the primary key is just the IDNumber field. Thanks again. :D
jvierra wrote: Wed Jan 03, 2018 7:21 am That will update all records for a server with the last update which is your issue. You still haven't defined a primary key. Without a PK this is what will happen.

You have to change your schema. It must contain a PK that allows you to target the record you need to update. It must contain at least the server and device plus one more item that tells you which record you need to update.
This topic is 6 years and 2 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked