Monday, May 16, 2016

The Pocket Directory Story

Back in the 1990’s I managed the Network Services team with responsibility for all voice and data communications. The team installed and upgraded phone systems, ran coax cables, installed hundreds of LAN switches from Austria to Japan. It was an exciting time as this thing called “The Internet” started to gain some traction and mobile phones started fitting in the palm of your hand. Then one day, along with all that, the CIO asked (OK, told) me that I now had responsibility for the fixing and reprinting, asap, the small, pocket-sized, Corporate phone directory which had been totally and embarrassingly messed up. That part was easy, but now that I had this in my control, and I really hated this little thing, I had my team carve out some time to make it better … way better. We discussed its shortcomings (I’m being polite), came up with ideas and asked other local companies how their’s looked. The following documents the highlights and shows what refusing to accept status quo and embracing change can fix, even with something as pedestrian as a phone directory.

1. We changed from glossy to uncoated paper

One constant in life is change and that certainly is true for people and telephone numbers over the yearly life of this directory, so writing updates is a common practice. But glossy paper is hard to write on and easily smears. Sure, it looks all pretty and shiny, but this isn’t an advertising brochure. Switching to uncoated paper solved this, and was less expensive.

2. We changed from a glued back to a spiral binder

What does one do with a telephone directory? Not a trick question, you look up phone numbers. Why do you look up phone number? To call someone. Can you remember a 10-digit phone number? You are in the minority if you can. I’m with the majority who can’t. The old directory had a glued back to it, so you had to hold it in one hand while holding the phone another one hand and dialing with your third hand. Oops, ran out of hands. So you held the phone under you chin and dialed. That was comfortable (not). The other option was to break the glue so the directory would lay flat. That led to pages falling out. Switching to a spiral binder solved all that nonsense.

3. We changed from portrait to landscape

The old portrait layout resulted in multiple lines per person and generally a messy looking layout. Switching to landscape permitted a person’s name, title, work and home telephone numbers to fit in a single line, and we switched to a consistent 10-digit phone number format. Improved the readability at least ten fold. We also made it a little larger, but still kept it under the size of a standard dress shirt pocket.

4. The entire network team did a QA check

Unlike many IT departments, the network team knows people and had visited about everywhere in the company. The biggest source of recurring errors in the content of the the directory was the process used to collect the data. The old process was basically just send us your department’s updates and we’ll print another book. No updates, we’ll print last year’s. Hence any errors in content was someone else’s fault. I just wanted to make the best directory possible, not cover my backside when mistakes were made. So after the first draft of the new directory was ready we sat down over pizza and soda’s to look for mistakes, over the objections that we would be wasting our time. Page one, the Board of Directors. A must-get-it-right page in any large corporation. Yep, first error found. “But they said it was OK!”. It wasn’t a blame game any more. My favorite was when we got to the Hawai’i office and Charlie jumped up exclaiming “That’s not the right main office number, I just know it!”, ran to phone dialed it with his expected result, then dialed what he knew was right, had a brief conversation with the Hawai’ian receptionist, and gave us the correction. We spent two hours and made it half way through the directory that day. Before we published, we must have fixed hundreds of mistakes. The quality had never been better.

5. We delivered a printed copy to the print shop

Since the root cause of my group picking up this responsibility was a breakdown between the previous owner and the internal print shop, we knew we had to get this right. At the heart of the breakdown was the technology difference between the two groups, and could easily cause future problems. After much discussion we settled on an unexpectedly simple solution. We would give them a printed copy of the directory, one-sided and on full-size 8 ½ by 11 paper and they would shrink and print it double-sided. Really hard to mess that process up.

6. We surveyed our customers

I think it’s pretty obvious that we were excited by the changes and the quality of our final product. But what did everyone else think? What other good ideas had we not thought of? So I asked that a short survey be sent out. “Why in the world would we do that?” was the general response from the team. But I insisted and we got some ideas and some praise. My favorite was “It’s obvious that whoever designed the new directory was a traveler!”.

Spot on.

Thursday, May 12, 2016

My Collection of SQL

Over the course of the last couple years I got to know Microsoft’s SQL Server. I’m no DBA, but with a good night’s sleep and lots of Google searches, I’ve been able to get the job done. I’m also rather fond of collecting those things I find useful, so just in case my list would do you some good, here it is. If it’s in italics, you need to replace that with your stuff. I hope I got most of that right. Some might run on one version of SQL Server, but not another.

No promises, no warranties. Here goes.

Row Counts for Every Table in a Database
SELECT o.name, rows
FROM sysindexes i join sysobjects o on o.id=i.id
WHERE indid < 2 and type='U'
ORDER BY rows DESC

Get Information on All Columns for All Tables in a Database
SELECT table_schema, table_name, column_name, ordinal_position,
      column_default, data_type, character_maximum_length
FROM information_schema.columns

The Last Time a Table was Updated For All Tables in a Database
SELECT DISTINCT OBJECT_NAME(object_id,database_id) as TableName,     last_user_update
FROM database name.sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('database name')
GROUP BY OBJECT_NAME(object_id,database_id), last_user_update
ORDER BY TableName

Find String in Any Object in a Database
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%string%'

Backup a Database
BACKUP DATABASE databasename
TO DISK = 'path\name.bak';

Restore Database to Another Location
RESTORE DATABASE databasename
FROM DISK = 'path\name.bak'
WITH RECOVERY,
MOVE 'databasename_Data' TO 'newpath\databasename_Data.MDF'
MOVE 'databasename_Log'  TO 'newpath\databasename_Data.LDF'

Copy Results from a Query to a Pipe-Delimited File
BCP "SELECT * FROM [database].[owner].[table_name]" queryout filename -Uuserid -Ppassword -t"|" -c -S server_name\instance

Change User Password
EXEC sp_password 'old password', 'new password', 'userid’

Select Rows Between Two Datetimes
SELECT *
FROM tablename
WHERE field BETWEEN '10/15/2015 00:00:00.00'
               AND '10/15/2015 23:59:59.999'

Select Yesterday's Data
SELECT *
FROM table
WHERE date_field >= dateadd(day,datediff(day,1,GETDATE()),0)
  AND date_field < dateadd(day,datediff(day,0,GETDATE()),0)

Count the Number of Occurances of Each Value
SELECT DISTINCT column_name, count(column_name) as CountOf
FROM tablename
GROUP BY column_name

Count Rows By Year
SELECT DISTINCT YEAR(datetime_field) as Year, COUNT(*) as Rows
FROM tablename
GROUP BY YEAR(datetime_field)
ORDER BY YEAR(datetime_field) DESC

Update Rows Based on a Time Difference in Minutes
UPDATE tablename
SET field = field
WHERE DATEDIFF(MINUTE,datetime,CURRENT_TIMESTAMP) < minutes

Replace String in a Field
UPDATE tablename
SET field = REPLACE(field, 'text', 'newtext')
Replace Substring in a Column
UPDATE tablename
SET field = CAST(REPLACE(CAST(field as NVarchar(4000)),'string1','string2') AS NText)
WHERE field LIKE '%string1%'

Delete Rows from a Table Between Two Datetimes
DELETE FROM tablename
WHERE date_field BETWEEN 'mm/dd/yyyy 00:00:00.00' AND 'mm/dd/yyyy 23:59:59.999'

Left Join
SELECT A.field1, A.field2, B.field3
FROM tablename1 A
LEFT JOIN tablename2 B
ON A.field1=B.field1

Saturday, April 30, 2016

Paulishing

I've moved this blog from the generic blogspot.com domain to my own ... paulishing.com.

Paulishing is a term I made up at NewPage Corporation where part of what I did was review documents and presentations for spelling, grammar and clarity, basically polishing up documents.  So Paulishing is just a twist on polishing, and reflects my love of writing.

The old blogspot link will redirect to the new.  The direct link to the new name is:

http://technologyviewpoint.paulishing.com/

Tuesday, April 26, 2016

Despicable Words

There are a few fashionable words and phrases thrown about in business that I simply can’t stand. They are used to shut down conversation, make something sound like something it’s not, or ask for something in a way they don’t mean. Here is my list of un-favorites.

  • Best Practices - I despise this one the most because it doesn’t mean a damn thing. “Our product incorporates Best Practices”. Really? How did you know that? How did you come to that conclusion? Which other practices were considered and why were they rejected as only good? Never an answer. It’s just marketing BS to make their stuff sound better than the competition.
  • Intuitive - “the ability to understand something immediately, without the need for conscious reasoning” is the dictionary definition. Generally used by technology vendors to make it sound like using their software is child’s play, and you, the buyer should feel ashamed if you don’t understand it at first glance.
  • Partnership - “an arrangement where parties, known as partners, agree to cooperate to advance their mutual interests”. Generally used in business to shut down any conversation on why a particular vendor is preferred even when they cost way too much or deliver crappy service. Just ask what’s the “mutual interest”. Nada.
  • Candid - Makes it sound like the requester is looking for an honest opinion and is expecting to hear the worst. Try that someday and tell me how that worked for you.
  • Politically Correct - A “polite” way of saying that it’s not fair or not right, but we do it anyway. If you’re the one being told that, that generally means you’re the one being screwed. Has nothing to do with politics, where being politically correct has true value.
  • To Tell The Truth and Well Honestly - How did these ever get any traction? It’s like they’re saying “I lie most of the time, but just this once I’ll give you the truth”. Should I just ignore everything you say until you preface it with “Hey I’m not lying this time”?

If you catch me rolling my eyes after any of these words are spoken, you’ll know you’ve just tapped my “that’s just plain stupid” nerve and I’ve likely just tuned you out. Just so you’re forewarned.

Tuesday, April 12, 2016

Wireshark and AWK


In the world of technology, guessing (or blaming) games as to the source of a problem seem to be a time-honored pastime.  While I’m not against the occasional bout, life’s too short to constantly repeat a loop of “guess-and-be-wrong” before stumbling upon the right solution, or giving up to find another game to play.

Seeing is believing, and in the computer world, a “Sniffer” trace, a capture of every bit of data flowing on a network is a great source of seeing.  A real “Sniffer” is an expensive device, deployed by a network guru, and is overkill for most diagnostic data capture.  Enter Wireshark, a free (I love free) software package that runs on Windows, Mac and Linux that captures every bit of data that enters or exits the machine it’s running on.  In most cases you crank it up, recreate your problem, stop the capture and scroll through a few thousand packets, looking for something out of place.  There’s also the capability to filter those packets to just those you’re interested in, for example DNS look-ups, reducing your search to a manageable few.

But what to do when you need to capture millions of packets over an hour or more when you’re looking for a needle in a haystack?  One solution I’ve found very effective is employ a small AWK formatting program.  AWK is a sleek programming language, named after its creators, Aho, Weinberger and Kernighan.  I’ll demonstrate one example of how I used this combination to help find one of those needles, including the AWK source code.

The problem involved trying to eliminate the printing of an unused label from an application with limited source code and even less documentation.  On the positive side, the application keeps just about everything in an SQLServer database.  I was hoping that capturing and analysing the database traffic between the app server and database server would reveal clues on how the label printing application worked.

To start, I ran a Wireshark trace on the app server, capturing all packets without any filters.  I stopped the trace after a label was printed and exported all packets using the “File...Export Packet Dissections…” as a text file.  There are a number of options on what to export, but only the Packet Header and Packet Bytes are needed, so I made sure only those two selection boxes were checked.  The resulting text file has many lines per packet, and it’s too tedious to scan down to find clues and near impossible to make Find commands useful.  Having one, long line for each packet is much more useful.  Enter the AWK code.

I use the GNU version of AWK which can be downloaded from:     http://gnuwin32.sourceforge.net/packages/gawk.htm

Besides AWK being a simple programming language that is very good at handing strings, I also find that having a single, stand-alone executable (gawk.exe) much easier to deploy, with no Windows installation, DLL’s or configuration files.  I keep the executable in same directory as all the input files I use and output files I create, C:\GAWK, which avoids the tedium of having repeatedly spell out directory paths.  To run the formatting program (code included below), open a Command Prompt, navigate to the C:\GAWK directory and enter the following command, replacing the italicized file names with the appropriate names.

< input file gawk.exe -f printpdml.awk > output file

The “< input file” throws the file into the input stream.  “-f printpdml.awk” tells “gawk.exe” which file contains the program code and “> output file” sends all the program’s print output to the named file.

The resulting file contains the desired one line per packet, and while I’m not claiming it’s perfect, it’s 90% of the way there.  In the case of the unwanted label, I was able to quickly find where the label printed, then backed up to find a Stored Procedure that looked appropriate. Searching that procedure led to another procedure that inserted one database record for each label.  Commenting out the unwanted insert resolved the issue.

Not bad for less than an hour’s time and using two free programs.


Source Code for “printpdml.awk”

# This awk program formats that text file into a smaller, more readable format.
#
BEGIN {
  line = "";
}
# Print the single, consolidated line collected by the code below.
{
if (NF < 2) {
  if (length(line) > 0) {
     print packet " " time " " source " " destination " " line;
     line = "";
     }
  }
#
# Get the information from the Packet Header line.
#
# This code assumes that the first field is the packet number, the second and third are the
# date/time, the fourth and fifth are the the source IP/port, and the sixth and seventh are the
# destination IP/port.  Most importantly, it assumes a “2” in column 9 of any line represents a
# header line.  These may need adjusted depending on the exact format of your export.
#
if (substr($0,9,1) == "2") {
  packet = $1;
  time = $2 ":" $3;
  source = $4 ":" $5;
  destination = $6 ":" $7;
  next;
  }
#
# Ignore the first three lines and part of the fourth of the Packet Bytes lines,
# which contain unneeded network header information.
#
if (substr($0,1,4) == "0000") {next;}
if (substr($0,1,4) == "0010") {next;}
if (substr($0,1,4) == "0020") {next;}
if (substr($0,1,4) == "0030") {i=8;} else {i=2;}
#
# Get the information from the Packet Bytes lines.
#
# Ignoring binary zeroes (“00”) reduces the line size and makes
# seeing and finding things much easier.
#
while (i < 18) {
  if (substr($0,7+((i-2)*3),2) != "00") {
     line = line substr($0,i+55,1);
     }
  i++;
  }
}