Sysadmin Field Notes

January 20, 2006

Who needs SQL? :-)

In the old-school spirit of giant, strung together command lines for unix, you can use sort and uniq (one of my favorite unix commands) as a sort of "group by" clause in SQL.

For example, say you have a bunch of different types of error messages coming from some process (in my case today, a nant build with tons of unit test failures). How many of each kind are there and how many different kinds are there?

mytask | grep "something present in each error message" | sort | uniq -c

That selects just the error messages, sorts them (because the next step only finds ADJACENT identical lines), then groups them and counts how many of each their are. Useful, useful stuff. If the formats aren't exactly the same or what you want, use sed to select just the part you wish to group by.

Here's one that if you feed it my mailbox, displays the spamicity scores and counts of each piece of mail in the box:

grep spamicity $1 | sed 's/.*spamicity=\(.*\), ver.*/\1/g' | sort | uniq -c

Somewhere I have a useful one-line web log analyzer as well, for counting hits by day by selecting off the date only. Ah, here it is:

sed 's/.*\[\(.*\):[0-9]\{2\}:[0-9]\{2\}:[0-9]\{2\} -[0-9]\{4\}\] ".*/\1/g' | uniq -c

(No real pressing need to sort, the logs are about 99% time-ordered already). That regex didn't spring fully formed out of my head, I started with a chain of awk and sed that was much simpler to understand, then gradually built it into a single regex for performance. I still keep the original awk around somewhere as a comment. I think that works against the standard apache "combined" format log, and I'm not guaranteeing its perfect, but you get the idea.