OTRS ITSM Part III(b): Your Shop Is a Business

Introduction

In Part III(a), I mentioned that ITSM (the concept itself, not the OTRS module) really doesn’t make sense unless you think of your shop as a business unto itself. (I said that exactly, in fact. Now I’m just copying and pasting to fill space.) In that post, I focused on thinking of yourself as a business and managing your brand. In this post, I’ll talk about managing your shop’s brand and how ITSM helps you do that.

I’m going to continue with the assumption that you’re in the IT Department of a company, not running your own IT consulting firm. If you’re doing that, you probably know all this already.

A Generic Sketch of A Business

The idea of a business is pretty simple: in exchange for money, a business provides you with goods or services. But there’s a lot that’s implicit there. For example, if you give me a hamburger today, when do you want to be paid?

Probably before you give me the hamburger, right? And did I agree that the hamburger is acceptable if it is medium well and has pickles on it? Or for me to be willing to pay for it, does it need to be medium and have tomatoes and pickles? So let’s refine our definition of a business: In exchange for an agreed upon amount of money, a business provides goods or services that meet an agreed upon standard within an agreed upon amount of time.

In other words, every interaction with a business is a temporary partnership. In the case of buying a hamburger, it’s a pretty short partnership. In the case of purchasing a software license, it can be a long or even indefinite partnership.

IT As Partner

Your IT department provides valuable service to the business it is a part of as long as the work you do aligns with larger business goals. However, in most businesses I’ve been a part of, there’s no direct exchange of money for each service. I hear that large IT departments in big corporations do bill time back to the departments that utilize their services. If that’s not the case for your shop, though, your role as partner might be implicit–or it might be ignored. In fact, you might feel a lot like you’re just a team of go-fers. The business recognizes that it needs IT, but it really just needs a team to do its bidding. It is not offering IT a place at the table so much as keeping them around to pick up the table if it falls over. If you’ve ever been in a meeting where the first time you’ve heard about a new document management system is during the week that you’re being asked to help deploy it (and you didn’t just start working there that week), you’re probably in a shop where the CEO tells IT what he wants without asking IT what’s possible and reasonable.

If that’s all your business wants, ITSM probably can’t help much. It’s unlikely that your “partners” will have respect for the constraints that ITSM will put on your team and your customers. For ITSM to work, other departments must recognize that your expertise can help them be more successful. Password resets and installing new printers is one thing. Deploying new hardware company-wide or integrating new accounting software in the environment are jobs that need teams of experts with proper resources in order to succeed. If you are a partner, the VP of Finance will call you in to review processes and consult on what you can do to alleviate the problem before they tell you what to do. Note that they still get to tell you what to do, and it still might not be what you suggested, but they take your input as part of the decision-making process. In this case, though, you’ve been invited to hold a stake in the project and let your partner know what they can expect you to deliver.

How Does ITSM Help You Be a Better Business Partner?

Back in my first post on ITSM, I gave a brief introduction to some of the categories of IT Service Management. I’m now going to elaborate on how things like Change Management make you a better business partner.

Configuration Management

You’ve probably heard the term “Asset Management.” I used it in Part I of this series. Some will argue that there’s a difference between “Configuration Management” and “Asset Management.” I agree with that, but to avoid a semantic argument, let’s say that Configuration Management includes Asset Management.

Assets are pretty important for a business. If I’m going to sell hamburgers, I’d better have some ground beef. If I’m going to provide electrical services, I’d better have the tools I need that are in working condition. You can see why this would be important for an IT shop. When a new employee starts, you’ll need to know if you have a laptop for them, that it has the latest image installed, that they have an AD username and password, etc.

If someone from HR calls you up and says, “I have a new employee starting on Monday, and I need a laptop for them,” and you say, “Gee… Uh… Okay… Um… I ‘ll have to check and see if we have any laptops…” You’ll have to check and see? That’s not going to be great for your brand. You’ll lose the trust of your partners–and eventually, if none of your partners trust you, they’ll look for ways to go around you. This can only make your life harder.

In addition to keeping track of all the pieces, you also want to keep track of dependencies. It’s not enough to have a motherboard if you don’t have a case to put it in or RAM to accompany it. It’s no use having a server if you don’t have Cat 5 cable on hand. And then there’s the stream of software dependencies when you’re installing something new…

Now imagine that you know exactly how many printers you have, but you don’t know where they are in the building, whether they’re all operating properly, or what their makes and models are. Eventually this will come back to bite your business in the rear. Ideally, you’ll have a database somewhere that contains all of this information. If you can pull up information on what equipment you have and key information about it at any time, that’s going to be great for your brand. Your partners will know that you’re on top of your game and will gain respect for your authority in your domain.

OTRS::ITSM provides a Configuration Management Database.

Incident Management

What do your partners do when they have a problem with your product? How will you record interactions? How will you resolve them and make the resolution repeatable if the event comes up again?

You need some policies and processes in place for dealing with service and purchase requests, complaints, and system alerts. If you don’t have them, you’ll become a firefighter. Every time a customer calls in or raises a red flag, you’ll either have to respond immediately or ignore it, letting it get lost in the shuffle (or maybe exploding not too far down the line). If you want your brand to have the highest possible value, you’ll establish clear policies on how you expect your partners to alert you to their needs and how you expect your team to respond to those alerts.

Of course, if you’re looking into OTRS::ITSM, you know that OTRS already includes pretty much everything you need for incident management in its ticketing system. It’s not the only possible use for the ticketing system, but it’s probably what you’re using it for.

Change Management

We’ve all had one of those “Oh, crud!” moments when we realize we just executed that SQL query on a production server. “Did I make a backup before I did that? Do I know how to restore the backup?” And while it’s bad enough that you’ve made the mistake, you might also be bringing heat down on your boss and any other number of people who interact with that system. This can impact your relationship with your partners as well as your personal brand and team morale.

There are basic things you should be tracking whenever you implement changes in your business, regardless of the scale. The larger the scale of the change, the more important it is to track these things, but it’s always appropriate to track them:

  • What is changing?
  • Why is it changing?
  • Who requested the change?
  • Who is responsible for the change?
  • What will the change impact/what’s the overall scope?
  • When will the change be initiated?

  • When will the change be complete?
  • What is the rollback plan in case of issues/failure?

