We help IT Professionals succeed at work.

Inventory of servers/pcs

Colchester_Institute
on
591 Views
Last Modified: 2012-05-10
Hi There

I'm in the process of creating an Inventory using POwershell of our servers and have so far found various bits and pieces that i've been able to use from the web to get done what i need.  I've now hit a sticking point.

I need 3 more types of information they are:
* The Physical Disk sizes and free space left on them on the server
* The Speed of the Processor(s)
* The version of a certain piece of software that is installed on them all

As you'll see from the code attached I'm also getting this info into a Excel Spreadsheet so we can save it for reference.  As you'll see from the code lines 56 and 60 are for the Drives/processor info and i still need to the add the software data.  Of course I'll no doubt re-arrange the layout of the columns once i have the information outputting correctly

Thank You




$a = New-Object -comobject Excel.Application
$a.visible = $True

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "Server Name"
$c.Cells.Item(1,2) = "Operating System"
$c.Cells.Item(1,3) = "IP Address"
$c.Cells.item(1,4) = "Subnet"
$c.Cells.item(1,5) = "Default Gateway"
$c.Cells.item(1,6) = "MAC Address"
$c.Cells.Item(1,7) = "Service Packs"
$c.Cells.Item(1,8) = "System Type"
$c.Cells.Item(1,9) = "Install Date"
$c.Cells.Item(1,10) = "Manufacturer"
$c.Cells.Item(1,11) = "Model"
$c.Cells.Item(1,12) = "Service Tag"
$c.Cells.Item(1,13) = "Drives"
$c.Cells.Item(1,14) = "Number of Processors"
$c.Cells.Item(1,15) = "Total Phsyical Memory (GB)"
$c.Cells.Item(1,16) = "Last Reboot Time"
$c.Cells.Item(1,17) = "Processor Speed"
$c.Cells.Item(1,18) = "Report Time Stamp"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True

$intRow = 2

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
$OS = get-wmiobject Win32_OperatingSystem -computername $strComputer
$Computer = get-wmiobject Win32_computerSystem -computername $strComputer
$Bios =get-wmiobject Win32_bios -computername $strComputer
$NICCard=Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
foreach($NIC in $NICCard){
if($NIC.ipenabled -eq $true)
{

$c.Cells.Item($intRow,1) = $strComputer.Toupper()
$c.Cells.Item($intRow,2) = $OS.Caption
$c.Cells.Item($intRow,3) = $NIC.IPaddress[0]
$c.Cells.Item($intRow,4) = $NIC.IPSubnet
$c.Cells.Item($intRow,5) = $NIC.DefaultIPGateway
$c.Cells.Item($intRow,6) = $NIC.MACAddress
$c.Cells.Item($intRow,7) = $OS.CSDVersion
$c.Cells.Item($intRow,8) = $Computer.SystemType
$c.Cells.Item($intRow,9) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.InstallDate)
$c.Cells.Item($intRow,10) = $Computer.Manufacturer
$c.Cells.Item($intRow,11) = $Computer.Model
$c.Cells.Item($intRow,12) = $Bios.serialnumber
$c.Cells.Item($intRow,13) = 
$c.Cells.Item($intRow,14) = $Computer.NumberOfProcessors
$c.Cells.Item($intRow,15) = "{0:N0}" -f ($computer.TotalPhysicalMemory/1GB)
$c.Cells.Item($intRow,16) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.LastBootUpTime)
$c.Cells.Item($introw,17) = 



$c.Cells.Item($intRow,18) = Get-date

$intRow = $intRow + 1
$d.EntireColumn.AutoFit()
}}}

Open in new window

Comment
Watch Question

Commented:
Sorry i can't help with the code above but thought you might want to know of some software called spiceworks that does just this.  It's free and has a lot more to it like licences, HDD history, Wannings, Print ink low warning.  Easy to setup. I would recommend a look, even if it just gives you some more idea's.

www.spiceworks.com

One thing i would say is once installed, it's a lot quicker to navigate pages using anything but IE. (recommend: firefox)
Thanks myhc

We've used Spiceworks before the biggest problem we had with that it was just a bit time consuming...THe script once done will literally be a one stop click which anyone in the team will be able to run
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
you want Disk sizes and free space for each drive or all together?
Well each phsyical drive.  Not any of the mapped ones. Space and Free space yes

so ie:

C:\ System
D:\ Data
E:\ Odd bits and pieces

also need to put in at the start some where a username and password so that i can run this script remotely.
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
which software you wish to have their version?
that's the only thing left to complete your script...
just our anti virus which currently is bitdefender, but ideally i'd like to be able to change that should we change the software or if someone decides they want another item of software also

Thank you
Full stack Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
@Sedgewick - Thats great thank you also good to see that some of the bits i've been doing to resolve this myself i werent far from getting right!

@Soostibi - I dont want to have the password on display but obvisouly the password is need for all the servers in the serverlist.txt file and I only want to have to input it once, so that then for each server the credential is already known

Commented:
Of course, you can define the $cred at the beginning of your script, so you have to give the password once, not in the foreach loop.
see i thought that but must be entering it wrong as it dont seem to pick up
@ Sedgwick - The main script works fine especially the hard drive data, I however cant get the Processor Info to appear in the spreadsheet.  If i run the command as a single line ie:

Get-wmiobject Win32_Processor - computername $strcomputer | select maxclockspeed

or

Get-wmiobject Win32_Processor - computername $strcomputer | select name

they work fine to disply the info in Powershell but not in a cell in the spreadsheet.

And also the Cells for the HDD Data dont autoformat with the others
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
the processor speed should be in column 17
Untitled.jpg
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
>>And also the Cells for the HDD Data dont autoformat with the others
what do you mean?

can u post a screenshot or post the excel?
I've rearranged the Columns so that the sheet reads in a more "logical" way however I've made sure that by doing so i've changed both sections so where it was Column 17 its now simply 15, but i just get an empty cell.  Even if i try and get the name of the processor.

Also regarding the "auto" formatting at the very beginning of the script i posted on lines 28-31 i've written the format of hte cells.  However when the HDD data goes in this data isnt formatted like the others?

So no cell shading on the column titles, no bold and no autoformat on the width

Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
can u post the script after your changes?
Sure here goes....I've removed the software bit as i may well use that code in something else.

One thing i have noticed which may mena me ditching the HDD info is that if i have mulitple servers and SERVER A has two drives i.e C:\ and S:\

But then SERVER B has two drives i.e C:\ and E:\

THe Column Called Drive S:\ gets over written with the title Drive E:\ but which then means that the data in the Previous row will now be wrong.

Thus now my main concern is really just getting the Processor Speed/Name
$a = New-Object -comobject Excel.Application
$a.visible = $True

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "Server Name"
$c.Cells.Item(1,2) = "Service Tag"
$c.Cells.Item(1,3) = "Manufacturer"
$c.Cells.Item(1,4) = "Model"
$c.Cells.Item(1,5) = "System Type"
$c.Cells.Item(1,6) = "Install Date"
$c.Cells.Item(1,7) = "IP Address"
$c.Cells.item(1,8) = "Subnet"
$c.Cells.item(1,9) = "Default Gateway"
$c.Cells.item(1,10) = "MAC Address"
$c.Cells.Item(1,11) = "Operating System"
$c.Cells.Item(1,12) = "Service Packs"
$c.Cells.Item(1,13) = "Total Phsyical Memory (GB)"
$c.Cells.Item(1,14) = "Number of Processors"
$c.Cells.Item(1,15) = "Processor Speed"
$c.Cells.Item(1,16) = "Drives"
# $c.Cells.Item(1,16) = "Last Reboot Time"

# $c.Cells.Item(1,18) = "Report Time Stamp"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True

$intRow = 2

$colComputers = get-content C:\Serverlist.txt
$colSoftwares = get-content C:\softwarelist.txt

foreach ($strComputer in $colComputers)
{
	$OS = get-wmiobject Win32_OperatingSystem -computername $strComputer
	$Computer = get-wmiobject Win32_computerSystem -computername $strComputer
	$Bios =get-wmiobject Win32_bios -computername $strComputer
	$NICCard = Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
	$procSpeed = get-wmiobject Win32_Processor -computername $strComputer | select MaxClockSpeed
	
	foreach($NIC in $NICCard)
	{
		if($NIC.ipenabled -eq $true)
		{
			$c.Cells.Item($intRow,1) = $strComputer.Toupper()
			$c.Cells.Item($intRow,2) = $Bios.serialnumber
            $c.Cells.Item($intRow,3) = $Computer.Manufacturer
			$c.Cells.Item($intRow,4) = $Computer.Model
            $c.Cells.Item($intRow,5) = $Computer.SystemType
            $c.Cells.Item($intRow,6) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.InstallDate)
            $c.Cells.Item($intRow,7) = $NIC.IPaddress[0]
			$c.Cells.Item($intRow,8) = $NIC.IPSubnet
			$c.Cells.Item($intRow,9) = $NIC.DefaultIPGateway
			$c.Cells.Item($intRow,10) = $NIC.MACAddress
            $c.Cells.Item($intRow,11) = $OS.Caption			
			$c.Cells.Item($intRow,12) = $OS.CSDVersion
			$c.Cells.Item($intRow,13) = "{0:N0}" -f ($computer.TotalPhysicalMemory/1GB)
            $c.Cells.Item($intRow,14) = $Computer.NumberOfProcessors
			$c.Cells.Item($introw,15) = $procSpeed.MaxClockSpeed
			# $c.Cells.Item($intRow,16) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.LastBootUpTime)
			# $c.Cells.Item($introw,18) = Get-Date

			$driveindex = 17
			$drives = Get-WmiObject -ComputerName $strComputer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
			foreach($drive in $drives)
			{
				$driveID = $drive.DeviceID
				$disksize = $drive.size / 1GB 
				$freespace = $drive.freespace / 1GB
				
				$c.Cells.Item(1,$driveindex) = "Drive [$driveID] Physical Disk Size (GB)"
				$c.Cells.Item(1,$driveindex+1) = "Drive [$driveID] Disk Free Space (GB)"
				$c.Cells.Item($intRow,$driveindex) = "{0:N0}" -f $disksize
				$c.Cells.Item($intRow,$driveindex+1) = "{0:N0}" -f $freespace
				$driveindex=$driveindex+2
			}
			$c.Cells.Item($intRow,16) = ($driveindex-17)/2

			}
				$driveindex=$driveindex+1
			}
			$intRow = $intRow + 1
			$d.EntireColumn.AutoFit()
		}

Open in new window

Right I've tidied up all the Code and got it sorted doing the HDD stuff, run a couple of google seraches and found a way of doing it.

However I still cannot get any Processor info written into the spreadsheet and I just dont know why?
$xl = New-Object -comobject Excel.Application
$xl.visible = $True

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet3"}
$s3.delete()
$ws = $wb.Worksheets.Item(1)

$ws.name = "General Info"

$ws.Cells.Item(1,1) = "Server Name"
$ws.Cells.Item(1,2) = "Service Tag"
$ws.Cells.Item(1,3) = "Manufacturer"
$ws.Cells.Item(1,4) = "Model"
$ws.Cells.Item(1,5) = "System Type"
$ws.Cells.Item(1,6) = "IP Address"
$ws.Cells.Item(1,7) = "Subnet"
$ws.Cells.Item(1,8) = "Default Gateway"
$ws.Cells.Item(1,9) = "MAC Address"
$ws.Cells.Item(1,10) = "Install Date"
$ws.Cells.Item(1,11) = "Operating System"
$ws.Cells.Item(1,12) = "Service Packs"
$ws.Cells.Item(1,13) = "Memory (GB)"
$ws.Cells.Item(1,14) = "Processors"
$ws.Cells.Item(1,15) = "Processor Type/Speed"
$ws.Cells.Item(1,16) = "Last Reboot Time"
$ws.Cells.Item(1,17) = "Report Time Stamp"

$d = $ws.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()

$intRow = 2

$colComputers = get-content C:\ServerList.txt
foreach ($strComputer in $colComputers)
{
$OS = get-wmiobject Win32_OperatingSystem -computername $strComputer
$Computer = get-wmiobject Win32_computerSystem -computername $strComputer
$Bios = get-wmiobject Win32_bios -computername $strComputer
$ProcType = get-wmiobject Win32_Processor -computername $strcomputer
$NICCard= Get-WmiObject win32_networkadapterconfiguration -computername $strComputer
foreach($NIC in $NICCard){
if($NIC.ipenabled -eq $true)
{

$ws.Cells.Item($intRow,1) = $strComputer.Toupper()
$ws.Cells.Item($intRow,2) = $Bios.serialnumber
$ws.Cells.Item($intRow,3) = $Computer.Manufacturer
$ws.Cells.Item($intRow,4) = $Computer.Model
$ws.Cells.Item($intRow,5) = $Computer.SystemType
$ws.Cells.Item($intRow,6) = $NIC.IPaddress[0]
$ws.Cells.Item($intRow,7) = $NIC.IPSubnet
$ws.Cells.Item($intRow,8) = $NIC.DefaultIPGateway
$ws.Cells.Item($intRow,9) = $NIC.MACAddress
$ws.Cells.Item($intRow,10) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.InstallDate)
$ws.Cells.Item($intRow,11) = $OS.Caption
$ws.Cells.Item($intRow,12) = $OS.CSDVersion
$ws.Cells.Item($intRow,13) = "{0:N0}" -f ($computer.TotalPhysicalMemory/1GB)
$ws.Cells.Item($intRow,14) = $Computer.NumberOfProcessors
$ws.Cells.Item($introw,15) = $ProcType.name
$ws.Cells.Item($intRow,16) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.LastBootUpTime)
$ws.Cells.Item($intRow,17) = Get-date

$intRow = $intRow + 1
$d.EntireColumn.AutoFit()
}
}
}

