{"id":11,"date":"2008-05-21T17:03:49","date_gmt":"2008-05-21T22:03:49","guid":{"rendered":"http:\/\/www.theboobiehatch.com\/tbh\/blaarg\/?p=11"},"modified":"2012-03-07T07:04:21","modified_gmt":"2012-03-07T13:04:21","slug":"time-to-do-work","status":"publish","type":"post","link":"https:\/\/www.briandeyo.us\/index.php\/2008\/05\/21\/time-to-do-work\/","title":{"rendered":"Time to do work?"},"content":{"rendered":"
\n

When I was working at IBM there was a daily report that I issued on the state of the environment, drivespace, and crap like that.\u00a0 I always told myself that if I ever had time I would automate much or all of the process as thoroughly as possible.\u00a0 As time was not on my side\u2026 that never happened, and instead I browsed my computer for 4 different servers, and 32 different directories every working day just so I knew how much drivespace I *didn\u2019t* have left.<\/p>\n

Now that I don\u2019t work for them anymore it is much easier to breathe and script a bit.\u00a0 Here\u2019s what 2 hours netted me:<\/p>\n

# Returns pre-formatted list of drivespace available to all logical drives on given servers
\n# outputs list to text file<\/p>\n

$output = \u201cc:\\scripting\\mtptfs.txt\u201d
\ndel $output
\n$evs = @(\u201devs01\u2033,\u201devs02\u2033,\u201devs03\u2033)<\/p>\n

foreach ($name in $evs) {
\ngwmi -computer $name -class win32_volume -filter \u201cdrivetype=3\u2033 |
\nsort-object Caption |
\nselect-object SystemName, Caption,{[math]::round($_.Capacity \/1GB, 2)},{[math]::round($_.freespace \/ 1GB, 2)} >> $output
\n}<\/p>\n

That is a neat little powershell script.\u00a0 It grabs info from a WMI object of each of my exchange virtual servers, and dumps some numbers to a text file,\u00a0in a not\u00a0so classy way.<\/p>\n

After another 2 hours I ditched all of that and came to a decent, but not completely done final product:<\/p>\n

###
\n###\u00a0 Returns pre-formatted list of drivespace available to all
\n###\u00a0 exchange virtual servers, and returns the information in a
\n###\u00a0 programmatically generated excel spreadsheet
\n###
\n\ufffd
\n\ufffd
\n\ufffd
\n### CREATES NEW EXCEL FILE USING COM OBJECT
\n$Excel = New-Object -Com Excel.Application
\n$Excel.visible = $True
\n$Excel = $Excel.Workbooks.Add()\n<\/p>\n

###SETS VARIABLE FOR WORKSHEETS
\n$p = 1\n<\/p>\n

###CREATES ARRAY FOR WHICH SERVERS TO QUERY
\n$evs = @(\u201dEVS01\u2033,\u201dEVS02\u2033,\u201dEVS03\u2033)
\n\ufffd
\n\ufffd
\n\ufffd
\n###LOOP FOR CREATING 1 EXCEL SHEET FOR EACH EVS
\nforeach ($name in $evs) {\n<\/p>\n

###WRITES COLUMN HEADERS ON EACH EXCEL SHEET
\n$Sheet = $Excel.WorkSheets.Item($p)
\n$Sheet.Cells.Item(1,1) = \u201cComputer\u201d
\n$Sheet.Cells.Item(1,2) = \u201cDrive\/MountPoint\u201d
\n$Sheet.Cells.Item(1,3) = \u201cTOTAL SIZE\u201d
\n$Sheet.Cells.Item(1,4) = \u201cFREE SPACE\u201d\n<\/p>\n

###CHANGES COLORS AND FONTS ON COLUMN HEADERS
\n$WorkBook = $Sheet.UsedRange
\n$WorkBook.Interior.ColorIndex = 8
\n$WorkBook.Font.ColorIndex = 11
\n$WorkBook.Font.Bold = $True
\n\ufffd
\n\ufffd
\n\ufffd
\n###SETS CELL STARTING POINT FOR WRITING EVS & DRIVESPACE INFORMATION
\n$intRow = 2\n<\/p>\n

###POWERSHELL SCRIPT FOR GETTING WMI INFORMATION ABOUT ALL LOGICAL VOLUMES ON AN EVS
\n$colItems = gwmi -computer $name -class win32_volume -filter \u201cdrivetype=3\u2033 | sort-object Caption\n<\/p>\n

###LOOP FOR WRITING WMI INFORMATION BY ROW
\nforeach ($objItem in $colItems) {
\n$Sheet.Cells.Item($intRow,1) = $objItem.SystemName
\n$Sheet.Cells.Item($intRow,2) = $objItem.Caption
\n$Sheet.Cells.Item($intRow,3) = $objItem.Capacity \/ 1GB
\n$Sheet.Cells.Item($intRow,4) = $objItem.freespace \/ 1GB
\n$intRow = $intRow + 1
\n\ufffd
\n}
\n\ufffd
\n###CHANGES WORKBOOK TO NEXT SHEET
\n$p = $p + 1\n<\/p>\n

###ADJUSTS COLUMN WIDTH FOR SHEET AUTOMATICALLY
\n$WorkBook.EntireColumn.AutoFit()\n<\/p>\n

###FORMATS THE CAPACITY INFORMATION INTO INTEGERS WITH TWO DECIMAL PLACES
\n$Sheet.Cells.NumberFormat = \u201c#.00\u2033\n<\/p>\n

}<\/p>\n

This script is much nicer.\u00a0 It creates a new excel file using the COM object, and dumps each EVS to its own sheet.\u00a0 This comes complete with colors.\u00a0 I have also included the pictures, circles,\u00a0and\u00a0arrows on the back of each one, and a paragraph describing what each one was.<\/p>\n

I didn\u2019t finish this completely because I ran out of time trying to get the borders completed.<\/p>\n

All that being said my 2nd week with powershell is actually a good experience.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

When I was working at IBM there was a daily report that I issued on the state of the environment, drivespace, and crap like that.\u00a0 I always told myself that if I ever had time I would automate much or all of the process as thoroughly as possible.\u00a0 As time was not on my side\u2026 …<\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[88,11,12],"class_list":["post-11","post","type-post","status-publish","format-standard","hentry","category-tech","tag-exchange","tag-powershell","tag-scripting"],"_links":{"self":[{"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/posts\/11","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/comments?post=11"}],"version-history":[{"count":4,"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/posts\/11\/revisions"}],"predecessor-version":[{"id":392,"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/posts\/11\/revisions\/392"}],"wp:attachment":[{"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/media?parent=11"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/categories?post=11"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.briandeyo.us\/index.php\/wp-json\/wp\/v2\/tags?post=11"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}