Fixed iChat AV thru OpenBSD PF Firewall

This has been bugging me for ages, but the fix is so simple.
I use OpenBSD for my router, and have PF (Packet Filter) running the firewall and NAT rules.

I have previously setup the port forwarding, etc. according to Apple, but never could get a successful connection using video or audio (or screen sharing).

After reading a post on the OpenBSD misc mailing list I went back and read the OpenBSD 4.5 pf.conf man page.

There is a section of the man page that states (my emphasis):

fragment reassemble
Using scrub rules, fragments can be reassembled by normalization.
In this case, fragments are buffered until they form a complete
packet, and only the completed packet is passed on to the filter.
The advantage is that filter rules have to deal only with complete
packets, and can ignore fragments. The drawback of caching frag-
ments is the additional memory cost. But the full reassembly
method is the only method that currently works with NAT. This is
the default behavior of a scrub rule if no fragmentation modifier
is supplied.

My scrub settings were such that I was filtering not complete packets, but fragments. The moment I changed the scrub settings to this:

scrub in on $ext_if all fragment reassemble


All worked perfect. I can initiate iChat calls and receive them too. Desktop sharing now works as well. All using Google talk (jabber) in iChat.

Note that in OpenBSD 4.6 or current there have been PF changes, so the wording of the scrub rule may be different. Always read the pf.conf man page for the release you are on.

Clone a part of an SVN repository in git

I was trying to clone my wordpress plugin from the wordpress svn repo using git-svn. I had no luck for about the past 8 weeks, with this problem:

Initialized empty Git repository in /Users/lantrix/tweet/.git/
Using higher level of URL: http://plugins.svn.wordpress.org/tweet => http://plugins.svn.wordpress.org

And it would proceed to hit up the entire wordpress repo.
After reading a possible solution on Charlie’s Old blog, I stubmled across a newer way to do this.

As of v1.6.4 of git, you can now use a –no-minimize-url when doing a git-svn clone. This makes git clone only the part of the repo you want; and the added bonus is you can get all your tags and branches.

Here is how I did it:

git svn clone --prefix=svn/ --stdlayout \
--authors-file=authors.txt \
--no-minimize-url http://plugins.svn.wordpress.org/tweet/


It still took a while to parse all the SVN history, and now of course this stands out in the git-svn doco

The authors.txt file just mapped my svn users to git user/email pair, e.g.:

lantrix = Lantrix 
plugin-master = none 
(no author) = none 

You will probably want to have a look at these set of scripts. Have a read of NothingMuch’s perl blog for extra steps and details on extra svn conversion scripts.

Git branch name in your bash prompt

Here is a quick way to show the current git branch when you are in a repository directory. Place this in your .bashrc or .bash_profile:

I’ve also customised the PS1 to show user/host/path.

You should probably setup git bash completion as well. This can be done by sourcing the bash completion script that is available in the git source code as shown (change path to where you place script):

Have fun.

Excel – Determining worksheet cell references

Worksheet names in Excel Cells

If you are working in Excel, and you want to show the worksheet name in a Cell on that worksheet, you can use the CELL function to do so.
By default the CELL function will return the current document name, if used with the filename info_type:

=CELL("filename")

This provides a full path to the spreadsheet, with the worksheet of the current Cell at the end, e.g:

C:\folder\[myfile.xls]Sheet1

You can easily get just the worksheet name by using the FIND and MID functions to do the hard work. You need to find the location of the last square bracket, and find achieves this as shown:
=FIND("]",CELL("filename"))

This would return the position of the last bracket. In this case it is at position 22 of the text that CELL(“filename”) returns. The MID function can extract text starting at a location for n length, where n is an arbitrary number. So we would combine MID, FIND and CELL functions to return just the worksheet name like this:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

The reason we add a +1 is because we want to start extracting the text one character AFTER the right square bracket, e.g. at the start of the Worksheet name. Our result is:

Sheet1

Worksheet names from another Worksheet

So far so good, and how is this any different than any other blog post or forum post on the net explaining this? So far it’s not, but here comes the fun part.

What if you have multiple Worksheets, and you do this:

1. Have a cell with content, Sheet1!B2
2. Sheet1!B2 displays the content of OtherSheet!H5, i.e.:

   =OtherSheet!H5

3. You want Sheet1!B1 to display the worksheet name where the CONTENT of Sheet1!B2 comes from.

You could try using the MID/FIND/CELL function combination to try this. In Sheet1!B1 you would enter:

=MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,255)

However this would yield the worksheet name of B2 itself, not the worksheet where you are taking your content from:

Sheet1

Not what we wanted. Somehow you need to get the Value of the formula used =OtherSheet!H5 and look up the worksheet name for OtherSheet!H5

The Solution

To do this you ware going to need to do two things:

1. Make a new function to display the formula, sans the equal sign
2. Make your CELL function use the result of your function to lookup the filename info_type

We can use the Excel VB Editor to create a new function, and call it GetLocation:


Function GetLocation(Cell As Range) As String
GetLocation = Mid(Cell.Formula, 2)
End Function


But we can’t just use GetLocation to directly feed the CELL function. We need to use another handy function INDIRECT. This allows us to return the result of the GetLocation function as a Reference. This then allows the CELL function to evaluate the filename/Worksheet details for the destination cell in the other worksheet:

=MID(CELL("filename",INDIRECT(GetLocation(B2))),FIND("]",CELL("filename",INDIRECT(GetLocation(B2))))+1,256)

This now provides the Worksheet name of the cell that Sheet1!B1 is using to get it’s content from which is OtherSheet!H5:

OtherSheet

This is very handy when you need to show on a master worksheet which other worksheet your data is actually coming from. Windows Excel only, not Mac I’m afraid – until they bring back VB. Enjoy!

BOE XIR2 cmsdbsetup failure on Oracle10g

If you use Unix, and need to migrate your Business objects CMS from one database to another database, you will probably use the cmsdbsetup.sh script. This script migrates and manages your database connection in a Unix environment using Business Objects Enterprise (BOE).

In my case I am Using Solaris 9, and have Oracle 10g databases and client files for use by BOE.

When running the cmsdbsetup.sh script you get the following error pertaining to clntsh:

           Business Objects 

Current CMS Data Source: DBNAME 

err: Error: Failed to get cluster name.
err: Error description: Unable to load clntsh 

select (Select a Data Source)
reinitialize (Recreate the current Data Source)
copy (Copy data from another Data Source)
changecluster (Change current cluster name)
selectaudit (Select an Auditing Data Source) 

[select(6)/reinitialize(5)/copy(4)/changecluster(3)/selectaudit(2)/back(1)/quit(0)]
----------------------------------------------------------

This error “Unable to load clntsh” refers to the libclntsh.so library used by the Oracle client. Since BOE runs as 32bit, the 32bit Oracle client libraries should be accessible by the user running BOE.

If you are running a 64 bit Unix and a 64bit Oracle install check that the environment for the user running BOE (user that will run the CMS) has the 32bit libraries in the path:
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/lib32

Then check that either the user is a member of the Oracle dba Unix group or everyone has permissions to access the 32bit libraries under Oracle 10g:

su - oracle
chmod o+rx $ORACLE_HOME/lib32/*

Feel free to leave any comments if you need help with this.