Big Step Consulting

How to Convert an IP Address to IP Number in SQL

2007-08-10T17:44:00.000-07:00

Let us first preface this by saying that this blog entry is going to be rather technical. We apologize in advance.

A client of ours recently needed reports built that would track what parts of the world their users came from. By analyzing their logs and doing a little bit of research we determined that with relative accuracy we could determine a user’s country of origin by their IP Address. There is a great product called IP 2 Location that publishes a database of IP Number ranges and their associated Country. Notice that we said they publish a list of ip number ranges, which are a completely different form of an ip address.

The challenge was we had a database of about 1.5 million user’s ip addresses and somehow had to be able to compare them in the database to this list of ip number ranges and countries. The vendor of the product had great documentation of how to perform the necessary conversion in ASP, C#, and PHP but unfortunately provided no instructions on how to convert the data directly in a MS SQL database. Because of some of the limitations of the SQL language data manipulation is often somewhat of a pain. We're guessing that is probably why the vendor choose to not deal with coming up with a strictly MS SQL solution, and probably why we couldn't find any articles on this topic. In the end the solution ended up being a pretty small piece of code, but it took a while to get there. Hopefully the following can help the next person facing this issue.

The process of converting an IP Address to an IP Number is relatively simple. For example take the IP Address 192.168.1.0 - To convert it to an IP Number you would do the following.
(256*256*256*192) + (256*256*168) + (256*1) + (0)

Unfortunately MS SQL doesn't provide any native Split functionality to split the decimal separated ip address into the small pieces of data that we could easily work with. We thought we were going to have to do some nasty string dissection using Substring and CharIndex and then we stumbled upon the beautiful Parsename function. Apparently this function was originally designed to help developers navigate through the MS SQL naming structure (ie databasename.dbo.tablename). For our purposes it worked great to pull apart our decimal separated string so we could perform calculations on each piece of data.

In the end what we came up with was the simple trigger that follows. Please feel free to reuse it, we do ask however that you leave the credits intact.

CREATE TRIGGER Insert_IpNumberON Downloads
AFTER INSERT,UPDATE
AS
BEGIN
--PLEASE LEAVE THE FOLLOWING CREDITS INTACT
--CODE BY BIGSTEP CONSULTING
--WWW.FIRSTBIGSTEP.NET

SET NOCOUNT ON;
declare @downloadid bigint
declare @ipaddress varchar(20)

select @downloadid = ObjectDownloadID, @ipaddress = ipaddress from inserted

update Downloads set IpNumber =256 * 256 * 256 * CAST(PARSENAME(@ipaddress, 4) AS float) + 256 * 256 * CAST(PARSENAME(@ipaddress, 3) AS float) + 256 * CAST(PARSENAME(@ipaddress, 2) AS float) + CAST(PARSENAME(@ipaddress, 1) AS float)
where objectdownloadid = @downloadid

END

After implementing this trigger and updating all the existing data we were able to finish writing the necessary reports. It was great to be able to leverage the existing rather useless ip addresses our client had stored in their database into usable metrics that will help them accurately track their website content’s usage by country.

Flash Only Web Sites, Good or Bad?

2007-07-17T16:54:00.000-07:00

For the last few years there has been an ongoing debate on whether it really makes sense to develop websites entirely in Flash. On one hand you have very creative designers who complain that the visual limitations of HTML give them no other option, and on the other hand you have the purists who complain that given the usability limitations of flash, it isn't really an option at all.

For movement and overall "cool" factor, flash really can't be beat. It allows you create very visually captivating animations and interactive applications that otherwise would not be possible inside a user's browser. This makes for great intro animations, fancy website widgets, and an increasingly popular method of delivering streaming video and audio. Flash is absolutely unbeatable at what it does well, the problem becomes when people try to develop entire sites using it.

In a nutshell, flash can generate great components of a site, but was never designed to be the framework to build your entire site with.

Problem #1 Bookmarking - Ever find a great page you want to send to a friend, or save to your book marks? On a normal HTML website, the URL changes on each page so you can send the link to your friend, or have your browser save it to your book marks. With a flash site this generally isn't possible. You'll usually notice that as you click links in an all flash site your URL stays exactly the same. That means there is no way to send people directly to a specific page.
If you visit this flash template, you will see as you click on the links, the URL does not change.

Problem #2 Search Engines - Search engines these days are very smart. When search engines look at your website they have been programmed to recognize normal HTML tags and give additional value to specific tags. For example if something in your HTML code has been designated as a heading, then the search engine knows that text is more important then text that just appears in a paragraph. When your entire site is in Flash the search engine often can no longer contextualize what is important and what is not, and in that sense you end up loosing ranking with the search engine. Many search engines like Google are now also begining to take into account what other websites link to you. Since you can't link directly to a specific section on your website if its all flash (see above) even if you have really great content people probably won't link to your site.

Problem #3 Accessibility - As the generation that grew up with computers continues to get older, and as more and more people in general are online, the number of people with disabilities using the internet is sky rocketing. Simple things such as color blindness or other visual impairements can greatly effect someones ability to navigate the web. Fortunately properly designed websites allow users to take advantage of special settings in their web browser to display information in such a way that it is easier for them to read and use - they completely control how they want sites to appear. Flash however doesn't recognize these settings, which for many users results in an unpleasant, if not downright useless experience. This can not only drive away potential visitors, but in some cases may constitute may actually constitute discrimination and is illegal.

We could go on about a variety of other downsides to flash only sites, such as download speed, users being required to have a plugin to view your site, maintenance costs.... but we don't want to beat up flash. It is an amazing tool at what it does, the key is just to use it properly.

So why would anyone use all flash for a website? The short answer is that with very few exceptions, they shouldn't. Usually people trying to sell you an all flash website aren't looking at the bigger picture. Normally its nothing malicious, they simply either don't know any better, or flash is the only tool that they are familiar with. On the more malicious side some companies have realized that regardless of the drawbacks, it is a lot easier to toss together a site in flash then it is to take a layout and properly convert it to standards compliant cross browser friendly css.

This is always a hotly debated topic and we welcome your comments. Let us know what you think, is an all flash website ever appropriate?

Price Is Not an Indication of Value.

2007-06-26T08:46:00.000-07:00

Although everyone this morning is talking about Paris Hilton and her release from jail, we decided to be mavericks and talk about somewhat of a different topic.

We recently met with the VP of marketing at a mid sized company who had invested over $250,000 in an email marketing/contact management system. He went out of a limb to get the necessary funding to purchase the new system and ended up blowing almost his entire quarterly budget on this one piece of software. The sales people he had worked with prior to the purchase promised him everything he needed - Easy to setup and track marketing campaigns that would seamlessly integrate into his existing CRM system.

Right from the get go there were huge problems. The system took forever for the vendor to install and configure and once it was finally up and running performance was painfully slow. The vendor recommended installing a dedicated server to resolve the issues, which after thousands of more dollars, and weeks more of work, only slightly improved the bottleneck. The first quarter came and went without a single email campaign being sent... From there things just went down hill. The user interface to the system continued to be slow, confusing, and did not allow marketing to customize the emails and web forms to match their corporate look and feel. The "easy to setup" campaigns turned out to be so difficult the marketing department had to offload large portions of the work to programmers in their IT department.

After almost 3 full quarters the first email marketing campaign was finally sent. Calls immediately started coming in. Some people were unable to read the emails, some people complained the opt out forms weren't working, and almost everyone agreed the website associated with the marketing campaign was incredibly slow. To add insult to injury, when the reports about the success of the campaign were finally ran, the data was definitely off.

For the next 6 months they continued to try and streamline the process, work the kinks out of the system, and find a way to capitalize on the rather large investment they had made. Unfortunately despite trying everything imaginable (including flying the vendor out for personalized 1 on 1 training) they were unable to find a way to make the software work for them. They had to make the hard decision to walk away.

A couple mistakes this VP of Marketing mentioned to us:

He didn't involve his IT department in the decision. These people are paid for their knowledge and expertise in technology, but he failed to leverage their skills to help him make an informed technology investment. In retrospect there were several issues they would have raised the red flag on right away.
Brand Recognition got the Better of him. Because the vendor who made the email marketing software also manufactured the CRM his company was already using, he assumed this product was the only logical choice.
He didn't outline his specific needs. During the purchase process he never clearly outlined the details of exactly what he needed to accomplish and how his department was planning on accomplishing it. Relaying this information to the vendor and having them respond on the specifics of how their tool could accomplish these goals would have either given him insight into some of the shortcomings of the software, or given him ammunition for a partial refund when things ended up not working out.

The 4th thing that became clear during the course of our discussion was that he assumed any piece of software that was $250,000 had to be able to do the simple things he had envisioned, plus a whole lot more. He thought anything with that sort of price tag just had to be an enterprise class solution they would be using for years to come. He found himself making the unfortunate, but common assumption that the price was an indication of the value.

As a company, Big Step often runs into that same sort of thinking. Companies often wonder how our price tag is so much lower than other companies competing in the same market. They make the unfortunate assumption that our price is an indication of the value we can deliver. Normally after a quick discussion with them about how we keep our overhead low, utilize our time and resources efficiently, and leverage existing pieces of code, they realize that the value we can deliver (especially considering our price) is rather exceptional.

This story does have a happy ending. We helped work with this VP to find a new hosted email marketing solution that met all his needs. With a little integration work the new solution is now talking to their in house CRM software. Marketing campaigns are taking days (not months) for them to design, target and launch. Perhaps best of all the new solution only runs them a few hundred dollars per month.

If you don't want to fall into the trap of paying too much for your next technology investment, we encourage you to contact us over at Big Step Consulting. Whether you need an affordable website, desktop application, or just some cost effective technology consulting, we can help work with you to deliver an immense amount of value, without a ridiculous price tag.

How Do Customers Know You Exist?

2007-06-25T08:36:00.000-07:00

We just finished inking a deal with a radio show on KLAY (serving Seattle, Tacoma, and Olympia) and will have radio spots running during drive time in the next few months. We're excited about continuing to get the word out about our great web design, development, and consulting services - With our radio ads reaching a potential listening audience of over a million people, they definitely will help!

Many clients we build websites for initially approach their project with a "if you build it they will come" type of mentality. The reality is the internet these days is a very, very big place. The chances of you just putting up a site and having it be successful by accident are rather slim.

Just like a brick and mortar store, a website's visibility is essential to its success. Increasing your website’s visibility may mean doing simple things like securing an appropriate domain name, putting your website address on your business cards and letter head, getting links to your site from industry associations, or just making sure to mention your website to EVERYONE. Often though, these simple approaches are not enough and you may need to look into things such as search engine optimization, the stickiness of your content, paid advertising options, and aggressive cross linking campaigns.

Regardless of what method you are using to advertise your website, the most important thing is that you have a strategy in place. 9 times out 10, whatever approach you choose to market your site with will take time, patience, and consistency. Without a cohesive plan of attack in place it is very easy to get caught up in the moment, become frustrated if there are not immediate results, and walk away from something that was on the verge of success.

If you don’t want to fall into the “If you build they will comes” mentality and need help formulating a marketing strategy, we encourage you to give Big Step Consulting a call. More then just a Design or Development shop, Big Step Consulting can expertly handle your project in house from beginning to end and make sure that your next technology investment not only turns out looking and working great, but is a long term success for you and your business.

New Web Site Coming....

2007-06-22T08:43:00.001-07:00

As part of our initiative to redesign our web site, we have decided to add a blog. Our goal is to share what is happening over at Big Step Consulting with our clients, contractors, and whomever else is interested. In addition we'll also be recording unique technological and business challenges we run into and the solutions we implement to solve them.

Its been almost 5 years since our last web site update and a fresh look and feel is long overdue. Our new layout is going to be an entirely standards based CSS (cascading style sheets) creation. This will be a nice upgrade from our existing table based design. The search engine benefits of going with a CSS design really are astounding. It continues to amaze us that although CSS support is increasingly standardized, there are many people out there still doing things the old fashioned tabular way. If search engine ranking is important to your site then then we encourage you to contact our search engine optimization (SEO) gurus. It's amazing what a new design and a simple rewrite of your content can do for your search engine rankings. In the future we'll probably discuss keyword density, code to text rations, and other search engine optimization concepts a little bit more to give the non technical folks in our audience a better understanding of what all this gibberish means.

In the meantime, we're excited about the new redesign and the opportunity to spend some time applying our consulting experience to our in house systems. We are shooting for a go live date at the end of July for the new website and we'll keep you posted as things progress.