none
WMI Daten in Excel eintragen RRS feed

  • Frage

  • Sehr geehrte Foren Mitglieder.

     

    Ich habe eine Frage

    und Zwar, ich muss um unser Inventar neu zu erstellen ein Programm in Powershell schreiben, welches mir direkt in Excel alles einträgt.

    Nun das Problem

     

    Wen ich den Code ausführe, Stimmen die Spaltenüberschriften, nur die 2 Zeile wird immer überschrieben.

    Ich sollte es aber so haben, das alle Spalten ausgefüllt sind ( bis auf Service pack )

     

    Könnt Ihr mir helfen, das ich all diese Einträge habe ?

     

    Hier der Code

    ______________________________________________________________________________

    # Begin Script #



    $erroractionpreference = “SilentlyContinue”
    $a = New-Object -comobject Excel.Application
    $a.visible = $True
    $z = 1
    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item($z)
    $z = $z++
    #define column headers for the first row of the spreadsheet
    $ColHeaders = (”Rechner Name”,"Username",”Operating System”,”Service Packs”,
    “System Type”,”Install Date”,”Manufacturer”,”Model”,”Service Tag”,“Serial Number”,"MACadresse",
    ,"CPU",”Number of Processors”,”Total Physical Memory (GB)”,"Harddisk (GB)","Bios",
    “Last Reboot Time”,”Report TimeStamp”)
    $idx=0
    #write the column headings to the spreadsheet
    foreach ($title in $ColHeaders) {
    $idx+=1
    $c.Cells.Item(1,$idx) = $title
    }

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

    $intRow = 2

    ### Empty array for the computer name list ###
    $ComputerList = @($strComputer--)
    ### Run Net View for Netbios computer list ###
    $Net_view = & net view
    ### Clean up the data for the computer list ###
    Foreach ($Index in $Net_View)
    {$Net_View_Data = [regex]::Match(“$Index”,”[A-Z].*”) ## Change regex to match computer name patterns ###
    IF ($Net_View_Data -match ‘^$’)
    {
    ### Nothing to do here for this script ###
    }
    ELSE {
    $Clean_ComputerNameData = [regex]::Replace(“$Net_View_Data”,”\s.*$”,”")
    $ComputerList += (,($Clean_ComputerNameData))
    }
    }
    Foreach ($strComputer in $ComputerList)
    {
    ### This will trap cmdlet issues and can be used for error handling ###
    Trap {
    # Log error to a file if needed #
    Continue
    }

    $sheet.Cells.Item("2:2").value2="NULL"

    write-host “Processing…”
    $OS = gwmi -computername $strComputer Win32_OperatingSystem
    $Computer = gwmi -computername $strComputer Win32_computerSystem
    $Bios = gwmi -computername $strComputer win32_bios
    $MACa = gwmi Win32_NetworkAdapterConfiguration | Where-Object {$_.DefaultIPGateway -contains "172.16.2.1"}
    $cpu = gwmi win32_Processor
    $HD = gwmi win32_logicaldisk |Where-Object {$_.drivetype -match "3"}
    $Biosvers = gwmi win32_Bios
    $srvName = gwmi win32_bios
    #populate eash row of the spreadsheet with data collected from WMI
    $c.Cells.Item($intRow,1) = $srvName.__Server
    $c.Cells.Item($intRow,2) = $Env:USERNAME
    $c.Cells.Item($intRow,3) = $OS.Caption
    $c.Cells.Item($intRow,4) = $OS.CSDVersion
    $c.Cells.Item($intRow,5) = $Computer.SystemType
    $c.Cells.Item($intRow,6) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.InstallDate)
    $c.Cells.Item($intRow,7) = $Computer.Manufacturer
    $c.Cells.Item($intRow,8) = $Computer.Model
    $c.Cells.Item($intRow,9) = $Bios.serialnumber
    $c.Cells.Item($intRow,10) = $OS.SerialNumber
    $c.Cells.Item($intRow,11) = $MACa.MACaddress
    $c.Cells.Item($intRow,12) = $cpu.Name
    $c.Cells.Item($intRow,13) = $Computer.NumberOfProcessors
    $c.Cells.Item($intRow,14) = “{0:N0}” -f ($computer.TotalPhysicalMemory/1GB)
    $c.Cells.Item($intRow,15) = $HD.Size/1GB
    $c.Cells.Item($intRow,16) = $Biosvers.SoftwareElementID
    $c.Cells.Item($intRow,17) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.LastBootUpTime)
    $c.Cells.Item($intRow,18) = Get-date

    #$intRow += 1

    }

    Donnerstag, 18. November 2010 10:20

Antworten

  • Hi, hier so gehts:

    $a = New-Object -comobject Excel.Application
    $a.visible = $True
    $z = 1
    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item($z)
    $z = $z++
    #define column headers for the first row of the spreadsheet
    $ColHeaders = (”Rechner Name”,"Username",”Operating System”,”Service Packs”,
    “System Type”,”Install Date”,”Manufacturer”,”Model”,”Service Tag”,“Serial Number”,"MACadresse",
    ,"CPU",”Number of Processors”,”Total Physical Memory (GB)”,"Harddisk (GB)","Bios",
    “Last Reboot Time”,”Report TimeStamp”)
    $idx=0
    #write the column headings to the spreadsheet
    foreach ($title in $ColHeaders) {
    $idx+=1
    $c.Cells.Item(1,$idx) = $title
    }
    
    $d = $c.UsedRange
    $d.Interior.ColorIndex = 19
    $d.Font.ColorIndex = 11
    $d.Font.Bold = $True
    $intRow = 2
    
    ### Empty array for the computer name list ###
    $ComputerList = @()
    ### Run Net View for Netbios computer list ###
    $Net_view = & net view
    ### Clean up the data for the computer list ###
    Foreach ($Index in $Net_View) {
    	
    	IF ( $index -match "\\[A-Z].*"){
    		$Clean_ComputerNameData = [regex]::Replace($index.Substring(2),”\s.*$”,"")
    		$ComputerList += (,($Clean_ComputerNameData))
    	}
    }
    
    Foreach ($Computername in $ComputerList)
    {
    	### This will trap cmdlet issues and can be used for error handling ###
    	Trap {
    	# Log error to a file if needed #
    	Continue
    	}
    		$sheet.Cells.Item("2:2").value2="NULL"
    		write-host “Processing… $Computername$OS = gwmi -computername $Computername Win32_OperatingSystem 
    		$Computer = gwmi -computername $Computername Win32_computerSystem 
    		$Bios = gwmi -computername $Computername win32_bios 
    		$MACa = gwmi -computername $Computername Win32_NetworkAdapterConfiguration | Where-Object {$_.DefaultIPGateway -contains "172.16.2.1"}
    		$cpu = gwmi -computername $Computername win32_Processor 
    		$HD = gwmi -computername $Computername win32_logicaldisk |Where-Object {$_.drivetype -match "3"}
    		$Biosvers = gwmi -computername $Computername win32_Bios 
    		$srvName = gwmi -computername $Computername win32_bios 
    		#populate eash row of the spreadsheet with data collected from WMI
    		$c.Cells.Item($intRow,1) = $srvName.__Server
    		$c.Cells.Item($intRow,2) = $Env:USERNAME
    		$c.Cells.Item($intRow,3) = $OS.Caption
    		$c.Cells.Item($intRow,4) = $OS.CSDVersion
    		$c.Cells.Item($intRow,5) = $Computername.SystemType
    		$c.Cells.Item($intRow,6) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.InstallDate)
    		$c.Cells.Item($intRow,7) = $Computername.Manufacturer
    		$c.Cells.Item($intRow,8) = $Computername.Model
    		$c.Cells.Item($intRow,9) = $Bios.serialnumber
    		$c.Cells.Item($intRow,10) = $OS.SerialNumber
    		$c.Cells.Item($intRow,11) = $MACa.MACaddress
    		$c.Cells.Item($intRow,12) = $cpu.Name
    		$c.Cells.Item($intRow,13) = $Computername.NumberOfProcessors
    		$c.Cells.Item($intRow,14) = “{0:N0}” -f ($Computername.TotalPhysicalMemory/1GB)
    		$c.Cells.Item($intRow,15) = $HD.Size/1GB
    		$c.Cells.Item($intRow,16) = $Biosvers.SoftwareElementID
    		$c.Cells.Item($intRow,17) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.LastBootUpTime)
    		$c.Cells.Item($intRow,18) = Get-date
    		$intRow += 1
    }
    

    Ich habe auch deinen Comuternamenfilter vereinfacht und angepasst, vorher hat er auch die Titelzeile sowie die "der Befehl wurde..." -Zeile durchgelassen und versucht die entsprechenden Rechner zu erreichen.

    Grüße, Denniver

    Freitag, 19. November 2010 03:22
    Moderator

