Check Windows pacth to Excel
The result script:
I read 2 increible post to search/control patch in our hosts. At step 3 I have a mix with my result. This script run only with the hosts that are power on.
The post are 3 parts, one part with to "Powershell script to query a particular patch is installed on remote computers" and other to check the " Microsoft Security Bulletin MS17-010" -> protect of wanncry virus. The part 3 have my version, witch some little modication, and part 4 step to execute.
[1] Title: "Powershell script to query a particular patch is installed on remote computers"
[1.1] Link
[1.1] Code
#### Spreadsheet Location
$DirectoryToSaveTo = "c:\"
$date=Get-Date -format "yyyy-MM-d"
$Filename="Patchinfo-$($patch)"
###InputLocation
$Computers = Get-Content "c:\computers.txt"
# Enter KB to be checked here
$Patch = Read-Host 'Enter the KB number ? - eg: KB3011780 '
# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
{
New-Item "$DirectoryToSaveTo" -type directory | out-null
}
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$sheet.Name = 'Patch status - '
#Create a Title for the first worksheet
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$column)= 'Patch status'
$range = $Sheet.Range("a1","f2")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
#Give it a nice Style so it stands out
$range.Style = 'Title'
#Increment row for next set of data
$row++;$row++
#Save the initial row so it can be used later to create a border
#Counter variable for rows
$intRow = $row
$xlOpenXMLWorkbook=[int]51
#Read thru the contents of the Servers.txt file
$Sheet.Cells.Item($intRow,1) ="Name"
$Sheet.Cells.Item($intRow,2) ="status"
$Sheet.Cells.Item($intRow,3) ="Patch status"
$Sheet.Cells.Item($intRow,4) ="OS"
$Sheet.Cells.Item($intRow,5) ="SystemType"
$Sheet.Cells.Item($intRow,6) ="Last Boot Time"
for ($col = 1; $col –le 6; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow++
Function GetStatusCode
{
Param([int] $StatusCode)
switch($StatusCode)
{
0 {"Success"}
11001 {"Buffer Too Small"}
11002 {"Destination Net Unreachable"}
11003 {"Destination Host Unreachable"}
11004 {"Destination Protocol Unreachable"}
11005 {"Destination Port Unreachable"}
11006 {"No Resources"}
11007 {"Bad Option"}
11008 {"Hardware Error"}
11009 {"Packet Too Big"}
11010 {"Request Timed Out"}
11011 {"Bad Request"}
11012 {"Bad Route"}
11013 {"TimeToLive Expired Transit"}
11014 {"TimeToLive Expired Reassembly"}
11015 {"Parameter Problem"}
11016 {"Source Quench"}
11017 {"Option Too Big"}
11018 {"Bad Destination"}
11032 {"Negotiating IPSEC"}
11050 {"General Failure"}
default {"Failed"}
}
}
Function GetUpTime
{
param([string] $LastBootTime)
$Uptime = (Get-Date) - [System.Management.ManagementDateTimeconverter]::ToDateTime($LastBootTime)
"Days: $($Uptime.Days); Hours: $($Uptime.Hours); Minutes: $($Uptime.Minutes); Seconds: $($Uptime.Seconds)"
}
foreach ($Computer in $Computers)
{
TRY {
$OS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Computer
$sheetS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Computer
$sheetPU = Get-WmiObject -Class Win32_Processor -ComputerName $Computer
$drives = Get-WmiObject -ComputerName $Computer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
$pingStatus = Get-WmiObject -Query "Select * from win32_PingStatus where Address='$Computer'"
$OSRunning = $OS.caption + " " + $OS.OSArchitecture + " SP " + $OS.ServicePackMajorVersion
$systemType=$sheetS.SystemType
$date = Get-Date
$uptime = $OS.ConvertToDateTime($OS.lastbootuptime)
if
($kb=get-hotfix -id $Patch -ComputerName $computer -ErrorAction 2)
{
$kbinstall="$patch is installed"
}
else
{
$kbinstall="$patch is not installed"
}
if($pingStatus.StatusCode -eq 0)
{
$Status = GetStatusCode( $pingStatus.StatusCode )
}
else
{
$Status = GetStatusCode( $pingStatus.StatusCode )
}
}
CATCH
{
$pcnotfound = "true"
}
#### Pump Data to Excel
if ($pcnotfound -eq "true")
{
#$sheet.Cells.Item($intRow, 1) = "PC Not Found"
$sheet.Cells.Item($intRow, 1) = $computer
$sheet.Cells.Item($intRow, 2) = "PC Not Found"
}
else
{
$sheet.Cells.Item($intRow, 1) = $computer
$sheet.Cells.Item($intRow, 2) = $status
$Sheet.Cells.Item($intRow, 3) = $kbinstall
$sheet.Cells.Item($intRow, 4) = $OSRunning
$Sheet.Cells.Item($intRow, 5) = $SystemType
$sheet.Cells.Item($intRow, 6) = $uptime
}
$intRow = $intRow + 1
$pcnotfound = "false"
}
$erroractionpreference = “SilentlyContinue”
$Sheet.UsedRange.EntireColumn.AutoFit()
########################################333
##############################################################
$filename = "$DirectoryToSaveTo$filename.xlsx"
#if (test-path $filename ) { rm $filename } #delete the file if it already exists
$Sheet.UsedRange.EntireColumn.AutoFit()
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$Excel.Saved = $True
$Excel.Close()
$Excel.DisplayAlerts = $False
$Excel.quit()
[2] Title: "Como detectar si tu Windows server están protegidos frente Wannacry"
[2.1] Link
[2.2] Code
#### Spreadsheet Location
$DirectoryToSaveTo = “c:\temp\”
$date=Get-Date -format “yyyy-MM-d”
$Filename=”WannaCry_Servers_Patches_status_report”
###InputLocation
$Computers = Get-Content “c:\temp\computers.txt”
# Enter KB to be checked here
$Patchw2k8R2 = “KB4012212”
$Patchw2k8R2MRMAR = “KB4012215”
$Patchw2k8R2MRAPR = “KB4015549”
$Patchw2k8R2MRMAY = “KB4019264”
$Patchw2k12R2 = “KB4012213”
$Patchw2k12R2MRMAR = “KB4012216”
$Patchw2k12R2MRAPR = “KB4015550”
$Patchw2k12R2MRMAY = “KB4019215”
$Patchw2k8 = “KB4012598”
$Patchw2k12 = “KB4012214”
$Patchw2k12MRMAR = “KB4012217”
$Patchw2k12MRAPR = “KB4011551”
$Patchw2k12MRMAY = “KB4019216”
$Patchw2k3 = “KB4012598”
# before we do anything else, are we likely to be able to save the file?
# if the directory doesn’t exist, then create it
if (!(Test-Path -path “$DirectoryToSaveTo”)) #create it if not existing
{
New-Item “$DirectoryToSaveTo” -type directory | out-null
}
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$sheet.Name = ‘Patch status – ‘
#Create a Title for the first worksheet
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$column)= ‘Patch status’
$range = $Sheet.Range(“a1″,”f2″)
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
#Give it a nice Style so it stands out
$range.Style = ‘Title’
#Increment row for next set of data
$row++;$row++
#Save the initial row so it can be used later to create a border
#Counter variable for rows
$intRow = $row
$xlOpenXMLWorkbook=[int]51
#Read thru the contents of the Servers.txt file
$Sheet.Cells.Item($intRow,1) =”Name”
$Sheet.Cells.Item($intRow,2) =”status”
$Sheet.Cells.Item($intRow,3) =”Patch status”
$Sheet.Cells.Item($intRow,4) =”OS”
$Sheet.Cells.Item($intRow,5) =”SystemType”
$Sheet.Cells.Item($intRow,6) =”Last Boot Time”
for ($col = 1; $col –le 6; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow++
Function GetStatusCode
{
Param([int] $StatusCode)
switch($StatusCode)
{
0 {“Success”}
11001 {“Buffer Too Small”}
11002 {“Destination Net Unreachable”}
11003 {“Destination Host Unreachable”}
11004 {“Destination Protocol Unreachable”}
11005 {“Destination Port Unreachable”}
11006 {“No Resources”}
11007 {“Bad Option”}
11008 {“Hardware Error”}
11009 {“Packet Too Big”}
11010 {“Request Timed Out”}
11011 {“Bad Request”}
11012 {“Bad Route”}
11013 {“TimeToLive Expired Transit”}
11014 {“TimeToLive Expired Reassembly”}
11015 {“Parameter Problem”}
11016 {“Source Quench”}
11017 {“Option Too Big”}
11018 {“Bad Destination”}
11032 {“Negotiating IPSEC”}
11050 {“General Failure”}
default {“Failed”}
}
}
Function GetUpTime
{
param([string] $LastBootTime)
$Uptime = (Get-Date) – [System.Management.ManagementDateTimeconverter]::ToDateTime($LastBootTime)
“Days: $($Uptime.Days); Hours: $($Uptime.Hours); Minutes: $($Uptime.Minutes); Seconds: $($Uptime.Seconds)”
}
foreach ($Computer in $Computers)
{
TRY {
$OS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Computer -ErrorAction SilentlyContinue
$sheetS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Computer -ErrorAction SilentlyContinue
$sheetPU = Get-WmiObject -Class Win32_Processor -ComputerName $Computer -ErrorAction SilentlyContinue
$drives = Get-WmiObject -ComputerName $Computer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} -ErrorAction SilentlyContinue
$pingStatus = Get-WmiObject -Query “Select * from win32_PingStatus where Address=’$Computer'”
$OSRunning = $OS.caption + ” ” + $OS.OSArchitecture + ” SP ” + $OS.ServicePackMajorVersion
$systemType=$sheetS.SystemType
$date = Get-Date
$uptime = $OS.ConvertToDateTime($OS.lastbootuptime)
#Check patch for windows server 2008 R2
if ($OSRunning -like “*2008 R2*”)
{
if
($kb=get-hotfix -id $Patchw2k8r2 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k8r2 is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k8r2MRMAR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k8r2MRMAR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k8r2MRAPR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k8r2MRAPR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k8r2MRMAY -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k8r2MRMAY is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k8r2 , $Patchw2k8r2MRMAR , $Patchw2k8r2MRAPR or $Patchw2k8r2MRMAY ”
}
}
#Check patch for windows server 2012 R2
elseif($OSRunning -like “*2012 R2*”)
{
if
($kb=get-hotfix -id $Patchw2k12r2 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k12r2 is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12r2MRMAR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12r2MRMAR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12r2MRAPR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12r2MRAPR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12r2MRMAY -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12r2MRMAY is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k12r2 , $Patchw2k12r2MRMAR , $Patchw2k12r2MRAPR or $Patchw2k12r2MRMAY ”
}
}
#Check patch for windows server 2012
elseif($OSRunning -like “*2012*” -and $OSRunning -notlike ‘*R2*’ )
{
if
($kb=get-hotfix -id $Patchw2k12 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k12 is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12MRMAR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12MRMAR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12MRAPR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12MRAPR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12MRMAY -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12MRMAY is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k12 , $Patchw2k12MRMAR , $Patchw2k12MRAPR or $Patchw2k12MRMAY ”
}
}
#Check patch for windows server 2008
elseif($OSRunning -like “*2008*” -and $OSRunning -notlike ‘*R2*’ )
{
if
($kb=get-hotfix -id $Patchw2k8 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k8 is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k8″
}
}
#Check patch for windows server 2003
elseif($OSRunning -like “*2003*”)
{
if
($kb=get-hotfix -id $Patchw2k3 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k3 is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k3″
}
}
else
{
$kbinstall=”Operating system not covered for this script”
}
[3] I mixed the 2 post with some change, the result is:
#### Spreadsheet Location
$DirectoryToSaveTo = "c:\temp"
$date=Get-Date -format "yyyy-MM-d"
$Filename="Patchinfo-$($patch)"
###InputLocation
$Computers = Get-Content "c:\temp\computers.txt"
# Enter KB to be checked here
#GON $Patch = Read-Host 'Enter the KB number ? - eg: KB3011780 '
# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
{
New-Item "$DirectoryToSaveTo" -type directory | out-null
}
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$sheet.Name = 'Patch status - '
#Create a Title for the first worksheet
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$column)= 'Patch status'
$range = $Sheet.Range("a1","f2")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
#Give it a nice Style so it stands out
#$range.Style = 'Title'
#Increment row for next set of data
$row++;$row++
#Save the initial row so it can be used later to create a border
#Counter variable for rows
$intRow = $row
$xlOpenXMLWorkbook=[int]51
#Read thru the contents of the Servers.txt file
$Sheet.Cells.Item($intRow,1) ="Name"
$Sheet.Cells.Item($intRow,2) ="status"
$Sheet.Cells.Item($intRow,3) ="Patch status"
$Sheet.Cells.Item($intRow,4) ="OS"
$Sheet.Cells.Item($intRow,5) ="SystemType"
$Sheet.Cells.Item($intRow,6) ="Last Boot Time"
$Sheet.Cells.Item($intRow,7) ="Ip"
for ($col = 1; $col –le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow++
Function GetStatusCode
{
Param([int] $StatusCode)
switch($StatusCode)
{
0 {"Success"}
11001 {"Buffer Too Small"}
11002 {"Destination Net Unreachable"}
11003 {"Destination Host Unreachable"}
11004 {"Destination Protocol Unreachable"}
11005 {"Destination Port Unreachable"}
11006 {"No Resources"}
11007 {"Bad Option"}
11008 {"Hardware Error"}
11009 {"Packet Too Big"}
11010 {"Request Timed Out"}
11011 {"Bad Request"}
11012 {"Bad Route"}
11013 {"TimeToLive Expired Transit"}
11014 {"TimeToLive Expired Reassembly"}
11015 {"Parameter Problem"}
11016 {"Source Quench"}
11017 {"Option Too Big"}
11018 {"Bad Destination"}
11032 {"Negotiating IPSEC"}
11050 {"General Failure"}
default {"Failed"}
}
}
Function GetUpTime
{
param([string] $LastBootTime)
$Uptime = (Get-Date) - [System.Management.ManagementDateTimeconverter]::ToDateTime($LastBootTime)
"Days: $($Uptime.Days); Hours: $($Uptime.Hours); Minutes: $($Uptime.Minutes); Seconds: $($Uptime.Seconds)"
}
foreach ($Computer in $Computers)
{
TRY {
$OS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Computer
$sheetS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Computer
$sheetPU = Get-WmiObject -Class Win32_Processor -ComputerName $Computer
$drives = Get-WmiObject -ComputerName $Computer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
$pingStatus = Get-WmiObject -Query "Select * from win32_PingStatus where Address='$Computer'"
$OSRunning = $OS.caption + " " + $OS.OSArchitecture + " SP " + $OS.ServicePackMajorVersion
$systemType=$sheetS.SystemType
$date = Get-Date
$uptime = $OS.ConvertToDateTime($OS.lastbootuptime)
$iphost =Test-Connection -ComputerName $Computer -Count 1 | Select -ExpandProperty IPV4Address
# *********************************************************************************
# *********************************************************************************
# *********************************************************************************
# *********************************************************************************
# Enter KB to be checked here
$Patchw2k8R2 = “KB4012212”
$Patchw2k8R2MRMAR = “KB4012215”
$Patchw2k8R2MRAPR = “KB4015549”
$Patchw2k8R2MRMAY = “KB4019264”
$Patchw2k12R2 = “KB4012213”
$Patchw2k12R2MRMAR = “KB4012216”
$Patchw2k12R2MRAPR = “KB4015550”
$Patchw2k12R2MRMAY = “KB4019215”
$Patchw2k8 = “KB4012598”
$Patchw2k12 = “KB4012214”
$Patchw2k12MRMAR = “KB4012217”
$Patchw2k12MRAPR = “KB4011551”
$Patchw2k12MRMAY = “KB4019216”
$Patchw2k3 = “KB4012598”
#Check patch for windows server 2008 R2
if ($OSRunning -like “*2008 R2*”)
{
if
($kb=get-hotfix -id $Patchw2k8r2 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k8r2 is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k8r2MRMAR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k8r2MRMAR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k8r2MRAPR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k8r2MRAPR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k8r2MRMAY -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k8r2MRMAY is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k8r2 , $Patchw2k8r2MRMAR , $Patchw2k8r2MRAPR or $Patchw2k8r2MRMAY ”
}
}
#Check patch for windows server 2012 R2
elseif($OSRunning -like “*2012 R2*”)
{
if
($kb=get-hotfix -id $Patchw2k12r2 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k12r2 is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12r2MRMAR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12r2MRMAR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12r2MRAPR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12r2MRAPR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12r2MRMAY -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12r2MRMAY is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k12r2 , $Patchw2k12r2MRMAR , $Patchw2k12r2MRAPR or $Patchw2k12r2MRMAY ”
}
}
#Check patch for windows server 2012
elseif($OSRunning -like “*2012*” -and $OSRunning -notlike ‘*R2*’ )
{
if
($kb=get-hotfix -id $Patchw2k12 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k12 is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12MRMAR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12MRMAR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12MRAPR -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12MRAPR is installed”
}
elseif ($kb=get-hotfix -id $Patchw2k12MRMAY -ComputerName $computer -ErrorAction SilentlyContinue)
{
$kbinstall=”$Patchw2k12MRMAY is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k12 , $Patchw2k12MRMAR , $Patchw2k12MRAPR or $Patchw2k12MRMAY ”
}
}
#Check patch for windows server 2008
elseif($OSRunning -like “*2008*” -and $OSRunning -notlike ‘*R2*’ )
{
if
($kb=get-hotfix -id $Patchw2k8 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k8 is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k8”
}
}
#Check patch for windows server 2003
elseif($OSRunning -like “*2003*”)
{
if
($kb=get-hotfix -id $Patchw2k3 -ComputerName $computer -ErrorAction SilentlyContinue )
{
$kbinstall=”$Patchw2k3 is installed”
}
else
{
$kbinstall=”Not installed $Patchw2k3"
}
}
else
{
$kbinstall="Operating system not covered for this script"
}
# *********************************************************************************
# *********************************************************************************
# *********************************************************************************
# *********************************************************************************
if($pingStatus.StatusCode -eq 0)
{
$Status = GetStatusCode( $pingStatus.StatusCode )
}
else
{
$Status = GetStatusCode( $pingStatus.StatusCode )
}
}
CATCH
{
$pcnotfound = "true"
}
#### Pump Data to Excel
if ($pcnotfound -eq "true")
{
#$sheet.Cells.Item($intRow, 1) = "PC Not Found"
$sheet.Cells.Item($intRow, 1) = $computer
$sheet.Cells.Item($intRow, 2) = "PC Not Found"
}
else
{
$sheet.Cells.Item($intRow, 1) = $computer
$sheet.Cells.Item($intRow, 2) = $status
$Sheet.Cells.Item($intRow, 3) = $kbinstall
$sheet.Cells.Item($intRow, 4) = $OSRunning
$Sheet.Cells.Item($intRow, 5) = $SystemType
$sheet.Cells.Item($intRow, 6) = $uptime
$sheet.Cells.Item($intRow, 7) = $iphost.IPAddressToString
}
$intRow = $intRow + 1
$pcnotfound = "false"
}
$erroractionpreference = “SilentlyContinue”
$Sheet.UsedRange.EntireColumn.AutoFit()
########################################333
##############################################################
$filename = "$DirectoryToSaveTo$filename.xlsx"
#if (test-path $filename ) { rm $filename } #delete the file if it already exists
$Sheet.UsedRange.EntireColumn.AutoFit()
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$Excel.Saved = $True
$Excel.Close()
$Excel.DisplayAlerts = $False
$Excel.quit()
[4] How to execute
[4.1] Open a Windows PowerShell with one user with administrative priveleges for the host
[4.2] Create one file with the host list to verify.
In the code view or modify:
The hosts file:
[4.3] Execute!
When execute you can view some error, not problem is normal, the result will be OK!
You can view in real time how to one excel is performing. When the script is near to finish, the excel need known if you want save or not save the document.
The result is the first screenshoot.
The result is good, but there are somethings to modify ;-)-. If the hosts list is long it's possible that the result need a long time.
by GoN | Published: May 17, 2017 | Last Updated: May 18, 2017
