Retrieving Data From Hyphenated Server
-
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?
-
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 -
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!
-
Try with square brackets? [server-name]?
-
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 🙂
-
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.
-
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.
-
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 -
opened a connect bug for it
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply