Xenophane's Blog

At work we have a spreadsheet, where we keep track of our mobile phones, and 3G dongles, in it we keep info about the IMEI code, PUK/PIN codes etc.

Today we found a few SIM cards lying around, the only thing on them was the SIM card number (19 digits). When I went to compare that to the info in the file, I realised that that was not noted in the file, so I had no way to match the SIM with a Phone number, PIN/PUK code.

So I called the telco, they were willing/able to supply me with an csv file containing the phone number, SIM card number, and PUK code.

So I took on the task to update our document, and check that the info in our original document was correct, but doing this for 45+ subscriptions, I thought I would write a script, and what better language than PowerShell.

Here is what I ended up with.

The CSV file contains 3 headers
MSISDN1,UICCID,PUK1

#Location of the Excel file I want to edit
$FileLoc = "C:\temp\test.xls"
#Create Excel Com Object, and display it
$excel = new-object -com Excel.Application
$excel.visible = $true

#Open Workbook,
$workbooks = $excel.workbooks.Open($FileLoc)
$worksheets = $workbooks.Worksheets
$worksheet = $worksheets.Item(1)
#Select the range we want to look at
#In this example, I am only checking within one Column
$range = $worksheet.Range("M3", "M150")
#Load CSV file
$PhoneInfo = Import-Csv c:\Temp\TlfPUK.csv
#Run through the colums
foreach($col in $range.Columns){
        foreach($row in $col.Rows){
                Foreach ($Phone in $PhoneInfo){
				#In the CSV file,  MSISDN1 represents the phone number, but with the international dial code in front
				#that is why I use substring to get the last 8 chars and compare it the value of the current cell.
				#If the phone numbers match, do something
				If ( $Phone.MSISDN1.substring($Phone.MSISDN1.length -8,8) -eq $row.Text) {
				#The two colums what will contain the data is set, this is just 4 & 5 columns to the right of the column that contains the phone number
				$ColIMEI = $row.Column + 5
				$ColPUK = $row.Column + 4
				#Here I fill in the SIMCard number (UICCID) and PUK code
        $worksheet.Cells.Item($row.Row, $ColIMEI) = $Phone.UICCID
		$worksheet.Cells.Item($row.Row, $ColPUK) = $Phone.PUK1
				#$Phone.UICCID
				}
 				}

		}

} 

A while back I created a small AutoIt script, to prevent the screensaver from kicking in, last employer had screensaver kick after 5 min, which was rather annoying when you are out doing presentations, and you have to punch in your password everytime the screensaver kicks in..

Then I read a post from Dimitry Sotnikov that he had written a small PowerShell script to do something similar, and figured out that he uses Wshell to send a keystroke, I thought that could be annoying, if you start working on the computer, and forget to stop the execution of the script, and it suddenly types a character.
So I decided to figure out how to move the mouse from PowerShell, l assumed that I would have to do it using a .Net class, after looking around MSDN I found

[System.Windows.Forms.Cursor]

So combining that with Dimitry’s script we get:

param($minutes = 60)

for ($i = 0; $i -lt $minutes; $i++) {
  Start-Sleep -Seconds 60
  $Pos = [System.Windows.Forms.Cursor]::Position
[System.Windows.Forms.Cursor]::Position = New-Object System.Drawing.Point((($Pos.X) + 1) , $Pos.Y)

}

When you try to install Exchange 2007 mgmt tools on a machine running anything else than PowerShell V1 (yah yah I know, shouldn’t run “beta” software in production, but I want my remoting).
So I guessed it was checking some registry value, did a bit of digging and found it it was looking for this :

“HKLM:\SOFTWARE\Microsoft\PowerShell\1″ PID “89383-100-0001260-04309″

So using PowerShell’s Set-item it looks something like this

Set-Item “HKLM:\SOFTWARE\Microsoft\PowerShell\1″ PID “89383-100-0001260-04309″

I stumbled on a post about getting Exchange rates from a bank, from an online XML file.. So I thought I would try that with a local bank, I figured out the Danish national bank, has an XML file online with the current rates…

#Get Exchange Rates from The Danish National Bank
$wc = New-Object System.Net.WebClient
$rates = [xml]$wc.DownloadString("http://www.nationalbanken.dk/dndk/valuta.nsf/valuta.xml")
$MonCode = @{Name="ValutaKode";Expression = {$_.Code}}
$Description = @{Name="Valuta";Expression = {"100 " + $_.desc + " = "}}
$Kurs = @{Name="Kurs";Expression = {$_.Rate + " KR"}}
$rates.exchangerates.dailyrates.currency | Select-Object $MonCode,$Description,$Kurs  | ft -AutoSize

Another option would be to use Out-Gridview as well, then you would get the result in a GUI form, instead of in the command prompt

The other day I was tasked with creating a spreadsheet containing a list of all servers in our network, manager wanted a “sheet” for each computer, and an “index” sheet with links to all the other sheets.

Since we have more than 150 servers, there was no way I was going to create this list by hand… So I wrote a quick and dirty little Powershell function.

Function Fill-Excel {
BEGIN {
   $xl = new-object -comobject excel.application
   $xl.Visible = $true
   #I specify an existing filename, since creating the link in Excel requires a filename
   $wb = $xl.Workbooks.Open("C:\Temp\test.xlsx")
   $ws = $wb.Worksheets.Item(1)
   $xl.ActiveSheet.Name = "List"
   $row = 1
  }

Process {
$_
$q = $_
$z = $q.ToUpper()
$xl.Sheets.Add()
$xl.ActiveSheet.Name = $z
$wh = $xl.ActiveSheet
$wh.Cells.Item(1,1) = "=HYPERLINK(`"[test.xlsx]List!A1`";`"Back To List`")"
$ws.Cells.Item($row,1) = "=HYPERLINK(`"[test.xlsx]$z!A1`";`"$z`")"
$row++
}
}

$s= "Computer1","CompPUTER2","comPUTer3"
$s | Fill-Excel

This will open the xslx filed called test.xlsx under c:\temp and create an index sheet called “list”, and a sheet for each object that is passed to the function, it also creates a Link in A1 that refers back to the index sheet. On the Index sheet a link to each new sheet is created.

In the above example I “manually” pipe in some text string, but it could also be something like this:

#
#
$s = Get-QADComputer | Where {$_.OSname -match "Server"} | select name

So since it is a function, you can pass virtually any string to it, and it will populate the excel sheet for you.