Hi Experts,
I am facing a problem in running a powershell script which I am using for exporting basic inventories like server IP address, Operating system, Last boot time, Last patch time, Hyper-v server on which server is hosted (By getting registry Value), Status
of service (Stopped OR Running).
I am using a txt file for input and exporting results in a Excel sheet. Script is working as expected but it gets stuck after processing some servers like (30, 35, 50) and it stuck and data does not grow in excel sheet.
In troubleshooting I tried below things.
1. Tried to change the servers on which I am running the script.
2. Upgraded Powershell version to 5.0.
3. I found somewhere to create below registry keys in order to resolve this problem but same issue.
#Enable Excel macros in registry.
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\15.0\excel\Security" -Name AccessVBOM -PropertyType DWORD -Value 1 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\15.0\excel\Security" -Name VBAWarnings -PropertyType DWORD -Value 1 -Force | Out-Null
I will appreciate your help if someone has the solution.
Below are the codes of Script which I am running through ISE.
#### Spreadsheet Location and email sending details
$DirectoryToSaveTo = “C:\Scripts\Again”
$date=Get-Date -format “yyyy-MM-d”
$Filename=”serverinfo-$($date)”
$FromEmail=”xxxxxxxxx”
$ToEmail=”xxxxxxxx”
$SMTPMail=”xxxxxxxx”
###InputLocation
$Computers = Get-Content “C:\Scripts\again\servers.txt”
$DirectoryToSaveTo = "C:\Scripts\New"
$date=Get-Date -format "yyyy-MM-d"
$Filename="serverinfo-$($date)"
$FromEmail="xxxxxxxx"
$ToEmail="xxxxxxx"
$SMTPMail="xxxxxxxx"
$Computers = Get-Content "C:\Scripts\again\servers.txt"
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
{
New-Item "$DirectoryToSaveTo" -type directory | out-null
}
#Enable Excel macros in registry.
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\15.0\excel\Security" -Name AccessVBOM -PropertyType DWORD -Value 1 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\15.0\excel\Security" -Name VBAWarnings -PropertyType DWORD -Value 1 -Force | Out-Null
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$sheet.Name = 'Server Inventory'
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$column)= 'Server Inventory'
$range = $Sheet.Range("a1","s2")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
$range.Style = 'Title'
$row++;$row++
$intRow = $row
$xlOpenXMLWorkbook=[int]51
$Sheet.Cells.Item($intRow,1) ="Name"
$Sheet.Cells.Item($intRow,2) ="IP Address"
$Sheet.Cells.Item($intRow,3) ="Hyper-V Host"
$Sheet.Cells.Item($intRow,4) ="Total Physical Memory"
$Sheet.Cells.Item($intRow,5) ="OS"
$Sheet.Cells.Item($intRow,6) ="Windows Update"
$Sheet.Cells.Item($intRow,7) ="Last Patch"
$Sheet.Cells.Item($intRow,8) ="Symantec AV"
$Sheet.Cells.Item($intRow,9) ="Deep Security"
$Sheet.Cells.Item($intRow,10) ="Alert Logic"
$Sheet.Cells.Item($intRow,11) ="Last Boot Time"
$Sheet.Cells.Item($intRow,12) ="Remote Registry"
$Sheet.Cells.Item($intRow,13) ="BITS"
$Sheet.Cells.Item($intRow,14) ="status"
for ($col = 1; $col –le 19; $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
$Bios = Get-WmiObject -Class Win32_BIOS -ComputerName $Computer
$sheetS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Computer
$sheetPU = Get-WmiObject -Class Win32_Processor -ComputerName $Computer
$WinUpdate = Get-WmiObject -Class Win32_Service -ComputerName $Computer -filter "Name='wuauserv'"
$AV = Get-WmiObject -Class Win32_Service -ComputerName $Computer -filter "Name='SepMasterService'"
$DS = Get-WmiObject -Class Win32_Service -ComputerName $Computer -filter "Name='ds_notifier'"
$AL = Get-WmiObject -Class Win32_Service -ComputerName $Computer -filter "Name='al_agent'"
$RR = Get-WmiObject -Class Win32_Service -ComputerName $Computer -filter "Name='RemoteRegistry'"
$BITS = Get-WmiObject -Class Win32_Service -ComputerName $Computer -filter "Name='BITS'"
$drives = Get-WmiObject -ComputerName $Computer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $Computer | ? {$_.IPEnabled}).ipaddress
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name
$OSRunning = $OS.caption + " " + $OS.OSArchitecture + " SP " + $OS.ServicePackMajorVersion
$NoOfProcessors=$sheetS.numberofProcessors
$name=$SheetPU|select name -First 1
$Manufacturer=$sheetS.Manufacturer
$Model=$sheetS.Model
$systemType=$sheetS.SystemType
$ProcessorName=$SheetPU|select name -First 1
$DomainRole = $sheetS.DomainRole
$TotalAvailMemory = $OS.totalvisiblememorysize/1kb
$TotalVirtualMemory = $OS.totalvirtualmemorysize/1kb
$TotalFreeMemory = $OS.FreePhysicalMemory/1kb
$TotalFreeVirtualMemory = $OS.FreeVirtualMemory/1kb
$TotalMem = "{0:N2}" -f $TotalAvailMemory
$TotalVirt = "{0:N2}" -f $TotalVirtualMemory
$FreeMem = "{0:N2}" -f $TotalFreeMemory
$FreeVirtMem = "{0:N2}" -f $TotalFreeVirtualMemory
$date = Get-Date
$uptime = $OS.ConvertToDateTime($OS.lastbootuptime)
$BiosVersion = $Bios.Manufacturer + " " + $Bios.SMBIOSBIOSVERSION + " " + $Bios.ConvertToDateTime($Bios.Releasedate)
$sheetPUInfo = $name.Name + " & has " + $sheetPU.NumberOfCores + " Cores & the FSB is " + $sheetPU.ExtClock + " Mhz"
$sheetPULOAD = $sheetPU.LoadPercentage
$HVHost = Invoke-command -computer $computer {(Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Virtual Machine\Guest\Parameters").PhysicalHostNameFullyQualified}
$LastPatchTime = Invoke-command -computer $computer {(Get-ItemProperty -Path "HKLM:\Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install").LastSuccessTime}
$WindowsUpdate = $WinUpdate.state
$AVStatus = $AV.state
$DSStatus = $DS.state
$ALStatus = $AL.state
$RRStatus = $RR.state
$BITSStatus = $BITS.state
if($pingStatus.StatusCode -eq 0)
{
$Status = GetStatusCode( $pingStatus.StatusCode )
}
else
{
$Status = GetStatusCode( $pingStatus.StatusCode )
}
if (($DomainRole -eq "0") -or ($DomainRole -eq "1"))
{
$Role = "Work Station"
}
elseif (($DomainRole -eq "2") -or ($DomainRole -eq "3"))
{
$Role = "Member Server"
}
elseif (($DomainRole -eq "4") -or ($DomainRole -eq "5"))
{
$Role = "Domain Controller"
}
else
{
$Role = "Unknown"
}
}
CATCH
{
$pcnotfound = "true"
}
#### Pump Data to Excel
if ($pcnotfound -eq "true")
{
$sheet.Cells.Item($intRow, 1) = "$($computer) Not Found "
}
else
{
$sheet.Cells.Item($intRow, 1) = $computer
$sheet.Cells.Item($intRow, 2)=$IPAddress
$sheet.Cells.Item($intRow, 3)=$HVHost
$sheet.Cells.Item($intRow, 4)= "$TotalMem MB"
$sheet.Cells.Item($intRow, 5) = $OSRunning
$sheet.Cells.Item($intRow, 6)= $WindowsUpdate
$Sheet.Cells.Item($intRow, 7) = $LastPatchTime
$Sheet.Cells.Item($intRow, 8) = $AVStatus
$Sheet.Cells.Item($intRow, 9) = $DSStatus
$sheet.Cells.Item($intRow, 10) = $ALStatus
$sheet.Cells.Item($intRow, 11)= $uptime
$sheet.Cells.Item($intRow, 12)= $RRStatus
$sheet.Cells.Item($intRow, 13) = $BITSStatus
$sheet.Cells.Item($intRow, 14) = $status
}
$intRow = $intRow + 1
$pcnotfound = "false"
}
#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
$initalRow = $row
#Auto fit everything so it looks better
$usedRange = $Sheet.UsedRange
$usedRange.EntireColumn.AutoFit() | Out-Null
$sheet = $excel.Worksheets.Item(2)
$row++;$row++
$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()
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)
{
#initate message
$email = New-Object System.Net.Mail.MailMessage
$email.From = $emailFrom
$email.To.Add($emailTo)
$email.Subject = $subject
$email.Body = $body
# initiate email attachment
$emailAttach = New-Object System.Net.Mail.Attachment $filePath
$email.Attachments.Add($emailAttach)
#initiate sending email
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($email)
}
#Call Function
$message = @"
Hi Team,
The Discovery of Windows Server and Disk Space information for all the listed instances.
Autogenerated Email!!! Please do not reply.
Thank you,
XYZ.com
"@
$date=get-date
sendEmail -emailFrom $fromEmail -emailTo $ToEmail -subject "Windows Server Inventory & Disk Details -$($date)" -body $message -smtpServer $SMTPMail -filePath $filename
