Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with PS Script


Help with PS Script

Author
Message
Fred Stemp
Fred Stemp
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 219
Need help from the PS gurus out there!

I'm new to PS and I'm trying to create a script that dumps some basic server info to a CSV file. Now I can get it to work and gather most all my info I need and create the CSV. But, I cannot seem to return the hard drive info I'm trying to gather. Nothing goes in to the columns, they are blank.

If you copy this script, change to your SQL Server, test environment not live Smile, and execute, it should be pretty self evident what I'm trying to do and what is missing.

Any help is greatly appreciated!!!

Here is my script thus-far:



[cmdletbinding()]
param (
[parameter(ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
[string[]]$ComputerName = "YOURSERVERNAME"
)

begin {}
process {
foreach ($Computer in $ComputerName) {
if(Test-Connection -ComputerName $Computer -Count 1 -ea 0) {
$Networks = Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $Computer | ? {$_.IPEnabled}
$Memory = Get-WmiObject Win32_ComputerSystem -ComputerName $Computer | Select TotalPhysicalMemory, Manufacturer, Model
$Processor = Get-WMIObject Win32_Processor -ComputerName $Computer | Select Name
$DriveSize = Get-WMIObject Win32_LogicalDisk -ComputerName $Computer | Select Name, Size, FreeSpace
$SQLVersion = invoke-sqlcmd -query "select @@version" -ServerInstance $Computer | Select Column1
$Connections = invoke-sqlcmd -query "SELECT COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0" -ServerInstance $Computer | Select TotalConnections

foreach ($Network in $Networks) {
$IPAddress = $Network.IpAddress[0]
$mem = $memory.TotalPhysicalMemory / 1GB
$Manufacturer = $memory.Manufacturer
$Model = $memory.Model
$ProcessorSpeed = $Processor.Name
$DriveName = $DriveSize.Name
$DriveSizeTotal = $DriveSize.Size / 1GB
$DriveSizeFree = $DirveSize.FreeSpace / 1GB
$SQLVersionNo = $SQLVersion.Column1
$ConnectionsNo = $Connections.TotalConnections

$OutputObj = New-Object PSObject
$OutputObj | Add-Member -MemberType NoteProperty -Name ComputerName -Value $Computer.ToUpper()
$OutputObj | Add-Member -MemberType NoteProperty -Name IPAddress -Value $IPAddress

$OutputObj | Add-Member -MemberType NoteProperty -Name Manufacturer -Value $Manufacturer
$OutputObj | Add-Member -MemberType NoteProperty -Name ServerModel -Value $Model
$OutputObj | Add-Member -MemberType NoteProperty -Name TotalPhysicalMemoryGB -Value $mem
$OutputObj | Add-Member -MemberType NoteProperty -Name Processor -Value $ProcessorSpeed

$OutputObj | Add-Member -MemberType NoteProperty -Name DriveName -Value $DriveSize.Name
$OutputObj | Add-Member -MemberType NoteProperty -Name DriveSizeTotal -Value $DriveSize.Size
$OutputObj | Add-Member -MemberType NoteProperty -Name DriveSizeFree -Value $DriveSize.FreeSpace

$OutputObj | Add-Member -MemberType NoteProperty -Name SQLVersion -Value $SQLVersionNo
$OutputObj | Add-Member -MemberType NoteProperty -Name NumberOfConnections -Value $ConnectionsNo

$OutputObj | Export-CSV -Path “C:\TEMP\report.csv” -notype
}
}
}
}

end {}




Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC

'...if they take my stapler then I'll set the building on fire...'
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8376 Visits: 6161
Haven't got long so I will be brief (please don't mistake this for rudeness).

First, you have a typo ($DirveSize instead of $DriveSize):
$DriveSizeFree = $DirveSize.FreeSpace / 1GB



Secondly, $DriveSize is an array of objects with Name, Size and FreeSpace properties. So $DriveSize.FreeSpace makes no sense but $DriveSize[0].FreeSpace does (assuming there is at least one member in the array).

Hope this gets you further!!!

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Fred Stemp
Fred Stemp
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 219
Thanks Gary!

I'm still not getting the $DriveSize.Name back or $DriveSize.Size. I've tried adding the '[0]' and the same results. $DriveSize[0].DriveSizeFree returns now but in an expression like 2.49952E+11.

In the Drive array how do I populate for more then one drive? Many of my servers have more then 4 and vary in Name. I'd like to capture that in my CSV.

Thanks for the help and you were not rude in away! I appreciate you taking the time to help me!

Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC

'...if they take my stapler then I'll set the building on fire...'
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8376 Visits: 6161
I would guess that you need to replace:
$OutputObj | Add-Member -MemberType NoteProperty -Name DriveName -Value $DriveSize.Name
$OutputObj | Add-Member -MemberType NoteProperty -Name DriveSizeTotal -Value $DriveSize.Size
$OutputObj | Add-Member -MemberType NoteProperty -Name DriveSizeFree -Value $DriveSize.FreeSpace


with something like (NOTE: only partially tested):

$DriveNumber = 1
foreach ($Drive in $DriveSize)
{
$driveNameTitle = "DriveName_$DriveNumber"
$OutputObj | Add-Member -MemberType NoteProperty -Name $driveNameTitle -Value $Drive.Name
$driveSizeTotalTitle = "DriveSizeTotal_$DriveNumber"
$OutputObj | Add-Member -MemberType NoteProperty -Name $driveSizeTotalTitle -Value $Drive.Size
$driveSizeFreeTitle = "DriveSizeFree_$DriveNumber"
$OutputObj | Add-Member -MemberType NoteProperty -Name $driveSizeFreeTitle -Value $Drive.FreeSpace
$DriveNumber += 1
}



As for the "2.49952E+11" issue, I believe that it is a display in spreadsheet issue i.e. change the cell's format.

HTH!!!

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Fred Stemp
Fred Stemp
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 219
Gary you ROCK!!! The second solution works perfectly!!! Thanks so much for taking the time to help me!!

Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC

'...if they take my stapler then I'll set the building on fire...'
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8376 Visits: 6161
Thanks for the compliment Fred. Around here it appears that the attitude is that we like to help those who are trying to help themselves. I like that attitude. You did a fair amount of the ground work and got stuck. I realised that I may be able to help so I did. Others have done the same for me. No doubt you will help someone else too given the opportunity (or, indeed, may have already done so).

Note to other posters to this forum and who didn't get a response: consider whether you just posted a "I want to do this. How do you do it?" question. Fred already had his solution up and running and just needed a hand. That's the way we roll ;-P

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search