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

Retrieving Data From Hyphenated Server Expand / Collapse
Author
Message
Posted Wednesday, May 22, 2013 11:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:07 AM
Points: 1,260, Visits: 3,545
What better place to solve a nagging Powershell issue than good ol' SQL Server Central :)

I'm trying to retrieve disk usage data from servers on my domain. Server list is pulled from a simple text file on the local computer, each server is then queried for disk information, data is saved in a table, data is written to a database table.
This was taken from somewhere on the internet and tweaked to work in my environment, and it has worked splendidly until I was asked to incorporate additional servers that had hyphenated names.

#define servers to be monitored
$server = get-content C:\<path>\serverList.txt

#data table to hold results
Function out-DataTable
{
$dt = new-object Data.datatable
$First = $true

foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.get_properties() | foreach {
if ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
elseif ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
else {
$DR.Item($_.Name) = $_.value
}
}
$DT.Rows.Add($DR)
$First = $false
}

return @(,($dt))

}

#function to retrieve disk information
Function Get-DisksSpace ([string]$Servername, $unit= "GB")
{
$measure = "1$unit"

Get-WmiObject -computername $serverName -query "
select SystemName, Name, DriveType, FileSystem, FreeSpace, Capacity, Label
from Win32_Volume
where DriveType = 2 or DriveType = 3" `
| select SystemName `
, Name `
, @{Label="SizeIn$unit";Expression={"{0:n2}" -f($_.Capacity/$measure)}} `
, @{Label="FreeIn$unit";Expression={"{0:n2}" -f($_.freespace/$measure)}} `
, Label
}

#execute the functions
foreach ($s in $server)
{
#Write what is being retrieved
Get-DisksSpace $s

#Load into table and database
$dataTable = Get-DisksSpace $s | where {$_.name -like "E:\*" -or $_.name -like "C:\*"} | out-DataTable
$connectionString = "Data Source=<Server\Instance>; Integrated Security=True;Initial Catalog=<DestinationDB>;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "<Schema.Table>"
$bulkCopy.WriteToServer($dataTable)
}

This returns all the information requested except for a system name for hyphenated servers. If you run just the Get-WmiObject cmdlet, two different results are returned.
Get-WmiObject -computername computername -query "
select SystemName
from Win32_Volume
where DriveType = 2 or DriveType = 3"

Returns

__GENUS : 2
__CLASS : Win32_Volume
__SUPERCLASS :
__DYNASTY :
__RELPATH :
__PROPERTY_COUNT : 7
__DERIVATION : {}
__SERVER :
__NAMESPACE :
__PATH :
SystemName : COMPUTERNAME

Get-WmiObject -computername computer-name -query "
select SystemName
from Win32_Volume
where DriveType = 2 or DriveType = 3"

Returns

__GENUS : 2
__CLASS : Win32_Volume
__SUPERCLASS :
__DYNASTY :
__RELPATH :
__PROPERTY_COUNT : 1
__DERIVATION : {}
__SERVER :
__NAMESPACE :
__PATH :
SystemName :

I've tried surrounding the computer-name with single and double quotes. I've tried escaping the hyphens with backquotes " ` ". Has anyone else ran into this issue before?


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1455628
Posted Wednesday, May 22, 2013 1:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:52 PM
Points: 7,079, Visits: 12,569
That's odd. I am not having any issues accessing my vm which includes a hyphen in the name:



Maybe check that you have permissions to access WMI on that server, i.e. that you're a local admin. Also double-check that you did not copy and paste an n-dash or an m-dash from an email or Word Doc and that you actually have a hyphen in there.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato


  Post Attachments 
519cc9ef.jpg (104 views, 36.21 KB)
Post #1455666
Posted Thursday, May 23, 2013 6:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:07 AM
Points: 1,260, Visits: 3,545
Great suggestions, unfortunately it was still an unsuccessful attempt. I tried hyphenated, en-dash, and em-dash, all of which returned the same results, yet still no systemName. I went so far as to add myself as a local admin, set the execution policy to unrestricted, and run the Get-WmiObject snippet locally. Still a blank systemname returned.

On a positive note, I've always wondered why/how word changed the size of my hyphens. Never heard of en-dash or em-dash so thanks for that tidbit of info!


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1455975
Posted Friday, May 24, 2013 6:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:39 AM
Points: 428, Visits: 926
Try with square brackets? [server-name]?


Post #1456430
Posted Tuesday, May 28, 2013 6:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:07 AM
Points: 1,260, Visits: 3,545
Alright. I did some more testing and I figured it out!
The computer naming convention we have is 15 characters in length which is apparently too long for powerShell.
I created a test machine with an 12 characters and two hyphens and it returns the system name just fine. I have to now find a way to convince the systems guy to change his naming convention :)


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1457284
Posted Tuesday, May 28, 2013 6:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:39 AM
Points: 428, Visits: 926
Wow, good to know.

It appears you can use an ip address, perhaps that will allow you to move forward while they debate changes to the naming convention.



Post #1457293
Posted Tuesday, May 28, 2013 7:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:07 AM
Points: 1,260, Visits: 3,545
I had tried IP addresses in the scripts and it also returns a null systemName. I even tried creating a DNS alias for the long server name and it still returns NULL. Must be a variable somewhere in the powershell cmdlet code.

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1457309
Posted Tuesday, May 28, 2013 9:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:52 PM
Points: 7,079, Visits: 12,569
AD supports 15 character computer names so this sounds like a bug in the cmdlet that would have surfaced pretty quick and been fixed, but maybe not. I don't have a machine that qualifies to test with but may setup a vm if I have time. Which version of PoSh? Run $host.version.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1457380
Posted Tuesday, May 28, 2013 9:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:07 AM
Points: 1,260, Visits: 3,545
powershell 2.0

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1457394
Posted Wednesday, May 29, 2013 7:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:07 AM
Points: 1,260, Visits: 3,545
opened a connect bug for it


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1457736
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse