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 »

Sorry for the lack of clarity.

the START switch only enters start date, start time and current free space at that time on each local disk. This leave 3 fields set to NULL. Endate, EndTime and EndSpace (current free space at the time on each local disk.

All the end switch is supposed to do and did do before I ran into the multiple local disks issue (that you resolved in the START switch) is to update the NULL fields with the correct information for each drive.

So what I was thinking was to use a 'WHERE ID =' clause to identify which record to change and then search that server for that device which is in the row of the record (field: DeviceID).

Does that make more sense. Again sorry for my lack of clarity.

Mike
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 »

You need to have a schema that is designed to provide you with an ID or a primary key that uniquely identifies a record. You don't have any of that.

You will need to find a DBA or someone who can help you with your SQL. The issue is one of design and not of PS code.
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 »

When we design a database that has updateable records we define a primary key that allows us to find any record for update. There is another way to approach this which is to sue a stored procedure to insert and update a record. The "Insert" SP is designed to return the ID which is defined on the table. We can then save this ID for the update cycle.

All of this depends on what the purpose of this is. If we have a table that gets added to once a day then we would use the date for the key. If there are multiple servers involved we can use the server and the date. In your case you are using multiple drives and servers so you would need to have the device id as part of the key.

You have to sit down and work out a design that can support your CRUD operations as needed by your process requirements. A DBA can help you through this operation.
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 »

We do have a field called 'ID' that is the primary key. I am not sure what you are trying to get at. The schema is fine for what I am doing, I am just having trouble coding it in powershell.

I will keep at it.

Thank you for your assistance. Greatly appreciated.
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 are you planning on finding the correct ID? The issue is design and not PowerShell.

Your code can insert as many records as it wants to with nom way of telling which one is the matching record.
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'll post it when its done.
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 »

If you want to try playing around with the ID of the inserted row here is how to get the ID without a stored procedure.

Code: Select all

    $tmplt = @'
		INSERT into SQLSpace(SystemName, DeviceID, StartDate, StartTime, StartSpace)
               OUTPUT Inserted.ID
		VALUES('{0}', '{1}', '{2}', '{3}', '{4}')
'@
    foreach ($server in $servers) {
        Get-WmiObject -Class Win32_LogicalDisk -Filter 'DriveType=3' -ComputerName $server -Credential $creds |
        
        ForEach-Object {
            $query = $tmplt -f $_.SystemName, $_.DeviceID, (Get-Date -UFormat %m/%d/%y), (Get-Date -UFormat %r), $_.FreeSpace
            $dt = Invoke-Sqlcmd -ServerInstance $sqlserver -Database $database -Username $sqluser -Password $sqlpass -Query $query
            $newID = $dt.ID
        }
Not the inserted table command "OUTPUT Inserted.ID" which causes the insert to return the ID as a table. with one row. The code "$dt.ID" gets the ID from the table.
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 »

All things considered you will not succeed without a primary key which seems like it should be: "SystemName, DeviceID" and may also require the date. What these records are used for and how they come into exisitence is still a mystery.
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 »

table.png
table.png (9.17 KiB) Viewed 2658 times
This is the table I am working with. As you can see I have an ID field (Which is the primary key). Here are the steps I am thinking of performing in order to update the the 'END' fields.
NOTE: There is a SQ: view that shows all records that are NULL for the END fields as shown in the attached screenshot.

1. Assign all the rows in this view to a variable some how.
2. use a search query to pull out all ID's from this view. (We can't use SystemName since there can be more than one row with the same name).
3. Then systematically UPDATE each 'END' field with their respective data.

This script is a much smaller part of the overall picture of what I have been asked to do. But what this is simply doing is getting a snap shot of how much space is available on a server in the morning and a snapshot at night.
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 »

Without a primary key related to the original insert how will you know which ID is the one you are interested in?

How often does the start block run per system. Daily? Hourly? Monthly? How can you tell these apart.
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