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
Trouble with multiple Device ID's (Local Hard drives)
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.
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.
- mtartaglia
- Posts: 101
- Last visit: Mon Dec 19, 2022 11:45 am
Re: Trouble with multiple Device ID's (Local Hard drives)
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.
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.
Re: Trouble with multiple Device ID's (Local Hard drives)
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.
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.
- mtartaglia
- Posts: 101
- Last visit: Mon Dec 19, 2022 11:45 am
Re: Trouble with multiple Device ID's (Local Hard drives)
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.
I will keep at it.
Thank you for your assistance. Greatly appreciated.
Re: Trouble with multiple Device ID's (Local Hard drives)
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.
Your code can insert as many records as it wants to with nom way of telling which one is the matching record.
- mtartaglia
- Posts: 101
- Last visit: Mon Dec 19, 2022 11:45 am
Re: Trouble with multiple Device ID's (Local Hard drives)
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.
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.
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
}
Re: Trouble with multiple Device ID's (Local Hard drives)
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.
- mtartaglia
- Posts: 101
- Last visit: Mon Dec 19, 2022 11:45 am
Re: Trouble with multiple Device ID's (Local Hard drives)
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.
Re: Trouble with multiple Device ID's (Local Hard drives)
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.
How often does the start block run per system. Daily? Hourly? Monthly? How can you tell these apart.