If you don’t answer all of these questions well before implementing a change, you may be reaching for your disaster recovery kit sooner rather than later. If there’s no agreement about who is responsible for a change, it might never happen. It definitely won’t happen on schedule. If there’s no schedule for initiation, it might end up interfering with other processes when someone just decides to roll it out. And if there’s no rollback plan, there’s no way to mitigate a worst-case scenario.In some cases, a few of those questions will be difficult to answer. “What will the change impact?” for example. Answering this question accurately requires having a detailed understanding of all the dependencies in your business. Your partners may resist waiting on answers to questions like “What’s the overall scope?” They may want to implement the new hiring system by the end of the month without much regard for the consequences. But as you provide transparency into your process and manage lots of successful implementations for your partners, they’ll trust you when you say that something is important and that following your established policies is what allows you to help them reach their goals.OTRS::ITSM to the rescue once again. All this data can be entered into the system and built into a workflow.

Conclusion

IT Service Management is involved. It requires a lot of documentation. It requires imposing constraints on how you do things. If you’re not required to implement it (and even if you are), there will be times when you’ll need a reminder of what is motivating the use of such an elaborate and sometimes cumbersome process. I’ve tried to show how ITSM, and OTRS::ITSM in particular, strengthen your ability to be a reliable, respected, and successful business partner. My motivation here has been the same as for OTRS: if you know how the system is intended to be used, figuring out how to use it is a lot simpler.

I’m not entirely sure where I’ll go next in exploring OTRS::ITSM. If the concepts I’ve explained make sense to you, then the official documentation is really your next step. Let me know if you have specific questions about implementing OTRS::ITSM. Otherwise, I’ll continue to look for gaps in the documentation that need fleshing out.

Love and kisses,

Tyler

OTRS ITSM Part III(a): You’re a Business

Introduction

ITSM really doesn’t make sense unless you think of your shop as a business unto itself. This is easy if you’re a consulting or services firm that does contract work. If you’re in the IT department of a company, you might be less inclined to think of your shop as a business or service center, which could mean that you’re in a very reactive shop that seems to shift focus at the whim of whatever VIP last touched base with your boss about what IT should be doing. For all your geeky powers and ability to ruin the entire business with 1 shell script, to a lot of folks outside of IT, you’re just another go-fer. So in the next couple posts I’m going to focus less on your geeky powers and more about having a business-minded approach to yourself and your department.

My Business Cred

I’m not an expert at business, but I’ve had a lot of exposure to running businesses. I’ve been part of a few start-ups; I have several entrepreneurs in my family; and my wife has shared with me pretty much everything she learned while getting her MBA. I’ve also had a lot of success employing the principles I’ll be writing about here, so at the very least I can verify that what I’m telling you has actually worked once.

You’re a Business

This is business 101 for everyone: You, yourself, are a business. Your name is your brand, and your face (or maybe your face tattoo or that weird fedora you always wear) is your logo. Your skill set and the benefits you can provide are definitely drivers of value, but your personality also plays a huge role. Yes, if you’re smart enough, strong enough, fast enough, or unique enough, you can get away with being a huge jerk and still be valuable. Pop culture is full of these figures. Most of the time, though, you can win a job not by being the most qualified candidate but by being the most desirable to work with. A company can teach job skills but generally doesn’t want to work on making you friendly or fun. So you have two major assets as a business: your job-related skills and experience, and your personality.

Managing Your Brand

You need to think about this. What gives your brand value? Are you the printer expert? Are you the ultimate OTRS guru? Are you the jack of all trades who can give the quick answer to any question and knows who to call for more advanced problems? Think about and outline what you offer and how well you’re selling that. If you don’t concentrate on what makes your brand strong, you’ll just be a random IT person. And if you’re a random IT person, you’re a “computer person.” And “computer people” aren’t always highly regarded. The reason is obvious: if you’re the printer expert, but the user’s problem is with their iPhone, to them it doesn’t matter that you can fix printers. You can’t help them with their immediate concern, and so you’re useless. And that’s what they’ll remember–and it will get reinforced every time they have a problem you can’t solve.

This isn’t fair to you, but how are you managing it?

Here are some rules I try to live by:

  • If you have no idea how to fix the problem, be honest.
    • “That’s not my area of expertise, but I’m going to run it by my team and do some research.”
  • Help the user understand the context of their problem and what you can actually do about it. This helps you build expectations correctly.
    • “Well, Sharon, it looks like this isn’t a printer issue. You’re actually not connected to the network at all. I’m going to try a couple basic troubleshooting steps with you, but if they don’t work I’m going to have to call in the big guns. I can help you with all your printer problems, but when it comes to networks, I’m about as much of an expert as Google allows me to be.” (Hey, look at that! Next time Sharon has a network issue, she either knows not to ask you for help, or she knows what level of expertise she’s getting.)
  • Don’t hand off–collaborate.
    • This doesn’t always happen because it can spread resources too thin, but no customer likes to be passed from agent to agent trying to get their problem resolved–explaining the problem over and over again. When possible, stay with the user until either the issue is resolved or your presence is a hindrance. This is a huge value add for your personal brand.

Conclusion

You might be wondering what any of this has to do with ITSM. “Great life advice, Tyler, but I really just need to know how to implement ITSM.” The next post will make clear how this discussion is relevant. Remember that my goal here is not so much to restate the technical details that are already available in the official documentation but to provide context for the implementation and the mentality that will help you design a successful setup.

Love and kisses,

Tyler

OTRS ITSM Part II: Installation

Introduction

There are only a couple of paragraphs on OTRS::ITSM installation, but it turns out that there are a couple of gotchas in that small a space. I’ll cover those here.

Scope of Application

  • Amazon EC2
  • RHEL 6.6
  • OTRS 4 (patch level 9)
  • OTRS::ITSM 4 (patch level 9)

Install OTRS

It may not be obvious, especially if you are new to OTRS, but OTRS::ITSM is not a stand-alone piece of software. It is a package (actually a set of packages) that you add to an existing instance of OTRS. So before you can install ITSM, you’ll need to have OTRS up and running. You can find information about how to install OTRS in the official manual. Then you can check out my posts on getting started with OTRS for your initial configuration. If you’ve already done all that, you’re ready to install ITSM.

It’s Not as Easy as It Looks (But It’s Still Easy)

The official documentation offers a couple options for downloading the necessary packages: either enter some specific text into the package manager (Admin->System Administrator->Package Manager), or download the packages manually from the FTP site. I didn’t have anywhere to enter the provided text on the Package Manager page and had to go with the option of downloading from the FTP site. Unfortunately, even though the manual is for ITSM 4, the link provided will take you to the packages for ITSM 3.3. To get the correct packages for ITSM 4, you need to visit ftp://ftp.otrs.org/pub/otrs/itsm/packages4/, which should look something like this:

ITSM-package_download

From here, right-click the package you want (I just grabbed the latest version of each package), select “Save Link As…” (they’re XML files), and save it to somewhere you know you can find it in a minute. As the manual says, you need to install GeneralCatalog and ITSMCore first.

Note: When selecting packages, make sure you get the same version number for each package. In other words, if you get GeneralCatalog-4.0.9.opm, make sure you also get ITSMCore-4.0.9.opm.

Once you have the packages you need, just select them with the Browse button under “Actions” on the Package Manager page (top left), click the Install Package button, and follow any prompts or instructions. These prompts will usually let you know if there are prerequisites or if you’re trying to install an incompatible version.

ITSM-package-browse-2

Repeat these steps for the remaining packages on the FTP page. See? Easy.

Love and kisses,

Tyler

OTRS ITSM Part I: What is ITSM?

[A couple of boring paragraphs about why I’m writing these posts. Skip to next set of brackets to ignore. :]

What seems like a long time ago now, I wrote a few posts on how to configure the online ticketing system OTRS. While it’s a great system with thorough documentation on how to install the system and configure it, I felt that there were some gaps in the official documentation. So I made some up and wrote a brief manual on the basics of getting started with OTRS, split up over three posts. To my surprise, those posts have brought a consistent stream of traffic to this blog ever since. I’ve received quite a few kind and encouraging comments, which I really appreciate.

In addition to those comments, I get questions. The question I get most consistently is this: Will you do a similar series on ITSM? I promised I would, but in the meantime I had changed jobs and was no longer working with OTRS on a daily basis. On top of that, I started going to school part time. So while I’ve had it on my to-do list to write these posts for well over a year, it took until now for me to work in the time to write them. But it’s finally happening. For those who have been waiting for these posts, thanks for coming back. Sorry for making you wait so long…

[Okay, on to the meat of the subject.]

Introduction

This post will cover the basics of what ITSM is and why you would want to use it. It’s not meant to be a replacement for the official ITSM manual (which is really well done, btw), but since you were nice enough to stop by and see me here, I’m going to give you my take.

Scope of Application

I won’t be going into a lot of technical detail here, but just so you know where the starting point will be on the technical side, I’m now running OTRS 4, patch level 9 on an Amazon EC2 instance. The OS is RHEL 6.6, which means all the following should apply to CentOS, as well. I’ll be working with OTRS::ITSM 4, patch level 9.

IT Service Management

That should clear up what ITSM stands for. (If you don’t know what IT stands for, you’ve come to the wrong blog–the wrog, maybe.) But what is it?

The official documentation gives a brief reminder of ITIL and the standards that exist for IT best practices. If you’re in a shop that is or wants to be ITIL certified, you’ll be well versed in this. If you’re not part of one of those shops, you still probably want to employ industry best practices whenever possible. A big part of the appeal of OTRS is that it is (or at least claims to be) fully ITIL compliant–meaning it will help you enforce best practices. Being open-source and fully customizable means that you’re not bound by those practices and can implement your own strategies, but you’ll start with an ITIL compliant foundation.

“But Tyler,” you say, “I don’t know what ITIL is, and I’m pretty sure I don’t care. I just want to know that IT Service Management is.” Okay. Fine. From here on, I’m going to assume that if you’re looking into OTRS::ITSM, you’re already familiar with the OTRS ticketing system. If not, check out my series on that.

Incident Management

If you work in IT, there aren’t a lot of standard days at the office. That’s what attracts a lot of people to the work–the only constant is problem-solving. There’s always a problem. In the last few weeks, you’ve probably had to reinstall someone’s printer driver, reset a password or two, flushed some caches, imaged a couple machines, etc., etc.

If somebody asked you to write a job description for your position, it would either be incredibly long, or it would just say this:

  • Ideal candidate will know everything about computers and all peripheral technologies and be ready to fix every possible problem at a moment’s notice, likely under duress.

This is why you have a ticketing system like OTRS. Requests are constantly streaming in, and you need some way to organize and prioritize them. This is incident management, and it’s one part of ITSM. But wait… there’s more!

Asset Management

You may have a storage room somewhere that looks like this:

If you were lucky enough to be in that shop before anybody started thinking about asset management, you may have even been lucky enough tog et to catalog everything that was in that storage room as you asked yourself, “Why in the world are we hanging onto this?” Well, part of ITSM is establishing (and hopefully following) the policies and practices for things like tracking equipment, deciding when and how to buy new equipment and who’s responsible for approving it, etc. It’s not usually fun to walk into someone’s office to help them with their printer problem and find out that they bought their own printer and want you to make it functional on the office network.

Change Management

This is a big one, and it’s too often neglected. When you are making changes in production, no matter how sure you are that everything will be fine, there’s a good chance that some edge case you weren’t ready for will interfere with your plan. Change management is planning what the change will be, when the change will happen, who is responsible for the change, and, probably most important, the roll-back strategy in the event that the change fails or breaks something. OTRS::ITSM provides tools for handling this whole process.

Conclusion

I’ve covered some of the high-level categories for IT Service Management as an overview of what OTRS::ITSM aims to help with. But to get even more general, ITSM covers any of the policies and practices that help IT deliver the services needed to help the business reach its goals. That’s a pretty broad task, which means that OTRS::ITSM has a lot of features. My goal isn’t to cover them all. I’m going to do with these posts what I did with the original OTRS series and make recommendations for the initial setup while also trying to elaborate on any “gotchas” in the system. Hopefully it will be useful to those who find the official documentation overwhelming.
Love and kisses,Tyler

On Being Self-Stupicient

I really like portmanteau words. I use them frequently in my writing, so this is probably already clear. Lately I’ve had a new one on my mind that I thought I’d share as part of the spirit of this blog… that spirit being that I’m usually a dummy the first time around. So today’s entry is on being Self-Stupicient. (Fair warning: this is not a technical post. This is more about personal patterns of behavior than a how-to or a tech share. But I’ve seen myself and others in my profession fall into this trap enough to feel like it’s relevant to share here. Also, if you haven’t figured it out by now, I’m kind of a goof. That side of my personality comes out strong here. Thanks for judging me in whispers behind my back so that I’m none the wiser like respectable people do, and not to my face or in the comments like reality-TV stars and teenage boys do.)

I feel a lot of pressure to be self-sufficient and to get my job done standing on my own two feet. I hate asking for help. I don’t know where that comes from, but I don’t like to feel like I’m bothering other people–especially if it turns out to be something really dumb. I don’t want to be that guy who asks for help looking for his glasses and finds out they’re on his head. I’m always embarrassed when I ask someone to see if they can pick out what I’m doing wrong in my code and they point out that I spelled a variable name wrong or left out a semi-colon. (C’mon MySQL… I hit “Return” six times! Why don’t you just get that I want to run my SELECT statement? Do what I want you to do, not what I’m telling you to do!)
I think part of this comes from thinking I’m a smart guy. I look in the mirror, and the fella in there says, “Gee whiz, buddy! It’s a good thing you’re smart!” And I think, “Wow! What a nice guy! And so handsome!” Since that guy is also extremely trust-worthy, I go about my day wondering when people are going to start referring to me as “Professor,” or say things like, “Watch out! Genius on the loose!” when I come down the hall–unironically… instead of ironically like they do now…The point is that when I come across a problem, I believe that given enough time and the proper resources, I can figure out the solution. On top of that, I’m a firm believer in learning by doing. So when I come across a problem or a road block, I often feel the need to prove (to myself… I realize no one else cares) how smart I am and how much I’m growing and learning. For this reason, I’ve had what I would consider a lot of successes academically, professionally, and personally. I’ve become really self-sufficient.
I’ve also wasted a lot of time being stupid so I can maintain my (extremely flawed and almost exclusively internal) reputation of being so self-sufficient. So I’ve been trying to identify the threshold where I go from being a useful guy that my team can count on to take a problem off their plate and keep it out of their way to a guy who comes to the daily stand-up and says, “I spent about six hours trying to figure out why I couldn’t connect using protocol Y yesterday, and I’m no any closer than I was when I started,” only to have the gal sitting next to me say, “Hey, um, Tyler… we’re using protocol H for this project.” Wah wah…When I’ve come to the conclusion that there’s no better way to do something, or that something can’t be done, I trust myself. This might be pride or vanity (sometimes I wonder about that guy in the mirror I told you about earlier), but I think it’s a pattern we all get into. When we’re given an assignment and we’re plowing through it at a steady clip, we feel good, smart, and successful. Then we come up against something we didn’t expect or that didn’t work even when we followed the instructions exactly (I’m looking at you, sample code!), it kind of baffles us. It does me, at least. And because I’ve hopped a long from A to P so well, smoothing out any kinks on the way, when Q doesn’t bend to my will, I get mad at myself, and I start to feel unsuccessful.
But I know I’m smart. I know I’m a problem-solver. So it’s not my immediate instinct to ask for help. It’s my instinct to kick myself in the butt for not already knowing the answer. Besides, I’m the one who’s been staring at this project for weeks. I’m the one who knows it inside out. If I can’t figure it out (okay… there’s definitely at least a little vanity there… I see it now), how can Adam or Brian help? (Adam doesn’t know anything. He thinks Michael Keaton was miscast as Batman, for cryin’ out loud!)
But you’ve been there, right? You know where this goes. The longer I go without asking for help, the more convinced I become that there’s no help to be found and that this is an unsolvable problem. But I’m smart! There are no unsolvable problems for me! So I keep going… Then, at some point, I realize I’m wasting time. I start performing the same tests or trying the same things three times over just to confirm that I did what I thought I did and got the results I thought I got. I start taking more breaks from thinking about it, because that’s what they tell you: as soon as you stop thinking about it, the answer will come. So I switch tasks, go for a walk, chat with my cube-mates about the merits of Michael Keaton’s outstanding performance in Birdman. (No one else in the office has seen it, so I get to always be right about everything when we talk about that. Builds my self-confidence back up.) Occasionally, this works. More often, I’m too embarrassed or too mad at myself to admit to anyone that I’m stuck and need help.  Sometimes I even stop people from helping me. I won’t show them what I’m doing. I don’t want to be proven stupid. That’s when I think I’m being self-stupicient. I’ve made this all sound pretty silly, but it’s easy to get to the silly point. It’s easy, if you don’t take yourself too seriously, to laugh at your own dumb mistakes and not make them again. But it’s hard to know when your efforts take that turn from working toward a solution, to firmly securing the problem. I don’t know how many times ten minutes or an hour of “bothering someone else” has saved me days of wasted effort. On the other hand, I don’t know how many times I’ve felt as if I’ve stolen someone else’s time because I didn’t read instructions carefully enough, or because I didn’t look at the fifth web search result before I asked for their help. So I worry that if I ask for help too soon, I’ll look like an idiot. But I’ll also look like an idiot if I wait too long to ask for help. Where’s the sweet spot? Wish I knew. But I’m starting to watch for it, and it’s getting better. Time-boxing definitely helps as a strategy to combat self-stupiciency. This is where you give your self a certain amount of time to solve a problem. If you can’t solve it by then, you ask for help or move on to something else. If you lack self-discipline, though, it’s tempting to give yourself 15 more minutes… then 15 more minutes…
I’ve also learned to go to the forums quicker. And by that I mean posting to forums. I’ve always been quick to search them. Posting questions helps me in a couple ways. First, articulating my process and questions in writing often leads me to the answer. I will realize in writing out my question or framing my problem that I missed something, forgot to try a recommended solution, or that there’s another way I can approach the problem. Second, people are really, astonishingly helpful. This makes sense. One of the main reasons to become a software developer or join an IT department is because you like to diagnose and solve problems–specifically technical problems. It’s fun for us! It’s really satisfying. And a lot of the time, something that’s hard for you is someone else’s bread and butter. If I just stop being so self-stupicient, I get to benefit from their marvelous experience and goodness.I also try to check out of my own head a little more often. When I’m really hammering out a problem, I often get extremely focused. I lose track of time. I don’t hear when people are speaking to me. I don’t notice that everyone else has gone home. Sometimes I also don’t notice that I’m getting tired, or that I haven’t eaten, or that I’m getting anxious and fidgety. I’ve made very few situations better by skipping lunch or losing sleep to work on them. I’ve also never gotten closer to solving a problem by getting agitated. So every now and then I have to give myself an honest assessment of whether or not I’m still thinking clearly about the problem, and if I wouldn’t be better off getting a disinterested third-part to lend a hand. Because if I hit that point where I’m mad at the problem and I’m mad at myself for not solving it, and then someone points out to me that it’s a tiny, stupid mistake… well, then I’m going to be mad at everything. That’s easy to cut off. I just turn around and say, “Adam, I’ve hit a wall here. Will you take a look at this and see what I’m missing?” Then, if Adam doesn’t see anything I’m not seeing, I know I haven’t crossed the self-stupicient threshold. Plus, I get to say, “Well, thanks for nothing, Adam!” which is always fun for everyone.I know this post was a little off the beaten path for this blog, but thanks for indulging me. I’ve been wanting to articulate that for a while for my own sake. In my next post, I plan to turn my focus back to OTRS.  Love and kisses,Tyler

A Few Cool Technologies (2014-11-15)

I wanted to point out a few cool technologies that I’ve come across lately. I decided to put a date on the title, because I imagine I’ll do this fairly regularly as I come across cool stuff.

The first couple tools (Docker and OpenShift) are for both admins and developers. The last one (Code School) is more directly relevant to developers–specifically folks who are looking to learn something new.

Docker

I know I’m a little late to the game on this one, but I’ve been living primarily in the Windows ecosystem over the last year or so. I didn’t hear about Docker until Microsoft announced that they’re incorporating the platform into Azure. And it’s a smart move! Docker isn’t the only technology of its kind, but it’s the one that everyone is talking about, and it’s clearly going to get credit for being the game-changer.

I’m going to assume that you know what a virtual machine is and have some idea how useful they are. But the downside of virtual machines (VM) is that, in terms of system resource usage, they can be expensive to distribute, maintain, and use. A VM makes a lot of sense if you need to split a server into multiple virtual servers, but if you only need it to run one legacy application, it’s pretty cumbersome.

Emerging as an alternative to VMs for one-off applications (among other uses) is something called a “container,” which basically allows an application to bring its own little environment with it. One way to think about it is that a virtual machine is kind of like building a guest house on your property for a family of five to use. Other than relying on the same utilities running to the property, you won’t interact a whole lot. Docker is more like having a guest in the spare bedroom. For the most part, they’ll keep to themselves, and if they like Jif better than Skippy, they’ll bring their own peanut butter. But they’re still in the same house and sharing a lot of the same resources.

If you do development, testing, or are in charge of moving applications from one environment to another in a Linux or Windows environment, Docker should be on your radar. There’s a pretty sweet slide show here. And here’s a hands-on.

OpenShift

Akin to Docker is Red Hat’s OpenShift, which is actually going to be incorporating Docker into its next version. OpenShift is a Platform as a Service (PaaS) product like Heroku or the Google App Engine. I’m not going to go into much detail here, because I’m going to point you to a great deep dive passed along to me by Scott McCarty, a Red Hat Solutions Architect. (Yes, I drank the Kool-Aid. But so will you.)  I’m also going to let Red Hat do the talking on what PaaS is in their words.

But in short, PaaS takes a lot of the administrative work of distributing a cloud application off of your plate. Want to set up a new WordPress site to your cloud but wish you could get straight to coding or deploying existing code and bypass the infrastructure and platform setup? Well, OpenShift has something they advertise as One-Click Deployment for WordPress. And it’s pretty much as simple as it sounds. You’ll have configuration to do, of course, but you’d have to do that anyway. It’s got a growing list of software already supported, but it’s also extensible. You don’t have to wait on Red Hat to get up to speed and add Hadoop, for example. If you’ve got the chops, you can take the initiative and add it to the mix yourself.

Code School

Finally, I wanted to give a shout out to Code School. I was recently told to start getting up to speed on Angular.js, and my manager recommended a Code School tutorial. I’m used to text-based tutorials with code samples that you just kind of copy and paste, then maybe tweak to see what happens–at least when it comes to the free stuff. Code School, however, took me by surprise. These folks are producing some pretty amazing screencast content, and whether you are just getting started as a code, want to add new arrows to your quiver, or just get a better idea of what the heck CoffeeScript or Ember.js might be, I’d recommend checking out their course offering. They have everything from basic HTML and CSS to R to mobile-first web design.

Most of it is geared toward front-end web development, so if you’re trying to do bare metal programming on your Raspberry Pi, this probably isn’t for you.

The coolest thing is that after each morsel, they have challenges. Yes, it’s gamified and you can earn points for completing challenges. If you’re into that, all the better. But gamified or not, it actually forces you to apply each piece as you learn it and makes you get it right before you can proceed. Sometimes you can just copy what you saw in the video exactly, and that’s good for making sure you’re paying attention to the important parts at least. But as you progress, they throw little changes into the challenge code that wasn’t in the video, forcing you to actually think about and gain an understanding of the relationships among the different components to be able to get them working together properly.

It’s also fun. Angular.js actually has a theme song. Where else are you gonna get that with your code?

Love and kisses,

Tyler

 

Interesting Intervew w/ Michael Tiemann

So I didn’t know who Michael Tiemann was until listening to this interview on Hacker Public Radio, but apparently he’s been one of the most major forces in Open Source over the last couple of decades. He’s incredibly well spoken, knows what he’s about, and the interviewer does a good job of guiding the discussion. Tiemann is also no edge-case hacker who just loves Open Source. He’s the VP of Open Source Affairs at Red Hat, Inc.

I thought my OTRS readers might be especially interested in the interview and in the philosophies presented. Check it out.

 

http://hackerpublicradio.org/eps.php?id=1622

 

PS: I don’t necessarily recommend Hacker Public Radio by default. There are some stellar episodes, for sure, but it’s not like standard commercial podcasts you might be used to where there is consistent subject matter or a consistent host you can form a bond with. All I can say is that I get something out of Hacker Public Radio and you don’t have to be a “hacker” to appreciate every episode (sometimes they’re just talking about how to use free software like LibreOffice Calc effectively). So if you’re shopping around for a new podcast, consider giving it a shot.

Quick Note On My Absence

Hi folks. I’m sure no one is watching this blog religiously, but it’s gained more popularity than I ever would have expected–and quickly–so I thought I should post a quick note about why I haven’t been posting lately. First, I did a lot of traveling over the summer and relaxed my commitment to the blog. It was just too much to keep up with. I had plans to start a series on OTRS ITSM this fall, but I at the end of the summer I had a very nice surprise. I was accepted to Georgia Tech’s Online Master of Science in Computer Science program!

I started out taking a single course, hoping that would allow me to ease back into the student mindset and hopefully leave me time to continue with some of my other side projects. However, I’ve found that working full time and taking just one course is usually enough to fill up my time. I’ve gotten much better at managing my time, so I hope to return to the blog soon. I will probably include some posts about what I’m learning in the program, as well. OTRS ITSM is still on my radar. It will just be behind my anticipated schedule.

Thanks everyone for reading and for your kind comments. I look forward to providing more help to you as soon as I can!

Anatomy of a Stackoverflow Discussion, Part II

Introduction

There’s a reason I named this blog the way I did. The first post is a good example of how a dummy does it. Turns out I did the opposite of what the asker needed. He wanted to find non-matching pairs, and I gave him matching pairs. So I don’t reexplain from scratch here, but I’m going to give an updated macro and explain the differences.

Scope of Application

This post references Microsoft Excel 2013 running on a Windows 8.1 machine. I’ll also be referencing Stackoverflow as experienced through Firefox 28.0 running in Sandboxie 4.08.

User Requirements

This one’s on me. I read the question once, started looking at the sample data, then forgot what the ultimate goal was because I was caught up in the minutiae of making things work. Granted, this was a casual response to an online question, but this is about par for the course in programming. Sometimes the requirements change, many times they were ill-defined in the first place, you have to constantly be on the lookout for scope creep, and other times, it turns out that I’m just kind of a dummy. So here’s a macro that does what the user actually asked for:

Sub TakeThree()

Dim rowCount1 As Long
Dim rowCount2 As Long

rowCount1 = ThisWorkbook.Sheets(1).Range("B20").SpecialCells(xlCellTypeLastCell).Row
rowCount2 = ThisWorkbook.Sheets(2).Range("C2").SpecialCells(xlCellTypeLastCell).Row

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ThisWorkbook.Sheets(1).Range("B20:B" & rowCount1)
Set rng2 = ThisWorkbook.Sheets(2).Range("C2:C" & rowCount2)

Dim currentRow As Long
currentRow = 2

For Each cell In rng1.Cells
For Each cell2 In rng2.Cells
If cell2.Value = cell.Value And cell2.Offset(0, -1).Value = cell.Offset(0, 5).Value And cell2.Offset(0, -2).Value = cell.Offset(0, 2).Value Then
GoTo NextIteration
End If
Next cell2
ThisWorkbook.Sheets(1).Rows(cell.Row).Copy Destination:=ThisWorkbook.Sheets(3).Range("A" & currentRow)
currentRow = currentRow + 1
NextIteration:
Next cell

End Sub

What’s the Difference?

We really haven’t changed much. We just moved the lines that copy and paste the row from sheet 1 and the currentRow increment outside of the loop through sheet 2. Why does that make it work? As I’ve said before (I think), the biggest part of coding isn’t so much about knowing how to read and write the language, it’s knowing how to work the logic.

Let’s frame the requirements one more time to help us understand how we got here. The asker wanted to compare data in 3 cells on sheet 1 to data in 3 cells on sheet 2. If the cells on sheet 1 do not match any of the corresponding cells in sheet 2, they should be copied to sheet 3. To be sure that each set of 3 cells from sheet 1 doesn’t match any of the corresponding cells in sheet 2, our only course of action is to compare each set of cells on sheet 1 to every set of cells on sheet 2–unless we get a match. As soon as the cells on sheet 1 match some cells on sheet 2, we don’t have to keep going with the comparison and can move to the next set of cells on sheet 1. But unless we get a match, we can’t copy anything from sheet 1 to sheet 3 until we’re sure we’ve exhausted every option.

So now look at what the loops are doing. The first one, “For Each cell in rng1.Cells,” is going to loop through every cell in sheet 1, as we discussed. It immediately starts another loop that will check the values at this spot in sheet 1 against a set of cells in sheet 2. If the cells from sheet 2 match the cells from sheet 1, the code jumps to the next iteration–completely bypassing the step that copies a row from sheet 1 to sheet 3.  However, if the code gets through every single set of cells in sheet 2 without finding a match, then it leaves that loop. There waiting on the other side is the code that says, “Oh. Hey. Couldn’t find a match on the other sheet, and now you want me to give you a ride to sheet 3? I guess that’s cool.”

Hopefully that’s clear enough. Cuz that’s all I’ve got.

Love and kisses,

Tyler

Anatomy of a Stackoverflow Discussion

Introduction

I’ve mentioned Stackoverflow in a couple of other posts. It’s not the only place the cool kids in coding hang out, but it’s a pretty important one. And its sister sites aren’t just for coders. I had an interesting exchange there over the last couple of days that I thought would fit my “Learn to Code” series. After this, though, I’m going to spend some time digging into OTRS ITSM and writing about that. I’ve had several requests for it, and I’ve noticed that my blog is getting the most traffic for the OTRS stuff. I feel like the people have spoken. If you come around here for the Learn to Code posts, let me know where you’d like those to head next.

Scope of Application

This post references Microsoft Excel 2013 running on a Windows 8.1 machine. I’ll also be referencing Stackoverflow as experienced through Firefox 28.0 running in Sandboxie 4.08.

The Question

Stackoverflow, to severely oversimplify, is a Q&A site. It’s a really sophisticated Q&A site with some conventions that its users can be fairly zealous about, but overall it’s an awesome community that makes getting answers to programming questions about as easy as it could possibly be without having Dennis Ritchie as a personal advisor.

Every post starts with a question. In this case, the question had to do with an Excel macro. I won’t rehash the whole question for you here. You can follow the link and take a look. The format is fairly familiar: header, body, some up-/downvote arrows and a star to “favorite” the question. At the time that I’m writing this, the question has a score of “-3.” You can hover over the up-/downvote arrows to see how the site designers intended those features to be used. It’s hard to know exactly why questions get downvoted sometimes, but it’s also important to note that questions can be edited over time–don’t always trust a negative score. New users who are inexperienced programmers can have an especially hard time getting their first couple questions right on Stackoverflow, but many of them make quick edits and improve the question.

My guess is that this one was down-voted because the community at large didn’t find that the original question was framed well, or that it didn’t show enough leg work. A lot of folks get on the site and ask things as general as, “How do you add two numbers together?” (Ok. That’s a slight exaggeration… but only a slight one.) If you can Google the answer fairly easily, the community generally doesn’t like that.

The Answer

I don’t think my answer is perfect, but I did see a solution to the problem based on some similar issues I’d faced in the past. So I wrote and tested a quick macro that did what I understood to be the goal of the asker. It took a little more back and forth, and because the person asking the question is fairly new to macros, last time I checked, he still hadn’t quite gotten it to work with the real data. Because of my recent posts on macros, I thought I’d take this opportunity to explain my code for his sake and for anyone else who might be working at the same level.

The entirety of the macro is already on Stackoverflow, so I’m going to go ahead and go into the detail.

Terms

Some folks might be used to talking about Excel files and tabs. The official jargon for an Excel file is a workbook, and the tabs are worksheets. Knowing this makes understanding the code a little easier.

The Code

Here are the first few lines:

Dim rowCount1 As Long
Dim rowCount2 As Long

rowCount1 = ThisWorkbook.Sheets(1).Range("A2").SpecialCells(xlCellTypeLastCell).Row
rowCount2
= ThisWorkbook.Sheets(2).Range("A2").SpecialCells(xlCellTypeLastCell).Row

“Dim” is a keyword in VBA that you use whenever you want to declare a variable. (If you want to know why, Google has the answer.) So we’re declaring and assigning two variables as “Long” data types. When you declare a variable in VBA, you have to tell it whether the data is going to be a string of characters, a number, or something else. So you’ll see “Dim [myVariable] As [someType]” everywhere in VBA.

A “Long” is just a type of number. We want to be able to loop through a column of cells without having to tell Excel explicitly how many rows we’ve used in that column. We want Excel to figure out where the last used row is. And that’s what we’re doing with the next two lines. Their practically identical, but they’re looking at different sheets.  The line is pretty self-explanatory, but let’s take a quick close look:

  • ThisWorkbook
    • Refers to the workbook that contains the macro
  • Sheets(1)
    • This is how you tell Excel which sheet to look at. You can also pass it a name: Sheets(“Payments”)
  • Range(“A2”)
    • A Range in Excel is a group of cells. In this case, we’re referencing only a single cell, but you can reference more, as we’ll see later
  • SpecialCells(xlCellTypeLastCell)
    • This might look a little cryptic, but it’s pretty simple. “SpecialCells” is a method on the Range that takes one of a few specific arguments predefined by VBA. In this case, we’re telling it to find the last cell in the column with any information in it–xlCellTypeLastCell.
  • Row
    • SpecialCells is going to return a cell address. “Row” grabs the row number off of that address. Now our “rowCount” variables refer to the last row in Column A on their respective sheets.

The next section is really similar:

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ThisWorkbook.Sheets(1).Range("A2:A" & rowCount1)
Set rng2 = ThisWorkbook.Sheets(2).Range("A2:A" & rowCount2)

Here, we declare two new variables of the Range data type. That means that we’re going to refer to a couple of groups of cells. When you assign a Range variable, you have to use the “Set” keyword. Notice that we didn’t have to do that to assign the Long variables. This has to do with the different ways Excel handles different data types. I’m not going to get into the details of that here. For now, just know that if you try to set a Range variable without the Set keyword, you’re gonna have a bad time.

You already know what “ThisWorkbook.Sheets(1).Range” means, but the range reference is a little different than the one we saw before. Instead of simply A2, we have “A2:A” & rowCount1. If you’re familiar with Excel, you’ve probably used or seen notation like “A2:A10” in a formula before. This refers to the range of cells in Column A, rows 2 through 10. If the notation were “A2:C10”, it would include all the cells in Columns A, B, C, Rows 1 through 10. The notation here means the exact same thing. “A2:A” tells Excel to refer to Column A, Row 2 through Column A… What?

You may have seen the rest of this notation in a formula somewhere, as well. The ampersand (&) is the concatenation symbol–concatenate being a fancy word for “smoosh together.” If you concatenate “antidisestablishment” with “arianism,” you get “antidisestablishmentarianism.” Simple as that. So that last piece of our assignment is telling Excel to assign rng1 to all the cells in Column A from row 2 through the last used row–the value we stored in that variable earlier.

The next variable is a little more complex, and I’m not going to go into a lot of detail on it. If you understand based on the explanation I give, great. If not, don’t worry about it. I’m not exactly starting at the beginning.

Dim sheet1() As Variant
ReDim sheet1(rowCount1 - 1,2)

Here I declare sheet1 as an array. You can tell it’s an array because of the parentheses–which up to this point we’ve only seen as part of a function or method. (See my post on the coin slot).  They also show up with array variables, because arrays hold collections of values instead of a single value, and we need a way to tell it which one we want. It’s not entirely wrong to think of an array as a kind of mini-function: You call its name and pass in an argument, and it gives back the value that matches that argument.

The Variant data type means that the array might (and in our case, definitely will) hold different types of data. They won’t all be numbers or all be letters. We’re going to mix it up.

The next line has a “ReDim” statement. If you really want to understand that now, you’ll have to Google it. I’ll leave my explanation at this: VBA needs you to tell it what size an array is going to be so it can reserve the appropriate amount of space. We didn’t reserve any space when we declared it, so we’re reserving that space with the ReDim statement.

I’m also using a multidimensional array, meaning each entry in the array will consist of multiple pieces of data. That’s why the coin slot has two values: rowCount1 – 1, and 2. The first dimension, rowCount1-1, is the size of the array, or the number of elements it will have. In this case, I need to store one element in the array for each row of data in my used range on Sheet1. Why do I subtract 1 from the row count? Let me explain roundaboutly. The second dimension, 2, indicates that each of my elements will have 3 pieces of data.

“Wait… it sounds like you just said 2 = 3.” Well, that’s because I did. You know how 0 is supposed to mean nothing? Well, not with arrays in programming. Arrays treat 0 like something. It’s the first element in an array. So 0 = 1. It’s called base 0, and it’s kind of annoying sometimes. But it will make more sense in a few lines.

Just to give you a more concrete idea of how a multidimensional array works, let’s say I have the following data on the characters of 30 Rock:

  • Liz Lemon
    • University of Maryland
    • Theater Tech
  • Jack Donaghy
    • Harvard
    • MBA
  • Kenneth Parcell
    • Kentucky Mountain Bible College
    • Television Studies and Bible Sexuality

These are related sets of data that I want to be able to reference together. For this data set, my multidimensional array would need to contain three elements, each of which has three pieces of data. I could declare this like so:

Dim 30Rock() As Variant
ReDim 30Rock(2,2)

 Cool? Cool.

We then declare another number variable and set it to 0.

Dim n As Long
n = 0

And now, something a little more interesting happens:

For Each cell In rng1.Cells
sheet1
(n,0)= cell.Value
sheet1
(n,1)= cell.Offset(0,2).Value
sheet1
(n,2)= cell.Offset(0,5).Value
Debug
.Print cell.Value
n
= n + 1
Next cell

We haven’t covered “For Each” loops yet, but you’re smarter than me. You understand that these tell the computer to loop through a certain section of code for each element in some kind of collection. In our case, we’re telling it to repeat these lines of code for each cell in the collection of cells in the range we assigned to rng1 several lines ago.

