How to Reformat PS Script to Output to CSV

This topic contains 4 replies, has 3 voices, and was last updated by  Phil Hinton 7 months, 3 weeks ago.

  • Author
    Posts
  • #64918

    Phil Hinton
    Participant

    Hello everyone, I have been running a maintenance script for the last few months to pull CPU and Memory data from each of our 57 servers, then output the info to a txt file. A few weeks ago, I was asked to create a spreadsheet to track the differences from day to day. This is getting annoying, because I now have to type all that info into a spreadsheet. So, what I'd like to do is modify my script to output the data into a csv, so I can just copy/paste into my daily log. Here are a few examples of what I am requesting:

    Current Output (.txt):
    ComputerName : Server_1
    CPU_Percent_Used : 6
    MEM_Available_MBytes : 15239
    MEM_Percent_Commited_Bytes : 18
    MEM_Committed_MBytes : 9308
    Status : Success

    ComputerName : Server_2
    CPU_Percent_Used : 0
    MEM_Available_MBytes : 14738
    MEM_Percent_Commited_Bytes : 13
    MEM_Committed_MBytes : 5567
    Status : Success

    ComputerName : Server_3
    CPU_Percent_Used : 5
    MEM_Available_MBytes : 3189
    MEM_Percent_Commited_Bytes : 28
    MEM_Committed_MBytes : 4691.5
    Status : Success

    The Output I would like to see (.csv)
    ComputerName CPU_Percent_Used MEM_Available_MBytes MEM_Percent_Commited_Bytes MEM_Commited_Mbytes Status
    Server_1 6 15239 18 9308 Success
    Server_2 0 14738 13 5567 Success
    Server_3 5 3189 28 4691.5 Success

    And, here is my script:

    $servers = Get-Content .\server_list.txt
    $outputFileName = "..\..\scriptOutputFiles\get_CPU_MEM_output_v3_$(get-date -f yyyy-MM-dd).txt"
    $errorString = "NO DATA"
    
    $results = foreach($server in $servers)
    {
        if (!(Test-Connection $server -quiet))
        {
            $data = [ordered]@{
                "ComputerName"               = $server
                "CPU_Percent_Used"           = $errorString
                "MEM_Available_MBytes"       = $errorString
                "MEM_Percent_Commited_Bytes" = $errorString
                "MEM_Committed_MBytes"       = $errorString
                "Status"                     = "***************  Problem connecting to $server!  ***************"
            }
        }
        else
        {
            $perfProc = Get-WmiObject -ComputerName $server -Class Win32_PerfFormattedData_PerfOS_Processor | 
            Select -Property @{Name="Proc#"; Expression = {($_.Name)}},
                             @{Name="Percent_Used"; Expression = {($_.PercentProcessorTime)}},
                             @{Name="Percent_Idle"; Expression = {($_.PercentIdleTime)}}
    
            $perfOS = Get-WmiObject -ComputerName $server -Class Win32_PerfFormattedData_PerfOS_Memory | 
            Select -Property @{Name="Percent_Commited_Bytes"; Expression = {($_.PercentCommittedBytesInUse)}},
                             @{Name="Available_MBytes"; Expression = {($_.AvailableMBytes)}},
                             @{Name="Committed_MBytes"; Expression = {[Math]::Round($_.CommittedBytes/1mb, 1)}}
            
            
            $data = [ordered]@{
                "ComputerName"               = $server
                "CPU_Percent_Used"           = $perfProc.Get(0).Percent_Used
                "MEM_Available_MBytes"       = $perfOS.Available_MBytes
                "MEM_Percent_Commited_Bytes" = $perfOS.Percent_Commited_Bytes
                "MEM_Committed_MBytes"       = $perfOS.Committed_MBytes
                "Status"                     = "Success"
            }
        }
    
        New-Object -TypeName PSObject -Property $data
    }
    
    $results | Out-File $outputFileName
    

    Any help would be greatly appreciated.

    Phil

  • #64920

    Phil Hinton
    Participant

    My suggested output did not save properly when I submitted my post, but I think you should be able to get the basic idea on what I'm looking for.

  • #64923

    random commandline
    Participant

    Change your output filename and last line to export csv. If your results contains array properties, you may have to use 'Out-String' or '-join' operator to have those display correctly in your spreadsheet.

    $outputFileName = "..\..\scriptOutputFiles\get_CPU_MEM_output_v3_$(get-date -f yyyy-MM-dd).csv"
    $results | export-csv $outputFileName -NoTypeInformation
    
  • #64926

    Graham Beer
    Participant

    Try using the command Export-Csv to send your output to a file. Also have a look at the ConvertFrom-CSV cmdlet as well.

  • #64938

    Phil Hinton
    Participant

    I'll give that a shot, thank you!

You must be logged in to reply to this topic.