Archive for the ‘W2003’ tag
Exporting Data to Excel using PowerShell
I had to get a quick audit of systems and i needed to know OS Version, SP version, Memory and Disk Details.
One problem is that if you don’t know what disks you have exporting this to excel is difficult. I chose to dynamically create the headers for the excel file based on the drive letter names. This approach is by no means perfect as you rely on the fact that people have built the systems with sequential drive letters. Any drive letter out of sequence will disturb the excel header names.
Example: If you have 3 servers and two have letters C: and D: and the third has C: and E: this approach will not work for you. Luckily I know my servers have sequential drive letters.
Code:
$excel = New-Object -comobject Excel.Application $excel.visible = $True $wbook = $excel.Workbooks.Add() $wsheet = $wbook.Worksheets.Item(1) $wsheet.Cells.Item(1,1) = "Date" $wsheet.Cells.Item(1,2) = "Server" $wsheet.Cells.Item(1,3) = "Memory" $wsheet.Cells.Item(1,4) = "OSName" $wsheet.Cells.Item(1,5) = "SPVersion" $iRow = 2 $InputFile = "C:\Server.txt" #Input File Containing Servers to be Scanned $Servers = Get-Content $InputFile ForEach($Server in $Servers) { $wsheet.Cells.Item($iRow,1) = Get-Date $wsheet.Cells.Item($iRow,2) =$Server $AllCS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Server | Select-Object TotalPhysicalMemory ForEach($CS In $AllCS){$wsheet.Cells.Item($iRow,3) = $CS.TotalPhysicalMemory/1GB} $AllOS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Server ForEach($OS In $AllOS){$OSVersion = $OS.Caption;$SPVersion = $OS.CSDVersion} $wsheet.Cells.Item($iRow,4) = $OSVersion $wsheet.Cells.Item($iRow,5) = $SPVersion $logicalDisk = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $Server -Filter "DriveType=3" | Select-Object Caption,Size $j = 6 For($i=0;$i -le $LogicalDisk.Length - 1; $i++){ $wsheet.Cells.Item(1,$j) = $LogicalDisk[$i].Caption $wsheet.Cells.Item($iRow,$j) = $LogicalDisk[$i].Size/1GB $j++} $iRow++ } $range = $wsheet.UsedRange $range.Interior.ColorIndex = 19 $range.Font.ColorIndex = 11 $range.Font.Bold = $True $range.EntireColumn.AutoFilter() $range.EntireColumn.AutoFit() $excel.ActiveWorkbook.SaveAs("C:\Server_Data.xls") $excel.ActiveWorkbook.Close $excel.Application.Quit If (ps excel) { kill -name excel}
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Exporting Server Network Configuration to Excel using PowerShell
Win32_NetworkAdapterConfiguration
Win32_NetworkAdapter
Recently I had to examine over 100+ servers and their network configurations prior to a network IP address change. I also wanted the output in an Excel file for easy transport to my group. I used the above two WMI classes to gather all the required data i needed, the script requires the Excel application installed on the machine to have access to the excel.application COM object. I used an .TXT file as the input for the script, the file contains the servers that I wanted to scan. The file is formatted with one server name per line with no spaces.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
