0

I am writing a short script to inventory all servers in our infrastructure, this used to be done manually which means some servers may show up as active in our SQL DB but is actually offline, or vice versa. What i want to do is query our vCenter, SQL and AD and then compiling the result into a csv to be viewed in Excel (see pic ) http://imgur.com/NnwEM4H

Now here is my question: How do i add the columns and format this properly? I want a seperate row for each servername which means sometimes that row will be a blank/empty in one or more of the columns. The Refrence list will always contain a servername though.

I've tried the following:

$ServerList = @()

foreach ($p in $RefList)
{   
    $Server = New-Object System.Object
    $Server | Add-Member -MemberType NoteProperty -Name RefrenceList -Value $p.Name

    $ServerList += $Server
}

foreach ($s in $VMList)
{   
    $Server = New-Object System.Object
    $Server | Add-Member -MemberType NoteProperty -Name vCenter -Value $s.name

    $ServerList += $Server
}

Which doesnt create the vCenter column or add any data to it.

I also asked over at reddit/r/powershell and tried the following, which unfortently doesn't work either:

$ServerList = @()

foreach ($p in $RefList)    
{    
    $Server = New-Object System.Object   
    $Server | Add-Member -MemberType NoteProperty -NameRefrenceList -Value $p.Name       

    $ServerList += $Server     
}         

Foreach ($server in $serverlist)    
{
    if ($vmlist -contains $server)
    {
        $server | add-member -MemberTypeNoteProperty -Name VMList -Value $Server
    }
}

I'm at a loss here, i can't even get the first 2 columns to work.. Any advice i greatly appriceiated

Mykola Yashchenko
  • 5,103
  • 3
  • 39
  • 48
Notumlord
  • 33
  • 5

1 Answers1

0

First build the List will all Noteproperties :

$ServerList = $reflist | select Name, SQL,SCCM, AD, VCenter #as needed.

Now you can easily update the fields with your foreach Statements.

foreach ($server in $ServerList)    
{
  if ($vmlist -contains $server)
  {
    $Server.VCenter =  $Server.Name
  }
  if ($sqllist -contains $server)
  {
    $Server.SQL =  $Server.Name
  }
 #....
}

and so on with your other lists.

Martin
  • 1,853
  • 3
  • 15
  • 22
  • Every field is missing though? Running that results in: RefList: SQL: SCCM: VCenter: – Notumlord Feb 24 '16 at 13:02
  • Ive asked for an example output to get the names of your fields. If you want to have renamed fields you can use with @{l=fieldname;e={$_.oldfieldname}} in select statement. (l = label, e = expression) – Martin Feb 24 '16 at 13:15
  • Do you mean what the variables contain? $reflist is the result of a SQL Query “SELECT Name, Status FROM server_view" $VMList is a result of $VMList = Get-VM | Select-Object -Property Name, PowerState Sorry if im misunderstanding you – Notumlord Feb 24 '16 at 13:24
  • I meant the properties in the var. Like $VMList contains 2 properties Name and PowerState, SQL contains Name and Status. I will update the answer accordingly. – Martin Feb 24 '16 at 13:33
  • That's not right though? That just creates a long list of the servernames and it's properties. I want to compare servernames contained in variables against the $reflist variable and have the output like in the pic below. If no match is found leave that row/field blank – Notumlord Feb 24 '16 at 14:37
  • Okay, i think i got you know. Will edit original answer. – Martin Feb 24 '16 at 14:42