Excel Server Storage Report help needed
the following script check disk space list of server(s) , output results directly excel.
what need on formatting of results.
currently column "g" show percentage of free disk space. i'd script able colour cell's in column "g"where servers free disk space less 10% in red , less 20% in orange.
could me this?
$strcomputer = get-content -path 'c:\servers.txt' $excel = new-object -com excel.application $excel.visible = $true $excel = $excel.workbooks.add() $sheet = $excel.worksheets.item(1) $sheet.cells.item(1,1) = “computer” $sheet.cells.item(1,2) = “drive letter” $sheet.cells.item(1,3) = “description” $sheet.cells.item(1,4) = “filesystem” $sheet.cells.item(1,5) = “total size in gb” $sheet.cells.item(1,6) = “free space in gb” $sheet.cells.item(1,7) = "free space in %" $workbook = $sheet.usedrange $workbook.entirecolumn.numberformat = "#.00" $workbook.entirecolumn.autofit() $workbook.entirecolumn.autofilter() $workbook.interior.colorindex = 19 $workbook.font.colorindex = 11 $workbook.font.bold = $true $workbook = $sheet.name = "storage results" $blue = 16711680 $workbook = $sheet.tab.color = $blue $introw = 2 $wmi1 = get-wmiobject -class “win32_logicaldisk” -filter "drivetype = 3" -namespace “root\cimv2" -computername $strcomputer $wmi2 = get-wmiobject -class “win32_logicaldisk” -filter "drivetype = 3" -namespace “root\cimv2" -computername $strcomputer | select name foreach ($objitem in $wmi1) { $sheet.cells.item($introw,1) = $objitem.systemname $sheet.cells.item($introw,2) = $objitem.deviceid $sheet.cells.item($introw,3) = $objitem.description $sheet.cells.item($introw,4) = $objitem.filesystem $sheet.cells.item($introw,5) = $objitem.size / 1gb $sheet.cells.item($introw,6) = $objitem.freespace / 1gb $sheet.cells.item($introw,7) = ($objitem.freespace / 1gb) / (($objitem.size / 1gb))*100 foreach($letter in $wmi2){$drive = $letter.name.split(":","") } $introw = $introw + 1 } $date = (get-date).tostring('_hhmm_dd_mm_yyyy') $excel.saveas("c:\results\storage$date.xlsx") ## closes excel ## $excel.activeworkbook.close $excel.application.quit() #[system.runtime.interopservices.marshal]::releasecomobject($excel) #start-sleep 1 #'excel processes: {0}' -f @(get-process excel -ea 0).count clear
this it formatting cells in powershell if condition. color orange not correct. i'll leave find out. red 255.
$strcomputer = get-content -path 'c:\servers.txt' $excel = new-object -com excel.application $excel.visible = $true $excel = $excel.workbooks.add() $sheet = $excel.worksheets.item(1) $sheet.cells.item(1,1) = “computer” $sheet.cells.item(1,2) = “drive letter” $sheet.cells.item(1,3) = “description” $sheet.cells.item(1,4) = “filesystem” $sheet.cells.item(1,5) = “total size in gb” $sheet.cells.item(1,6) = “free space in gb” $sheet.cells.item(1,7) = "free space in %" $workbook = $sheet.usedrange $workbook.entirecolumn.numberformat = "#.00" $workbook.entirecolumn.autofit() $workbook.entirecolumn.autofilter() $workbook.interior.colorindex = 19 $workbook.font.colorindex = 11 $workbook.font.bold = $true $workbook = $sheet.name = "storage results" $blue = 16711680 $workbook = $sheet.tab.color = $blue $introw = 2 $wmi1 = get-wmiobject -class “win32_logicaldisk” -filter "drivetype = 3" -namespace “root\cimv2" -computername $strcomputer $wmi2 = get-wmiobject -class “win32_logicaldisk” -filter "drivetype = 3" -namespace “root\cimv2" -computername $strcomputer | select name foreach ($objitem in $wmi1) { $sheet.cells.item($introw,1) = $objitem.systemname $sheet.cells.item($introw,2) = $objitem.deviceid $sheet.cells.item($introw,3) = $objitem.description $sheet.cells.item($introw,4) = $objitem.filesystem $sheet.cells.item($introw,5) = $objitem.size / 1gb $sheet.cells.item($introw,6) = $objitem.freespace / 1gb $freespace = $sheet.cells.item($introw,7) = ($objitem.freespace / 1gb) / (($objitem.size / 1gb))*100 if ($freespace -lt 10) {$sheet.cells.item($introw,7).interior.color = 255} elseif ($freespace -lt 20) {$sheet.cells.item($introw,7).interior.color = 123} foreach($letter in $wmi2){$drive = $letter.name.split(":","") } $introw = $introw + 1 } $date = (get-date).tostring('_hhmm_dd_mm_yyyy') $excel.saveas("c:\results\storage$date.xlsx") ## closes excel ## #$excel.activeworkbook.close #$excel.application.quit()
([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
Windows Server > Windows PowerShell
Comments
Post a Comment