Needs some CSV help for some complex info

[dennis-christilaw]

Hey all.

I am not “new” to PowerShell, but more complex scripting is something I have not really dug into as much as I need too so am working on things now.

I have hundreds of Web Servers that i need to manage and need to gather some information on them so I can populate a database.

First things first. I have to pull the information. I have the beginnings of a script and am getting output, but it’s formatting the output into a useable format that I am having a hard time with. What I would ultimately like to end up with is a CSV file that I can dabble with and use to insert into a database.

Here is my script so far:

Begin { 
 
 $array0 = @()
 $array1 = @()
 
 # Sets location of APPCMD.exe - This is needed to pull data from IIS from the script
 Set-Location "c:\windows\system32\inetsrv\"
 } 

 # Queries the registry to see what .NET versions are installed
 Process{ 
 
 if((Get-ItemProperty -ErrorAction SilentlyContinue "HKLM:\Software\Microsoft\NET Framework Setup\NDP\v2.0.50727").Version -like "2.*") {
 $array0 += "2.0"
 }
 if((Get-ItemProperty -ErrorAction SilentlyContinue "HKLM:\Software\Microsoft\NET Framework Setup\NDP\v3.0\Setup").Version -like "3.0*") {
 $array0 += "3.0"
 }
 if((Get-ItemProperty -ErrorAction SilentlyContinue "HKLM:\Software\Microsoft\NET Framework Setup\NDP\v3.5").Version -like "3.5*") {
 $array0 += "3.5"
 }
 if((Get-ItemProperty -ErrorAction SilentlyContinue "HKLM:\Software\Microsoft\NET Framework Setup\NDP\v4\Full").Version -like "4.0*") {
 $array0 += "4.0"
 }
 if((Get-ItemProperty -ErrorAction SilentlyContinue "HKLM:\Software\Microsoft\NET Framework Setup\NDP\v4\Full").Version -like "4.5*") {
 $array0 += "4.5"
 } 

# Stores the .NET information into an array for later use
[String]$dotNet = $array0

# Get computer name and domain
$compInfo = gwmi win32_computersystem | Select DNSHostName,Domain | ConvertTo-csv -NoTypeInformation

# Populate Array with Object properties
$array1 += $compInfo
$array1

# Query for site information
[XML]$sites = .\appcmd list Site /config:* /XML
$site1 = $sites.SelectNodes("//SITE")
$sitesOutput = $site1 | Select SITE.NAME,bindings | ConvertTo-csv -NoTypeInformation
$sitesOutput

# Putting it all together
$csvfile = "D:\scripts\" + $env:ComputerName + "_config.csv"

Out-File -FilePath $csvfile

}

(Yeah, I know I have not done anything with the .NET version info yet.

Now, I get SCREEN output:

"DNSHostName","Domain"
"servername","domain.com"
"SITE.NAME","bindings"
"Default Web Site","http/*:80:,net.tcp/808:*,net.pipe/*,net.msmq/localhost,msmq.formatname/localhost"
"AdminApps","http/10.224.96.10:80:"
"NotificationServices","http/10.224.96.12:80:"
"GoSomeSite","http/10.224.96.16:80:,https/10.224.96.16:443:"
"SomeOtherSite","http/10.224.96.14:80:"
"Domain","http/10.224.97.19:80:,http/10.224.97.19:80:www1.domain.com"

However, the CSV is empty.

I know the output is messy, but it’s a start and i can get it going. I have researched a bit and am kinda brain dead at the moment. Any help is appreciated and any tips on making things look nicer would be great too!

Thanks!

[donj]

The problem is that CSV is a flat format, meaning it isn’t intended for complex data. For example, what you’ve got in your Domain column would be better represented as a separate “table” linked to the “DNSHostName” table. Which a relational database can do, but which a single CSV can’t. XML is a better format for that kind of complex data.

I guess the other thing is that you’re not really leveraging PowerShell’s strengths. I would probably write a tool that queried the desired information and created custom objects, which were output to the pipeline. I could then pipe that tool’s output to Export-CSV or Export-CliXML to save it in a data file. That’s the design approach “Learn PowerShell Toolmaking in a Month of Lunches” teaches, for example.

I’m also not sure why there’s a BEGIN block… that usually suggests a tool that accepts pipeline input, but I’d expect that input to be computer names, which means a lot of this would be in the PROCESS block. So perhaps I’m not understanding what you’re trying to achieve!

[_timpringle]

Hi Talderon.

You could try something like below. Because you’re talking about querying multiple systems, i’ve added computername as a parameter which can either be a single string or multiple ones. The results will be output to the pipeline, and you can do whatever you want with the data after then.

NB The invoke-command part is a possible way running the command on the remote box, it will all depend on your exact requirements what specific parameters and format you will need to use for the remote session. Use the value of ‘comp’ as the computer to do the remote session on, and put the code into your -scriptblock {} part.

You’ll probably have problems with this if you’re using legacy port listener settings for WinRM.

function Get-WebServerInfo
{
    [CmdletBinding()]
    
    Param
    (
        [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]  [string[]] $ComputerName = $env:computername
    )
    
    Process
    {
        ForEach ($comp in $ComputerName)
        {
            
            [array] $allServerInfo += Invoke-Command -ComputerName $comp -Scriptblock {
                
                $dotNetVersions = (Get-ChildItem -Path "HKLM:\Software\Microsoft\NET Framework Setup\NDP" | Get-ItemProperty).Version
                [xml] $xmlSites = .$env:windir\system32\inetsrv\appcmd.exe list site /CONFIG:* /XML
                $sites = $xmlSites.appcmd.site | Select Site.Name, bindings
                $compInfo = Get-WmiObject -class win32_computersystem | Select-Object DNSHostName, Domain
                
                
                $hash = @{
                    dotNetVersions = $dotNetVersions
                    sites = $sites
                    hostname = $compInfo.dnshostname
                    domain = $compInfo.domain
                }
                New-Object -TypeName psobject -Property $hash
            }
        }
        $allServerInfo
    }
}

[dennis-christilaw]

Hey guys, thanks for the input. I managed to get quote far in my script and am able to get all of the data I want. I am just at the point now where I am either going to mess with the output some more and TRY to get it the way I want, or just do it manually.

Taking on this script has taught me a LOT and also had a LOT of frustration to go with it, but I am very happy with what I have been able to do with this.

Here is the current version of the script:

Process{ 

$array1 = @()
 
# Sets location of APPCMD.exe - This is needed to pull data from IIS from the script
Set-Location "c:\windows\system32\inetsrv\"
 
# Populate Array with Object properties
$array1 += $compInfo +=$sitesOutput +=$appOutput +=$vDirOutput +=$poolOutput
$array1

# Get computer name and domain
$compInfo = gwmi win32_computersystem | Select DNSHostName,Domain | ConvertTo-csv -NoTypeInformation | % {$_ -replace '"',''}

# Query for site information
[XML]$sites = .\appcmd list Site /config:* /XML
$site1 = $sites.SelectNodes("//SITE")
$sitesOutput = $site1 | Select SITE.NAME,bindings | %{$_ -Replace ":80:", ";"} | %{$_ -Replace ":443:", ";"} | %{$_ -Replace ";,", ";"} | %{$_ -Replace "@{SITE.NAME=", ""} | %{$_ -Replace " bindings=", ""} | %{$_ -Replace "}", ""} | %{$_ -Replace ",http", ";http"}  | ? {-not($_ -match "Default Web Site;")}  | %{$_ -Replace "http/", "http://"} | %{$_ -Replace "https/", "https://"} | %{$_ -Replace ";", ","}
$sitesOutput

# Query for App Information
[XML]$app = .\appcmd list app /config:* /XML
$app1 = $app.appcmd.APP 
$appOutput = $app1 | Select APP.NAME,APPPOOL.NAME | ConvertTo-csv -NoTypeInformation | % {$_ -replace '"',''} | ? {-not($_ -match "Default")}
$appOutput

# Query for Directory/Path Information
[XML]$vDir = .\appcmd list VDIR /config:* /XML
$vDir1 = $vDir.SelectNodes("//VDIR")
$vDirOutput = $vDir1 | Select VDIR.NAME, APP.NAME,physicalPath | ConvertTo-csv -NoTypeInformation | % {$_ -replace '"',''} | ? {-not($_ -match "Default")}
$vDirOutput
 
# Query for App Pool Information
# Retrieve app pool summary config
[XML]$poolNames = .\appcmd list apppool /config:* /XML
$poolNames1 = $poolNames.appcmd.APPPOOL
$poolOutput = $poolNames1 | Select APPPOOL.NAME,PipelineMode,RuntimeVersion | ConvertTo-csv -NoTypeInformation | % {$_ -replace '"',''} | ? {-not($_ -match "Default")}
$poolOutput
 
# Putting it all together
$csvfile = "D:\scripts\" + $env:ComputerName + "_IISconfig.csv"

$array1 | Out-File -FilePath $csvfile
}

Here are a few things I was able to get done and working:

 % {$_ -replace '"',''}

- What this does is strips the double quotes out of the output when using the ConvertTo-Csv command

? {-not($_ -match "Default")}

- This removes the Default Website entries as I do not need those as it is not used

This part was a mess:

$sitesOutput = $site1 | Select SITE.NAME,bindings | %{$_ -Replace ":80:", ";"} | %{$_ -Replace ":443:", ";"} | %{$_ -Replace ";,", ";"} | %{$_ -Replace "@{SITE.NAME=", ""} | %{$_ -Replace " bindings=", ""} | %{$_ -Replace "}", ""} | %{$_ -Replace ",http", ";http"}  | ? {-not($_ -match "Default Web Site;")}  | %{$_ -Replace "http/", "http://"} | %{$_ -Replace "https/", "https://"} | %{$_ -Replace ";", ","}

The bindings output is a real mess and looks like this:

 Sorry, board stripped the code, but trust me, it's a MESS

Rather than mussing with it “post production”, I created a very messy, but functional, replace group of commands. Why did I use ; as a delimiter and then convert it all to , at the end? Less mess.

Now, that same line looks like this:

Microsoft,http://172.25.150.100,http://172.25.150.100,www1.uatmicrosoft.com,https://172.25.150.100,

Although, I just can’t seem to get rid of that last Comma…

Oh yeah, but my ORIGINAL issue, I fixed it with this near the beginning:

# Populate Array with Object properties
$array1 += $compInfo +=$sitesOutput +=$appOutput +=$vDirOutput +=$poolOutput
$array1

and then

$array1 | Out-File -FilePath $csvfile

at the end.

I’ll update a bit more when I have time.

Thanks!

[dennis-christilaw]

Ok, now I have a few minutes again.

What the issue for me on making this script REALLY complete is that the output is CSV, but each section is just appended to the end of the file, so headers for different sections are mixed in with the data. Like:

HEADER1,HEADER2
data1,data2
HEADER3,HEADER4
data3,data4

…and so on and so forth.

Ultimately, i would like this to take the headers and append them along with the rest, but because of the nature of the raw data, I don’t think that will be a reality… yet.

Thanks for listening to my ramblings!