Selecting specific columns in a query

Hello!

Executing the following two lines creates the files with the same number of columns:

Get-CimInstance -Query "Select * from Win32_LogicalDisk where DeviceID <> 'A:'" |Export-CSV c:\Reports\Disks.csv

Get-CimInstance -Query "Select DeviceID,  DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk where DeviceID <> 'A:'" |Export-CSV c:\Reports\Disks2.csv


Q: Why querying for specific columns yields the same result as querying for all columns (*)?

The same query written in VBscripts gives me exactly what I want - just the five columns.

Thank you in advance,

Michael

June 23rd, 2015 5:35am

Sorry but VBScript does not support Get-CimInstance.

Get-CimInstance Win32_LogicalDisk |
   Select DeviceID,  DriveType, VolumeName, Size, FreeSpace

Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 5:58am

>>
PS C:\scripts> Get-CimInstance -Query "Select DeviceID,  DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk"

DeviceID DriveType ProviderName VolumeName Size         FreeSpace
-------- --------- ------------ ---------- ----         ---------
C:       3                      ACER       484766117888 321252593664

June 23rd, 2015 5:59am

Sorry but VBScript does not support Get-CimInstance - of course not. I mean I can use VBscript to select the specific columns right in the query, NOT after selecting *:

"Set colDisks = objService.ExecQuery("Select DeviceID, Name, ... from Win32_LogicalDisk"

Does PS not support it?

Regards,

Michael

Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 6:10am

Not really the same as Get-CimInstance.  VBScript is more like Get-WmiObject.

I posted the results of the query and it selects only the columns specified.

Here it is again:

PS C:\scripts> Get-CimInstance -Query "Select DeviceID,  DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk"

DeviceID DriveType ProviderName VolumeName Size         FreeSpace
-------- --------- ------------ ---------- ----         ---------
C:       3                      ACER       484766117888 321252593664


June 23rd, 2015 6:15am

"Not really the same as Get-CimInstance.  VBScript is more like Get-WmiObject." - didn't understand it... GetCimInstance is one of the ways PS gets WMI data and theoreticaly that data must be exactly the same.

I also see only 5 columns when running the code from the PS console, but when I redirect it to a csv file I see all available columns... Strange enough.


  • Edited by MF47 20 hours 44 minutes ago
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 6:24am

So do it like this:

PS C:\scripts> Get-CimInstance Win32_LogicalDisk | Select DeviceID,  DriveType, VolumeName, Size, FreeSpace |export-csv test.csv
PS C:\scripts> import-csv test.csv


DeviceID   : C:
DriveType  : 3
VolumeName : ACER
Size       : 484766117888
FreeSpace  : 321227030528

June 23rd, 2015 6:35am

"...export-csvtest.csv
PS C:\scripts> import-csv test.csv"  ??? Is it some intricate trick??? I don't understand it :(

...but the result is still the same.

Frankly speaking I don't understand how a command can output 5 requested columns to the console and all available ones to the file :(


  • Edited by MF47 20 hours 17 minutes ago
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 6:55am

The code I posted works exactly as expected.  It stores 5 columns in the CSV<  I even posted the proof.

June 23rd, 2015 10:09am

"Not really the same as Get-CimInstance.  VBScript is more like Get-WmiObject." - didn't understand it... GetCimInstance is one of the ways PS gets WMI data and theoreticaly that data must be exactly the same.

I also see only 5 columns when running the code from the PS console, but when I redirect it to a csv file I see all available columns... Strange enough.


  • Edited by MF47 Tuesday, June 23, 2015 10:24 AM
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 10:19am

"Not really the same as Get-CimInstance.  VBScript is more like Get-WmiObject." - didn't understand it... GetCimInstance is one of the ways PS gets WMI data and theoreticaly that data must be exactly the same.

I also see only 5 columns when running the code from the PS console, but when I redirect it to a csv file I see all available columns... Strange enough.


  • Edited by MF47 Tuesday, June 23, 2015 10:24 AM
June 23rd, 2015 10:19am

"...export-csvtest.csv
PS C:\scripts> import-csv test.csv"  ??? Is it some intricate trick??? I don't understand it :(

...but the result is still the same.

Frankly speaking I don't understand how a command can output 5 requested columns to the console and all available ones to the file :(


  • Edited by MF47 Tuesday, June 23, 2015 10:51 AM
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 10:50am

"...export-csvtest.csv
PS C:\scripts> import-csv test.csv"  ??? Is it some intricate trick??? I don't understand it :(

...but the result is still the same.

Frankly speaking I don't understand how a command can output 5 requested columns to the console and all available ones to the file :(


  • Edited by MF47 Tuesday, June 23, 2015 10:51 AM
June 23rd, 2015 10:50am

"I even posted the proof." - I do beleive it, but my results are still the same.

Would you please explain to me what's the purpose of importing the file that just been created via export process? Why there should be any difference with/without import-csv?

Regards,

Michael

Free Windows Admin Tool Kit Click here and download it now
June 24th, 2015 3:18am

Sorry, I didn't notice that you used "Get-CimInstance Win32_LogicalDisk| Select DeviceID, ..." and not "Get-CimInstance -Query "Select DeviceID,  DriveType," in your last example.

As I've already said the problem arises only when selecting specific columns right in the query - filtering the output of Get-CimInstance Win32_LogicalDisk is not what I want.

I have found the root of the problem: it is the "where DeviceID <> 'A:' " operator.

