PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL

This script is used to discover all the server information and captures disk drive utilization details. The drive details are aggregated based on the status of free space. The aggregated data is pictorially represented using pie chart. The excel sheet is then send to intended users.

The script requires input file where all the servers are listed. You can also populate the server list by quering AD. The script will try to communicate all the servers which are listed in the input file to get the required information

Script Server Inventory on sheet1 and Disk Information on sheet2
Directory It checks for the directory C:\Scripts\ and create a one if it doesn’t exists.
Source File GetAllOsDrSrvInfo.PS1
Input File E:\Scripts\InputServer.txt
Output Excel sheet as an attachment
Email Notification Yes
Recipients More than one recipients can be separated by comma
SMTPMail SMTP address
Functionality Discovery all the server information and consolidate the data  in a excel sheet

Change the first few lines of colored code as per your requirement and change the alert threshold in the bottom of the code

#### Spreadsheet Location and email sending details

 $DirectoryToSaveTo = “c:\”

 $date=Get-Date -format “yyyy-MM-d”

 $Filename=”serverinfo-$($date)”

 $FromEmail=”<ToEmail>”

 $ToEmail=”<FromEmail>”

 $SMTPMail=”<SMTP MAIL>”

 ###InputLocation

 $Computers = Get-Content “c:\server.txt”

###################

The code is tested on Powershell 2.0 onwards

Download the code : – OS and DiskInfo

 Code:-

  
#### Spreadsheet Location 
 $DirectoryToSaveTo = "c:\" 
 $date=Get-Date -format "yyyy-MM-d" 
 $Filename="serverinfo-$($date)" 
 $FromEmail="<ToEmail>" 
 $ToEmail="<FromEmail>" 
 $SMTPMail="<SMTP MAIL>" 
  
 ###InputLocation 
 $Computers = Get-Content "c:\server.txt" 
  
  
# 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 = 'Server Inventory' 
#Create a Title for the first worksheet 
$row = 1 
$Column = 1 
$Sheet.Cells.Item($row,$column)= 'Server Inventory' 
 
$range = $Sheet.Range("a1","s2"$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 SQL_Servers.txt file 
 
$Sheet.Cells.Item($intRow,1)  ="Name" 
$Sheet.Cells.Item($intRow,2)  ="status" 
$Sheet.Cells.Item($intRow,3)  ="OS" 
$Sheet.Cells.Item($intRow,4)  ="Domain Role" 
$Sheet.Cells.Item($intRow,5)  ="ProcessorName" 
$Sheet.Cells.Item($intRow,6)  ="Manufacturer" 
$Sheet.Cells.Item($intRow,7)  ="Model" 
$Sheet.Cells.Item($intRow,8)  ="SystemType" 
$Sheet.Cells.Item($intRow,9)  ="Last Boot Time" 
$Sheet.Cells.Item($intRow,10) ="Bios Version" 
$Sheet.Cells.Item($intRow,11) ="CPU Info" 
$Sheet.Cells.Item($intRow,12) ="NoOfProcessors" 
$Sheet.Cells.Item($intRow,13) ="Total Physical Memory" 
$Sheet.Cells.Item($intRow,14) ="Total Free Physical Memory" 
$Sheet.Cells.Item($intRow,15) ="Total Virtual Memory" 
$Sheet.Cells.Item($intRow,16) ="Total Free Virtual Memory" 
$Sheet.Cells.Item($intRow,17) ="Disk Info" 
$Sheet.Cells.Item($intRow,18) ="FQDN" 
$Sheet.Cells.Item($intRow,19) ="IPAddress" 
 
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 
 $drives = Get-WmiObject -ComputerName $Computer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} 
 $pingStatus = Get-WmiObject -Query "Select * from win32_PingStatus where Address='$Computer'" 
 $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}" -$TotalAvailMemory 
 $TotalVirt = "{0:N2}" -$TotalVirtualMemory 
 $FreeMem = "{0:N2}" -$TotalFreeMemory 
 $FreeVirtMem = "{0:N2}" -$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 
  
 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) = $status 
 $sheet.Cells.Item($intRow, 3) = $OSRunning 
 $sheet.Cells.Item($intRow, 4) = $Role 
 $sheet.Cells.Item($intRow, 5) = $name.name 
 $Sheet.Cells.Item($intRow, 6) = $Manufacturer 
 $Sheet.Cells.Item($intRow, 7) = $Model 
 $Sheet.Cells.Item($intRow, 8) = $SystemType 
 $sheet.Cells.Item($intRow, 9) = $uptime 
 $sheet.Cells.Item($intRow, 10)= $BiosVersion 
 $sheet.Cells.Item($intRow, 11)= $sheetPUInfo 
 $sheet.Cells.Item($intRow, 12)=$NoOfProcessors 
 $sheet.Cells.Item($intRow, 13)= "$TotalMem MB" 
 $sheet.Cells.Item($intRow, 14)= "$FreeMem MB" 
 $sheet.Cells.Item($intRow, 15)= "$TotalVirt MB" 
 $sheet.Cells.Item($intRow, 16)= "$FreeVirtMem MB" 
 $sheet.Cells.Item($intRow, 19)=$IPAddress 
 $sheet.Cells.Item($intRow, 18)=$FQDN 
 
  
$driveStr = "" 
 foreach($drive in $drives) 
 { 
 $size1 = $drive.size / 1GB 
 $size = "{0:N2}" -$size1 
 $free1 = $drive.freespace / 1GB 
 $free = "{0:N2}" -$free1 
 $freea = $free1 / $size1 * 100 
 $freeb = "{0:N2}" -$freea 
 $ID = $drive.DeviceID 
 $driveStr +"$ID = Total Space: $size GB / Free Space: $free GB / Free (Percent): $freeb % ` " 
 } 
 $sheet.Cells.Item($intRow, 17) = $driveStr 
 } 
 
  
$intRow = $intRow + 1 
 $pcnotfound = "false" 
 } 
 
$erroractionpreference = “SilentlyContinue”  
 
$Sheet.UsedRange.EntireColumn.AutoFit() 
########################################333 
 
$Sheet = $Excel.Worksheets.Item(2) 
$sheet.Name = 'DiskSpace' 
$Sheet.Activate() | Out-Null 
 
#Create a Title for the first worksheet 
$row = 1 
$Column = 1 
$Sheet.Cells.Item($row,$column)= 'Disk Space Information' 
 
$range = $Sheet.Range("a1","h2"$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 
$initalRow = $row 
 
#Create a header for Disk Space Report; set each cell to Bold and add a background color 
$Sheet.Cells.Item($row,$column)= 'Computername' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'DeviceID' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'VolumeName' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'TotalSizeGB' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'UsedSpaceGB' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'FreeSpaceGB' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= '%Free' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'State' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
 
#Set up a header filter 
$headerRange = $Sheet.Range("a3","h3"$headerRange.AutoFilter() | Out-Null 
 
#Increment Row and reset Column back to first column 
$row++ 
$Column = 1 
$critical=0 
$warning=0 
$good=0 
 
#Get the drives and filter out CD/DVD drives 
foreach ($computer in $Computers) 
 { 
$diskDrives = Get-WmiObject win32_LogicalDisk -Filter "DriveType='3'" -ComputerName $computer 
 
#Process each disk in the collection and write to spreadsheet 
ForEach ($disk in $diskDrives) { 
    $Sheet.Cells.Item($row,1)= $disk.__Server 
    $Sheet.Cells.Item($row,2)= $disk.DeviceID 
    $Sheet.Cells.Item($row,3)= $disk.VolumeName 
    $Sheet.Cells.Item($row,4)= [math]::Round(($disk.Size /1GB),2) 
    $Sheet.Cells.Item($row,5)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2) 
    $Sheet.Cells.Item($row,6)= [math]::Round(($disk.FreeSpace / 1GB),2) 
    $Sheet.Cells.Item($row,7)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))   
    
    #Determine if disk needs to be flagged for warning or critical alert 
    If ($disk.FreeSpace -lt 5GB -AND ("{0:P}" -f ($disk.FreeSpace / $disk.Size))  -lt 40) { 
        $Sheet.Cells.Item($row,8) = "Critical" 
        $critical++ 
        #Check to see if space is near empty and use appropriate background colors 
        $range = $Sheet.Range(("A{0}"  -$row),("H{0}"  -$row)) 
        $range.Select() | Out-Null     
        #Critical threshold          
        $range.Interior.ColorIndex = 3 
    } ElseIf ($disk.FreeSpace -lt 10GB -AND ("{0:P}" -f ($disk.FreeSpace / $disk.Size)) -lt 60) { 
        $Sheet.Cells.Item($row,8) = "Warning" 
        $range = $Sheet.Range(("A{0}"  -$row),("H{0}"  -$row)) 
        $range.Select() | Out-Null         
        $warning++ 
        $range.Interior.ColorIndex = 6 
    } Else { 
        $Sheet.Cells.Item($row,8) = "Good" 
        $good++ 
    } 
 
     $row++ 
} 
} 
 
#Add a border for data cells 
$row-- 
$dataRange = $Sheet.Range(("A{0}"  -$initalRow),("H{0}"  -$row)) 
7..12 | ForEach { 
    $dataRange.Borders.Item($_).LineStyle = 1 
    $dataRange.Borders.Item($_).Weight = 2 
} 
 
#Auto fit everything so it looks better 
 
$usedRange = $Sheet.UsedRange                                                             
$usedRange.EntireColumn.AutoFit() | Out-Null 
 
$critical 
$warning 
$good 
 
$sheet = $excel.Worksheets.Item(2)  
  
$row++;$row++ 
 
$beginChartRow = $Row 
 
$Sheet.Cells.Item($row,$Column) = 'Critical' 
$Column++ 
$Sheet.Cells.Item($row,$Column) = 'Warning' 
$Column++ 
$Sheet.Cells.Item($row,$Column) = 'Good' 
$Column = 1 
$row++ 
#Critical formula 
$Sheet.Cells.Item($row,$Column)=$critical 
$Column++ 
#Warning formula 
$Sheet.Cells.Item($row,$Column)=$warning 
$Column++ 
#Good formula 
$Sheet.Cells.Item($row,$Column)= $good 
 
$endChartRow = $row 
 
$chartRange = $Sheet.Range(("A{0}" -$beginChartRow),("C{0}" -$endChartRow)) 
 
##Add a chart to the workbook 
#Open a sheet for charts 
$temp = $sheet.Charts.Add() 
$temp.Delete() 
$chart = $sheet.Shapes.AddChart().Chart 
$sheet.Activate() 
 
#Configure the chart 
##Use a 3D Pie Chart 
$chart.ChartType = 70 
$chart.Elevation = 40 
#Give it some color 
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.TintAndShade = .34 
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5 
$sheet.Shapes.Item("Chart 1").Fill.BackColor.TintAndShade = .765 
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5 
 
$sheet.Shapes.Item("Chart 1").Fill.TwoColorGradient(1,1) 
 
#Set the location of the chart 
$sheet.Shapes.Item("Chart 1").Placement = 3 
$sheet.Shapes.Item("Chart 1").Top = 30 
$sheet.Shapes.Item("Chart 1").Left = 600 
 
$chart.SetSourceData($chartRange$chart.HasTitle = $True 
 
$chart.ApplyLayout(6,69) 
$chart.ChartTitle.Text = "Disk Space Report" 
$chart.ChartStyle = 26 
$chart.PlotVisibleOnly = $False 
$chart.SeriesCollection(1).DataLabels().ShowValue = $True 
$chart.SeriesCollection(1).DataLabels().Separator = ("{0}" -f [char]10) 
 
$chart.SeriesCollection(1).DataLabels().Position = 2 
#Critical 
$chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = 255 
#Warning 
$chart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = 65535 
#Good 
$chart.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = 5287936 
 
#Hide the data 
$chartRange.EntireRow.Hidden = $True 
 
$sheet.Name = 'DiskInformation' 
 
 
 
 
$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 
 
 Sheet1 – Server Information
Sheet 2 – Disk Information – No of critical, warning and good state representation of drives

Email-Output-

 Reference

http://learn-powershell.net/2012/12/24/powershell-and-excel-adding-a-chart-and-header-filter-to-a-report/

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in Uncategorized and tagged , , , , , . Bookmark the permalink.

4 Responses to PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL

  1. Shreyas Rane says:

    Hi Prashant,

    Thanks for the script. I have tried this script in Windows Server 2012 R2 Datacenter. however xml was not created. Can you help with that?

    Regards,
    Shreyas

    • Hi Shreyas, This script don’t generate XML. This post talks about generating EXCEL file. Do you have excel installed on that server. If not try from workstation where excel installed and query the remote server, make sure you have administrator permission on the remote server.

  2. Rupesh Behera says:

    hi Prashanth,
    your PS script is awesome and it’s perfectly matching my requirement, the only change I need is if you can tell me, instead of generating excel I want to insert all these info into my SQL Server table.
    your help is appreciated

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s