October 10, 2013 at 3:26 pm
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 :), 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...'
October 11, 2013 at 1:59 am
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!!!
October 11, 2013 at 7:47 am
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...'
October 11, 2013 at 8:24 am
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!!!
October 11, 2013 at 8:42 am
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...'
October 11, 2013 at 9:00 am
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!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply