At my previous job I was tasked with creating reports for management. As an intern, I was tasked with creating reports for management and their subordinates. This involved a significant amount of work that required organization. In the beginning I was overwhelmed with the amount of work involved. In an effort to protect my sanity I made a todo list, separating each task step by step. Creating a task list helped make the project more manageable and I kept my sanity.
The work involved pulling data remotely from cellular towers across the United States. This was a huge amount of data to process and took some learning. Accessing the devices proved to be quite a task, there are a lot of different pieces of equipment at a cellular site, all with different operating systems. I began learning the different commands to login and pull the diagnostics that I wanted. There was a lot of useful data that I needed from each device. Smartphone applications are capable of sending a large amount of data, and it is important to monitor any packet loss between the cell towers and cellular devices. Not only that, but it was important to monitor the health of the various pieces of equipment. We needed to know if power was out at a cellular site and make sure the backup batteries were working properly. It was also important to figure out how long the batteries could sustain all the equipment. If a cellular site was out of power for too long, a field technician would be dispatched to bring a generator until power is restored. Another consideration was overheating. It was imperative that temperatures were being monitored regularly and that fans and AC units were operating normally.
I was to take all this data that I pulled and present it daily in a visually pleasing format. The data helped technicians see cell sites that may be having problems among many other things. Lastly, I was tasked with separating the data to area specific managers and their subordinates. I was determined to make a name for myself and put in long hours to learn everything that I needed to. I found additional beneficial data that I incorporated into the reports as well.
After a couple weeks of painstakingly, tedious reporting I had had enough. There must be a faster way to do this. After some research I decided I was going to learn how to automate the process. I taught myself how to script using VBScript through SecureCRT and automated the data pull from each piece of equipment at every cellular tower. This involved logging into several pieces of technology at each cell site; routers, switches, microwaves, antennas, battery controllers, etcetera and parsing the data I wanted to report on. Additionally, I had to learn a lot of different technologies; Cisco, Juniper and so on. Automating all this in a secure way presented its own challenges.
After I figured out how to automate the gathering of all the data I wanted, I then had to figure out how to take all that data and turn it into graphs and pivot tables. So I got to work learning how to automate Microsoft Excel. I bought a great book titled “Power Programming with VBA” by John Walkenbach and began to learn how to automate the reports. After about three months I had the data pull and the Excel reports completely automated. I turned an eight hour a day project into about an hour, with 55 minutes of that hour consisting of emailing each manager and their corresponding technicians.
Another couple weeks and I taught myself how to automate all the emails, sending each individualized report to the correct person based on many factors. For example, a microwave that is displaying errors that it could be pathed incorrectly in Sector 2386 in Utah would go to the technician that was responsible for that cell tower.
Finally I had automated my job; a simple click of the mouse and I watched as my scripting did the rest of the work, completing my tasks for the day. Giving me 7 hours and 59 minutes to work on other projects and study for certifications. I have since utilized scripting to automate emails and tasks in a variety of settings, at home and work.
I really enjoy sharing my knowledge and am happy to pass on scripts and answer questions you may have in your pursuit to automate your job!