none
Powershell check drive RRS feed

  • Question

  • Hello; I have this script. 

    I try to test a computer with if test-connection 'true' than write the cells in excel. if not write x in the rows. 

    But I dont understand whats wrong. 

    # Welke dag is het
    $date = (get-date).AddDays(-1).ToString("yyyMMdd")
    
    # Doorgaan bij fouten
    $erroractionpreference = "SilentlyContinue"
    
    write-host-host "Start Excel on background" -Foregroundcolor "Yellow"
    # Excel openen en niet zichtbaar maken voor het oog
    $Excel = New-Object -comobject Excel.Application 
    $Excel.visible = $False
    
    $ExcelSheet = $Excel.Workbooks.Add() 
    $ExcelCell = $ExcelSheet.Worksheets.Item(1)
    
    write-host "Add data to Cells" -Foregroundcolor "Yellow"
    # Cellen vullen (1e rij)
    $ExcelCell.Cells.Item(1,1) = "Machine Name" 
    $ExcelCell.Cells.Item(1,2) = "System Status" 
    $ExcelCell.Cells.Item(1,3) = "Drive" 
    $ExcelCell.Cells.Item(1,4) = "Total size (GB)" 
    $ExcelCell.Cells.Item(1,5) = "Free Space (GB)" 
    $ExcelCell.Cells.Item(1,6) = "Free Space (%)" 
    $ExcelCell.Cells.item(1,7) = "Used Space (GB)"
    
    
    write-host "Doing some Makeup on the Cells" -Foregroundcolor "Yellow"
    # Cellen opmaken 
    $ExcelMakeup = $ExcelCell.UsedRange 
    $ExcelMakeup.Interior.ColorIndex = 46 
    $ExcelMakeup.Font.ColorIndex = 2 
    $ExcelMakeup.EntireColumn.AutoFit()
    $intRow = 2
    
    write-host "Searching for the systems to check" -Foregroundcolor "Yellow"
    # Ophalen van te controleren systemen
    $colComputers = get-content "E:\scripts\d-drive beta 2\servers.txt"
    
    write-host "Start the check for the systems in servers.txt" -Foregroundcolor "Yellow"
    foreach ($strComputer in $colComputers) 
    {
    foreach ($objdisk in $colDisks) 
    {
    
    $status = Test-Connection $strComputer -Count 1 -Quiet
    
         if (Test-Connection $strComputer -Count 1 -Quiet $true)
            {
            write-host $strComputer "online " -Foregroundcolor "Green"
    
            $colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter "DriveType ='3' "
    		
            $ExcelCell.Cells.Item($intRow, 1) = $strComputer.ToUpper() 
            $ExcelCell.Cells.Item($introw, 2) = "Online"
            $ExcelCell.Cells.Item($intRow, 3) = $objDisk.DeviceID 
            $ExcelCell.Cells.Item($intRow, 4) = "{0:N0}" -f ($objDisk.Size/1GB) 
            $ExcelCell.Cells.Item($intRow, 5) = "{0:N0}" -f ($objDisk.FreeSpace/1GB) 
            $ExcelCell.Cells.Item($intRow, 6) = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size) 
            $ExcelCell.cells.item($introw, 7) = "{0:N0}" -f ([double]$objDisk.Size/1GB - [double]$objDisk.Freespace/1GB)
            }
            
         else
            {    		
            write-host "Host offline" -Foregroundcolor "Red"
    
            $ExcelCell.Cells.Item($intRow, 1) = $strComputer.ToUpper() 
            $ExcelCell.Cells.Item($intRow, 2) = "Offline"
            $ExcelCell.Cells.Item($intRow, 3) = "x"
            $ExcelCell.Cells.Item($intRow, 4) = "x"
            $ExcelCell.Cells.Item($intRow, 5) = "x"
            $ExcelCell.Cells.Item($intRow, 6) = "x"
            $ExcelCell.cells.item($introw, 7) = "x"
            }    
            
        $intRow = $intRow + 1 
    
    }   
    }  
    
    # Excel opmaak nog een keer juist zetten. 
    $ExcelMakeup.EntireColumn.AutoFit() 
    
    write-host "Save file and exit Excel + Powershell... " -Foregroundcolor "Yellow"
    # Opslaan en afsluiten van excel. Met datum (JaarMaandDag_UurMin_D_Drive.xlsx
    $Excel.ActiveWorkbook.SaveAs("E:\scripts\d-drive beta 2\res\" + $date + "_" + (get-date -format "HHmm") + "_D_Drive.xlsx")
    $Excel.Workbooks.Close()
    $Excel.Quit()
    
    #  TEMP
    
      #  Test-Connection -ComputerName $strComputer -Count 1 -Quiet
      #  if (Test-Connection -computername $strComputer -Count 1 -Quiet true) 
      # $status = Test-Connection -computername $strComputer -Count 1 -Quiet


    • Edited by t-bl Tuesday, June 17, 2014 2:32 PM
    Tuesday, June 17, 2014 1:07 PM

All replies

  • Test this one:

    # Welke dag is het
    $date = (get-date).AddDays(-1).ToString("yyyMMdd")
    
    # Doorgaan bij fouten
    $erroractionpreference = "SilentlyContinue"
    
    write-host-host "Start Excel on background" -Foregroundcolor "Yellow"
    # Excel openen en niet zichtbaar maken voor het oog
    $Excel = New-Object -comobject Excel.Application 
    $Excel.visible = $False
    
    $ExcelSheet = $Excel.Workbooks.Add() 
    $ExcelCell = $ExcelSheet.Worksheets.Item(1)
    
    write-host "Add data to Cells" -Foregroundcolor "Yellow"
    # Cellen vullen (1e rij)
    $ExcelCell.Cells.Item(1,1) = "Machine Name" 
    $ExcelCell.Cells.Item(1,2) = "System Status" 
    $ExcelCell.Cells.Item(1,3) = "Drive" 
    $ExcelCell.Cells.Item(1,4) = "Total size (GB)" 
    $ExcelCell.Cells.Item(1,5) = "Free Space (GB)" 
    $ExcelCell.Cells.Item(1,6) = "Free Space (%)" 
    $ExcelCell.Cells.item(1,7) = "Used Space (GB)"
    
    
    write-host "Doing some Makeup on the Cells" -Foregroundcolor "Yellow"
    # Cellen opmaken 
    $ExcelMakeup = $ExcelCell.UsedRange 
    $ExcelMakeup.Interior.ColorIndex = 46 
    $ExcelMakeup.Font.ColorIndex = 2 
    $ExcelMakeup.EntireColumn.AutoFit()
    $intRow = 2
    
    write-host "Searching for the systems to check" -Foregroundcolor "Yellow"
    # Ophalen van te controleren systemen
    $colComputers = get-content "Z:\servers.txt"
    
    write-host "Start the check for the systems in servers.txt" -Foregroundcolor "Yellow"
    foreach ($strComputer in $colComputers) 
    {
        $colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter "DriveType ='3' "
        Write-Output $colDisks
        foreach ($objdisk in $colDisks) 
        {
    
    $status = Test-Connection $strComputer -Count 1 -Quiet
    
         if ($status -eq $true)
            {
            write-host $strComputer "online " -Foregroundcolor "Green"
    		
            $ExcelCell.Cells.Item($intRow, 1) = $strComputer.ToUpper() 
            $ExcelCell.Cells.Item($introw, 2) = "Online"
            $ExcelCell.Cells.Item($intRow, 3) = $objDisk.DeviceID 
            $ExcelCell.Cells.Item($intRow, 4) = "{0:N0}" -f ($objDisk.Size/1GB) 
            $ExcelCell.Cells.Item($intRow, 5) = "{0:N0}" -f ($objDisk.FreeSpace/1GB) 
            $ExcelCell.Cells.Item($intRow, 6) = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size) 
            $ExcelCell.cells.item($introw, 7) = "{0:N0}" -f ([double]$objDisk.Size/1GB - [double]$objDisk.Freespace/1GB)
            }
            
         else
            {    		
            write-host "Host offline" -Foregroundcolor "Red"
    
            $ExcelCell.Cells.Item($intRow, 1) = $strComputer.ToUpper() 
            $ExcelCell.Cells.Item($intRow, 2) = "Offline"
            $ExcelCell.Cells.Item($intRow, 3) = "x"
            $ExcelCell.Cells.Item($intRow, 4) = "x"
            $ExcelCell.Cells.Item($intRow, 5) = "x"
            $ExcelCell.Cells.Item($intRow, 6) = "x"
            $ExcelCell.cells.item($introw, 7) = "x"
            }    
            
        $intRow = $intRow + 1 
    
    }   
    }  
    
    # Excel opmaak nog een keer juist zetten. 
    $ExcelMakeup.EntireColumn.AutoFit() 
    
    write-host "Save file and exit Excel + Powershell... " -Foregroundcolor "Yellow"
    # Opslaan en afsluiten van excel. Met datum (JaarMaandDag_UurMin_D_Drive.xlsx
    $Excel.ActiveWorkbook.SaveAs("Z:\" + $date + "_" + (get-date -format "HHmm") + "_D_Drive.xlsx")
    $Excel.Workbooks.Close()
    $Excel.Quit()
    
    #  TEMP
    
      #  Test-Connection -ComputerName $strComputer -Count 1 -Quiet
      #  if (Test-Connection -computername $strComputer -Count 1 -Quiet true) 
      # $status = Test-Connection -computername $strComputer -Count 1 -Quiet


    If have moved the

    $colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter "DriveType ='3' "

    before the ForEach part which is referenzing the $colDisks. And changed your if statement to

    if ($status -eq $true)

    as you are writing the Test-Connection into the $status variable. Another solution for this would be

    if ((Test-Connection $strComputer -Count 1 -Quiet) -eq $true)

    the you can drop the Test-Connection part before the if statement.

    Thursday, June 19, 2014 3:32 PM