Powershell - trying to merge 2 result in 1 txt/csv-CodePudding

I'm trying to make a daily script to check status of list of URLS and pinging servers.

I've tried to combine the csv, however, the output of $status code is different from the one in csv

$pathIn = "C:\\Users\\test\\Desktop\\URLList.txt"
$URLList = Get-Content -Path $pathIn
$names = gc "C:\\Users\\test\\Desktop\\hostnames.txt"

#status code
$result = foreach ($uri in $URLList) {
    try {
        $res = Invoke-WebRequest -Uri $uri -UseBasicParsing -DisableKeepAlive -Method Head -TimeoutSec 5 -ErrorAction Stop
        $status = [int]$res.StatusCode
    }
    catch {
        $status = [int]$_.Exception.Response.StatusCode.value__
    }
    
    # output a formatted string to capture in variable $result
    "$status - $uri"
}

$result

#output to log file
$result | Export-Csv "C:\\Users\\test\\Desktop\\Logs.csv"

#ping
$output = $()

foreach ($name in $names) {
    $results = @{ "Host Name" = $name }
    if (Test-Connection -Computername $name -Count 5 -ea 0) {
        $results["Results"] = "Up"
    }
    
    else {
        $results["Results"] = "Down"
    }

    New-Object -TypeName PSObject -Property $results -OutVariable nameStatus
    $output  = $nameStatus
}

$output | Export-Csv "C:\\Users\\test\\Desktop\\hostname.csv"

#combine the 2 csvs into 1 excel file
$path = "C:\\Users\\test\\Desktop" #target folder
cd $path;
$csvs = Get-ChildItem .\*.csv
$csvCount = $csvs.Count
Write-Host "Detected the following CSV files: ($csvCount)"

foreach ($csv in $csvs) {
    Write-Host " -"$csv.Name
}

Write-Host "--------------------"
$excelFileName = "daily $(get-Date -Format dd-MM-yyyy).xlsx"
Write-Host "Creating: $excelFileName"

foreach ($csv in $csvs) {
    $csvPath = ".\"   $csv.Name
    $worksheetName = $csv.Name.Replace(".csv", "")
    Write-Host " - Adding $worksheetName to $excelFileName"
    Import-Csv -Path $csvPath | Export-Excel -Path $excelFileName -WorkSheetname $worksheetName
}

Write-Host "--------------------"
cd $path;
Get-ChildItem \* -Include \*.csv -Recurse | Remove-Item
Write-Host "Cleaning up"

Output in PowerShell

200 - https://chargebacks911.com/play-404/
200 - https://www.google.com/
500 - httpstat.us/500/

Host Name    Results

----------------

x.x.x.x    Down  
x.x.x.x Up  
Detected the following CSV files: (2)

- daily 26-03-2022.csv
- Logs.csv

--------------------

Creating: daily26-03-2022.xlsx

- Adding daily 26-03-2022 to daily26-03-2022.xlsx
- Adding Logs to daily26-03-2022.xlsx

--------------------

Cleaning up

\----------------------------------

result in excel

\#Hostname

Host Name   Results

x.x.x.x Down

x.x.x.x Up

\#Logs

Length

42

29

22

I would like to know

how to correct the output in "Logs" sheet
if there's anyway to simplify my script to make it cleaner

CodePudding user response：

Welcome to SO. You're asking for a review or refactoring of your complete script. I think that's not how SO is supposed be used. Instead you may focus on one particular issue and ask about a specific problem you have with it.

I will focus only on the part with the query of the status of your servers. You should stop using Write-Host. Instead you should take advantage of PowerShells uinique feature - working with rich and powerful objects instead of stupid text. ;-) I'd approach the task of querying a bunch of computers like this:

$ComputernameList = Get-Content -Path 'C:\Users\test\Desktop\hostnames.txt'

$Result =
foreach ($ComputerName in $ComputernameList) {
    [PSCustomObject]@{
        ComputerName = $ComputerName
        Online       = Test-Connection -ComputerName $ComputerName -Count 1 -Quiet
    }
}
$result

Now you have a PowerShell object you can pipe to Export-Csv for example or use it for further steps.

For example filter for the offline computers:

$result | Where-Object -Property Online -NE -Value $true

If you insist to have a visual control during the runtime of the script you may use Write-Verbose or Write-Debug. This way you can switch on the output if needed but omit it when the script runs unattended.