CSV File Cleanup

If you ever wanted to save a google search to a CSV file, you should check out SEOquake.

Recently I used SEOquake to generate 50 CSV files.  The end goal was to work with these CSV files in Powershell.  Unfortunately, each of the original files had a header that I wanted to remove.

I used the following powershell code to strip out the first 6 lines of each of these files:

After running that powershell code, I can now work with SEOquake output more easily inside of other powershell scripts.

Nested loops

A number of years ago, the company I worked for needed to generate a large set of unique codes. The codes needed to be 5 digits long, and we wanted to remove the letters I and O and the numbers 1 and 0 from the listing.  In an effort to avoid letting my PowerShell skills rust, I decided to recreate this project in PowerShell.

As I started thinking about ways to approach this, it occurred to me that I rarely seem to need to use nested loops.  I assume there are other ways to solve this, but nesting seemed like fun.  Maybe this outline will prove useful for someone looking to learn a little bit more about nesting.

I broke the problem down into two parts:

1. Create an alphanumeric array that conforms to the requirements (removing the I,O,1, and 0)
2. Create a series of nested loops, with each loop representing 1 of the places in the 5 digit code

Once finished, the output was written to a .CSV file, and it looked similar to the small sample below:

BH3NA

BH3NB

BH3NC

BH3ND

BH3NE

BH3NF

BH3NG

If you were to let this run to completion, I believe it would generate 24,000,000 + unique codes (this post is about PowerShell, not Finite mathematics).

Powershell Intro

Was excited to learn that a friend's son is going to a technology camp this summer.  Thought he might like this little bit of powershell code.

The code at the end of this post will ask what you want the computer to say, and then it will speak the text.  Assuming you have a Windows computer, do the following to run the code:

1) Click on the Start button --> and type Run

2) type in     Powershell ISE

3) Copy and paste the code at the bottom of the post into the powershell window (should look similar to the screen shot below)

4) Press F5 (or the icon that looks like a DVD Play button)

Code to copy and paste:

# ------------------------------------------------------

#   ignore this for now

# ------------------------------------------------------

$ErrorActionPreference= 'silentlycontinue'

[Reflection.Assembly]::LoadWithPartialName('System.Speech')

# ------------------------------------------------------

#   clear the screen

# ------------------------------------------------------

cls

# ------------------------------------------------------

#   Ask what you want the computer to say and put that

#   info in the variable $phrase

# ------------------------------------------------------

$phrase = read-host -prompt "What do you what the computer to say?"

# ------------------------------------------------------

#   ignore this for now

# ------------------------------------------------------

$object = New-Object -ComObject SAPI.SpVoice

# ------------------------------------------------------

#   Write the variable $pharse to screen (not needed)

# ------------------------------------------------------

write-host $phrase

# ------------------------------------------------------

#   Have the computer speak what was entered

# ------------------------------------------------------

$object.Speak($phrase)

The quizzer

The script outlined below is essentially a set of electronic flash cards. There are all sorts of websites & programs that offer similar functionality.  That being said, I had a large set of terms I wanted to memorize, and this seemed like a fun way to get a little more Powershell experience.  

Many parts of the script are VERY basic, but there were a few parts (Get-Random and hash tables) that were interesting to explore.  

Here is an overview of the components of the script:

1) Read in a CSV of terms and definitions
2) Randomly pick a Term / Definition to quiz the user on
3) Generate a hash of the correct term (answer) and 4 other false answers
4) Randomize the hash and present to the user
5) Prompt the user to select an answer and evaluate the response

Click here for the full script

Faster DNS Resolution

Recently a friend shared a link from TechRepublic that suggested changing your DNS servers in order improve web browsing speeds.

This seemed interesting and I initially changed my home DNS servers to 8.8.8.8 and 8.8.4.4.  Afterward, I started to wonder if my Internet provider might be able to route to one DNS server faster than another.  Additionally, it seemed logical to assume that the actual resolution could potentially be done faster on one DNS server farm than another.

It seemed like it would be neat to validate existing performance vs. the suggested DNS servers listed in the TechRepublic article.  The idea would be to highlight if a DNS server change would be beneficial on your home network.

Here is the type of output I ended up with:

10.10.1.1 is an internal DNS server and should not be modified

10.10.1.2 is an internal DNS server and should not be modified

Resolution times by DNS Servers (best to worst)

Name              Value
208.67.222.222    00:00:00.0728325
75.75.75.75*      00:00:00.0740533
208.67.220.220    00:00:00.0813375
8.8.8.8           00:00:00.0988982
8.8.4.4           00:00:00.1096580                                                                                      

* - denotes your existing DNS server

If you would like to test your current DNS settings, you are welcome to try the following powershell script on your system:

$ErrorActionPreference = "SilentlyContinue"
cls
$DNS = Get-WmiObject Win32_NetworkAdapterConfiguration -computername . | select DNSServerSearchOrder
$names = $dns.DNSServerSearchOrder
$techRepublicSuggested = ("208.67.222.222", "208.67.220.220", "8.8.8.8", "8.8.4.4")
$names = $names + $techRepublicSuggested
Foreach ($name in $names)
    {
    if (($name -like "10.*") -or ($name -like "176.*") -or ($name -like "192.168.*"))
        {
        Write-Host "$name is an internal DNS server and should not be modified"
        $names =  $names -replace $name
        }
    }
Write-Host ""
$names = $names | Where { $_.Trim(" `t")}
$hashlatency = @{}
$hashresolve = @{}
Foreach ($name in $names)
    {
    $resTime = Measure-command {resolve-dnsname systemzengineer.blogspot.com -server $name}
    if ($techRepublicSuggested -notcontains $name)
        {$name = $name + "*"}
    $hashresolve.add($name, $resTime)
    }
Write-Host "Resolution times by DNS Servers (best to worst)"
$hashresolve.GetEnumerator() | Sort-Object Value
Write-Host ""
Write-Host "* - denotes your existing DNS server"

Automate that Script

A few lines of Powershell commands is a great start, but setting up a scheduled task is essential to getting the most out of your scripts.

The Task Scheduler has been around so long that this might not even seem worth noting, but there are two steps to help get your script running consistently.

1) When setting up your Action, make sure to select "Start a program".  Next, in the box for "Program/script:" enter the path to where Powershell is located (likely C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe).  Finally the path \ script should be entered into the "Add arguments..." box.

2) The other key for our setup has been to change the task to run under a service account, and also select "Run whether user is logged on or not".

Rediscovering Good Ideas

Recently a teammate came up with a great idea: Create a way to quickly search the content of our Powershell script library.   Wouldn't it be great to know "which scripts reference specific servers?"   What if we had a way to determine "what scripts use the following command?" 

Although the script is fairly basic, the yield can be very powerful!

Easy to understand search

Matches displayed in a hash table

Selected script displayed in a temporary text file to avoid accidental overwrites. 

Here are the basics of the script:

    1) set the path to the library that will be searched

    2) create a hash table of the search results
    3) display the hash table and ask the user to make a selection

    4) get the full path to the script that was selected

    5) read the script into a temporary file (helps avoid accidental overwrites)

Demo Script