I'm attempting to incorporate the results of a powershell program (Get-IS) that lists installed software and software versions into another powershell program that creates an excel spreadsheet from wmi calls (CompInv). Both program were created by others and run with no issue, but I can't seem to get the software list and version information from the first program to output properly so it can be pumped in to the excel spreadsheet. Code for both programs below (note: Get-IS lists much more then Get-WmiObject or gp HKLM which is why I use it). What do I need to do to get it to work ? Code for both programs below
Get-IS does this
Param
(
[Alias('Computer','ComputerName','HostName')]
[Parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$true,Position=1)]
[string[]]$Name = $env:COMPUTERNAME
)
Begin
{
$LMkeys = "Software\Microsoft\Windows\CurrentVersion\Uninstall","SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall"
$LMtype = [Microsoft.Win32.RegistryHive]::LocalMachine
$CUkeys = "Software\Microsoft\Windows\CurrentVersion\Uninstall"
$CUtype = [Microsoft.Win32.RegistryHive]::CurrentUser
}
Process
{
ForEach($Computer in $Name)
{
$MasterKeys = @()
If(!(Test-Connection -ComputerName $Computer -count 1 -quiet))
{
Write-Error -Message "Unable to contact $Computer. Please verify its network connectivity and try again." -Category ObjectNotFound -TargetObject $Computer
Break
}
$CURegKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($CUtype,$computer)
$LMRegKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($LMtype,$computer)
ForEach($Key in $LMkeys)
{
$RegKey = $LMRegKey.OpenSubkey($key)
If($RegKey -ne $null)
{
ForEach($subName in $RegKey.getsubkeynames())
{
foreach($sub in $RegKey.opensubkey($subName))
{
$MasterKeys += (New-Object PSObject -Property @{
"ComputerName" = $Computer
"Name" = $sub.getvalue("displayname")
"SystemComponent" = $sub.getvalue("systemcomponent")
"ParentKeyName" = $sub.getvalue("parentkeyname")
"Version" = $sub.getvalue("DisplayVersion")
"UninstallCommand" = $sub.getvalue("UninstallString")
})
}
}
}
}
ForEach($Key in $CUKeys)
{
$RegKey = $CURegKey.OpenSubkey($Key)
If($RegKey -ne $null)
{
ForEach($subName in $RegKey.getsubkeynames())
{
foreach($sub in $RegKey.opensubkey($subName))
{
$MasterKeys += (New-Object PSObject -Property @{
"ComputerName" = $Computer
"Name" = $sub.getvalue("displayname")
"SystemComponent" = $sub.getvalue("systemcomponent")
"ParentKeyName" = $sub.getvalue("parentkeyname")
"Version" = $sub.getvalue("DisplayVersion")
"UninstallCommand" = $sub.getvalue("UninstallString")
})
}
}
}
}
$MasterKeys = ($MasterKeys | Where {$_.Name -ne $Null -AND $_.SystemComponent -ne "1" -AND $_.ParentKeyName -eq $Null} | select ComputerName,Name,Version | sort Name)
$MasterKeys
}
}
End
{
}
CompInv does this
Function WMILookup {
foreach ($StrComputer in $colComputers){
$GenItems1 = gwmi Win32_ComputerSystem -Comp $StrComputer
$GenItems2 = gwmi Win32_OperatingSystem -Comp $StrComputer
$GenItems3 = gwmi SoftwareLicensingService -Comp $StrComputer -Credential $cred
$Applist1 = Invoke-command -filepath C:\BPSIT\PowershellandTextFiles\Windows\Get-IS.ps1 -computername $StrComputer
$SysItems1 = gwmi Win32_BIOS -Comp $StrComputer
$SysItems2 = gwmi Win32_TimeZone -Comp $StrComputer
$SysItems3 = gwmi Win32_WmiSetting -Comp $StrComputer
$ProcItems1 = gwmi Win32_Processor -Comp $StrComputer
$MemItems1 = gwmi Win32_PhysicalMemory -Comp $StrComputer
$memItems2 = gwmi Win32_PhysicalMemoryArray -Comp $StrComputer
$DiskItems = gwmi Win32_LogicalDisk -Comp $StrComputer
$NetItems = gwmi Win32_NetworkAdapterConfiguration -Comp $StrComputer |`
where{$_.IPEnabled -eq "True"}
# Populate General Sheet(1) with information
foreach ($objItem in $GenItems1){
$Sheet1.Cells.Item($intRow, 1) = $StrComputer
Switch($objItem.DomainRole)
{
0{$Sheet1.Cells.Item($intRow, 2) = "Stand Alone Workstation"}
1{$Sheet1.Cells.Item($intRow, 2) = "Member Workstation"}
2{$Sheet1.Cells.Item($intRow, 2) = "Stand Alone Server"}
3{$Sheet1.Cells.Item($intRow, 2) = "Member Server"}
4{$Sheet1.Cells.Item($intRow, 2) = "Back-up Domain Controller"}
5{$Sheet1.Cells.Item($intRow, 2) = "Primary Domain Controller"}
default{"Undetermined"}
}
$Sheet1.Cells.Item($intRow, 3) = $objItem.Manufacturer
$Sheet1.Cells.Item($intRow, 4) = $objItem.Model
$Sheet1.Cells.Item($intRow, 5) = $objItem.SystemType
$Sheet1.Cells.Item($intRow, 6) = $objItem.NumberOfProcessors
# Format Cell to display whole numbers PJJ 12-7-2016
$Sheet1.Cells.Item($intRow, 7).NumberFormat="0"
$Sheet1.Cells.Item($intRow, 7) = $objItem.TotalPhysicalMemory / 1024 / 1024
}
foreach ($objItem in $GenItems2){
$Sheet1.Cells.Item($intRow, 8) = $objItem.Caption
$Sheet1.Cells.Item($intRow, 9) = $objItem.csdversion
$Sheet1.Cells.Item($intRow, 10) = $objItem.SerialNumber
}
foreach ($objItem in $GenItems3){
$Sheet1.Cells.Item($intRow, 11) = $objItem.OA3xOriginalProductKey
}
# Populate Systems Sheet
foreach ($objItem in $SysItems1){
$Sheet2.Cells.Item($intRow, 1) = $StrComputer
$Sheet2.Cells.Item($intRow, 2) = $objItem.Name
$Sheet2.Cells.Item($intRow, 3) = $objItem.SMBIOSbiosVersion
$Sheet2.Cells.Item($intRow, 4) = $objItem.SerialNumber
}
foreach ($objItem in $SysItems2){
$Sheet2.Cells.Item($intRow, 5) = $objItem.Caption
}
foreach ($objItem in $SysItems3){
$Sheet2.Cells.Item($intRow, 6) = $objItem.BuildVersion
}
# Populate Processor Sheet
foreach ($objItem in $ProcItems1){
$Sheet3.Cells.Item($intRowCPU, 1) = $StrComputer
$Sheet3.Cells.Item($intRowCPU, 2) = $objItem.DeviceID+" "+$objItem.Name
$Sheet3.Cells.Item($intRowCPU, 3) = $objItem.Description
$Sheet3.Cells.Item($intRowCPU, 4) = $objItem.family
$Sheet3.Cells.Item($intRowCPU, 5) = $objItem.currentClockSpeed
$Sheet3.Cells.Item($intRowCPU, 6) = $objItem.l2cacheSize
$Sheet3.Cells.Item($intRowCPU, 7) = $objItem.UpgradeMethod
$Sheet3.Cells.Item($intRowCPU, 8) = $objItem.SocketDesignation
$intRowCPU = $intRowCPU + 1
}
# Populate Memory Sheet
$bankcounter = 1
foreach ($objItem in $memItems2){
$MemSlots = $objItem.MemoryDevices +1
foreach ($objItem in $MemItems1){
$Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
$Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
if($objItem.BankLabel -eq ""){
$Sheet4.Cells.Item($intRowMem, 3) = $objItem.DeviceLocator}
Else{$Sheet4.Cells.Item($intRowMem, 3) = $objItem.BankLabel}
$Sheet4.Cells.Item($intRowMem, 4) = $objItem.Capacity/1024/1024
$Sheet4.Cells.Item($intRowMem, 5) = $objItem.FormFactor
$Sheet4.Cells.Item($intRowMem, 6) = $objItem.TypeDetail
$intRowMem = $intRowMem + 1
$bankcounter = $bankcounter + 1
}
while($bankcounter -lt $MemSlots)
{
$Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
$Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
$Sheet4.Cells.Item($intRowMem, 3) = "is Empty"
$Sheet4.Cells.Item($intRowMem, 4) = ""
$Sheet4.Cells.Item($intRowMem, 5) = ""
$Sheet4.Cells.Item($intRowMem, 6) = ""
$intRowMem = $intRowMem + 1
$bankcounter = $bankcounter + 1
}
}
# Populate Disk Sheet
foreach ($objItem in $DiskItems){
$Sheet5.Cells.Item($intRowDisk, 1) = $StrComputer
Switch($objItem.DriveType)
{
2{$Sheet5.Cells.Item($intRowDisk, 2) = "Floppy"}
3{$Sheet5.Cells.Item($intRowDisk, 2) = "Fixed Disk"}
5{$Sheet5.Cells.Item($intRowDisk, 2) = "Removable Media"}
default{"Undetermined"}
}
$Sheet5.Cells.Item($intRowDisk, 3) = $objItem.DeviceID
# Format Cell to display whole numbers PJJ 12-7-2016
$Sheet5.Cells.Item($intRowDisk, 4).NumberFormat="0.0"
$Sheet5.Cells.Item($intRowDisk, 4) = $objItem.Size/1024/1024/1024
# Format Cell to display whole numbers PJJ 12-7-2016
$Sheet5.Cells.Item($intRowDisk, 5).NumberFormat="0.0"
$Sheet5.Cells.Item($intRowDisk, 5) = $objItem.FreeSpace/1024/1024/1024
$intRowDisk = $intRowDisk + 1
}
# Populate Network Sheet
foreach ($objItem in $NetItems){
$Sheet6.Cells.Item($intRowNet, 1) = $StrComputer
$Sheet6.Cells.Item($intRowNet, 2) = $objItem.Caption+" (enabled)"
$Sheet6.Cells.Item($intRowNet, 3) = $objItem.DHCPEnabled
$Sheet6.Cells.Item($intRowNet, 4) = $objItem.IPAddress
$Sheet6.Cells.Item($intRowNet, 5) = $objItem.IPSubnet
$Sheet6.Cells.Item($intRowNet, 6) = $objItem.DefaultIPGateway
$Sheet6.Cells.Item($intRowNet, 7) = $objItem.DNSServerSearchOrder
$Sheet6.Cells.Item($intRowNet, 8) = $objItem.FullDNSRegistrationEnabled
$Sheet6.Cells.Item($intRowNet, 9) = $objItem.WINSPrimaryServer
$Sheet6.Cells.Item($intRowNet, 10) = $objItem.WINSSecondaryServer
$Sheet6.Cells.Item($intRowNet, 11) = $objItem.WINSEnableLMHostsLookup
$intRowNet = $intRowNet + 1
}
# Populate Software Sheet
foreach ($objItem in $Applist1){
$Sheet7.Cells.Item($intRowSW, 1) = $Applist1|select Computername | ft -hidetableheaders
$Sheet7.Cells.Item($intRowSW, 2) = $Applist1|select name | ft -hidetableheaders
$Sheet7.Cells.Item($intRowSW, 3) = $Applist1|select version | ft -hidetableheaders
}
$intRow = $intRow + 1
$intRowCPU = $intRowCPU + 1
$intRowMem = $intRowMem + 1
$intRowDisk = $intRowDisk + 1
$intRowNet = $intRowNet + 1
}
}
# ==============================================================================================
# Function Name 'WMILookupCred'-Uses Alternative Credential-Gathers info using WMI.
# ==============================================================================================
Function WMILookupCred {
foreach ($StrComputer in $colComputers){
$GenItems1 = gwmi Win32_ComputerSystem -Comp $StrComputer -Credential $cred
$GenItems2 = gwmi Win32_OperatingSystem -Comp $StrComputer -Credential $cred
$GenItems3 = gwmi SoftwareLicensingService -Comp $StrComputer -Credential $cred
**$Applist1 = Invoke-command -filepath C:\BPSIT\PowershellandTextFiles\Windows\Get-is.ps1 -computername $StrComputer**
$SysItems1 = gwmi Win32_BIOS -Comp $StrComputer -Credential $cred
$SysItems2 = gwmi Win32_TimeZone -Comp $StrComputer -Credential $cred
$SysItems3 = gwmi Win32_WmiSetting -Comp $StrComputer -Credential $cred
$ProcItems1 = gwmi Win32_Processor -Comp $StrComputer -Credential $cred
$MemItems1 = gwmi Win32_PhysicalMemory -Comp $StrComputer -Credential $cred
$memItems2 = gwmi Win32_PhysicalMemoryArray -Comp $StrComputer -Credential $cred
$DiskItems = gwmi Win32_LogicalDisk -Comp $StrComputer -Credential $cred
$NetItems = gwmi Win32_NetworkAdapterConfiguration -Comp $StrComputer -Credential $cred |`
where{$_.IPEnabled -eq "True"}
# Populate General Sheet(1) with information
foreach ($objItem in $GenItems1){
$Sheet1.Cells.Item($intRow, 1) = $StrComputer
Switch($objItem.DomainRole)
{
0{$Sheet1.Cells.Item($intRow, 2) = "Stand Alone Workstation"}
1{$Sheet1.Cells.Item($intRow, 2) = "Member Workstation"}
2{$Sheet1.Cells.Item($intRow, 2) = "Stand Alone Server"}
3{$Sheet1.Cells.Item($intRow, 2) = "Member Server"}
4{$Sheet1.Cells.Item($intRow, 2) = "Back-up Domain Controller"}
5{$Sheet1.Cells.Item($intRow, 2) = "Primary Domain Controller"}
default{"Undetermined"}
}
$Sheet1.Cells.Item($intRow, 3) = $objItem.Manufacturer
$Sheet1.Cells.Item($intRow, 4) = $objItem.Model
$Sheet1.Cells.Item($intRow, 5) = $objItem.SystemType
$Sheet1.Cells.Item($intRow, 6) = $objItem.NumberOfProcessors
$Sheet1.Cells.Item($intRow, 7) = $objItem.TotalPhysicalMemory / 1024 / 1024
}
foreach ($objItem in $GenItems2){
$Sheet1.Cells.Item($intRow, 8) = $objItem.Caption
$Sheet1.Cells.Item($intRow, 9) = $objItem.csdversion
$Sheet1.Cells.Item($intRow, 10) = $objItem.SerialNumber
}
foreach ($objItem in $GenItems3){
$Sheet1.Cells.Item($intRow, 11) = $objItem.OA3xOriginalProductKey
}
#Populate Systems Sheet
foreach ($objItem in $SysItems1){
$Sheet2.Cells.Item($intRow, 1) = $StrComputer
$Sheet2.Cells.Item($intRow, 2) = $objItem.Name
$Sheet2.Cells.Item($intRow, 3) = $objItem.SMBIOSbiosVersion
$Sheet2.Cells.Item($intRow, 4) = $objItem.SerialNumber
}
foreach ($objItem in $SysItems2){
$Sheet2.Cells.Item($intRow, 5) = $objItem.Caption
}
foreach ($objItem in $SysItems3){
$Sheet2.Cells.Item($intRow, 6) = $objItem.BuildVersion
}
#Populate Processor Sheet
foreach ($objItem in $ProcItems1){
$Sheet3.Cells.Item($intRowCPU, 1) = $StrComputer
$Sheet3.Cells.Item($intRowCPU, 2) = $objItem.DeviceID+" "+$objItem.Name
$Sheet3.Cells.Item($intRowCPU, 3) = $objItem.Description
$Sheet3.Cells.Item($intRowCPU, 4) = $objItem.family
$Sheet3.Cells.Item($intRowCPU, 5) = $objItem.currentClockSpeed
$Sheet3.Cells.Item($intRowCPU, 6) = $objItem.l2cacheSize
$Sheet3.Cells.Item($intRowCPU, 7) = $objItem.UpgradeMethod
$Sheet3.Cells.Item($intRowCPU, 8) = $objItem.SocketDesignation
$intRowCPU = $intRowCPU + 1
}
#Populate Memory Sheet
$bankcounter = 1
foreach ($objItem in $memItems2){
$MemSlots = $objItem.MemoryDevices +1
foreach ($objItem in $MemItems1){
$Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
$Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
if($objItem.BankLabel -eq ""){
$Sheet4.Cells.Item($intRowMem, 3) = $objItem.DeviceLocator}
Else{$Sheet4.Cells.Item($intRowMem, 3) = $objItem.BankLabel}
$Sheet4.Cells.Item($intRowMem, 4) = $objItem.Capacity/1024/1024
$Sheet4.Cells.Item($intRowMem, 5) = $objItem.FormFactor
$Sheet4.Cells.Item($intRowMem, 6) = $objItem.TypeDetail
$intRowMem = $intRowMem + 1
$bankcounter = $bankcounter + 1
}
while($bankcounter -lt $MemSlots)
{
$Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
$Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
$Sheet4.Cells.Item($intRowMem, 3) = "is Empty"
$Sheet4.Cells.Item($intRowMem, 4) = ""
$Sheet4.Cells.Item($intRowMem, 5) = ""
$Sheet4.Cells.Item($intRowMem, 6) = ""
$intRowMem = $intRowMem + 1
$bankcounter = $bankcounter + 1
}
}
#Populate Disk Sheet
foreach ($objItem in $DiskItems){
$Sheet5.Cells.Item($intRowDisk, 1) = $StrComputer
Switch($objItem.DriveType)
{
2{$Sheet5.Cells.Item($intRowDisk, 2) = "Floppy"}
3{$Sheet5.Cells.Item($intRowDisk, 2) = "Fixed Disk"}
5{$Sheet5.Cells.Item($intRowDisk, 2) = "Removable Media"}
default{"Undetermined"}
}
$Sheet5.Cells.Item($intRowDisk, 3) = $objItem.DeviceID
# Format Cell to display whole numbers PJJ 12-7-2016
$Sheet5.Cells.Item($intRowDisk, 4).NumberFormat="0.0"
$Sheet5.Cells.Item($intRowDisk, 4) = $objItem.Size/1024/1024/1024
# Format Cell to display whole numbers PJJ 12-7-2016
$Sheet5.Cells.Item($intRowDisk, 5).NumberFormat="0.0"
$Sheet5.Cells.Item($intRowDisk, 5) = $objItem.FreeSpace/1024/1024/1024
$intRowDisk = $intRowDisk + 1
}
#Populate Network Sheet
foreach ($objItem in $NetItems){
$Sheet6.Cells.Item($intRowNet, 1) = $StrComputer
$Sheet6.Cells.Item($intRowNet, 2) = $objItem.Caption+" (enabled)"
$Sheet6.Cells.Item($intRowNet, 3) = $objItem.DHCPEnabled
$Sheet6.Cells.Item($intRowNet, 4) = $objItem.IPAddress
$Sheet6.Cells.Item($intRowNet, 5) = $objItem.IPSubnet
$Sheet6.Cells.Item($intRowNet, 6) = $objItem.DefaultIPGateway
$Sheet6.Cells.Item($intRowNet, 7) = $objItem.DNSServerSearchOrder
$Sheet6.Cells.Item($intRowNet, 8) = $objItem.FullDNSRegistrationEnabled
$Sheet6.Cells.Item($intRowNet, 9) = $objItem.WINSPrimaryServer
$Sheet6.Cells.Item($intRowNet, 10) = $objItem.WINSSecondaryServer
$Sheet6.Cells.Item($intRowNet, 11) = $objItem.WINSEnableLMHostsLookup
$intRowNet = $intRowNet + 1
}
# Populate Software Sheet
foreach ($objItem in $Applist1){
$Sheet7.Cells.Item($intRowSW, 1) = $Applist1|select Computername | ft -hidetableheaders
$Sheet7.Cells.Item($intRowSW, 2) = $Applist1|select name | ft -hidetableheaders
$Sheet7.Cells.Item($intRowSW, 3) = $Applist1|select version | ft -hidetableheaders
}
$intRow = $intRow + 1
$intRowCPU = $intRowCPU + 1
$intRowMem = $intRowMem + 1
$intRowDisk = $intRowDisk + 1
$intRowNet = $intRowNet + 1
}
}
# =============================================================================================
# Function Name 'ListComputers' - Enumerates ALL computer objects in AD
# ==============================================================================================
Function ListComputers {
$strCategory = "computer"
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(objectCategory=$strCategory)")
$colProplist = "name"
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}
$colResults = $objSearcher.FindAll()
foreach ($objResult in $colResults)
{$objComputer = $objResult.Properties; $objComputer.name}
}
# ==============================================================================================
# Function Name 'ListServers' - Enumerates ALL Servers objects in AD
# ==============================================================================================
Function ListServers {
$strCategory = "computer"
$strOS = "Windows*Server*"
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(&(objectCategory=$strCategory)(OperatingSystem=$strOS))")
$colProplist = "name"
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}
$colResults = $objSearcher.FindAll()
foreach ($objResult in $colResults)
{$objComputer = $objResult.Properties; $objComputer.name}
}
# ========================================================================
# Function Name 'ListTextFile' - Enumerates Computer Names in a text file
# Create a text file and enter the names of each computer. One computer
# name per line. Supply the path to the text file when prompted.
# ========================================================================
Function ListTextFile {
$strText = Read-Host "Enter the path for the text file"
$colComputers = Get-Content $strText
}
# ========================================================================
# Function Name 'SingleEntry' - Enumerates Computer from user input
# ========================================================================
Function ManualEntry {
$colComputers = Read-Host "Enter Computer Name or IP"
}
# ==============================================================================================
# Script Body
# ==============================================================================================
$erroractionpreference = "SilentlyContinue"
#Gather info from user.
Write-Host "********************************" -ForegroundColor Green
Write-Host "Computer Inventory Script" -ForegroundColor Green
Write-Host "By: Jesse Hamrick" -ForegroundColor Green
Write-Host "Created: 04/15/2009" -ForegroundColor Green
Write-Host "Contact: www.PowerShellPro.com" -ForegroundColor Green
Write-Host "********************************" -ForegroundColor Green
Write-Host " "
Write-Host "Admin rights are required to enumerate information." -ForegroundColor Green
Write-Host "Would you like to use an alternative credential?" -ForegroundColor Green
$credResponse = Read-Host "[Y] Yes, [N] No"
If($CredResponse -eq "y"){$cred = Get-Credential DOMAIN\USER}
Write-Host " "
Write-Host "Which computer resources would you like in the report?" -ForegroundColor Green
$strResponse = Read-Host "[1] All Domain Computers, [2] All Domain Servers, [3] Computer names from a File, [4] Choose a Computer manually"
If($strResponse -eq "1"){$colComputers = ListComputers | Sort-Object}
elseif($strResponse -eq "2"){$colComputers = ListServers | Sort-Object}
elseif($strResponse -eq "3"){. ListTextFile}
elseif($strResponse -eq "4"){. ManualEntry}
else{Write-Host "You did not supply a correct response, `
Please run script again." -foregroundColor Red}
Write-Progress -Activity "Getting Inventory" -status "Running..." -id 1
#New Excel Application
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
# Create 7 worksheets
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Add()
$Sheet = $Excel.Worksheets.Add()
$Sheet = $Excel.Worksheets.Add()
$Sheet = $Excel.Worksheets.Add()
$Sheet = $Excel.Worksheets.Add()
$Sheet = $Excel.Worksheets.Add()
# Assign each worksheet to a variable and
# name the worksheet.
$Sheet1 = $Excel.Worksheets.Item(1)
$Sheet2 = $Excel.WorkSheets.Item(2)
$Sheet3 = $Excel.WorkSheets.Item(3)
$Sheet4 = $Excel.WorkSheets.Item(4)
$Sheet5 = $Excel.WorkSheets.Item(5)
$Sheet6 = $Excel.WorkSheets.Item(6)
$Sheet7 = $Excel.WorkSheets.Item(7)
$Sheet1.Name = "General"
$Sheet2.Name = "System"
$Sheet3.Name = "Processor"
$Sheet4.Name = "Memory"
$Sheet5.Name = "Disk"
$Sheet6.Name = "Network"
$Sheet7.Name = "Computer Software"
#Create Heading for General Sheet
$Sheet1.Cells.Item(1,1) = "Device_Name"
$Sheet1.Cells.Item(1,2) = "Role"
$Sheet1.Cells.Item(1,3) = "HW_Make"
$Sheet1.Cells.Item(1,4) = "HW_Model"
$Sheet1.Cells.Item(1,5) = "HW_Type"
$Sheet1.Cells.Item(1,6) = "CPU_Count"
$Sheet1.Cells.Item(1,7) = "Memory_GB"
$Sheet1.Cells.Item(1,8) = "Operating_System"
$Sheet1.Cells.Item(1,9) = "SP_Level"
$Sheet1.Cells.Item(1,10) = "Serial Number"
$Sheet1.Cells.Item(1,11) = "License Key"
#Create Heading for System Sheet
$Sheet2.Cells.Item(1,1) = "Device_Name"
$Sheet2.Cells.Item(1,2) = "BIOS_Name"
$Sheet2.Cells.Item(1,3) = "BIOS_Version"
$Sheet2.Cells.Item(1,4) = "HW_Serial_#"
$Sheet2.Cells.Item(1,5) = "Time_Zone"
$Sheet2.Cells.Item(1,6) = "WMI_Version"
#Create Heading for Processor Sheet
$Sheet3.Cells.Item(1,1) = "Device_Name"
$Sheet3.Cells.Item(1,2) = "Processor(s)"
$Sheet3.Cells.Item(1,3) = "Type"
$Sheet3.Cells.Item(1,4) = "Family"
$Sheet3.Cells.Item(1,5) = "Speed_MHz"
$Sheet3.Cells.Item(1,6) = "Cache_Size_MB"
$Sheet3.Cells.Item(1,7) = "Interface"
$Sheet3.Cells.Item(1,8) = "#_of_Sockets"
#Create Heading for Memory Sheet
$Sheet4.Cells.Item(1,1) = "Device_Name"
$Sheet4.Cells.Item(1,2) = "Bank_#"
$Sheet4.Cells.Item(1,3) = "Label"
$Sheet4.Cells.Item(1,4) = "Capacity_GB"
$Sheet4.Cells.Item(1,5) = "Form"
$Sheet4.Cells.Item(1,6) = "Type"
#Create Heading for Disk Sheet
$Sheet5.Cells.Item(1,1) = "Device_Name"
$Sheet5.Cells.Item(1,2) = "Disk_Type"
$Sheet5.Cells.Item(1,3) = "Drive_Letter"
$Sheet5.Cells.Item(1,4) = "Capacity_GB"
$Sheet5.Cells.Item(1,5) = "Free_Space_GB"
#Create Heading for Network Sheet
$Sheet6.Cells.Item(1,1) = "Device_Name"
$Sheet6.Cells.Item(1,2) = "Network_Card"
$Sheet6.Cells.Item(1,3) = "DHCP_Enabled"
$Sheet6.Cells.Item(1,4) = "IP_Address"
$Sheet6.Cells.Item(1,5) = "Subnet_Mask"
$Sheet6.Cells.Item(1,6) = "Default_Gateway"
$Sheet6.Cells.Item(1,7) = "DNS_Servers"
$Sheet6.Cells.Item(1,8) = "DNS_Reg"
$Sheet6.Cells.Item(1,9) = "Primary_WINS"
$Sheet6.Cells.Item(1,10) = "Secondary_WINS"
$Sheet6.Cells.Item(1,11) = "WINS_Lookup"
#Create Heading for Software Sheet
$Sheet7.Cells.Item(1,1) = "System"
$Sheet7.Cells.Item(1,2) = "Software"
$Sheet7.Cells.Item(1,3) = "Version"
$colSheets = ($Sheet1, $Sheet2, $Sheet3, $Sheet4, $Sheet5, $Sheet6, $Sheet7)
foreach ($colorItem in $colSheets){
$intRow = 2
$intRowCPU = 2
$intRowMem = 2
$intRowDisk = 2
$intRowNet = 2
$WorkBook = $colorItem.UsedRange
$WorkBook.Interior.ColorIndex = 20
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True
}
If($credResponse -eq "y"){WMILookupCred}
Else{WMILookup}
#Auto Fit all sheets in the Workbook
foreach ($colorItem in $colSheets){
$WorkBook = $colorItem.UsedRange
$WorkBook.EntireColumn.AutoFit()
clear
}
Write-Host "*******************************" -ForegroundColor Green
Write-Host "The Report has been completed." -ForeGroundColor Green
Write-Host "*******************************" -ForegroundColor Green
# ========================================================================
# END of Script
# ========================================================================
