none
Powershell Script for inventory getting stuck after completion few Servers RRS feed

  • General discussion

  • 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 


    • Edited by Sonu Khatri1 Wednesday, November 15, 2017 1:23 PM
    • Changed type Bill_Stewart Thursday, January 25, 2018 10:28 PM
    • Moved by Bill_Stewart Thursday, January 25, 2018 10:28 PM This is not "fix/debug/rewrite my script for me" forum
    Wednesday, November 15, 2017 1:09 PM

All replies

  • Please read the following first:

    This forum is for scripting questions rather than script requests


    -- Bill Stewart [Bill_Stewart]

    Wednesday, November 15, 2017 2:59 PM
  • You have failed to show the error message. 

    We cannot debug your whole script.  You have to ask a specific question and post specific error messages.

    "Getting stuck" is not an error and is not helpful.  Please also note that Excel cannot be run under task schedule.


    \_(ツ)_/


    • Edited by jrv Wednesday, November 15, 2017 3:43 PM
    Wednesday, November 15, 2017 3:41 PM
  • Hi Bill,

    Sorry for that if I raised my concern at inappropriate forum.

    Actually I am new in this Forum and it was my first concern.

    Thursday, November 16, 2017 8:33 AM
  • Thanks Sir for your reply.

    Actually I am new in powershell and I got this script from a website.

    I modified this according to my requirement like What OS, RAM, Last Boot time, What is Hyper-V host of VM, Windows Update Service Status.

    If possible, could you please help me how can I make inventory on my servers.

    Thursday, November 16, 2017 8:40 AM
  • New to PowerShell means you need to learn PowerShell.  Your script is very badly designed.  Whoever wrote it was not a very good coder.

    You are making far too many calls to WMI.  Why would you return the same class multiple time.  That will make the script very slow.

    If WMI is broken  on any server it will hang your script.

    Start by learning PowerShell and how to write PS scripts.  Next learn how WMI works. 

    Run the following in a loop to test WMI.

    $Computers = Get-Content d:\Scripts\again\servers.txt
    foreach ($Computer in $Computers) { Write-Host Begin Computer $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 Write-Host End Computer }
    This will help find any bad computers.


    \_(ツ)_/

    Thursday, November 16, 2017 8:54 AM