硬件信息统计_读取输出Excel_显示进度
#该脚本实现功能：通过WMI读取计算机硬件信息：计算机名称、IP地址、计算机型号、计算机序列号、BIOS版本、操作系统版本、CPU型号、CPU核心数量、内存大小、分区大小、网卡使用状态、网卡个数，并可显示脚本执行进度；
#“$strpath”变量定义了计算机信息存储位置，每列信息都要填写完整；“$strpath2”变量定义了结果保存位置，该文件自动生成，无需手工创建；
#如果该计算机可以ping通，则取其相关信息；如果无法ping通，则只输出其计算机IP地址信息
#####################################################################################
#读取存储计算机信息的excel
$strpath="e:\servers.xlsx"
$excel=new-object -comobject excel.application
$WorkBook = $excel.Workbooks.Open($strpath)
$WorkSheet = $WorkBook.Worksheets.Item(1)
#已用行数
$usedrows = $worksheet.usedrange.rows.count
####################################################
#将结果保存到存储计算机硬件信息的excel，在此检查该文件是否已存在
$strpath2="e:\ServerInfo_hardwars.xlsx"
If (Test-Path $strpath2)
{
$vbs = New-Object -ComObject WScript.Shell
$vbs.popup($strpath2 + " 文件已存在，请重新输入保存位置!",0,"提示")
#需要清理并关闭已打开的Excel进程
$excel.Quit()
$WorkSheet =$null
$WorkBook = $null
$excel = $null
[GC]::Collect()
Exit
}
$excel2=new-object -comobject excel.application
$excel2.visible=0
$WorkBook2=$excel2.workbooks.add()
$WorkSheet2=$WorkBook2.worksheets.item(1)
####################################################
For($i=2;$i -le $usedrows;$i++)
{
#取计算机IP，用户名和密码
$server = $WorkSheet.Cells.Item($i,2).value2
$UserName = $WorkSheet.Cells.Item($i,3).value2
$passwd = $WorkSheet.Cells.Item($i,4).value2
#将纯文本字符串“密码”转换为安全字符串，并将结果存储在 $Password变量中
$Password = ConvertTo-SecureString "$passwd" -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential($UserName,$Password)
#显示进度条，显示百分比 和 当前第几个
$w=($i-1).tostring()+'/'+($usedrows-1).tostring()
Write-Progress -Activity "进度显示" -status "正在处理 $server，请耐心等待" -percentcomplete (($i-1)/($usedrows-1)*100) -currentoperation $w
#################################################################################################
#先判断计算机是否可以ping通，如ping不通，则只输入该计算机名称
If (Test-Connection $server -count 1 -quiet)
{
#获取计算机名称，IP地址，型号、BIOS版本
$system = Get-WmiObject -Class Win32_ComputerSystem -Credential $cred -ComputerName $server
$os = Get-WmiObject -Class Win32_OperatingSystem -Credential $cred -ComputerName $server
$bios = Get-WmiObject -Class Win32_BIOS -Credential $cred -ComputerName $server
#system.Domain 域名 $system.Caption 计算机名
#################################################################################################
#获取CPU名称，单颗CPU核心个数*CPU个数，总核心个数
$cpus = Get-WmiObject win32_processor -Credential $cred -ComputerName $server
$cpucount = 0
Foreach ($cpu in $cpus)
{
If ($cpu.DeviceID -ne $null)
{$cpucount += 1}
}
#################################################################################################
#获取单条内存大小，将所有内存信息转换为字符串形式存储到一个单元格中
#获取内存总量
$memorys = Get-WmiObject -Class Win32_PhysicalMemory -Credential $cred -ComputerName $server
$memorylist = $null
$memorysize_sum = $null
Foreach ($memory in $memorys)
{
$memorylist += ($memory.capacity/1024/1024/1024).tostring("F1")+"GB + "
$memorysize_sum += $memory.capacity
}
#################################################################################################
#获取分区大小，将所有分区信息转换为字符串形式存储到一个单元格中
$drives = Get-WmiObject -Class Win32_LogicalDisk -Credential $cred -Filter "DriveType=3" -ComputerName $server
$drivelist = $null
Foreach($drive in $drives)
{$drivelist += $drive.deviceid + ($drive.size/1024/1024/1024).tostring("F1")+"GB，"}
#获取磁盘大小，将所有磁盘信息转换为字符串形式存储到一个单元格中
$disks = Get-WmiObject Win32_Diskdrive -Credential $cred -ComputerName $server
$disklist = $null
$disksize_sum = $null
Foreach ($disk in $disks)
{$disklist += ($disk.deviceid.replace("\\.\PHYSICALDRIVE","Disk") +"：" + ($disk.size/1024/1024/1024).tostring("F1")+"GB，")
$disksize_sum+=$disk.size
}
#################################################################################################
#获取网卡
$netcards = Get-WmiObject Win32_NetworkAdapter -Credential $cred -ComputerName $server |Where {$_.PhysicalAdapter -eq $true}
$netcardlist = $null
$netcardcount = 0
Foreach($netcard in $netcards)
{$netcardlist += $netcard.Name + ":" + $netcard.NetEnabled + "`n"
If ($netcard.GUID -ne $null)
{$netcardcount +=1}
}
#################################################################################################
#将上面的信息写入到Excel
$WorkSheet2.cells.item($i+1,1)= $system.Name
$WorkSheet2.cells.item($i+1,2)= $server
$WorkSheet2.cells.item($i+1,3)= $system.Model
$WorkSheet2.cells.item($i+1,4)= $bios.SerialNumber
$WorkSheet2.cells.item($i+1,5)= $bios.Manufacturer + " " + $bios.SMBIOSBIOSVersion
$WorkSheet2.cells.item($i+1,6)= $os.Caption + $os.Version
$WorkSheet2.cells.item($i+1,7)= $cpu.name
$WorkSheet2.cells.item($i+1,8)= [string]$cpu.NumberOfCores + '*' + [string]$cpucount + "C"
$WorkSheet2.cells.item($i+1,9)= $cpu.NumberOfCores * $cpucount
$WorkSheet2.cells.item($i+1,10)= $memorylist.substring(0,$memorylist.length-3)
$WorkSheet2.cells.item($i+1,11)= ($memorysize_sum/1024/1024/1024).tostring("F1")+"GB"
$WorkSheet2.cells.item($i+1,12)= $drivelist.substring(0,$drivelist.length-1)
$WorkSheet2.cells.item($i+1,13)= $disklist.substring(0,$disklist.length-1)
$WorkSheet2.cells.item($i+1,14)= ($disksize_sum/1024/1024/1024).tostring("F1")+"GB"
$WorkSheet2.cells.item($i+1,15)= $netcardlist
$WorkSheet2.cells.item($i+1,16)= $netcardcount
}
else
{$WorkSheet2.cells.item($i+1,2)= $server}
}
#################################################################################################
#写入第一行标题列
$WorkSheet2.cells.item(1,1)="系统信息"
$WorkSheet2.cells.item(1,7)="CPU"
$WorkSheet2.cells.item(1,10)="内存"
$WorkSheet2.cells.item(1,12)="磁盘"
$WorkSheet2.cells.item(1,15)="网卡"
#写入第二行标题列
$WorkSheet2.cells.item(2,1)="计算机名"
$WorkSheet2.cells.item(2,2)="IP地址"
$WorkSheet2.cells.item(2,3)="计算机型号"
$WorkSheet2.cells.item(2,4)="计算机序列号"
$WorkSheet2.cells.item(2,5)="BIOS版本"
$WorkSheet2.cells.item(2,6)="操作系统版本"
$WorkSheet2.cells.item(2,7)="CPU名称"
$WorkSheet2.cells.item(2,8)="单颗CPU核心数量*CPU个数"
$WorkSheet2.cells.item(2,9)="CPU核心数量"
$WorkSheet2.cells.item(2,10)="单条内存大小"
$WorkSheet2.cells.item(2,11)="内存总量"
$WorkSheet2.cells.item(2,12)="分区大小"
$WorkSheet2.cells.item(2,13)="单块磁盘大小"
$WorkSheet2.cells.item(2,14)="磁盘总大小"
$WorkSheet2.cells.item(2,15)="网卡名称：启用状态"
$WorkSheet2.cells.item(2,16)="网卡个数"
#第一行标题设置为粗体
$WorkSheet2.cells.item(1,1).font.bold=$true
$WorkSheet2.cells.item(1,7).font.bold=$true
$WorkSheet2.cells.item(1,10).font.bold=$true
$WorkSheet2.cells.item(1,12).font.bold=$true
$WorkSheet2.cells.item(1,15).font.bold=$true
#第二行标题设置为红色字体
$WorkSheet2.cells.item(2,1).font.ColorIndex=3
$WorkSheet2.cells.item(2,2).font.ColorIndex=3
$WorkSheet2.cells.item(2,3).font.ColorIndex=3
$WorkSheet2.cells.item(2,4).font.ColorIndex=3
$WorkSheet2.cells.item(2,5).font.ColorIndex=3
$WorkSheet2.cells.item(2,6).font.ColorIndex=3
$WorkSheet2.cells.item(2,7).font.ColorIndex=3
$WorkSheet2.cells.item(2,8).font.ColorIndex=3
$WorkSheet2.cells.item(2,9).font.ColorIndex=3
$WorkSheet2.cells.item(2,10).font.ColorIndex=3
$WorkSheet2.cells.item(2,11).font.ColorIndex=3
$WorkSheet2.cells.item(2,12).font.ColorIndex=3
$WorkSheet2.cells.item(2,13).font.ColorIndex=3
$WorkSheet2.cells.item(2,14).font.ColorIndex=3
$WorkSheet2.cells.item(2,15).font.ColorIndex=3
$WorkSheet2.cells.item(2,16).font.ColorIndex=3
#################################################################################################
#保存输出结果，清理并关闭已打开的Excel进程
$excel2.ActiveWorkbook.SaveAs($strPath2)
$excel.Quit()
$WorkSheet =$null
$WorkBook = $null
$excel = $null
[GC]::Collect()
$excel2.Quit()
$WorkSheet2 =$null
$WorkBook2 = $null
$excel2 = $null
[GC]::Collect()
获取IP
$w = gwmi Win32_NetworkAdapterConfiguration |where {$_.description -like "atheros*"}|select ip
$m = $w|foreach {$_.ipaddress}
$m.tostring().split(".")[1]
获取DNS地址
$a=gwmi Win32_NetworkAdapterConfiguration|?{$_.defaultipgateway -ne $null}
$a.dnsserversearchorder