#---------Create New WorkSheet------------------------------

$ws = $wb.Worksheets.Item(2)

$ws.Name = "Drive Info"
$ws.Cells.Item(1,1) = "Machine Name"
$ws.Cells.Item(1,2) = "Drive"
$ws.Cells.Item(1,3) = "Total size (MB)"
$ws.Cells.Item(1,4) = "Free Space (MB)"
$ws.Cells.Item(1,5) = "Free Space (%)"

$d = $ws.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()

$intRow = 2

$servers = get-content c:\serverlist.txt
foreach ($strcomputer in $servers)
{
$objDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter "DriveType = 3"
foreach ($objdisk in $objDisks)
{
 $ws.Cells.Item($intRow, 1) = $strComputer.ToUpper()
 $ws.Cells.Item($intRow, 2) = $objDisk.DeviceID
 $ws.Cells.Item($intRow, 3) = "{0:N0}" -f ($objDisk.Size/1GB)
 $ws.Cells.Item($intRow, 4) = "{0:N0}" -f ($objDisk.FreeSpace/1GB)
 $ws.Cells.Item($intRow, 5) = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)
$intRow = $intRow + 1
}
}

Open in new window

Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
cause u used $ProcType.name (line 63)

use $ProcType.MaxClockSpeed
No even hat dont work.....I used name as i was trying to get the Name of the porcessor also. I've tried various commands suchas

on line 43:
$ProcType = get-wmiobject Win32_Processor -computername $strcomputer | select name

and then on line 63 i'd have:
$ProcType.name

No commands from the Win32_Processor WMI seem to write to to excel? I know the machines have more than one processor buy i just want hte Name to appear in a cell like it does on the screen if i just type
get-wmiobject Win32_Processor -computername <SERVER> | select name
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
if you run this command fro powershell environment, does it work?
yeah if i run get-wmiobject Win32_Processor -computer <SERVER> | Select name

I get the result

name
------
Intel(R) Xeon(R) CPU        X5355 @2.66GHz
Intel(R) Xeon(R) CPU        X5355 @2.66GHz
Intel(R) Xeon(R) CPU        X5355 @2.66GHz
Intel(R) Xeon(R) CPU        X5355 @2.66GHz


Obviously for each server i just need the result once, but i simply can not understand why nothing is going into the cell....Its driving me mad!
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
try debug the code using PowerGUI Script Editor (it's freeware)
it works for me everytime

try put the following lines after line 43:

[reflection.assembly]::loadwithpartialname('system.windows.forms');
[system.Windows.Forms.MessageBox]::show('$ProcType.name')
sory dumb question but....What do you mean by After line43

Cheers
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
after this line:

$ProcType = get-wmiobject Win32_Processor -computername $strcomputer

put those lines:

[reflection.assembly]::loadwithpartialname('system.windows.forms');
[system.Windows.Forms.MessageBox]::show('$ProcType.name')
ok and what am i looking for\expecting to see
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
you should see a popup message box with the processor name displayed
No i got a box pop up with $ProcType.name  and if i run the script using the Powershell ISE i also got displayed:

True v2.0.50727 c:\WINNT\Assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.windows.forms.dll
OK
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
i forgot 2 remove the single quotes:

[reflection.assembly]::loadwithpartialname('system.windows.forms');
[system.Windows.Forms.MessageBox]::show($ProcType.name)
I just get an empty box now with the "OK" button
Meir RivkinFull stack Software Engineer
CERTIFIED EXPERT

Commented:
if u run:

$ProcType = get-wmiobject Win32_Processor -computername $strcomputer | select name

write-host $ProcType.name

in separate script, does it work?
No thing...but if I run:

Get-wmiobject Win32_Processor -computername $strcomputer | Select name

I get the output...I did change $strcomputer for a name of one of our servers as obviously i werent referencing $strcomputer to the Serverlist but the output i got was

name
----
Intel(R) XEON(TM) CPU 2.20Ghz
Intel(R) XEON(TM) CPU 2.20Ghz

which for hte server i put in would be correct as it has two processors....Of course on the spread sheet i just want the name once...but as mentioned i get nothing at the moment when the script is run?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.