First, we get the value of the cell in Column A, which is the column our range is set to. “cell.Value” means whatever is in the cell. It may not be what is displayed on the screen. If the cell contains a formula, the value is the formula. The result of that formula would be in the text property: cell.Text.

We assign that value as the first piece of data in the first element of the array. Remember that we set n to 0, so what that first line actually means is “sheet1(0,0).” You’ll notice that we increment n by 1 at the end of each loop. That way we’re always moving up one element in the array. The second time we pass through, the first line would mean “sheet1(1,0)”, the next time “sheet1(2,0)”, and so forth.

The next line gets the value from the cell 2 columns to the right. The “Offset” property allows you to create relative references. You pass it a row number and column number, respectively, and it finds the cell at that location. For example, in the first pass of the above code, the address of “cell” is “A2.” So cell.Offset(0,2).Value says to Excel, “go 0 rows up or down, and go 2 columns to the right.” (Positive numbers go down and to the right, negative numbers go up and to the left.) This will refer to “C2.”

The “Debug.Print” statement is just a way to show in the Immediate Window of the VBA Editor if I’m grabbing the value of the cell that I expect to get.

The reason I’m gathering these values is rooted in the original question. Based on the sample data in the original question, you can see that the asker has values in columns A, C, and F on sheet 1 that he would like to compare to data in Columns A, B, and C on sheet 2. That’s why I’m using the offsets. I only have to refer to one column by its letter, an then I can use relative references to get the values out of the other columns.

It’s worth asking why I’m collecting these values into a multidimensional array instead of just comparing the two ranges in the two sheets directly. The same task could be accomplished either way. To be perfectly honest, I don’t know why I did it except that that’s how it occurred to me to do it. Maybe I’m too tired now to remember why, or maybe I was in too much of a hurry then to consider all of my options, but I did it this way, it worked, and so I passed my answer along. Other than possibly making the final loop a little easier to read, this approach doesn’t actually offer any major advantage that I can see. Thinking about it a little more, I may have thought there was a performance advantage to holding those cell values in memory rather than reading them out of the ell each time. I don’t think that’s accurate. So learn from my mistakes: don’t add unnecessary code!

Okay, next step. We declare another variable and set it to 1:

Dim currentRow As Long
currentRow
=1

The asker wants to take any data from sheet1 that matches data from sheet2 and copy the row to sheet3. We’ll use this variable to track which row on sheet3 we’re pasting into.

Finally, the heavy lifting:

For n = 0 To UBound(sheet1)
For Each cell In rng2.Cells
If cell.Value = sheet1(n,0) And cell.Offset(0,1).Value = sheet1(n,1) And cell.Offset(0,2).Value = sheet1(n,2) Then
ThisWorkbook
.Sheets(1).Rows(n + 2).Copy Destination:=ThisWorkbook.Sheets(3).Range("A"& currentRow)
currentRow
= currentRow +1
GoTo NextIteration
End If
Next
cell
NextIteration
:
Next n

First, we have another For loop. This one is just a tad different. Rather than saying For Each, we tell it to count. You can start from any value and count to any value. Since we’re looping through an array, we start at 0. We then tell it to count to the upper boundary (UBound) of an array–meaning its highest value. UBound is a function that takes an array as an argument. We give it sheet1.

Now we do something really inefficient. We nest a For Each loop inside a For loop. I haven’t thought of a better way to address this particular problem, but be cautious about doing this. Let’s say we have an array with 1,000 elements in it, and we run a For loop on every element in that array. For each element in our array, we compare it against each value in another array to see if it matches. Well, what if that second array has 1,000,000 elements? And what if nothing matches? We end up running that comparison a thousand million times. If there’s any way to avoid running so many stinkin’ loops, we should figure it out!

You can see where this is going. We want to check everything in our original array (the values from sheet 1) against the corresponding cells in sheet 2. So if the value in sheet 2, Column A matches the first piece of data in the first element of our array AND the value in sheet 2, Column B matches the second piece of data in the first element of our array AND the value in sheet 2, Column C matches the third piece of data in the first element of our array, THEN do the following stuff.

The next line should be fairly self-explanatory. We refer to the matching row back on sheet 1, and tell Excel to copy that row. We find the row number by adding 2 to n. Remember that our data started in the second row, but n starts at 0. This corrects for that difference. The “Copy” method takes a Range as its “Destination” argument, so we point it to the first unused row on the third sheet using the currentRow variable we set up earlier. Then we increment that to make sure the next time we copy, we don’t overwrite what’s already there.

“GoTo NextIteration”… goes to… the NextIteration label… I don’t know how to explain that any better. But the reason we need it is because otherwise Excel would stay inside that inner loop even after it finds a match. By sending the code outside of that loop, we can move to the next element in our array without looping through all the cells in the second sheet.

That’s it! Easy peasy, right?

Discrepancy

Now, the sample data included in the question didn’t match the data the asker was actually using. If you go to the question now, you’ll find a link to the data file. That wasn’t there the first time. So the macro didn’t work with the live data. It needed some edits. Below is the revised macro, taking into account the realization I had that the multidimensional array wasn’t really necessary. I’m not going to explain all the changes. I’ve explained everything else in enough detail that you ought to be able to look at the sample data file and the code below and figure out why the changes work. Or maybe I’m wrong and they don’t. Then I guess it’s up to you to fix it and let me know.

Sub Test()

Dim rowCount1 As Long
 Dim rowCount2 As Long
rowCount1 = ThisWorkbook.Sheets(1).Range("B20").SpecialCells(xlCellTypeLastCell).Row
 rowCount2 = ThisWorkbook.Sheets(2).Range("C2").SpecialCells(xlCellTypeLastCell).Row
Dim rng1 As Range
 Dim rng2 As Range
Set rng1 = ThisWorkbook.Sheets(1).Range("B20:B" & rowCount1)
 Set rng2 = ThisWorkbook.Sheets(2).Range("C2:C" & rowCount2)
Dim currentRow As Long
 currentRow = 1
For Each cell In rng1.Cells
 For Each cell2 In rng2.Cells
 If cell2.Value = cell.Value And cell2.Offset(0, -1).Value = cell.Offset(0, 5).Value And cell2.Offset(0, -2).Value = cell.Offset(0, 2).Value Then
 ThisWorkbook.Sheets(1).Rows(cell.Row).Copy Destination:=ThisWorkbook.Sheets(3).Range("A" & currentRow)
 currentRow = currentRow + 1
 GoTo NextIteration
 End If
 Next cell2
 NextIteration:
 Next cell
End Sub

Love and kisses,

Tyler