mail@pastecode.io avatar
21 days ago
2.6 kB
# Import necessary module and define file paths and variables
# on your csv make the columns name as service,ip,port,sheet,column (this is used to match the exact column on the excel sheet ( for example in the csv I have the fqdn of the server/website but on the excel sheet I have just the coloquial name and I put that name on the "column" column
#on my Workbook I have two sheets for 2 sites. modify as needed. 
Import-Module ImportExcel
$excelFilePath = "C:\Path\To\Your\File.xlsx"
$currentDate = Get-Date -Format "MM/dd/yy"
$currentTime = Get-Date -Format "hh:mm tt"
$administrator = "YourAdminName"
$note = "dailycheck.ps1"
$existingDataSheet1 = Import-Excel -Path $excelFilePath -WorksheetName "LOG-Sheet1"
$existingDataSheet2 = Import-Excel -Path $excelFilePath -WorksheetName "LOG-Sheet2"
$services = Import-Csv -Path "C:\Path\To\Your\Services.csv"

# Iterate through each service and check its status (online or offline)
foreach ($serviceinfo in $services) {
    if ($($serviceinfo.port -eq "ICMP")) {
        $online = Test-Connection $($serviceinfo.ip) -Quiet -Count 1
    } else {
        $online = Test-NetConnection $($serviceinfo.ip) -InformationLevel Quiet -Port $($serviceinfo.port)
    # Add a new property "online" to the service with the check result
    $serviceinfo | Add-Member -MemberType NoteProperty -Name "online" -Value $online

# Define new rows for Sheet1 and Sheet2
$NewRowSheet1 = @{
    "Date" = $currentDate
    "Time" = $currentTime
    "Administrator" = $administrator
    "Notes" = $note

$NewRowSheet2 = @{
    "Date" = $currentDate
    "Time" = $currentTime
    "Administrator" = $administrator
    "Notes" = $note

# Add service statuses to the new rows
foreach ($service in $services) {
    if ($Null -ne $($services.column)) {
        if ($service.sheet -eq "LOG-Sheet1") {
            $NewRowSheet1[$($service.column)] = $($service.online)
        } elseif ($service.sheet -eq "LOG-Sheet2") {
            $NewRowSheet2[$($service.column)] = $($service.online)

# Update the existing data sheets with the new rows
$combinedDataSheet1 = $existingDataSheet1 + @($NewRowSheet1)
$combinedDataSheet2 = $existingDataSheet2 + @($NewRowSheet2)

# Export the updated data back to the Excel sheet
$combinedDataSheet1 | Export-Excel -Path $excelFilePath -WorksheetName "LOG-Sheet1" 
$combinedDataSheet2 | Export-Excel -Path $excelFilePath -WorksheetName "LOG-Sheet2"

# Display the services table in the console
Write-Host ($services | Format-Table | Out-String)

# Open the Excel sheet for verification
explorer.exe $excelFilePath
Read-Host -Prompt "Please press ENTER to exit"
Leave a Comment