Alle Antworten

  • Moin,

    einmal schreibst Du was von zweiter Zeile, die immer ausgefüllt wird (was vielleicht mit der auskommentierten vorletzten Zeile Deines Scripts zusammenhängt?), dann schreibst Du von Spalten. Was also meinst Du genau?

    Testen kann ich das Script hier nicht mal auf die Schnelle, mein Excel 2010 mag keine neuen Worksheets basteln.

    Viele Grüße
    Olaf

    Donnerstag, 18. November 2010 12:50
  • Des weiteren hast Du irgendwann im Verlauf des Scripts vergessen, Dich auf die Remotemaschine zu beziehen (-computername $strComputer) und bekommst fuer einige Spalten daher immer den Input der lokalen Maschine. Viele Grüße Olaf
    Donnerstag, 18. November 2010 13:05
  • Hi, hier so gehts:

    $a = New-Object -comobject Excel.Application
    $a.visible = $True
    $z = 1
    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item($z)
    $z = $z++
    #define column headers for the first row of the spreadsheet
    $ColHeaders = (”Rechner Name”,"Username",”Operating System”,”Service Packs”,
    “System Type”,”Install Date”,”Manufacturer”,”Model”,”Service Tag”,“Serial Number”,"MACadresse",
    ,"CPU",”Number of Processors”,”Total Physical Memory (GB)”,"Harddisk (GB)","Bios",
    “Last Reboot Time”,”Report TimeStamp”)
    $idx=0
    #write the column headings to the spreadsheet
    foreach ($title in $ColHeaders) {
    $idx+=1
    $c.Cells.Item(1,$idx) = $title
    }
    
    $d = $c.UsedRange
    $d.Interior.ColorIndex = 19
    $d.Font.ColorIndex = 11
    $d.Font.Bold = $True
    $intRow = 2
    
    ### Empty array for the computer name list ###
    $ComputerList = @()
    ### Run Net View for Netbios computer list ###
    $Net_view = & net view
    ### Clean up the data for the computer list ###
    Foreach ($Index in $Net_View) {
    	
    	IF ( $index -match "\\[A-Z].*"){
    		$Clean_ComputerNameData = [regex]::Replace($index.Substring(2),”\s.*$”,"")
    		$ComputerList += (,($Clean_ComputerNameData))
    	}
    }
    
    Foreach ($Computername in $ComputerList)
    {
    	### This will trap cmdlet issues and can be used for error handling ###
    	Trap {
    	# Log error to a file if needed #
    	Continue
    	}
    		$sheet.Cells.Item("2:2").value2="NULL"
    		write-host “Processing… $Computername$OS = gwmi -computername $Computername Win32_OperatingSystem 
    		$Computer = gwmi -computername $Computername Win32_computerSystem 
    		$Bios = gwmi -computername $Computername win32_bios 
    		$MACa = gwmi -computername $Computername Win32_NetworkAdapterConfiguration | Where-Object {$_.DefaultIPGateway -contains "172.16.2.1"}
    		$cpu = gwmi -computername $Computername win32_Processor 
    		$HD = gwmi -computername $Computername win32_logicaldisk |Where-Object {$_.drivetype -match "3"}
    		$Biosvers = gwmi -computername $Computername win32_Bios 
    		$srvName = gwmi -computername $Computername win32_bios 
    		#populate eash row of the spreadsheet with data collected from WMI
    		$c.Cells.Item($intRow,1) = $srvName.__Server
    		$c.Cells.Item($intRow,2) = $Env:USERNAME
    		$c.Cells.Item($intRow,3) = $OS.Caption
    		$c.Cells.Item($intRow,4) = $OS.CSDVersion
    		$c.Cells.Item($intRow,5) = $Computername.SystemType
    		$c.Cells.Item($intRow,6) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.InstallDate)
    		$c.Cells.Item($intRow,7) = $Computername.Manufacturer
    		$c.Cells.Item($intRow,8) = $Computername.Model
    		$c.Cells.Item($intRow,9) = $Bios.serialnumber
    		$c.Cells.Item($intRow,10) = $OS.SerialNumber
    		$c.Cells.Item($intRow,11) = $MACa.MACaddress
    		$c.Cells.Item($intRow,12) = $cpu.Name
    		$c.Cells.Item($intRow,13) = $Computername.NumberOfProcessors
    		$c.Cells.Item($intRow,14) = “{0:N0}” -f ($Computername.TotalPhysicalMemory/1GB)
    		$c.Cells.Item($intRow,15) = $HD.Size/1GB
    		$c.Cells.Item($intRow,16) = $Biosvers.SoftwareElementID
    		$c.Cells.Item($intRow,17) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.LastBootUpTime)
    		$c.Cells.Item($intRow,18) = Get-date
    		$intRow += 1
    }
    

    Ich habe auch deinen Comuternamenfilter vereinfacht und angepasst, vorher hat er auch die Titelzeile sowie die "der Befehl wurde..." -Zeile durchgelassen und versucht die entsprechenden Rechner zu erreichen.

    Grüße, Denniver

    Freitag, 19. November 2010 03:22
    Moderator