This code works well:

1) Get-CimInstance -Query "Select DeviceID,  DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk |Export-Csv C:\Reports\Disks.csv

...but this doesn't:

2) Get-CimInstance -Query "Select DeviceID,  DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk where DeviceID <> 'A:'" |Export-Csv C:\Reports\Disks2.csv

I think it's a bug in PS.

Regards,

Michael



  • Edited by MF47 21 hours 18 minutes ago
  • Proposed as answer by jrv 20 hours 52 minutes ago
June 24th, 2015 5:29am

If you discover a bug it should be posted here: http://connect.microsoft.com/PowerShell

Free Windows Admin Tool Kit Click here and download it now
June 24th, 2015 6:18am

"I even posted the proof." - I do beleive it, but my results are still the same.

Would you please explain to me what's the purpose of importing the file that just been created via export process? Why there should be any difference with/without import-csv?

Regards,

Michael


I am importing the file as proof that the method works.  It was just for your convenience.
June 24th, 2015 6:25am

jrv, thank you very much for your help!

Regards,

Michael

Free Windows Admin Tool Kit Click here and download it now
June 24th, 2015 6:34am

It is an interesting issue.  Post it in connect to see what happens.
June 24th, 2015 6:44am

Sorry, I didn't notice that you used "Get-CimInstance Win32_LogicalDisk| Select DeviceID, ..." and not "Get-CimInstance -Query "Select DeviceID,  DriveType," in your last example.

As I've already said the problem arises only when selecting specific columns right in the query - filtering the output of Get-CimInstance Win32_LogicalDisk is not what I want.

I have found the root of the problem: it is the "where DeviceID <> 'A:' " operator.

This code works well:

1) Get-CimInstance -Query "Select DeviceID,  DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk |Export-Csv C:\Reports\Disks.csv

...but this doesn't:

2) Get-CimInstance -Query "Select DeviceID,  DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk where DeviceID <> 'A:'" |Export-Csv C:\Reports\Disks2.csv

I think it's a bug in PS.

Regards,

Michael



  • Edited by MF47 Wednesday, June 24, 2015 9:50 AM
  • Proposed as answer by jrv Wednesday, June 24, 2015 10:16 AM
Free Windows Admin Tool Kit Click here and download it now
June 24th, 2015 9:27am

Sorry, I didn't notice that you used "Get-CimInstance Win32_LogicalDisk| Select DeviceID, ..." and not "Get-CimInstance -Query "Select DeviceID,  DriveType," in your last example.

As I've already said the problem arises only when selecting specific columns right in the query - filtering the output of Get-CimInstance Win32_LogicalDisk is not what I want.

I have found the root of the problem: it is the "where DeviceID <> 'A:' " operator.

This code works well:

1) Get-CimInstance -Query "Select DeviceID,  DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk |Export-Csv C:\Reports\Disks.csv

...but this doesn't:

2) Get-CimInstance -Query "Select DeviceID,  DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk where DeviceID <> 'A:'" |Export-Csv C:\Reports\Disks2.csv

I think it's a bug in PS.

Regards,

Michael



  • Edited by MF47 Wednesday, June 24, 2015 9:50 AM
  • Proposed as answer by jrv Wednesday, June 24, 2015 10:16 AM
June 24th, 2015 9:27am

I've posted it today... but don't know how(if) I can post a link to it...

I named the post "Exported results of the query may contain excessive columns".

Regards,

Michael


  • Edited by MF47 21 hours 20 minutes ago
Free Windows Admin Tool Kit Click here and download it now
June 25th, 2015 5:49am

Link here: https://connect.microsoft.com/PowerShell/Feedback/Details/1470262

Voted and commented that it is reproducible in WMF 4 and 5

June 25th, 2015 7:04am

Thank you very much, jrv!

Regards,

Michael

Free Windows Admin Tool Kit Click here and download it now
June 25th, 2015 7:37am

You are welcome. It is a curious bug or feature.
June 25th, 2015 8:04am

I've posted it today... but don't know how(if) I can post a link to it...

I named the post "Exported results of the query may contain excessive columns".

Regards,

Michael


  • Edited by MF47 Thursday, June 25, 2015 9:48 AM
Free Windows Admin Tool Kit Click here and download it now
June 25th, 2015 9:48am

I've posted it today... but don't know how(if) I can post a link to it...

I named the post "Exported results of the query may contain excessive columns".

Regards,

Michael


  • Edited by MF47 Thursday, June 25, 2015 9:48 AM
June 25th, 2015 9:48am

Hello!

Here's the MS's answer:

"Thanks for the bug report! Unfortunately, this is working as designed. The output on the console is using a custom formatxml for CIM instances that don't show properties with null values. If you inspect the actual object, you'll see that it has all the properties of the class schema although those property values are null. Export-csv doesn't treat null differently (as it may be important) so you get the extra columns in the output. To get the behavior you want, you can use the PowerShell select-object cmdlet (aliased as 'select'):

Get-CimInstance -Query "Select DeviceID, DriveType, VolumeName, Size, FreeSpace from Win32_LogicalDisk | select deviceid, drivetype, volumnename, size, freespace | Export-Csv C:\Reports\Disks.csv"

https://connect.microsoft.com/PowerShell/feedback/details/1470262/exported-results-of-the-query-contain-excessive-columns

Regards,

Michael

Free Windows Admin Tool Kit Click here and download it now
July 6th, 2015 9:24am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics