• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

A Server inventory outputed to Excel

I am looking for a powershell script that pulls from a text file that contains server names(targets)c:\server.txt, I would like it to build an Excel file that shows:
1.      CPU
a.      Count of CPU’s
b.      # of cores
2.      RAM
3.      OS Name
4.      OS Version
5.      OS Service Pack
6.      IP
7.      MAC Address
8.      BIOS version (not a must but would be nice)
9.      Local Hard Drive(s) – no cd/dvd or mapped drives.
          a.      Hard Drives capacity
          b.      Hard Drives Remaining Space
10.      Last Reboot Time
11.      Check to see if server has SQL Server Installed and Running
          a.      What version of SQL server
12.      Stamp the Excel File with current time of report

I am new to Powershell, I do have Quest's Plugin "Active Roles Management Shell for Active Directory" installed . Don't think that's needed here since no AD calls needed, as far as I know anyway.

Your help is greatly appreciated.
0
ewalsh
Asked:
ewalsh
1 Solution
 
Shabarinath RamadasanInfrastructure ArchitectCommented:
Try this powershell script.
I wrote this based on my requirement, so may need few more addition to include other fields.

# Powershell script to generate server inventory report
# Created by Shabarinath Ramadasan
# shabarinath@mydomain.com

# Require Quest AD roles for powershell and shoud run with a domain admin

# Fetch all computer objects with OS=Windows Server*
Write-Output "###############################################################################"
Write-Output "--------------------------- Generating Server List ----------------------------"
Get-QADComputer -searchroot "OU=OUNAME,DC=DOMAIN,DC=COM" |foreach {$_.name} >serverlist.txt
# Create an Excel Sheet and populate the headers
Write-Output "---------------------------  Creating Excel Sheet  ----------------------------"
$excel = new-object -comobject excel.application
$excel.visible = $true
$workbook = $excel.workbooks.add()
$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()
$workbook.WorkSheets.item(1).Name = "ServerTracker"
$sheet = $workbook.WorkSheets.Item("ServerTracker") 
For($b = 1 ; $b -le 17 ; $b++) 
{
$sheet.cells.item(1,$b).font.bold = $true
$sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
$sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
$sheet.Cells.Item(1, $b).Font.ColorIndex = 5
$sheet.Cells.Item(1, $b).Interior.Color = $rgbSpringGreen
} 
$sheet.cells.item(1,1) = "ServerName"
$sheet.cells.item(1,2) = "Category"
$sheet.cells.item(1,3) = "Physical/Virtual"
$sheet.cells.item(1,4) = "Serial Number"
$sheet.cells.item(1,5) = "Device Purpose"
$sheet.cells.item(1,6) = "Application Owner"
$sheet.cells.item(1,7) = "IP Address"
$sheet.cells.item(1,8) = "Purchase Date"
$sheet.cells.item(1,9) = "Warranty Expiry"
$sheet.cells.item(1,10) = "Location"
$sheet.cells.item(1,11) = "Datacentre/SwitchRoom"
$sheet.cells.item(1,12) = "Hardware"
$sheet.cells.item(1,13) = "Processor"
$sheet.cells.item(1,14) = "RAM"
$sheet.cells.item(1,15) = "Operating System"
$sheet.cells.item(1,16) = "Critical Service"
$sheet.cells.item(1,17) = "Virtulaize Y/N"

# Itration for each item from the server list 
Write-Output "---------------------------  Updating  Excel  Sheet   -------------------------"
$servercount = (get-content serverlist.txt).count
$servercount = $servercount+1
$n=2
foreach ($cname in get-content "serverlist.txt") 
{
		Write-Output "---------------------------  Processing server $cname   --------------------"
		#Checking if the server is alive
		if (((Get-WmiObject win32_PINGSTATUS -f "Address='$cname'").statuscode) -eq 0)
			{
			$sheet.cells.item($n,1) = $cname
			$hardware = Get-WmiObject -ComputerName $cname win32_computersystem|foreach {$_.Model}
			# Checking if this server is a Virtual or Physical
			if ((Get-WmiObject -ComputerName $cname win32_computersystem|foreach {$_.Model}) -ne "Virtual Machine")
				{
				$sheet.cells.item($n,3) = "Physical Server"
				}
			else
				{
				$sheet.cells.item($n,3) = Get-WmiObject -ComputerName $cname win32_computersystem|foreach {$_.Model}
				}
			# Collecting the basic information and updating on the excel sheet
			$sheet.cells.item($n,4) = Get-WmiObject -computername $cname -Query ("select identifyingnumber from Win32_computersystemproduct")|foreach {$_.identifyingnumber}
			$sheet.cells.item($n,7) = Get-WmiObject -computername $cname -Query ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled=True")| foreach {$_.IPAddress} |select -First 1
			$sheet.cells.item($n,12) = Get-WmiObject -ComputerName $cname -Query ("select * from win32_computersystem")|foreach {$_.model}
			$sheet.cells.item($n,13) = Get-WmiObject -computername $cname -Query ("select name from Win32_processor") |foreach {$_.name}
			$sheet.cells.item($n,14) = (Get-WmiObject -computername $cname -Query ("select Totalphysicalmemory from win32_computersystem")|foreach {$_.totalphysicalmemory})/1000000000
			$sheet.cells.item($n,15) = Get-WmiObject -computername $cname -Query ("select version from win32_operatingsystem")|foreach {$_.version}
			$n
			}
		else 
			{
			$sheet.cells.item($n,1) = $cname
			$sheet.cells.item($n,2) = "Server not reachable"
			}
		Write-Output "---------------------------  Completed server $cname   --------------------"
		Write-Output "----------------------------------------------------------------------------"
		$n=$n+1	

}
[void]$sheet.cells.entireColumn.Autofit()

Open in new window

0
 
ewalshAuthor Commented:
Sorry for delay, I will be back to this soon, some important things at home has taken my time.
0
 
AshleySPMCommented:
I tweaked this a little bit based on your requirements but this is what I made for myself. I think it has all your requirements except for SQL

#Powershell script for server inventory
#Creation Date: 3/10/2011
#Created by Ashley Schmidt

##########Declare Computer variable##########

$strComputer = “computername”

##########Open Excel Application and start new workbook##########

$Excel = New-Object -Com Excel.Application
  $Excel.visible = $True
  $Excel = $Excel.Workbooks.Add()

##########Name the Spreadsheet and begin to populate it for GENERAL INFO##########

$Sheet = $Excel.WorkSheets.Item(1)
     $Sheet.name = "$strComputer"
	$Sheet.Cells.Item(1,1) = “$strComputer Documentation”
	$Sheet.Cells.Item(1,2) = "Date of Documentation:" 
	$Sheet.Cells.Item(1,3) = Get-Date -format "M-dd-yyyy"


##########Format the cells for green color##########
   
   $Sheet.Cells.Item(1,1).Interior.ColorIndex = 50
   $Sheet.Cells.Item(1,1).Font.ColorIndex = 20
   $Sheet.Cells.Item(1,1).Font.Bold = $True
   $Sheet.Cells.Item(1,2).Interior.ColorIndex = 50
   $Sheet.Cells.Item(1,2).Font.ColorIndex = 20
   $Sheet.Cells.Item(1,2).Font.Bold = $True
   $Sheet.Cells.Item(1,3).Interior.ColorIndex = 50
   $Sheet.Cells.Item(1,3).Font.ColorIndex = 20
   $Sheet.Cells.Item(1,3).Font.Bold = $True
   $Sheet.Cells.Item(1,4).Interior.ColorIndex = 50
   $Sheet.Cells.Item(1,4).Font.ColorIndex = 20
   $Sheet.Cells.Item(1,4).Font.Bold = $True
   $Sheet.Cells.Item(1,5).Interior.ColorIndex = 50
   $Sheet.Cells.Item(1,5).Font.ColorIndex = 20
   $Sheet.Cells.Item(1,5).Font.Bold = $True
   $Sheet.Cells.Item(1,6).Interior.ColorIndex = 50
   $Sheet.Cells.Item(1,6).Font.ColorIndex = 20
   $Sheet.Cells.Item(1,6).Font.Bold = $True
  

$intRow = 2

##########Begin to populate the spreadsheet with headers for SYSTEM INFO##########

$intRow = $intRow + 1

     $Sheet = $Excel.WorkSheets.Item(1)
        $Sheet.Cells.Item($intRow,1) = “Computer Manufacturer:”
	$Sheet.Cells.Item($intRow,2) = “Model:”
	$Sheet.Cells.Item($intRow,3) = “Total Memory:”


##########Change color of the header row to GREEN##########

for($i = 1; $i -le 6; $i++){
   $Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 50
   $Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 20
   $Sheet.Cells.Item($intRow,$i).Font.Bold = $True
}

$intRow = $intRow + 1

##########Populate the SYSTEM INFO from root\CIMV##########

$colItems = Get-WmiObject Win32_ComputerSystem -Namespace “root\CIMV2" `
-ComputerName $strComputer

   foreach($objItem in $colItems) {
	$Sheet.Cells.Item($intRow,1) = $objItem.Manufacturer
	$Sheet.Cells.Item($intRow,2) = $objItem.Model
	$Sheet.Cells.Item($intRow,3) = $objItem.TotalPhysicalMemory / 1GB
	$Sheet.Cells.Item($intRow,3).NumberFormat = "#.00" 

 for($i = 1; $i -le 6; $i++)
	 {
	 	$Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 35;
     		$Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 0;
		$Sheet.Cells.Item($intRow,$i).Font.Bold = $true;
	 }

$intRow = $intRow + 1
}

$intRow = $intRow + 1

##########Begin to populate the spreadsheet with headers for BIOS INFO##########	
$intRow = $intRow + 1
     $Sheet = $Excel.WorkSheets.Item(1)
	$Sheet.Cells.Item($intRow,1) = “BIOS:”
	$Sheet.Cells.Item($intRow,2) = “Version:”
	$Sheet.Cells.Item($intRow,3) = “Service Tag:”

##########Change color of the header row to GREEN##########
	
for($i = 1; $i -le 6; $i++){
   $Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 50
   $Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 20
   $Sheet.Cells.Item($intRow,$i).Font.Bold = $True
}

$intRow = $intRow + 1

$colItems = Get-WmiObject Win32_BIOS -Namespace “root\CIMV2"`
-ComputerName $strComputer

   foreach ($objItem in $colItems) {
	$Sheet.Cells.Item($intRow,1) = $objItem.Description
	$Sheet.Cells.Item($intRow,2) = $objItem.SMBIOSMajorVersion
	$Sheet.Cells.Item($intRow,3) = $objItem.SerialNumber

 for($i = 1; $i -le 6; $i++)
	 {
	 	$Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 35;
     		$Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 0;
		$Sheet.Cells.Item($intRow,$i).Font.Bold = $true;
	 }

$intRow = $intRow + 1
}

##########Begin to populate the spreadsheet with headers for OPERATING SYSTEM##########

$intRow = $intRow + 1
     $Sheet = $Excel.WorkSheets.Item(1)
	$Sheet.Cells.Item($intRow,1) = “Operating System:”
	$Sheet.Cells.Item($intRow,2) = “OS Version:”
	$Sheet.Cells.Item($intRow,3) = “Last Reboot:”
 for($i = 1; $i -le 6; $i++)
	 {
   $Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 50
   $Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 20
   $Sheet.Cells.Item($intRow,$i).Font.Bold = $True
	}
$intRow = $intRow + 1

$colItems =  Get-WmiObject "Win32_OperatingSystem" -Namespace "root/CIMV2" `
-Computername $strComputer

   foreach($objItem in $colItems) {
	$Sheet.Cells.Item($intRow,1) = $objItem.Name
	$Sheet.Cells.Item($intRow,2) = $objItem.Version
	$Sheet.Cells.Item($intRow,3) = $objItem.ConvertToDateTime($objItem.LastBootupTime) 
	 for($i = 1; $i -le 6; $i++)
	 {
	 	$Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 35;
     		$Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 0;
		$Sheet.Cells.Item($intRow,$i).Font.Bold = $true;
	 }

$intRow = $intRow +1
}

##########Begin to populate the spreadsheet with headers for HARD DRIVE INFO##########

$intRow = $intRow + 1
   $Sheet = $Excel.WorkSheets.Item(1)
	$Sheet.Cells.Item($intRow,1) = “Hard Drive Name”
	$Sheet.Cells.Item($intRow,2) = “Drive Letter”
	$Sheet.Cells.Item($intRow,3) = “Description”
	$Sheet.Cells.Item($intRow,4) = “FileSystem”
	$Sheet.Cells.Item($intRow,5) = “Size in GB”
	$Sheet.Cells.Item($intRow,6) = “Free Space in GB”

##########Change color of the header row to GREEN##########

for($i = 1; $i -le 6; $i++){
   $Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 50
   $Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 20
   $Sheet.Cells.Item($intRow,$i).Font.Bold = $True
}

$intRow = $intRow + 1

$colItems = Get-wmiObject -class “Win32_LogicalDisk” -namespace “root\CIMV2" `
-computername $strComputer

   foreach ($objItem in $colItems) {
	$Sheet.Cells.Item($intRow,1) = $objItem.VolumeName
	$Sheet.Cells.Item($intRow,2) = $objItem.DeviceID
	$Sheet.Cells.Item($intRow,3) = $objItem.Description
	$Sheet.Cells.Item($intRow,4) = $objItem.FileSystem
	$Sheet.Cells.Item($intRow,5) = $objItem.Size / 1GB
	$Sheet.Cells.Item($intRow,6) = $objItem.FreeSpace / 1GB
	   $Sheet.Cells.Item($intRow,5).NumberFormat = "#.00"
	   $Sheet.Cells.Item($intRow,6).NumberFormat = "#.00"

 for($i = 1; $i -le 6; $i++)
	 {
	 	$Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 35;
     		$Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 0;
		$Sheet.Cells.Item($intRow,$i).Font.Bold = $true;
	 }


$intRow = $intRow + 1

}

##########Begin to populate the spreadsheet with headers for PROCESSOR INFO##########

$intRow = $intRow + 1

     $Sheet = $Excel.WorkSheets.Item(1)
	$Sheet.Cells.Item($intRow,1) = “Processor Name:”
	$Sheet.Cells.Item($intRow,2) = “Number of Cores:”

##########Change color of the header row to GREEN##########

 for($i = 1; $i -le 6; $i++)
	 {
	 	$Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 50;
     		$Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 20;
		$Sheet.Cells.Item($intRow,$i).Font.Bold = $true;
	 }


$intRow = $intRow + 1

$colItems = Get-WmiObject Win32_Processor -Namespace “root\CIMV2" `
-Computername $strComputer

   foreach($objItem in $colItems) {
	$Sheet.Cells.Item($intRow,1) = $objItem.Name
	$Sheet.Cells.Item($intRow,2) = $objItem.NumberofCores
 for($i = 1; $i -le 6; $i++)
	 {
	 	$Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 35;
     		$Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 0;
		$Sheet.Cells.Item($intRow,$i).Font.Bold = $true;
	 }
 

$intRow = $intRow +1
}

##########Begin to populate the spreadsheet with headers for NETWORK INFO##########

$intRow = $intRow + 1

     $Sheet = $Excel.WorkSheets.Item(1)
	$Sheet.Cells.Item($intRow,1) = “DHCP Enabled:”
	$Sheet.Cells.Item($intRow,2) = “IP Address:”
	$Sheet.Cells.Item($intRow,3) = “Subnet Mask:”
	$Sheet.Cells.Item($intRow,4) = “Default Gateway:”
	$Sheet.Cells.Item($intRow,5) = “MAC Address:”

##########Change color of the header row to GREEN##########

for($i = 1; $i -le 6; $i++){
   $Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 50
   $Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 20
   $Sheet.Cells.Item($intRow,$i).Font.Bold = $True
}

$intRow = $intRow + 1
$colItems = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace “root\CIMV2" `
-ComputerName $strComputer | where{$_.IPEnabled -eq “True”}

   foreach($objItem in $colItems) {
	$Sheet.Cells.Item($intRow,1) = $objItem.DHCPEnabled
	$Sheet.Cells.Item($intRow,2) = $objItem.IPAddress
	$Sheet.Cells.Item($intRow,3) = $objItem.IPSubnet
	$Sheet.Cells.Item($intRow,4) = $objItem.DefaultIPGateway
	$Sheet.Cells.Item($intRow,5) = $objItem.MACAddress

 for($i = 1; $i -le 6; $i++)
	 {
	 	$Sheet.Cells.Item($intRow,$i).Interior.ColorIndex = 35;
     		$Sheet.Cells.Item($intRow,$i).Font.ColorIndex = 0;
		$Sheet.Cells.Item($intRow,$i).Font.Bold = $true;
	 }


$intRow = $intRow +1
}




##########Autofit the used columns in the spreadsheet##########

$Sheet.UsedRange.EntireColumn([A1]) = 60
$Sheet.UsedRange.EntireColumn.AutoFit()
Clear 

$Excel.SaveAs("C:\$strComputer.xlsx")

Open in new window

0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now