| Downloads |
|---|
| 48232.zip |
Windows IT professionals are, at heart, problem solvers who, in the course of their routine duties, might do extraordinary things to get the job done. Often, solving an IT problem—a server crash, a security exposure, or just trying to get users the data they need—is simply a matter of using a Windows application, system tool, or third-party product. But sometimes, solving a problem means trying a new technique or using a familiar tool or product in a new way. It means making the leap from simply being a skilled technician to being a true innovator.
Last spring, Windows IT Pro invited you to submit your most original and resourceful technicalsolutions to the first annual Windows IT Pro Innovators contest, co-sponsored by Microsoft. Out of 68 entries, Windows IT Pro judges selected three grand-prize winners and six honorable mentions in the small, medium-sized, and large business categories. The solutions represent a spectrum of Windows technologies: scripting and application development, Microsoft and third-party applications, freeware, and Windows OS features such as Group Policy. Our 2005 Innovators—your peers—show that Windows IT pros are a creative bunch. We hope their solutions will inspire you to pursue your own IT innovations!
GRAND PRIZE Small Business
David Soussan,
Chief Engineer, DAS Computer Consultants, Ltd.
Customize 400 PowerPoint Slides on the Fly
David Soussan is a computing jack-of-all-trades. His consulting business provides a full gamut of services—from network troubleshooting and hardware diagnosis to software design and systems integration. So when a client came to David and asked him to automate the tedious process of creating more than 400 individual Microsoft PowerPoint presentations, each containing dozens of unique graphs, he knew he could find an efficient way to do the job, although he didn't immediately see how to fulfill the client's request. "When a client needs something, I jump in and figure it out. I don't like the 'I can't do that' concept. Just because I don't know doesn't mean I can't figure it out," he says.
David's client, McGraw Wentworth, an employee-benefits brokerage consulting firm, conducts an annual Web survey of employers in southeastern Michigan to gather detailed data about the employee benefits they offer, such as medical and dental employee contributions, deductibles, copays, and data on short-and longterm disability plans. Participants answer more than 600 questions about their benefits packages. After the survey was closed, David split each survey from a single Microsoft SQL Server record into multiple Microsoft Access tables, then used both Microsoft Excel and Business Objects' Crystal Reports to crunch the data. The client wanted to use numerous graphs within PowerPoint slides tailored to each survey participant as a sales tool in seminars. Participants could see how their employee benefits stacked up against those provided by employers in the same region, in the same industry, of a similar size, and nationwide.
When David learned that the client would have to manually generate each PowerPoint presentation with more than 100 customized entries, then handassemble each presentation, he says he "just cringed" and thought, "I don't know how I'm going to do this, but I know there's a way."
To discover that way, David first researched in the Microsoft Knowledge Base. "I found an article that shows how to create a PowerPoint slide through a VBA application." (See "How to create a Graph object on a PowerPoint 97 for Windows slide in Access 97 by using Visual Basic for Applications" at http://support.microsoft.com/?kbid=177270.) "That \[explained\] some of the object structure of \[Microsoft\] Office. You can put a Word document or an Excel spreadsheet or a Microsoft graph object or whatever inside Excel or inside another Word document or inside PowerPoint."
Using that knowledge, his Visual Basic for Applications (VBA) programming skill, and the Microsoft Office VBA Help files, David learned how to manipulate a graph object and the data it contained within a PowerPoint slide. When PowerPoint is run for a particular customer, David's VBA application opens the database record that contains the customer's survey data. When the customer selects a graph to display (e.g., median PPO-plan deductible for companies in the region), the application accesses a table that contains data corresponding to the x and y axes for that graph.
The client was thrilled with David's solution. "They loved it. Because these guys are healthcare consultants, they want help in marketing to \[their customers\]. And one of the ways they do that is by showing them, here's how you compare, and here are some things that we might suggest for you. So it's a marketing opportunity for the firm."
For David and his client, the clear benefit of his solution is the manual work it eliminates, which naturally translates into cost savings. "I built in about 20 hours something that saves thousands of manual hours and is reusable (the solution is fully table-driven), so that next year the client can change the template and reuse all the code," he says. "Being able to leverage technology to make the impossible possible is where IT creates 10-to-100-times improvements and where the real bang-for-the-buck is."
GRAND PRIZE Medium-Sized Business
Brandon Jones,
Systems Administrator, Northern Arizona University
Turn Students' Network Access On and Off
If you've spent any time in a college computer-lab class recently, you might have noticed many students Websurfing, checking email, or IMing while the instructor lectured. For instructors, students' Web-induced attention deficit becomes a serious problem during exams. Last year, the computer faculty of Northern Arizona University's (NAU's) College of Business asked the IT department for help in combating the problem by locking down about 150 PCs running Windows XP Professional Edition in three on-campus labs.
Brandon Jones explains the problem: "The faculty needed to have the Internet there because the exams were conducted on an external Web site. But they didn't want students to send or receive email during exams, copy data from the exam and put it on a USB drive or a floppy disk, or bring in data on their own media and use it to look up answers."
IT's initial solution was to install a UNIX-based hardware firewall on the lab network. (The college uses both Windows and UNIX servers.) But the solution was cumbersome, and after 2 months, IT abandoned it. "Our UNIX administrator had to be there at the beginning and end of every exam and physically remove cables and plug it into a new switch," Brandon explains. Moreover, the firewall appliance didn't address the problems of students copying exam data to and accessing data from their own storage media or from network shares.
Brandon wondered whether he could devise a solution by using Group Policy to restrict outbound packets on the network. "I started tinkering around, doing research, asking questions on some newsgroups, and was led to the possibility of using IPsec within Group Policy," he says.
Although Brandon was experienced in using Group Policy, he says that IPsec "was one aspect of Group Policy I knew nothing about. I basically just got in there and started doing a bunch of testing. Within a few hours, I was able to configure-a Group Policy Object \[GPO\] that accomplished exactly what the UNIXbased firewall did."
Configuring the GPO was the easy part. Brandon's next challenge was to create a means to turn the GPO on and off as the instructors needed. "The whole idea of using \[a GPO\] on the fly—it's time for the exam; let's turn it on. Now that the exam is done, let's turn it off—was completely new, I think. I'd never heard of anybody trying to use Group Policy in that fashion," says Brandon. Brandon broke with Group Policy tradition and wrote a VBScript script that turned the GPO on and off. He also scripted a VBScript front end that let instructors run the application—called the Exam Firewall—simply by clicking a desktop icon.
The script interacts with Group Policy Management Console (GPMC) to turn the IPsec policy on and off and invokes the Gpupdate command on all the clients to refresh the policy. The script also replicates the policy changes on the college's multiple domain controllers (DCs). It takes about 20 seconds to turn the policy on or off in a lab of 50 computers, Brandon says.
Since the college started using Brandon's solution in March 2005, it's become wildly popular with faculty members, some of whom use it daily to control Web access during lectures and during exams to more tightly lock down access to resources. "During final exam week in May, faculty used \[the application\] to control security for more than a dozen exams," Brandon says. "They tell me it solves problems they've been struggling with for years." Moreover, word of the solution's success has spread across campus, and another NAU college has started using it.
Brandon hopes that his solution will inspire other IT pros to explore using Group Policy, especially IPsec, in creative ways. "This implementation utilizes Microsoft tools in ways they weren't necessarily intended to be used, but the end result is an extremely useful and effective solution that has potential to benefit Microsoft users everywhere."
GRAND PRIZE Large Business
Jack Bridgman,
Enterprise Strategy Consultant, International Network Services
Determine Email Usage Costs to Justify an Exchange Upgrade
Upgrading a huge, aging Exchange infrastructure is a formidable task, one that an organization might be inclined to postpone as long as possible. A client of Jack Bridgman's—a large government agency—was about "10 years behind in its Exchange architecture," he says. The agency enlisted Jack to advise it about how to overhaul its Exchange organization, then explain how it could cost-justify the upgrade to the elected officials in charge of the budget.
The client had 450 standalone Exchange Server 5.5 servers serving 15,000 government employees. The servers were scattered across multiple physical locations, and multiple support teams managed them. The Exchange organization was ripe for modernization and consolidation, and Jack's first piece of advice to the client addressed both needs. "They weren't taking advantage of some of the technologies that Microsoft had already incorporated in Exchange Server, like the antivirus API and edge spam-protection capabilities," says Jack, who advised the client to migrate to Exchange Server 2003 on a SAN. "Their servers and personnel were scattered around at five different data centers, and their backup capabilities were on different platforms using different software. \[I told them\] they needed to bring all their Exchange servers into two locations, put them on a SAN, and have them basically replicate that storage to each other for disaster recovery purposes," he says.
Although server consolidation seemed a straightforward solution, identity management was a big concern for the agency, whose employees include undercover law-enforcement personnel. Jack needed to find a way to ensure that security-sensitive departments' user profiles, email addresses, and other confidential information would be off limits to employees in other departments. "Certain groups—undercover detectives, vice, and narcs—felt security pains about letting the other groups know that they existed. Those guys didn't want anybody to know they even worked there," says Jack. To assuage security worries, Jack created IPsec policies to block unauthorized users from accessing secured domains on the servers and used public key infrastructure (PKI) to secure email messages.
Although the client approved of Jack's plan to update its Exchange infrastructure, the agency couldn't move forward with the plan until the mayor's office allocated it the money to do so. So Jack's next task was to produce the numbers—in this case, assign a dollar cost to email usage and storage—that would back up the client's request for new hardware and software. To do so, Jack first found a Gartner research report that provided dollar costs for email-message storage, sending email over the Internet, and sending email within the corporate network.
Now Jack had to determine the number and size of messages that agency employees were sending and receiving. To do so, he used Quest Software's Quest MessageStats to obtain an accurate count of the number of messages sent and received, message size, message origin and destination, and message volume within the Information Store (IS). Jack then used the Gartner figures as a basis for estimating how much a particular department or division was costing the government in terms of overhead for email support. "Because we were able to verify and certify the cost of these messages,we could provide charge-back dollar-amounts by department (and by individuals within those departments) to the CIO. If budgets had to be allocated based on storage of email, we could actually provide the IT department with the exact costs of storing email for different departments across their environment. The CIO can now implement a charge-back system, which is something he couldn't do before. And he can justify what he would be asking for and why," Jack says.
Jack's solution helped his client simplify and upgrade its archaic Exchange infrastructure while addressing security concerns. At the same time, he devised a solid approach to cost-justifying an Exchange and storage upgrade. "We came up with some astounding figures to provide to the CIO, basically some ammunition to let the agency go back and get another dip out of the budgetary well," says Jack. The agency was able to justify consolidating its Exchange servers on a SAN in one data center and successfully implemented Jack's recommendations.
HONORABLE MENTIONS Small Business
Alex Apodaca,Database/Systems Administrator, BioFilm
[email protected]
Although he's been a DBA and in his present position for only 2 years, Alex Apodaca brims with enthusiasm for the profession and Microsoft technology. "I've been learning like a machine," he says. When the marketing department of Alex's employer, a manufacturer of a well-known consumer healthcare product, needed a reporting application that would show product sales breakdowns for selected BioFilm customers, Alex jumped right in to meet the request.
"Marketing needed an application that could let a user choose one or more companies from our database," he explains. "The app would display an executive summary of monthly sales and units sold for every product class that we offer. Marketing needed sales and units totals and subtotals for each month, year, and product class for all the selected customers. The app also needed to subtotal each annual period and give the annual percent change, year-to-date percent change, and last-month-this-year versus last-monthlast-year percent change."
To meet these requirements, Alex used SQL Server Query Analyzer to create a TSQL query that calculates, subtotals, totals, and summarizes monthly product sales data for its customers. He also used ActiveX Data Objects (ADO) and Excel VBA to design an Excel interface that lets users run a query for the customers they select through a drop-down list box. "I used ADO to write the connection string and create the recordset," Alex says.
"Then I used Visual Basic and VBA to wrap up the SQL and send it to the SQL Server system," which runs the company's ERP application. "The recordset is created and displayed on an Excel worksheet, then formatted with Excel VBA."
Alex's solution automates a task that previously took two employees up to 2 weeks to perform. "Now one untrained person can get the reports done for all our brokers in about an hour," he says. Alex says that his solution shows that "with Microsoft technologies and a basic understanding of programming concepts, one can truly achieve amazing results!"
Bishoy Ghaly,
Software Developer, Business Network Communication
[email protected]
Bishoy Ghaly, who's been a software developer for 3 years, admits that writing software is his passion. He recently needed to find a way to automatically apply security permissions changes to all clients on a LAN, to ensure a secure computing environment across the LAN boundary. Sounds simple enough, but the catch was that the solution couldn't run on a server because it was geared toward small businesses that didn't necessarily have an IT administrator to oversee permissions. "Managing permissions and security in a large computer environment without a centralized solution is really painful," says Bishoy. "We needed to manage permissions in a \[Windows\] XP LAN—for example, allowing/disallowing programs to run, hiding drives, and tweaking configurations— without using a server solution and also without requiring someone to manually apply configuration changes on each computer."
Bishoy's solution was to write an application that would run on one of the XP clients in the LAN and manage permissions on the other LAN clients. To do so, he used Windows' remote registry feature, which developers use in applications to manage permissions on multiple systems. The remote registry feature is available in both the Windows .NET Framework and Delphi, and a C++ remote registry API is available. You can find more information about the remote registry feature at http://msdn.microsoft.com/library/ default.asp?url=/library/en-us/cpref/ html/frlrfmicrosoftwin32registry keyclassopenremotebasekeytopic.asp.
When Bishoy's application is installed on a LAN client, a user who has Administrator privileges can apply configuration changes to any computer on the network. Bishoy says his solution "saves time and effort for network administrators and helps increase their productivity without depending on server technologies."
HONORABLE MENTIONS Medium-Sized Business
Paul Ingram,
former IT director for CI Travel
[email protected]
In his previous job as IT director for a $150 million travel company, Paul Ingram had to address the company mandate to reduce its gargantuan phone bill. CI Travel has about 300 employees at 49 locations across the United States and relies heavily on telephone communications to conduct business. "High phone bills were eating up company profits," Paul says. "Because reducing call volume really wasn't an option, I decided to take advantage of \[Voice over IP\] VoIP technology to reduce per-call expenses."
Paul oversaw the company's purchase and implementation of its VoIP service (currently 175 VoIP phones) on the corporate network, which consists of a highcapacity Gigabit Ethernet copper backbone located at corporate headquarters and a Multiprotocol Label Switching (MPLS) WAN that connects the 49 sites. The new VoIP phones dramatically reduced per-call costs but "came with a new set of problems," Paul says. The biggest problem was sound quality, which was far less consistent than regular phone lines. "Since much of the company's business is conducted over phone lines, I had to be certain that VoIP users were getting the best quality of service attainable," says Paul.
Paul soon discovered that what the company saved on its phone bill it might end up spending again on IT resources to monitor and troubleshoot the VoIP exchange. He sought a way to reduce the amount of monitoring and make the VoIP service more reliable. Paul chose a network-monitoring product, Network Instruments' Observer, which included a probe (a software agent) that let Paul monitor VoIP packets traveling over the WAN backbone.
Paul used Observer to troubleshoot problems such as erratic jitter occurring between network nodes. He captured packets traveling between the two sites, then replayed them so that he could pinpoint the problem. "A packet capture identified a misconfigured application that was hogging bandwidth and causing a general network slowdown. I reconfigured the misbehaving application and also defined a \[Quality of Service\] QoS policy on the switch to give VoIP traffic the highest priority, thereby preventing other applications from compromising VoIP reliability," he says.
Paul says his solution benefited the company by ironing out the kinks in its VoIP implementation and making its quality and reliability comparable to the standard phone system. "I was able to successfully manage VoIP traffic, providing the best QoS to our customers—and saving the company 25 to 30 percent on phone bills."
Bernie Pannone,
Systems Engineer, Delta Health Group
[email protected]
Deploying an application on multiple PCs isn't an unusual job for an IT pro. Typically, you'd use Windows Installer to create a package, then maybe write a script that copies the files and performs the deployment. But what happens when the application files you have to deploy don't work with Installer? While on assignment as a systems engineer for a previous employer, Bernie Pannone faced this exact situation and found a clever way to solve the problem.
Bernie's client wanted him to deploy an inhouse-written application on about 200 XP PCs at six sites at various locations. "I can't mention the name of the software because it was so poorly written!" he says, laughing. In fact, the application filenames weren't Universal Naming Convention (UNC)-compatible, so he couldn't simply deploy the software by using standard Windows commands. Bernie further discovered that he couldn't package the software as an .msi file.
An old hand at scripting, Bernie naturally thought of writing a script to solve the problem. "I wrote a script (local.bat) that mapped the unused I: drive of each PC to a share where the .exe \[the application\] existed. As no user has the rights to install software, I created an Installer account and added it to the Domain Admins group. The customer ensures that the account is either deleted or disabled after each use because the password is in clear text and changes at every use," he says.
He wrote a second script, main.bat, which copied the local.bat file to each PC's Admin$ share and invoked the local.bat script by using a freeware tool, Sysinternals' PsExec, which lets you execute processes on remote systems from a central location.
Bernie used two other tools in his solution: the Windows Csvde utility and Excel. Before writing the scripts, Bernie used Csvde to import the client's Active Directory (AD) database into an Excel spreadsheetin comma-separated value (CSV) format. He then sorted the data he needed (i.e., names of the computers on which the application would be deployed) into one column, deleted the columns he didn't need, and saved the spreadsheet as a text file. The local.bat file contained this text file, the name of the executable, and the switches to use in deploying the application. (The application actually was compiled via InstallShield, so Bernie specified InstallShield switches in the script.) Once he'd determined the solution, working it out was fairly straightforward, Bernie says. "I had the Csvde conversion and both scripts written in under 30 minutes."
Bernie—who proudly says he's been a Windows IT Pro subscriber since the third issue of Windows NT in 1995—preaches the gospel of using scripting to solve administrative problems. "My soapbox is zero administration and scripting," he says. "I pound it into junior techs' heads at work." His ingenuity in using scripts to deploy a recalcitrant software package proves that Bernie practices what he preaches.
HONORABLE MENTIONS Large Business
Vlad Friedman,
CEO, Edge Web Hosting
[email protected], http://www.edgeweb hosting.net
Edge Web Hosting (EWH) provides managed application-and Webhosting services for midsized and enterprise corporations that require 100 percent uptime and hosts hundreds of servers for 20,000-plus domains. As you might expect, staying abreast of Internet security threats is a top priority and a never-ending job for CEO Vlad Friedman and his IT staff. "We take all the usual steps that a high-end infrastructure would.... We have antivirus software and firewalls. You hope that the antivirus software makers can keep up with the threats, but unfortunately, they can't," Vlad says.
Because of the company's 100-percent-uptime requirement, IT needs to ensure that it can restore downed systems quickly when a system is corrupted or disabled by a virus or worm. Several months ago, an Internet worm that actually disabled virus-scanning software was rapidly propagating across the Internet and threatened EWH's network of servers, which run a mix of OSs including Windows Server 2003 and Windows 2000 Server. "The only way to prevent our systems from being infected was to install the Windows 2000 \[Service Pack 4\] SP4 rollup, which had been released a few days prior," Vlad says. EWH quickly applied the service pack to the affected systems, but because the worm was spreading so fast, the company didn't have time to follow its usual procedure of applying a service pack on a test system and working out any problems before rolling it out on production machines.
Not surprisingly, installing the patches caused some of EWH's servers to crash, which could have been dire for the company and its customers. "As we started installing \[the service pack\] on our servers, we found an incompatibility with the new SCSI driver used in the rollup with probably about 20 of our systems. On the first system that crashed, we tried running the \[Windows recovery\] tools to perform the repair on the OS, which took about 4 hours."
EWH couldn't afford even 1 hour more of downtime, let alone 4 hours per server, and Vlad had to quickly find another way to repair the damaged servers. "We had just bought Winternals' ERD Commander and had recently used it to handle another issue," says Vlad. So the EWH IT staff ran ERD Commander, first using the product's Hotfix Uninstall feature to roll back the service pack upgrade and determine which file was causing the systems to crash. Next, they replaced the new SCSI driver with an older version on the downed servers and rebooted them successfully.
Sometimes creative IT solutions come from quick thinking and using the best tools for the job. Neither EWH nor its customers could have tolerated hours of downtime. By using ERD Commander, EWH averted a potential business catastrophe and cut downtime from hours to minutes, recovering the other 19 systems in about 5 minutes each.
Valerie Wampler,
Enterprise Messaging and Infrastructure Branch/EMIB, Infrastructure Lead, National Institutes of Health (NIH)
[email protected]
Valerie Wampler plays a key role in overseeing an extensive network for NIH, a major U.S. diseaseresearch facility. Because NIH comprises many research sites and supports researchers at thousands of institutions worldwide, many scientists who aren't NIH employees need to access the NIH network. Additionally, NIH's far-flung sites have different local network administrators who support their own domains and directories. Valerie faced the dual challenge of granting network access to non-NIH researchers and keeping up with numerous configuration changes. "NIH needed a way to support a very distributed network of individuals and sites while maintaining security and a high level of access to central databases and applications," she says.
NIH took three main steps to improve network security and performance and reduce the impact of configuration changes remote network administrators made. First, the network administration team responsible for AD established a root domain and forest for all of NIH and standardized on one destination for data repositories and research. Second, the team deployed two software products, NetPro DirectoryAnalyzer and NetPro Directory-Troubleshooter, to monitor system performance in real time and more quickly identify and resolve AD problems. Finally, NIH deployed NetPro DirectoryLockdown to support monitoring the AD configuration for unauthorized changes and to protect the network against Denial of Service (DoS) attacks, security breaches, and reliability and service interruptions.
The combination of establishing a centralized AD structure and repository locationand using third-party products to monitor AD problems and configuration changes has greatly improved the efficiency of NIH's IT staff and researchers' access to applications and data, Valerie says. IT staff "spends far less time on troubleshooting and data restoration. Scientists at remote locations have consistent access to applications, and all researchers now make better use of the knowledge base and data repositories within NIH." Valerie gauges that the overall cost savings from the solution amount to $2,028,846. "Now that \[NIH\] realizes the time savings resulting from more standardization and consistency in \[AD\], we regret not having deployed some kind of standardization earlier," she says.
