Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with PS Script Expand / Collapse
Author
Message
Posted Thursday, October 10, 2013 3:26 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:13 AM
Points: 17, Visits: 145
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
Southern Insurance Underwriters, Inc.

'...if they take my stapler then I'll set the building on fire...'
Post #1503813
Posted Friday, October 11, 2013 1:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 5,162, Visits: 2,767
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!!!
Post #1503906
Posted Friday, October 11, 2013 7:47 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:13 AM
Points: 17, Visits: 145
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
Southern Insurance Underwriters, Inc.

'...if they take my stapler then I'll set the building on fire...'
Post #1504016
Posted Friday, October 11, 2013 8:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 5,162, Visits: 2,767
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!!!
Post #1504030
Posted Friday, October 11, 2013 8:42 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:13 AM
Points: 17, Visits: 145
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
Southern Insurance Underwriters, Inc.

'...if they take my stapler then I'll set the building on fire...'
Post #1504042
Posted Friday, October 11, 2013 9:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 5,162, Visits: 2,767
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!!!
Post #1504055
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse