Needs some CSV help for some complex info

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Talderon Talderon 2 years, 3 months ago.

  • Author
    Posts
  • #18270
    Profile photo of Talderon
    Talderon
    Participant

    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!

  • #18271
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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!

  • #18276
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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
        }
    }
    
  • #18285
    Profile photo of Talderon
    Talderon
    Participant

    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!

  • #18286
    Profile photo of Talderon
    Talderon
    Participant

    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!

You must be logged in to reply to this topic.