May 2007 - Posts
As human being, we all like to complain every now and then and blame others if things don’t turn the way we would like.
Recently I am on the road a lot, I felt all of us can learn something from GPS!
When we get off at the wrong exit or make a wrong turn, the GPS never complains or screaming or try to find out whose fault it is. It always says “recalculating” and trying to figure out the best route for us at that moment. It doesn’t even change the tone no matter how off we are.
Wouldn’t it be a better world if we all reprogram ourselves with some GPS attitude?
Go GPS!
On average, a physician will interrupt a patient describing her symptoms within eighteen seconds. In that short time, many doctors decide on the likely diagnosis and best treatment. Often, decisions made this way are correct, but at crucial moments they can also be wrong – with catastrophic consequences!
Keep the above in mind when next time you or your loved ones see the doctor.
The script below is nothing special. It is usual WMI query and reporting in excel stuff. The reason I want to post this script is because this line: where {$_.name -match "[HC]* NC*"}.
This is so called PowerShell “Wildcard expressions”. It replaces the following code:
{$_.name -like "HP NC*"-or $_.name -like "COMPAQ NC*"}.
As expected, it returns all the Network Adapter from HP and COMPAQ
$a = New-Object -comobject Excel.Application
$a.visible = $True
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Cells.Item(1,1) = "Machine Name"
$c.Cells.Item(1,2) = "Network Adapter Name"
$c.Cells.Item(1,3) = "Report Time Stamp"
$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$intRow = 2
Foreach ($strComputer in get-content C:\PS\bkups.txt)
{
$c.Cells.Item($intRow,1) = $strComputer
$Adapters = Get-WMIObject Win32_NetworkAdapter -computer $strcomputer |where {$_.name -match "[HC]* NC*"}
Foreach ($Nic in $Adapters)
{
$c.Cells.Item($intRow,2) = $Nic.name
$c.Cells.Item($intRow,3) = Get-Date
$intRow = $intRow + 1
}
}
$d.EntireColumn.AutoFit()
cls
In my previous three posts, I described the technique to open excel workbook/worksheet and then get our focus on the working range. So we can use the ranges to do some work.
Here is a PS script to compare a list of names in one excel column X to a list of names in another column Y. If I find the column X name in column Y, I will write a simple yes or no in the column next to column X and color it differently.
#Create excel object and make the object visible
$excel = New-Object -comobject Excel.Application
$excel.visible = $True
# Use Inputbox to get the full path of the target file and create a $workbook object to hold it
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the full path of your excel file`",`"Workbook Name`") end function" )
$WorkbookFullPath = $x.eval("getInput")
$workbook = $excel.Workbooks.Open($WorkbookFullPath)
# Use Inputbox to get the target worksheet in the target file and create a $worksheet object to hold it
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the worksheet name`",`"WorkSheet Name`") end function" )
$worksheetname = $x.eval("getInput")
$worksheet = $workbook.Worksheets.Item($WorksheetName)
#Create $SearchRange and $CompareRange objects to hold the ranges – the ranges already defined in target file
$SearchRange = $worksheet.Range("SearchSource")
$Comparerange = $worksheet.Range("SearchTarget")
#Loop through $SearchRange and determine if the names can be found in $Comparerange - start from row 2 because the first row contain headers.
for($i = 2; $i -le $SearchRange.count; $i++)
{
$strComputer = $worksheet.cells.item($i,3).Text
$search = $Comparerange.find($strcomputer)
if($search -eq $null)
{
$worksheet.cells.Item($i,4).Interior.ColorIndex = 3
$worksheet.cells.Item($i,4) = "No"
}
Else
{
$worksheet.cells.Item($i,4).Interior.ColorIndex = 4
$worksheet.cells.Item($i,4) = "Yes"
}
}
$i = $i + 1
# Save the results and gracefuly shutdown excel.
$excel.save()
$a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet)
$a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook)
$a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
Here I will extend a little bid further from my two previous posts and to open a working range inside the target worksheet.
################################################################################
$excel = new-object -comobject Excel.Application
$excel.visible = $True
# Inputbox
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the full path of your excel file`",`"Workbook Name`") end function" )
$excelfilename = $x.eval("getInput")
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the worksheet name`",`"WorkSheet Name`") end function" )
$worksheetname = $x.eval("getInput")
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the Range name`",`"Working Range Name`") end function" )
$rangename = $x.eval("getInput")
function global:Open-Range()
{
param($excel, [string]$excelfilename, [string]$worksheetname,[string]$rangename)
if (!$(test-path $excelfilename))
{
write-host "File doesn't exist..."
return $null
}
if ([string]::IsNullOrEmpty($worksheetName))
{
write-host "Worksheet name cannot be null or empty."
return $null
}
if ([string]::IsNullOrEmpty($rangeName))
{
write-host "Range name cannot be null or empty."
return $null
}
$worksheet = $($excel.Workbooks.Open($excelfilename)).Worksheets.Item($worksheetName)
$range = $worksheet.Range($RangeName)
}
Open-Range $excel $excelfilename $worksheetname $rangename
#################################################################################
Now we have the target range open, we could manipulate the data, create an array out of certain column, do some comparision, update the open excel sheet and save the results - to be continued…
In my previous post, I wrote a script to open an excel workbook, now I extend that function a little bid to open your target worksheet in excel workbook.
$excel = new-object -comobject Excel.Application
$excel.visible = $True
# Inputbox
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the full path of your excel file`",`"Workbook Name`") end function" )
$excelfilename = $x.eval("getInput")
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the worksheet name`",`"WorkSheet Name`") end function" )
$worksheetname = $x.eval("getInput")
function global:Open-Worksheet()
{
param($excel, [string]$excelfilename, [string]$worksheetname)
if (!$(test-path $excelfilename))
{
write-host "File doesn't exist..."
return $null
}
if ([string]::IsNullOrEmpty($WorksheetName))
{
write-host "Worksheet name cannot be null or empty."
return $null
}
$worksheet = $($excel.Workbooks.Open($excelfilename)).Worksheets.Item($WorksheetName)
}
Open-Worksheet $excel $excelfilename $worksheetname
In my previous posting, I used excel comobject extensively but mainly limited to create new excel object and write report to it.
Here is a little script to open an existing excel workbook.
$excel = new-object -comobject Excel.Application
$excel.visible = $True
# Inputbox
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the full path of your excel file`",`"Workbook Name`") end function" )
$excelfilename = $x.eval("getInput")
function global:Open-Excel()
{
param ($excel, [string]$excelfilename)
if (!$(test-path $excelfilename))
{
write-host "File doesn't exist..."
return $null
}
$excelfile = $excel.Workbooks.Open($excelfilename)
}
Open-Excel $excel $excelfilename
When we run into “low disk space” error, we will need to figure out what folder or file causes the error. By default, Windows only show file size not folder size. We sometime have to click through the folders to find the “culprit.
I developed the below PS script to help smooth this process. What it does is to do a recurse scan of each sub folders inside target folder and calculate the total length of the files and write a report in excel.
$a = New-Object -comobject Excel.Application
$a.visible = $True
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Cells.Item(1,1) = "Machine Name"
$c.Cells.Item(1,2) = "Target Folder"
$c.Cells.Item(1,3) = "Max File Name"
$c.Cells.Item(1,4) = "Max File Sizem"
$c.Cells.Item(1,5) = "Folder Name"
$c.Cells.Item(1,6) = "Folder Size(MB)"
$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$intRow = 2
$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$intRow = 2
# Inputbox
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter Your Computer Name`",`"Computer Name`") end function" )
$Computer = $x.eval("getInput")
$c.Cells.Item($intRow,1) = $Computer.ToUpper()
$y = new-object -comobject MSScriptControl.ScriptControl
$y.language = "vbscript"
$y.addcode("function getInput() getInput = inputbox(`"Enter The Target Drive or Folder Name`",`"Folder Name`") end function" )
$TargetFolder = $y.eval("getInput")
$c.Cells.Item($intRow,2) = $TargetFolder.ToUpper()
$args = "\\$computer\$TargetFolder\"
$items = get-childitem $args | sort length -desc
$MaxFile = $items[0]
$c.Cells.Item($intRow,3) = $MaxFile.name
if ($MaxFile.length/1MB -ge 1024)
{$c.Cells.Item($intRow,4).Interior.ColorIndex = 3
$c.Cells.Item($intRow,4) = "{0:N0}" -f ($MaxFile.length/1MB)}
Else
{$c.Cells.Item($intRow,4) = "{0:N0}" -f ($MaxFile.length/1MB)}
$folders = $items | where{$_ -is [system.IO.directoryInfo]}
foreach ($folder in $folders)
{
$files = get-childitem ([String]$args + $folder.name) -include *.* -recurse -force
$foldersize=0
foreach ($f in $files) {$foldersize+=$f.length}
$c.Cells.Item($intRow,5) = $Folder.name
if ($foldersize/1MB -ge 1024)
{$c.Cells.Item($intRow,6).Interior.ColorIndex = 3
$c.Cells.Item($intRow,6) = "{0:N0}" -f ($foldersize/1MB)}
Else
{$c.Cells.Item($intRow,6) = "{0:N0}" -f ($foldersize/1MB)}
$intRow = $intRow + 1
}
$d.EntireColumn.AutoFit()
Below is a very interesting article I read from Yahoo this morning and it is very enlightening.
Thin people – welcome to the fat club! 
DIET: Thin people may be fat inside - Yahoo! News.
Sometime you get a list of hostname with fully qualified domain name but you might just need the computer name part.
Here is a powershell script to loop through the list and remove everything after “.” and create a new list with only computer name.
$PreSecond = get-content PreSecond.txt
New-Item "C:\Myworkplace\ps\Second.txt" -Type file
foreach ($Hostname in $PreSecond)
{
$second = $Hostname.split('.')[0]
Add-content "C:\Myworkplace\ps\Second.txt" $second
}
Recent research shows many of us knew all along: Our moods dictate what we eat. Researchers studied the diets of people to show how personality and foods collide – how our moods may steer us to certain foods, on the basis of their physical characteristics. The study theorized that many moods send specific signals; for example, stressed adrenal glands could be sending salt-craving signals. So what does your favorite turn-to food say about you?
If you reach for… You may be feeling
Tough foods, like meat, or hard and crunchy foods Angry
Sugars Depressed
Soft and sweet foods, like ice cream Anxious
Salty foods Stressed
Bulky, fill-you-up foods, like crackers and pasta Lonely, sexually frustrated
Anything and everything Jealous
$erroractionpreference = "SilentlyContinue"
$a = New-Object -comobject Excel.Application
$a.visible = $True
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Cells.Item(1,1) = "Machine Name"
$c.Cells.Item(1,2) = "PatchStatus"
$c.Cells.Item(1,3) = "Report Time Stamp"
$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$intRow = 2
Foreach ($strComputer in get-content C:\MachineList.Txt)
{
$c.Cells.Item($intRow,1) = $strComputer
$PatchStatus = Get-WMIObject Win32_QuickFixEngineering -computer $strcomputer |where {$_.HotFixID -eq "KB925902"}
If($PatchStatus -eq $Null)
{
$c.Cells.Item($intRow,2).Interior.ColorIndex = 3
$c.Cells.Item($intRow,2) = "NO"
}
Else
{
$c.Cells.Item($intRow,2).Interior.ColorIndex = 4
$c.Cells.Item($intRow,2) = "YES"
}
$c.Cells.Item($intRow,3) = Get-Date
$intRow = $intRow + 1
}
$d.EntireColumn.AutoFit()
cls
Physical activity is vital to inflate our health and deflate our waist size. Building muscle is one components, but cardiovascular training and increasing our flexibility are also part of our waist management plan. Together, the three components of exercise will have numerous effects on our body:
- Exercise increases our metabolism so that we burn energy at a higher rate, and it will also reduces our appetite to some extent.
- Exercise will help us lose the extra weight that’s stressing our joints. We’ll feel less pain in our knees, hips, ankles and back. That will put us into a positive cycle behavior, so that we’ll have the desire to exercise more.
- Exercise stimulates the release of endorphins, which stimulate the pleasure centers in the brain. It will give us a sense of control, which is associated with a decreased need to eat out of control.
- Exercise helps decrease depression and increases positive attitude, so we make other positive choices and don’t have to use food as our medication. That will also help prevent our couch, chair and bed from becoming anti-waist management devices.
- Exercise keeps our blood vessels open and clog-free, thus decreasing our risk of obesity-related morbidities like high blood pressure, elevated lousy cholesterol, memory problems and heart attack.
This by no means an exhausted lists but just trying to make the point.
Ever wondered where are all the fats stored in your body?
We all have three kinds of fat:
fat in our bloodstream – Triglycerides (Reported in your blood test)
Subcutaneous fat – which lies just underneath the skin’s surface
Omentum fat – a fatty layer of tissue located inside the belly that hangs underneath the muscles in your stomach (That’s why some men with beer guts have hard-as-keg bellies, their fat is underneath the muscle.
Study shows the fat in your thighs doesn't matter as much to your health as does omentum fat and the omentum fat is more harmful than subcutaneous fat.
So the real story of your body isn’t measured by scales but by your waist size – and what fat does inside your blood and arteries.
Here is an upgraded version of my previous script:
http://myitforum.com/cs2/blogs/yli628/archive/2007/02/13/powershell-script-to-get-symantec-antivirus-client-version-and-virus-definition-date.aspx
I modified the Date Difference Calculation and also added some colors to the excel report.
$erroractionpreference = "SilentlyContinue"
$a = New-Object -comobject Excel.Application
$a.visible = $True
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Cells.Item(1,1) = "Machine Name"
$c.Cells.Item(1,2) = "Parent Server"
$c.Cells.Item(1,3) = "Client Group"
$c.Cells.Item(1,4) = "SAV Version"
$c.Cells.Item(1,5) = "Virus Definition"
$c.Cells.Item(1,6) = "Rev Number"
$c.Cells.Item(1,7) = "Status"
$c.Cells.Item(1,8) = "Report Time Stamp"
$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$intRow = 2
$colComputers = get-content C:\Myworkplace\Clientlist.txt
foreach ($strComputer in $colComputers)
{
$c.Cells.Item($intRow,1) = $strComputer
Function GetRegInfo
{
$key="Software\INTEL\LANDesk\VirusProtect6\CurrentVersion"
$regkey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]::LocalMachine, $strComputer)
$regKey = $regKey.OpenSubKey($key)
$Parent = $regKey.GetValue("parent")
$strParent = $Parent.substring(0,7)
If($strParent -eq 'OPCOSAV')
{
$c.Cells.Item($intRow,2).Interior.ColorIndex = 7
$c.Cells.Item($intRow,2) = $Parent
}
Else
{
$c.Cells.Item($intRow,2) = $Parent
}
$c.Cells.Item($intRow,3) = $regKey.GetValue("ClientGroup")
$productver = $regKey.GetValue("ProductVersion")
Switch ($productver)
{
328336375 {$SavVersion ="10.1.5.5010"}
23528424 {$SavVersion = "10.0.0.359"}
65537001 {$SavVersion = "10.0.1.1000"}
65995753 {$SavVersion = "10.0.1.1007"}
66061289 {$SavVersion = "10.0.1.1008"}
131073002 {$SavVersion = "10.0.2.2000"}
131138538 {$SavVersion = "10.0.2.2001"}
131728362 {$SavVersion = "10.0.2.2010"}
132383722 {$SavVersion = "10.0.2.2020"}
132449258 {$SavVersion = "10.0.2.2021"}
25822194 {$SavVersion = "10.1.0.394"}
25953266 {$SavVersion = "10.1.0.396"}
26215410 {$SavVersion = "10.1.0.400"}
26280946 {$SavVersion = "10.1.0.401"}
65536905 {$SavVersion = "9.0.5.1000"}
72090503 {$SavVersion = "9.0.3.1100"}
65536903 {$SavVersion = "9.0.3.1000"}
65536902 {$SavVersion = "9.0.2.1000"}
65536901 {$SavVersion = "9.0.1.1000"}
22152068 {$SavVersion = "9.0.0.338"}
21562155 {$SavVersion = "8.1.1.329"}
21168939 {$SavVersion = "8.1.1.323"}
20906795 {$SavVersion = "8.1.1.319"}
20579115 {$SavVersion = "8.1.1.314"}
54068001 {$SavVersion = "8.1.0.825"}
29950753 {$SavVersion = "8.0.1.457"}
614597408 {$SavVersion = "8.0.0.9378"}
614335264 {$SavVersion = "8.0.0.9374"}
29229856 {$SavVersion = "8.0.0.446"}
28640032 {$SavVersion = "8.0.0.437"}
28443424 {$SavVersion = "8.0.0.434"}
28115744 {$SavVersion = "8.0.0.429"}
27853600 {$SavVersion = "8.0.0.425"}
85197700 {$SavVersion = "7.60.926"}
61997817 {$SavVersion = "7.6.1.946"}
61473529 {$SavVersion = "7.6.1.938"}
60949241 {$SavVersion = "7.6.1.930"}
60687096 {$SavVersion = "7.6.1.926"}
55509743 {$SavVersion = "7.5.1.847"}
48366268 {$SavVersion = "7.0.0"}
}
$VersionNumber = [int]$SavVersion.substring(0,2)
If ($VersionNumber -ge 9)
{$c.Cells.Item($intRow,4) = $SavVersion}
Else
{
$c.Cells.Item($intRow,4).Interior.ColorIndex = 3
$c.Cells.Item($intRow,4) = $SavVersion
}
}
GetRegInfo
Function GetDefInfo
{
$x = Test-path "\\$strcomputer\c$\Program Files\Common Files\Symantec Shared\VirusDefs\definfo.dat"
if($x -eq "True")
{
$y = get-content "\\$strcomputer\c$\Program Files\Common Files\Symantec Shared\VirusDefs\definfo.dat"
$z = $y[1]
$dtyear = $z.substring(8,4)
$dtmonth = $z.substring(12,2)
$dtday = $z.substring(14,2)
$Rev = $z.substring(17,3)
$ddate = "$dtmonth" + "/"+ "$dtday" + "/" + "$dtyear"
$DateVirDefs =[datetime]$ddate
$c.Cells.Item($intRow,5) = $DateVirDefs
$c.Cells.Item($intRow,6) = $Rev
$dtdiff = [datetime](get-date -format g) - $DatevirDefs
If ($dtdiff.totaldays -le 2)
{
$c.Cells.Item($intRow,7).Interior.ColorIndex = 4
$c.Cells.Item($intRow,7) = "OK"
}
Else
{
$c.Cells.Item($intRow,7).Interior.ColorIndex = 3
$c.Cells.Item($intRow,7) = "Need Attention!"
}
}
Else
{
$c.Cells.Item($intRow,5).Interior.ColorIndex = 6
$c.Cells.Item($intRow,5) = "Information can't be found"
$c.Cells.Item($intRow,7).Interior.ColorIndex = 6
$c.Cells.Item($intRow,7) = "Need Attention!"
}
}
GetDefInfo
$c.Cells.Item($intRow,8) = Get-date
$intRow = $intRow + 1
}
$d.EntireColumn.AutoFit()
cls