Imagine if alerting was what you wanted it to be:
- Every alert you received was actionable, and there were few false alerts
- Notifications were actually informative
- You received alerts in time to fix problems before they impacted your users
This isn’t the world we live in…
- We accept lots of notifications from our alerting system that are not actionable
- The notifications don’t tell us about the problem
- We get paged when stuff is dead and not when it is sick
In order to resolve the dissonance between reality and what alerting should be we need:
- An expressive way to evaluate alert conditions that isn’t a 1:1 mapping to the metrics
- Alerts backed by time-series and not just recent values
- A way to to make rich notifications that include useful information
- A way to iterate fast with alert design so that our alerts are continuously improved
A little less than a year ago, Matt Jibson and Kyle Brandt set out to create a system to solve this and other problems in monitoring; we call it Bosun. Our belief is that achieving excellence in alerting is a complex problem and requires a powerful and flexible platform to design alerts. Therefore, Bosun’s strategy is to provide a framework that enables the operator to create intelligent and informative alerts. We believe that you are smarter and more creative than any monitoring system can be when it comes to your environment.
In order to achieve that, at the highest level Bosun provides:
- An expression language (a small domain-specific language) designed to allow for the creation of highly flexible and specific alerts
- Notification templates that allow you to include whatever information you think is relevant
- A web interface the provides a workflow for more rapid iteration with improving and creating alerts: Graph -> Expression -> Rule + Template -> Test the rule over history
The Expression Language
We believe that every alert requires action. An alert asks for your attention, and human attention and time is a valuable asset. So alerting is about owning the operators attention. Taking action with alerts practically means one of two things. If the alert was accurate, then you fix the issue that triggered the alert. If the alert was a false positive, then the alert should be tuned in a way that the false positive won’t trigger the alert. This is where things tend to fall down because alert evaluations are not powerful enough to be tuned. With Bosun’s expression language, you can tune alerts in the following ways:
- Alert thresholds based on history vs static thresholds (or both combined)
- Statistics functions: Min, Percentile, Median, Deviations, Forecasting. You can change the duration that these evaluate over (i.e. 5 minutes, 1 hour, 1 week?)
- Scope-aware: How should components in your environment be grouped? By Host, subsystem, cluster, a combination of those things
- Boolean conditions: The interaction of multiple components
These possibilities, when applied selectively by a skilled operator, provide ample ways to reduce alerting noise.
Once you have someone’s attention with a valid alert, you need to direct them to the problem as accurately as possible. Our notification templates use the Go template language, which means they can be quite flexible. Notifications in Bosun allow you to:
- Include breakdowns of information related to your alert as embedded graphs, html tables, or whatever else you think makes sense
- Include information that wasn’t directly related to the alert: i.e. CPU of a host even though it was a memory alert
- Generate links to your dashboards or other sources of information
- Includes notes about why you created that alert, caveats, and other information the person being notified should be aware of
One of the main issues with alerting is that there is so much friction to tuning alerts that it doesn’t get done. One of Bosun’s goals was to provide a faster iteration cycle for creating and tuning alerts by making the web interface an alerting IDE: Graphs in Bosun’s interface link to expressions, which then link to alert rules and templates. You can then test alerts before implementing; the results of a rule and template can be tested in the interface. You can test how they will behave currently, how they might have behaved at a past time, or generate a timeline of how they might have behaved over a range of time.
This means that your alert tuning doesn’t need to be totally reactionary. You can test alert changes and see how and when they would have triggered over the past weeks (or longer, if you are patient). This results in less alert noise being sent to operators.
But wait! There’s more!
Bosun has also attempted to make some problems in monitoring easier:
- Getting data into the system: our agent (called “scollector”) runs on Windows and Linux and starts sending data to Bosun
- Applications can push metrics to the system via JSON API calls
- Human maintenance: Properly designed alerts will apply to new systems, and services are auto-discovered by scollector. This means you don’t have to remember to update your monitoring most of the time when a new services and hosts are deployed (as long as scollector is pushed out via your build or configuration management process)
We hope you go try this out. We have a docker image that has everything you need—just follow the getting started guide. We hope Bosun is useful to the community. We need your creativity and ideas to continue to grow it (and some contributors would be nice too!). We owe a special thanks to everyone else at Stack Exchange for:
- Contributing to scollector – Greg Bray has been working hard to fill out our Windows metrics, and Sam Torno did the same for Linux
- Getting a docker build – Peter Grace (who also did a lot of the dogfooding)
- Manning the front lines to keep the site up while we built this – the rest of the SRE Team
- Feature ideas and monitoring concepts – Tom Limoncelli and his monitoring chapters in The Practice of Cloud System Administration
- Letting Matt and I go tilting at windmills – Stack Exchange, Inc.
A lot of tools available in IT/Sysadmin/Ops/DevOps are disappointing:
- They don’t fit your environment. They lack features or our designed for a different sort of environment (i.e cloud vs hardware, Linux vs Windows, distributed vs centralized etc)
- You can’t interact with them programmatically
- They cost too much
- They are not customizable enough, or require too much customization to get off the ground
- Feel kludgy, unreliable, outdated, or like the programmers were stoned
- Don’t fit with your company’s culture (i.e. Enterprise vs Agile)
In short a lot of stuff is too expensive, isn’t a good fit, or is simply bad software. This ends up leaving an ops team with two options. They can whine about it, or create their own tools. So at Stack Exchange we build our own DevOps tools.
Nick Craver’s baby, which we just call “Status” is at first glance a monitoring dashboard, but is essentially a collection of tools that filled various needs:
- An Overview of CPU, Memory, and Network utilization for all our servers as well as a detailed view. Done with responsive and interactive D3 graphs as well as sparklines it helps compensate for Solar Wind’s terrible interface.
- SQL Server monitoring. SQL’s built in Clustering views are deeply flawed. If a node loses connectivity, it stops updating remote nodes status, so it could show everything as connected and fine, even if there is no connectivity. We also get to see the most expensive queries, active queries utilizing whoisactive, current connections, and which DBs are on which server
- HAProxy Monitoring and Administration: With multiple instances of HAProxy we needed a single view instead of HAProxy’s built-in display. Also, this gave us a nice web interface to take servers out of rotation
- Redis: A nice presentation of Redis Info across all instances and all servers. Also a display that shows what is slaved to what in at a quick glance
- Elastic Search: Health overview of or clusters (as well as index and shard data)
- A dashboard of all the exceptions generated by our applications
Status is C# / .NET app. It polls data from various sources – sometimes the system directly and other times it gets it from Orion. There is a lot more to status that makes it awesome. The real accomplishment is that status enables us to see the general health of our main infrastructure at a glance.
If you business is creating and running websites, your web logs are gold. We use the logs generated by our load balancer, HAProxy, as our canonical web logs. In their raw text format, web logs are often not that useful (this is particularly true with over 100 million records a day). However we parse and structure our web logs in a few different ways:
- We have C# service that Jarrod Dixon wrote that inserts them into SQL so we can query them. In order to query them we use an instance of Data Explorer, SQL management studio, and also have certain lookups directly from our sites
- Displaying realtime graphs of various log information with Realog, a system I created with Go, Redis, and NVD3.js so we could view activity live without having to write queries
One of the interesting things we do with our weblogs is to add extra information by adding headers inside the app and striping them from the response at HAProxy. For example, we capture how many Redis and SQL queries were involved in that request and how long they took.
- View the outstanding patches and patch count for both Linux and Windows
- Trigger updates on either Linux or Windows
- Schedule time frames for automatic Linux updates
If you want to learn more about these tools and DevOps at Stack Exchange, come see George, Nick, and Steven present “Building for Operations” at Velocity.
Keeping all this stuff to ourselves feels a bit greedy. However, for something open sourced to be very useful it usually needs to be made a bit more generic which takes time. We also want to build a lot more. Our inventory system Racktables lacks an API so we need a new one or a way to extend it. We want to build our own monitoring system (likely on top of OpenTSDB). In order to create more, and open source it we need help. So we are looking a full time developer with ops experience to join our SRE team. So if you are awesome, want to build awesome ops stuff and open source it, come join us!
Before our test last weekend, we posted THE PLAN, as promised here’s a follow-up of how things went:
- Prep (2 hours before the test)
- Shorten DNS TTL down to 5 minutes
- Pause page duty (that’s damn sure going to go off)
- Firewall Oregon redis to prevent mutation (went smooth, late plan addition)
- Slave Oregon redis from the New York master (smooth, late addition)
- The Test
- Shutdown affected backends in HAProxy (New York)
- Start the DNS swap to Oregon IPs
- Start the SQL 2012 Availability Group failovers to Oregon (largest problem)
- Drop redis firewalls in Oregon (went smooth, late plan addition)
- Wait for this to complete before moving forward
- Sanity check sites on the Oregon web tier
- Enable the backends in HAProxy (Oregon)
- Bring the sites out of read-only mode (can be improved)
- Find problems, squash bugs in our configuration until we’re running smooth (went well)
- Firewall New York redis to prevent mutation (broke OpenID)
- Slave New York redis from the Oregon master (smooth, late addition)
- Slave New York redis backup from the New York master/slave (smooth, late addition)
- Oregon went totally offline, twice!
- Failing back to New York
- Shut down backends in HAProxy (Oregon)
- Start the DNS swap to New York IPs
- Start the SQL 2012 Availability Group failovers to New York
- Drop redis firewalls in New York (smooth, late addition)
- Wait for this to complete before moving forward
- Sanity check sites on the New York web tier
- Enable the backends in HAProxy (New York)
- Bring the sites out of read-only mode (wasn’t actually needed)
- Get beer (check, check)
Some of these were late additions to the plan. Having redis be a warm cache once we were up in Oregon meant a few more steps added to the original plan, but well worth it. A cold cache for all sites means stumbling of the servers and slow page loads for the first wave of hits…why have slow pages when they can be fast? The above is a high level plan…the actual one has even more small steps in there, so let’s look at what failed at a high level and some of the smaller details as well.
- Time-wise, the biggest issue was the SQL 2012 always on availability group failover for our SENetwork_AG group; this group contains all of the databases for sites that aren’t stackoverflow.com. While the StackOverflow availability group failed over across the country in seconds, the much larger SENetwork_AG (by database count – that’s what matters in our case) did not. Here’s how that one played out:
- (+0 min): Failover of the SENetwork_AG begins
- (+5 min): After attempting to failover via the SSMS GUI and saw a timeout after 5 minutes
- (+6 min): We attempted to fail it over via script in case this was a tooling timeout in plan
- (+11 min): It’s not a tooling timeout; time to up the default timeouts on the listeners and AG resources in windows
- (+16 min): This had no effect, the 5 minute timeout is somewhere else in the pipe
- (+17 min): As a last ditch effort to get the AG ownership moved to Oregon, I disabled the AG’s dependency on the listener (which we don’t want or need, but have to have)
- (+17.5 min): Success, AG is spinning up
- (+19 min): All databases are back online, SE 2.0 sites are now up
- The second most visible failure was Oregon going completely offline, twice! We have traffic, lots of traffic. This means lots of simultaneous connections to our load balancers, especially when we’re coming up from an outage. This means the default conntrack limits in CentOS 6.3 on our HAProxy load balancers weren’t high enough. We solved this by upping the limit to 1,048,576, matching New York (it turns out we did this weeks ago…fail #2 revealed why it didn’t stick). Later, after another puppet deploy (we have things templated to keep 2 datacenter in sync so…puppet!), the iptables service reloaded. This caused CentOS to unload/reload the iptables module resetting the limit…causing another outage, hoorah. We fixed the limit again and then prevented further reloads – problem solved. This was a good pair of lessons we can apply for when New York load balancers are fully under puppet control.
- The third, lesser-noticed failure was that when we began the redis slaving back to New York to keep that warm cache, we blocked another service using that redis instance: Stack Exchange OpenID. Once we identified this issue we moved it to another instance that isn’t slaved or firewalled as part of a failover. There would be a similar problem when we test OpenID, Careers, etc. failover in a few weeks…so this fix takes care of things for that test as well.
Things that can be better
- When the sites were available (open via HAProxy) but the databases were not yet online, we broadcast a raw error page (YSOD) to users.
- While this can be fixed by not opening the HAProxy backends until the sites are ready, we prefer to at least know what was throwing that error.
- Bringing sites out of read-only mode was more tedious than anticipated
- We have a “disable read-only” button per-site…I’ll be adding a global one as well for situations like this
- Exceptions logging needs some thinking. Our exceptions log to a database that was failed over to Oregon, making it read-only in New York. This meant the services that didn’t failover in New York trying to write to that database had to queue up their exceptions and write them out to the database when it was available for writes again.
- While this was an excellent test of StackExchange.Exceptional’s error queuing in case of database failure…we’d still like better farm-wide visibility during a partial failover.
Overall, we were very happy with how this test went. Most issues were identified and solved quickly, and most of our fears were laid to rest. This has been a long, hard effort by many devs and sysadmins on multiple teams…and we’re not close to being done. This test going very well for the most part has been a very rewarding payoff on our side, we’ll keep you updated as our datacenter move progresses.
I have to apologize to the serverfault community for a few things:
First, we’ve been really, really busy around the offices of Stack Exchange, and we’ve just not had a good amount of time to write blog posts. Luckily, now that we’ve added a new Systems Administrator, Mr. Bart Silverstrim (http://serverfault.com/users/13647/bart-silverstrim) we might have some time for posts more often.
Secondly, we’d like to sincerely apologize for taking Bart from the ServerFault community. I’m sure with all the coffee he’ll be fetching, there will be little time for him to increase his already sizable 25k reputation (and give me time to catch up with him.)
All joking aside, we are very pleased that Bart is joining our team; he’s not only a very smart admin (which his serverfault profile will prove), he’s also a great guy to be around.
So, WELCOME SERVERFAULT VALUED ASSOCIATE #0000004! May you not crash the blogs with large images.
This topic was suggested by one of our users, Bart Silverstrim. He was curious about how companies could maintain an environment where maintenance downtimes would not actually affect customer experience. Think of large sites like Google, Facebook, CNN, pretty much any site in the quantcast or alexa top 100. These sites maintain extremely good uptime numbers, but how do they do it?
It’s no secret that sites like this employ a lot of servers to handle content delivery. There are not only load balanced server clusters but also CDNs and caching proxies that help mitigate some of the load on the environment. Eventually, though, all of these machines are going to need maintenance at some point. How does one do this and not affect perceived uptime from the users? This question is actually rather complex because it depends on multiple ecosystems to properly execute.
Monolithic Deployment vs. Continuous Deployment
For many years, the method of software development followed a pretty static and time consuming process:
- Define scope for this release
- Program features for the release
- Debug, if errors, back to #2
- QA (generally someone testing who ISNT the developer), if errors, back to #2
Now, I know a lot of shops that would laugh at this list and claim that they do only #2 and #3, then shove it up to the servers. This can very well be the case, but I’d hazard a guess that those companies have never gotten close to scraping the alexa top 100, nor would I believe with a straight face if they told me they had uptimes in excess of 99%. There is another way, though: Continuous Deployment.
Simply put, Continuous Deployment is when instead of having large, multi-bug/multi-feature releases, you instead implement many, many small changes continuously throughout the product lifetime. At first glance this method might sound dangerous to many sysadmins who like to plan a task to an atomic point algorithmically, but it’s actually extremely safe if you follow some standard methodologies, namely having an environment where you can test changes (for Stack Exchange, this is meta.stackoverflow.com) and use a build proctor like CruiseControl or TeamCity to facilitate the build process.
One of the main benefits of Continous Deployment is the fact that small changes are easy to deploy and usually just as easy to revert should problems arise. It becomes significantly harder to revert a deployment if there is a large corpus of changes contained in the update.
Code it like you’ll need to change it
One thing that new programmers often fall prey to is the laziness of not properly architecting their application. I am sure that every programmer remembers their earlier code shenanigans when they’ve needed to update something they wrote earlier in their career only to find that it was fraught with all sorts of freshman mistakes. To put it simply: you need to write your code to be portable, easily understood, and utilizing the best practices available for that language. If the language is OO, this means obeying standards like MVC, creating class interfaces and making the app as dynamic as possible. The reason should be clear: code that’s easier to interface with is easier to change and update, and it generally means that bugs introduced in most modules should not have global impact to the site.
Load Balancing is Good
Load Balancing is paramount to a seamless customer experience. It allows you to do some pretty cool tricks, but only so long as your web application is coded to properly handle a load balanced environment. The main thing one needs to think about when coding an app in a load balanced environment is that there’s no guarantee that request ‘n+1′ is going to land on the same server as request ‘n’, so you need to handle sessions in a centralized/db manner so that the cookies in the browser link you up to the proper session regardless of what server you hit. This does NOT mean, however, that you should disable persistance or affinity in your load balancer! There are benefits to keeping a session on the same server with regards to file caching and the like; we just want to make sure the app is ready should you want to take one of the servers down for maintenance.
Once properly load balanced, you gain several levels of win. First off, your app will be much more performant if there are more servers available to handle the load. Secondly, if you have multiple servers available, you can bring one of them offline and your users should never know the difference. A side benefit of Load Balancing is the peace of mind you’ll get knowing that if a box eats itself alive at 3am, your site will survive without you needing to fire up the laptop or, heaven forbid, head into the office at an extremely early hour.
How Stack Exchange does it
It may become clear after reading this that what I’m talking about isn’t necessarily how Stack Exchange works. People who visit us often know that we do have site downtimes for various reasons. For those curious, the below section is how we handle our deployment and development process.
There is one big place where the Stack Exchange Core Q&A Services are vulnerable, and this is at the database. Currently we employ SQL 2008 R2 database servers (currently SP1 as of this writing), with the primary server constantly replicating to the backup server via transaction log shipping. Those familiar with transaction log shipping will know that employing this method is really only usable in an active/”hot passive” mode. One of the downsides of using SQL Server is the lack of solid high availability given our transaction load. Simply put, both the asynchronous and synchronous methods of active/active can’t keep up with the sheer volume of transactions we throw at it. We’re hoping that when Denali comes out this spring, the HA features will improve to the point where we can be fully active/active.
The reason we incur downtimes for upgrades these days has to do partially with ease of execution and cleanliness; because we use transaction log shipping, if we wanted to go active on the backup node we’d have to break the replication and convert the backup server to the master server, then re-setup replication. Would we do this for a couple windows patches? Absolutely not. It’s a nontrivial amount of work that can incur human error and is unnecessary. We reserve this process for when we do have a major database emergency. We’re hoping that SQL 2012’s enhanced failover capabilities will permit us to enter read-only mode for brief maintenance windows, but this will need serious testing first, once Denali (2012) is released.
Development wise, this is the continuous deployment procedure that Stack Exchange uses:
- A developer will get the latest HEAD from the mercurial repository.
- That developer makes a change, then commits that change back to the Mercurial repository.
- TeamCity queries Mercurial (every 60 seconds) to check for new changes. If a change is found, TeamCity builds it immediately in the development testing environment.
- Once the developers have given the change a test in development, the developer then deploys the change to meta.stackoverflow.com.
- If the dev is having a good day, meta.so’s users won’t report any bugs and after a period of time the dev will push the code to everything except stackoverflow.
- If the change stands up to rigorous testing on the other approximately 80 sites in our network, the change will be pushed to stackoverflow, aka “the fire hose.”
One might be curious as to why we wait till the last minute to push code changes to stackoverflow. The reason behind this has to do with the fact that stackoverflow gets several orders of magnitude more traffic than the rest of Stack Exchange combined. A case study in this procedure: when we deployed ProtoBuf v2, the change worked great everywhere else, but as soon as the change was applied to stackoverflow.com, a “cold start” bug seriously degraded users’ experience.
An important thing to note as well is that a great deal of the sites’ code changes are toggleable by configuration, so if a problem is found it can be reversed and mitigated much, much faster than it would take a developer to debug and fix the problem. Employing this method where possible in your applications will be helpful for many reasons.
Uptime! Fsck Yeah!
For most of our readership, the problems described above might not apply to you. You may not have quite as many hits as Stack Exchange, or you might have less need for high availability and uptime. This isn’t to say that the above doesn’t apply; architecting for uptime should be in every developer’s best interests and best practices.
As always, your questions and comments are most welcome, feel free to post below.
Welcome back to my series on WiFi. In Part 1 of the series, I began with some basics of RF and explained some differences about antennas. It should be apparent at this point that there is a science behind this activity, and I’ll take this moment to warn you thoroughly before we move on: These posts are a good way for you to become familiar with WiFi and should provide you with some solid knowledge to help improve your WiFi coverage. However, this brief education is not a replacement for having an actual RF engineer do a site survey of your environment! If you have a “must work right the first time” environment, and you’re reading this because you’re the decision-maker and don’t have the slightest hint about what all this is about, Get An Expert. They do this all day long. It’s money well spent.
If you do use these techniques below, Your Mileage May Vary. It’s also important to note that if you go to all this work, setup your access points, then your neighbor goes and installs his AP right next to yours on the same channel, then you’re going to be stuck re-doing these activities all over again. WiFi isn’t a static situation; as people get more WiFi-connected devices, the playing field changes, and it will change on you, I guarantee it.
Understanding RF Interference and What It Does to WiFi
You hear people joke about microwave ovens interfering with WiFi equipment pretty often. Most people laugh it off as an urban legend. It’s not. Below, I have included some RF spectrograms for your entertainment. If you haven’t seen images like this before, they are a visualization of signal frequency and intensity over time. Past-to-present is a top-to-bottom relationship, and the colors are a heatmap (with red being a strong signal.) As you look at both types of graphs, the channels start from 1 at the left hand side of the graph, and go up through 12 in the right hand side. NOTE: Quick Shout-Out to the guys at metageek.net for creating the awesome Wi-Spy and accompanying Chanalyzer Pro software. We paid full price for the DBx bundle (Comes with the Wi-Spy DBx and Chanalyzer Pro) and I definitely feel like it was money well spent. Check them out if you want to do these types of visualizations yourself.
These images show what the wireless signal looks like in my suburban home. Not a lot of interference in this visualization, you can see my home Cisco Aironet 1240 AP humming along happily as visualized by the wavy lines in the waterfall spectrogram, above. In the lower graph, we see signal strength (Amplitude) measured by frequency.
Let’s shake things up, and show what happens when you fire up a microwave oven:
Look out, here comes that microwave burrito exploding all over your RF Spectrum! For about 30 seconds, I nuked a mug of water and this was the result. You can see through the swamp of RF that the access point does its best to compensate for the signal interference, but that’s a pretty strong blast of RFI.
Do you have a baby monitor at home? Is it on 2.4ghz? Ready to see what it’s doing to your wireless signal?
These two charts were captures I took from my friend’s house (incidentally, the gentleman who I mentioned in the previous post — he has a penchant for wifi problems.) I was over his house and ran some traces to get a visualization of his wireless conditions in preparation for installing a new wireless router. I asked him if he noticed the WiFi being slower at night and he’d mentioned that it did indeed seem to be more problematic at night. Welcome to the wonderful world of random equipment in your home causing issues with your wifi. In the above trace, you can see the telltale wavy lines of the access point, trying to power its way through the interference. The graph below has just the slighest hint of bell curve, which is where his AP was situated in the RF Spectrum. I believe in this case his AP was on channel 3. Needless to say, we popped his new wifi router on channel 11, which is quiet in these graphs.
One final graph to show. If you scroll back up to the initial image I showed of my suburban home, this will give you an idea of what your general household’s 2.4gHz spectrum might look like. Now, compare that image to this:
This, my friends, is what the 2.4gHz spectrum looks from the Stack Exchange offices. We’re located down by Wall Street, on the 26th Floor of One Exchange Plaza. Our scenic vantage point does come with a cost! These spectrograms show just how much RF interference we are subject to at this location. Astute readers may notice the timescale difference on the graphic, but I assure you that the 30 second view is just as nasty.
What can we take away from these charts? One could safely summarize this entire section as “Location and the gadgets in your home both play a significant role in how your WiFi might perform.”
Mapping Your Wireless Landscape
It’s worth noting that even though the above charts were taken using a very expensive measuring tool, your laptop’s WiFi card is a potent ally in your quest to improve your coverage. For the next section, I am assuming that you firstly are running Windows and have downloaded and installed both Vistumbler as well as Microsoft SQL express. I am aware that a strong number of our readership are Linux based, and there is also a big Mac contingent. I’ll unabashedly say that the steps I’m following and the software choices I made were purely for my convenience, but I hope that I explain the process in easy enough terms so that the tinkerers out there can take the wheat from the chaff, so to speak.
Step 1 – Take some measurements!
Vistumbler is a wardriving utility that, when attached to a gps, can help you map where there are wireless access points in range of your device. We’re going to borrow it for a more sedentary purpose. Fire up Vistumbler, set your laptop in the areas where you want to consume your WiFi signal, and then start the scan/capture process. Leave the laptop there for at least 30-60 minutes, as we want a whole lot of datapoints to work with. It will keep track of every time it hears of an access point and record the relative strength of the signal. Once the time is up, you can either run the detailed export to CSV now, or “Exit (Save DB)” and come back to export the file later. NOTE: If you’re in a tight urban region like we are at Stack Exchange, leaving Vistumbler up for 30+ minutes will result in a tremendous amount of data! It’s wise to have a very powerful PC to handle the vistumbler export process, or do seperate scans and aggregate the data together in a later step.
Step 2 – Massage the data!
Sadly, Vistumbler’s export to CSV does leave a bit to be desired with its field quoting. We’re going to open the csv in Excel, since it seems to be especially forgiving. Once we’ve opened it in Excel, we’ll do the following steps:
- Make a new column for Location. Populate the column with a location name. You’ll want this when you’re querying SQL later on.
- Save the file as an Excel spreadsheet.
- Fire up SQL Server Management Studio.
- Create a new database if you don’t already have a good scratch database, then right-click the db and select tasks->import data.
- Using “Microsoft Excel” as a datasource, submit your new excel file as the source data. HINT: If you’re getting an error about unable to find a particular OLE provider, and you’re on 2010 like me, with 64bit windows, you will likely need this link to load said provider.
- Select the destination database; elementary stuff here. From this point on, “Just Keep Hitting Next,” except for the prompt where you specify the destination table name. I strongly advise you to change that name to something easier to type rather than the default date/time string. Finally, Finish to start the import job. This may take a while, so don’t be afraid if it seems like it’s taking too long.
- Repeat these steps for each file of data. Be sure to specify the same table name for each import, it will append to the database.
Step 3 – Analyze!
With this complete, congratulations! You now have data in a sql server that you can use to leverage the power of SQL to get some statistics from. I’ll admit I’m a SQL neophyte — I can do some joins and “GROUP BY”s but I’m sure others could tease a lot more information out of it than I have. Here are some basic queries for your dataporn pleasure:
Get a sorted list of strongest access points across all locations:
select LOCATION, SSID, AVG(SIGNAL) AS AVGSIGNAL FROM [dbo].[wifilog] GROUP BY LOCATION,SSID ORDER BY AVGSIGNAL DESC
In the above query, we see that in our sysadmin office, as well as somewhere in our office (for shame, I forget where I took the trace!) the strongest signals are SO-GUEST (our current guest wireless AP) and ROVIO (for our cute little mobile webcam.) We’ve also got a couple shadow AP’s (as specified by NULL) followed up by some other AP’s that I’m not sure who or where they are. Suffice to say, our current AP is pretty strong in these two locations.
Find the average signal strength of all APs at a particular location:
select SSID, AVG(SIGNAL) AS AVGSIGNAL FROM [dbo].[wifilog] WHERE LOCATION='sysadmin' GROUP BY SSID ORDER BY AVGSIGNAL DESC
Similar to the first query, you can drill down by a particular location and see the top AP’s seen at that location. This is useful, but what we’re really looking for is the least used channels at a certain location.
Get a channel utilization chart
select LOCATION,CHANNEL,SSID,AVG(SIGNAL) AS AVGSIGNAL FROM [dbo].[wifilog] GROUP BY LOCATION,CHANNEL,SSID ORDER BY AVGSIGNAL DESC
The above gives us some pretty useful information. We can see here that channels 8 and 11 have several entries, and only one device in range of our scans is on channel 6. Using some critical thinking, it’d indicate that channel 3 might be a good choice should we want to add a new AP to this environment. Lets massage the query a bit to see if that’s confirmed by our other data:
select LOCATION,CHANNEL,SSID,AVG(SIGNAL) AS AVGSIGNAL FROM [dbo].[wifilog] WHERE CHANNEL in (1,2,3,4,5,6) GROUP BY LOCATION,CHANNEL,SSID ORDER BY AVGSIGNAL DESC
At first blush, one might be enthusiastic about channel 3 given the fact it’s not used as the main carrier frequency in any of our entries. Be careful, though, for WiFi channels have some pretty strong overlap:
As this graphic shows, each wifi “Channel” is merely just a 5MHz swatch of the 2.4ghz ISM band. WiFi signals have a 22Mhz bandwidth, so realistically there’s only 3 channels one can use in an environment without any fear of interference or overlap. Because of this, one needs to take into account not only the channel but also the signal strength of potentially interfering access points.
In our case at Stack Exchange, there are just so many APs utilizing so many channels that we’ve ultimately decided to go with a Cisco controller-based access point layout, which will dynamically change channels based on signal conditions in realtime. For those of us at home, this is way too expensive of an option for most. Sadly, we’ll just have to take these data queries and give it our best shot.
I hope this blog series helps you a bit with your next WiFi installation. In summary:
- Antenna choice matters when you’re trying to cut through interference or travel long distances.
- Most residential building materials will not diffuse wireless signals to an appreciable amount unless you’re talking about very far distances, (i.e. trying to use your laptop on the third floor at the far side of your house when the AP is in the basement, for instance.)
- Be aware of electronics in your home that might share the 2.4GHz radio spectrum; they can seriously affect your wireless transfer rate and signal strength.
- Apps like Vistumbler can catalog used channels in your environment and you can then use this data to find a quiet spot in the spectrum.
As always, I welcome your comments and criticisms, below. Also, feel free to share any specific SQL queries you used that might help glean even more information from the datasets you’ve gathered!
It has been my experience that many people simply buy a wireless access point, plop it down squarely next to their home cable/dsl modem, and assume that’s all they have to do to maximize their WiFi experience. Oh, were it to be so simple! I’d like to take a few minutes of your time today to cover some of the basics of what WiFi is, what it is and is not capable of, and how you as a SysAdmin or a home user can do a bit of detective work to help ensure your WiFi experience is less prone to issue.
Let’s take a moment and talk about Radio-Frequency Radiation. RF is a form of non-ionizing radiation where waves of energy radiate from a source and follow a predictable pattern based on the transmitter power and antenna. Radio waves are measured based on the size of the wave, and how frequently the wave oscillates. The frequency is measured in Hertz (Hz), or cycles per second.
Wavelength is the distance the radiation travels before it completes a single cycle. As we are mentioning travelling, we need to know the speed, right? This, my friends, is the speed of light.
C = f * λ , which translates to:
Speed of Light = Frequency * Wavelength
OR, if you're lazy, 300/Frequency in megahertz.
Light travels approximately 300 million meters per second, we can drop a whole bunch of zeros from the equation and still be reasonably accurate.
WiFi signals operate on 2.4 gHz (2.4 billion cycles per second), and that means that one full wave travels around 12-13 centimeters before the waveform returns to its starting position relative to the axis in the graph. 802.11a and 802.11n operate on the 5gHz range, which would put the signal wavelength at 6 centimeters.
OK, but, why should I care about this when all I want to do is surf porn and play online games? The answer lies in the fact that if your antenna is not properly suited for these measurements, it won’t work that well. The antennas you get from your access point vendor are “suitable” but far from ideal.
Not many people realize it, but there is an aftermarket for antennas for access points. When people/companies buy commercial grade access points, they usually don’t include any antennas, as it’s assumed you’re going to get the proper antenna for your application.
So, what types of antennas are there and what are the differences?
Omnidirectional – These are the antennas that people are most familiar with. They will usually be oriented vertically, and radiate their signal on the horizontal plane in all 360 degrees. See below radiation chart which does a good job of visualizing how the energy travels out of a veritcal omni antenna.
Directional/Yagi – Directional antennas are designed to send a signal straight to a specific spot with pinpoint accuracy. If you’re trying to setup a WiFi link between your house and a neighbor down the street, you’d need a directional antenna. The Pringles Cantenna is an example of a homemade directional antenna. Commercial antennas more closely resemble old TV antennas that everyone seemed to have on their house back in the 20th century. The below radiation pattern does look a bit weird, but understand that the directional beam is designed to be highly selective of signals based on its relative orientation versus the target signal. This allows a directional antenna to receive and send to stations much further away than an omnidirectional antenna, which sends RF energy in all directions.
patch – Patch antennas are normally flat antennas that are designed to radiate in a forward direction extremely well, with the signal attenuating sharply at the periphery. The radiation pattern below does have some similarities to the directional/yagi radiation pattern, but its lobe is more rounded in the forward direction. The patch antenna type is a good choice when you want to direct most of your energy in a particular direction but don’t necessarily want the pinpoint accuracy of a yagi.
What blocks WiFi?
WiFi, operating in the 2.4ghz and 5ghz ranges, propogates in “line of sight.” Due to the short wavelength, the energy dissipates quicker if it is not channeled into a high-gain directional antenna. Consumer grade access points come with omnidirectional rubber duck antennas, which people usually orient vertically. If you look at the above radiation pattern, you’ll see that there is a void of energy directly above omnidirectional antennas when they are oriented horizontally.
All matter will attenuate RF energy to some extent as it passes along. The question on many people’s minds is what are the worst places you can install a wireless router or access point? Believe it or not, most materials in the home are not capable of attenuating your WiFi signal to a noticeable degree. In order for WiFi signals to be blocked effectively, they need to move through several layers of dense material in order to shed the energy required to become unusable. Some antenna manufacturers will quote how well the radio waves will propogate from a given antenna, as shown here for one of Cisco’s branded antennas:
The density of the materials used in a building’s construction determines the number of walls the signal must pass through and still maintain adequate coverage. Consider the following before choosing the location to install your antenna:
- Paper and vinyl walls have very little affect on signal penetration.
- Solid and pre-cast concrete walls limit signal penetration to one or two walls without degrading coverage.
- Concrete and wood block walls limit signal penetration to three or four walls.
- A signal can penetrate five or six walls constructed of drywall or wood.
- A thick metal wall causes signals to reflect off, causing poor penetration.
- A wire mesh spaced between 1 and 1 1/2 in. (2.5 and 3.8 cm) acts as a harmonic reflector that blocks a 2.4-Ghz radio signal. (NOTE: as a commenter below further explains, this type of mesh is common in plaster walls from the 1940s as well as in stucco applications.)
I once ran into an issue with a friend who had his wireless router installed in the basement, next to his cablemodem. He was having sporadic connectivity issues in a second floor room and asked me to come help diagnose. Sure enough, his room was directly above the wireless router, two residential floors below, and given that traverse and the location of his room in relation to the radiation pattern, there wasn’t enough RF energy propogating up into that location. The short-term answer for the problem was to orient his access point antennas horizontally, so that the radiation pattern is then set on its side, covering a wider swath of his house.
In a blog post to come, I will show you some methods you can use to help properly locate your access point and also help you decide which frequency your access point should operate on. Stay tuned, and as always your comments,criticisms and suggestions are always welcome!
In order for system administrators to do their job well, particularly in a tech company, they need to know a lot of what is going on. This is because just about everything is done on the systems we control.
Lets look at some of examples of things system administrators probably need to know, and why they need to know them:
Example 1: Upcoming projects
In order to make sure we have enough capacity in servers, network, backups, etc we need to know what is incoming. If we don’t, it can be a lot more difficult to be prepared and that can slow things down.
Example 2: How a service or code works
System administrators are generally the first line of troubleshooting. In order to troubleshoot a problem, we need to know what is being done before we can trying to figure out why it isn’t working. We also need to monitor and backup the system, knowing how it works tells us what details to monitor and what data needs to be backed up.
Example 3: How important something is to the company
Resources are always limited. Although you want minimum standards of things like monitoring and backing up, time and money is limited — system administrators need some context for setting priorities. This can also help with figuring out an appropriate level of security.
Example 4: What people do
System administrators control access, so we need to have an idea of what sort of access people should have. We also need to know the best people to talk to when their is a problem or there is maintenance to do.
Knowing without Being Nosey
If we accept that system administrators really do need to know quite a bit of what is going on, then system administrators need to figure out how to do this without being nosey:
Definition of NOSY
: of prying or inquisitive disposition or quality : intrusive
The challenge is to have a good handle on what is going on, without prying or being intrusive. Part of the difficulty is that this is a two step process:
- Find a way to sincerely not be nosey
- Don’t come off as being nosey
These two steps are not easy, and require constant vigilance. If you have mastered them, then you probably don’t have to ask for information most of the time — information will be given to you and you will be invited to be part of the process.
Getting to that point is tricky, and I certainly don’t claim to have all the answers. In part it requires the cooperation of the other people in your company, but if we hold up our end of the bargain it goes a long way.
So what can system administrators do?
Don’t be nosey. Make it clear that knowing this information is not for your entertainment or to make you feel special, rather it is to enable you to better do your job.
Make things easier. Although sometimes doing your job requires you to get in the way, you should strive to add requirements because it makes things for everyone easier in the long run, not to exert power or justify your existence. If you don’t need to actually add a process or make things more difficult — then don’t. In many companies you want to be conservative with how much process you add.
Be consistent. Telling one person on the system administration team should be as good as telling everyone. Once you get involved, document, backup, and monitor everything. If your team is consistent it goes towards developing a reputation of making things easier for everyone.
Be respectful. If you work with great people, making sure things are good on the system side should be about being thorough. It is an SA’s job to think about that side of things full time, but it doesn’t mean the people you work with didn’t already think about it, or are being dumb if they didn’t.
Know your place. If you are invited into the process of a new project, keep in mind why you are there. If you have a really good idea out of your area of expertise try to share it tactfully. But if you are there mostly to listen, then try to mostly just listen.
In the end I think knowing everything that is going on, without being nosey, is pretty difficult. Most of us at some time or another have probably failed at some of the things I listed — it takes some honest self evaluation to find where you are falling short. Any readers have ideas for how to stay apprised of everything without being nosey?
On more than one occasion I have been asked “how do I get a job in IT?” This question could easily be shrugged off and relegated to the canned answers pile; phrases I have personally uttered in the past include “go get some certifications then send out resumes” or “play with computers for a few years and then apply.” These answers really are a cop-out on my part so I’d like to take a few minutes to apply some serious thought to the query and offer my thoughts.
What People Will Expect of You
While most people would say that having a large breadth and depth of knowledge in computers is the primary requirement for being employed in IT, I would disagree with this. There are a handful of very critical attributes that you will find in any IT Rockstar:
- Good Problem Solving Skills
- Critical Thinking
- Strong work ethic
- Ability to handle “Burst Stress”
Problem Solving is possibly the most important attribute any good IT team member will have. Problem solving is the core of our vocation, when you think about it. IT is tasked with solving problems that the other employees aren’t able to handle. You’ll also find that in many workplaces, the IT guys are the ones helping to solve other complex problems in the enterprise because of the problem solving skillset they demonstrate. Critical Thinking is another important skill to have when talking about technical jobs. Oftentimes the problem I’m trying to solve does not have an easy-to-see solution (sometimes, the cause itself is not immediately apparent.)
Do you like working long hours for little praise or thanks? That’s pretty much working IT in a nutshell. Performing server maintenance usually needs to wait until after hours, which generally means you’ll be putting in a full 8 hours for that workday, then many more hours that night to do the after-hours downtimes. Couple this with “burst stress” and you’ve got a recipe for gray hair. Burst Stress is common in jobs like police work or firefighting, where you have long periods of low stress followed by short bursts of extremely high stress situations.
What do you want to do?
There are a handful of disciplines in IT, each with their separate purposes. What follows is a breakdown of the individual disciplines and what that type of work entails. Regardless of what path you choose, you’ll likely start in an IT Generalist role, and many people stay in that role their entire career. I myself genuinely enjoy being a generalist; it means that any given day I might be working on any number of problems that aren’t the same old issues over and over again.
IT Generalist — This is often the least respected but most useful role in IT. As a generalist, you are a Jack of All Trades. You’re expected to understand not only Desktop and Server Support, but also have a useable knowledge set in Telephony, Networking, Backup and Disaster Recovery, and Security. When you start your new IT career, you’ll usually start as a quasi-apprentice in a generalist role. Once you’ve “earned your stripes” in a few disciplines, you’ll be more educated and ready to move to a specialization, if that’s your pleasure.
Desktop Support — The Desktop Support team will generally be tasked with maintaining the software and equipment that other people in the company utilize to get their jobs done. This job is very demanding from a customer service perspective, as the person doing this job will oftentimes be assisting users who are already annoyed that their machines aren’t working the way they’d like! Skillset wise, Desktop engineers need to be highly fluent in whichever desktop operating system your company uses, as well as any applications the company makes use of. Generally speaking, you’re going to need to know Windows and Office like the back of your hand if you want to get a job like this in the majority of businesses.
Server Support/Systems — The Systems or “Server Support” team has similar requirements as Desktop Support when it comes to strong knowledge sets of operating systems and software. The jobs diverge when you look at the “back-of-the-house” operations that a Systems team is often assigned. Knowledge of datacenter operations (power distribution, hot row/cold row, rack positioning) is essential in this role if your organization has more than just a “closet in the back with a few servers.” Server Support is also usually in charge of all backend/utility systems in the organization, including directory authentication, mail and groupware, and administrating backups.
Networking — This group focuses mostly on interconnecting sites and equipment together. To get into this group, you better be prepared to prove you know the OSI model like the back of your hand. This is a tough group to coast in; TCP/IP can be fickle under wavering hands. Usually, a particular company will standardize on one vendor for their equipment, and you’ll be expected to know the operating system for that particular vendor. If you’re looking to get into the networking team at a new company, you should likely know Cisco and Juniper OS’s (Cisco IOS/ASA-OS, JunOS, etc.) Even if the company does not use either of these major brands, having knowledge of how other platforms operate can assist you in doing the job. Networking also is susceptible to the above mentioned “Burst Stress”, since if a site-to-site link is down, it’s possible that hundreds of people are sitting waiting for you to fix the problem.
Security — To be a member of the security team is to be alternately loved and hated by differing groups in your organization, often different groups at different times. Security generally finds themselves tasked with doing audits of internal data security and setting standards to help achieve compliance with data security standards. Computer forensic applications are the toolbox of the IT Security team; to do the job it will help to be seasoned at data analysis/data mining and event correlation. Security is usually the team tasked with defending audits as well, so it helps if you have good verbal communication skills. Finally, Security is sometimes tasked with coming up with the Disaster Recovery and Business Continuity Plans for a business. In some cases, other departments handle this, but in my experience this has been a Security-type role.
Database Administration — DBA is a tough specialization to be hired in. Brent Ozar has a great blog post covering this, so I won’t go in to too much detail but instead refer you to Brent Ozar’s excellent blog post series linked here. Suffice to say, you need to be able to speak SQL and make it sound like Shakespeare before you can roll with the titans like Mr. Ozar.
Telephony — I’m hesitant to group Telephony into IT, but many times it falls under the moniker of IT, so we’ll cover it here. Telephony is the specialization that handles telephones and telephony technologies. You’re going to want to know the details of how signaling works in PSTN networks, things like TDM, FXO,FXS, E&M, CAS, and a whole lot of other crazy acronyms. Depending on the size of the organization, you might also need to have some cross-pollination with the Networking skillset.
In a future blog post, I will cover what I consider the “elephant in the corner” when it comes to hiring in IT. Remember, it’s not always what you know, but who you know that gets you hired. As always, your comments and criticisms are welcome/encouraged; I’d like to hear what the community feels on this topic.
System administrators can sometimes seem like they are trying to get everyone down. Someone goes to them with a great idea, application, feature, or just something they just want to get done. When the sysadmin comes in, instead of being enthusiastic about the great idea, he starts talking about viruses, hacking, earthquakes, floods, fires, Vogon invasions, and all the things that can go wrong. Because of this, the natural reaction is to stick the sysadmin in the basement and stop inviting them to parties.
In order to work past this issue, it takes a little work from both sides of the table. Coworkers need to remember that disaster is part of a sysadmin’s job and that Murphy’s law is part of what they do. Part of a sysadmin’s job to think about this stuff all the time because we are expected to have backups and to be able to recover from small events to major disasters. Major disasters are uncommon but do happen, hence the importance of offsite backups at a minimum. Small disasters, such as power failures, router failures are actually quite common. When a sysadmin is doing a really good job, people don’t even know about some of the smaller failures.
It also falls on the sysadmin’s shoulders not get stuck in this gloom and doom world. We have remind ourselves not to get in the habit of saying No to everything because something could go wrong. This is important because the danger is that that people will try to start bypassing you or just not believing you when you talk about very real threats because they think you are crying wolf. It largely comes down to being mindful of the bad things that can happen while being enthusiastic about all the good things that can come from a new idea or feature.
It can be tough not be the guy in the street with the end of the world sign when in a way it is part of your job, but learning how to find the balance is one of the challenges of being a sysadmin.