Author Posts

February 21, 2017 at 3:35 pm

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

February 21, 2017 at 3:37 pm

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.

February 21, 2017 at 3:57 pm

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

February 21, 2017 at 3:59 pm

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

February 21, 2017 at 4:33 pm

I'll give that a shot, thank you!