1

I need help getting started on this script and will do the best to explain what I am trying to accomplish. I have a set of clusters, some have no custom config, some have one custom config, some have two, and other have more. I would to export these custom configs into a CSV, with column headers like custom1, custom2, custom3, etc.

So I need the script to create new column headers based on how many custom config a cluster might have, while either adding NULL or leaving blank cluster that don't have such config. Here is an example of a layout in my head.

ClusterName Custom1 Custom2 Custom3 Custom4
ABC         123     456     NULL    NULL
DEF         NULL    NULL    NULL    NULL
GHI         123     456     789     abc

I don't want to statically create the column before hand, because the custom config could really vary and I need to programmatically allow the script to create the columns based on the data retrieved. I hope all this makes sense and thanks for any help.

jrob24
  • 454
  • 1
  • 7
  • 18
  • 1
    Nothing yet as I am trying to figure out where to begin. I believe a for loop would work, however I wanted to ask the question here first before starting. – jrob24 Mar 02 '14 at 13:49

3 Answers3

2

Get a list of all propertynames using Get-Member and use Select-Object + Export-CSV to export the objects using a common header. Objects missing a value will set it to null.

$a = @()
$a += [pscustomobject]@{ClusterName="ABC";Custom1=123;Custom2=456}
$a += [pscustomobject]@{ClusterName="DEF"}
$a += [pscustomobject]@{ClusterName="GHI";Custom1=123;Custom2=456; Custom3=789;Custom4="abc"}    

$properties = $a | ForEach-Object { 
    $_ | Get-Member -MemberType Property, NoteProperty
} | Select-Object -ExpandProperty Name -Unique

$a | Select-Object $properties | Export-Csv test.csv -NoTypeInformation

test.csv

"ClusterName","Custom1","Custom2","Custom3","Custom4"
"ABC","123","456",,
"DEF",,,,
"GHI","123","456","789","abc"
Frode F.
  • 52,376
  • 9
  • 98
  • 114
  • 1
    In your example you are defining Custom1-Custom4, in my situation I will not know how many Custom configs exist. So how would I go about creating CustomX, not knowing how many will be defined? – jrob24 Mar 03 '14 at 16:34
  • The Custom1-4 is just sample data. Everything before `$properties = $a ...` is just creating sample data with different properties. The code works without knowing anything about the property names. They could be Example1,Test2,Unknown10 for all it cares. The solution you need is `$properties = ...` and the rest of the script. Just replace `$a` with the variable where you collection of objects are stored. – Frode F. Mar 03 '14 at 16:55
0

Your can dynamically create any object within your script. That object can then be piped out to export-csv, whatever columns have been created will get exported.

Jimbo
  • 2,529
  • 19
  • 22
  • 1
    Yes, however how to do dynamically create them when don't know how many to create because it will vary by object? – jrob24 Mar 02 '14 at 13:48
  • 1
    By creating them as you encounter them in the course of processing the data. – alroc Mar 02 '14 at 16:28
0

I had to do something similar - where I was reporting on hard disks for VMs. Some VMs had up to 15 HDDs, while others only had a couple. The CSV was only creating 5 columns.

I added an extra field in the script to count the number of disks for each VM, then at the end, sorted the data by number of disks (descending). Then I could send the output to Out-Gridview or ConvertTo-CSV.

Example count column:

$disks = $vm.guest.disk
$disks = $disks.count
$Details | Add-Member -Name NumberOfDisks -Value $disks -Membertype NoteProperty

Example data sort before piping elsewhere:

$MyCollection = $MyCollection | Sort-Object NumberOfDisks -Descending

$MyCollection piped to Out-Gridview - note the NULL/empty columns:

enter image description here

KERR
  • 1,312
  • 18
  • 13
  • First: `$disks = $vm.guest.disk; $disks = $disks.count` why not just `$vm.guest.disk.count`? 2nd: `$Details` is undefined. 3rd: `$MyCollection` is undefined. Oh yeah, `$vm` is also undefined. – Clijsters Nov 22 '17 at 06:30
  • The idea of my answer is to sort the data descending (by finding a way to count the columns required for each row - eg number of disks) before sending to CSV etc. None of the variables I described have anything to do with OP's question, they're just an example. – KERR Nov 23 '17 at 01:25
  • They are a non-working example. Is it so hard to just add a declaration with pseudo data to them? I like this idea. However, I just found that your answer could be improved. – Clijsters Nov 23 '17 at 08:16