How to Reformat PS Script to Output to CSV

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Phil Hinton Phil Hinton 5 months ago.

  • Author
    Posts
  • #64918
    Profile photo of Phil Hinton
    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
    Profile photo of Phil Hinton
    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
    Profile photo of random commandline
    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
    Profile photo of Graham Beer
    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
    Profile photo of Phil Hinton
    Phil Hinton
    Participant

    I'll give that a shot, thank you!

You must be logged in to reply to this topic.