Excel use in IT in 12 minutes!!
I thought I'd base this post on Excel as from time to time you end up using it in IT. For consultants we generally pull it out when we're doing inventories for Datacenter Moves, or Discoveries among other things, at least on the delivery end.
I've been working on some Excel this week and started thinking about how the file was laid out and as I do, began making some improvements & changes to make life easier for myself. I thought I'd lay out my process here as it might help someone out or give them ideas on how to develop their own Excel knowledge when they have to dive into it.
Now, I'm no Excel expert but as you use it and try to figure things out you pick up a few things. Also helpful is when you get similar collateral from earlier projects from colleagues. So the longer you're in IT the more of this you have to draw on, if you can remember that it A) Exists, and B) Know where to find it!
I was asked to take part in a discovery exercise for a customer. I had two excel files from other projects, neither of which I worked on but had mature & valuable excel file designs. So once sanitized they could be of value, but which to use?!
The first was more basic, formulaes, pivot tables, lots of data and so on. The second used Macros to generate combinations of data and clean itself out when being used elsewhere. Now the Macro one looked much better, the report worksheets were enticing BUT...I don't know Macros. I could have had access to the guy who generated the original on a call but what if I run into difficulty later on? Choice one: Keep it simple. You can spend hours just trying to get something to work or just have it working. My advice - stick to a template you understand because if you don't, you're on your own. This lets you focus on the data and analysis and not to be fighting Excel all the time when you're in over your head!
So, I chose option A and am glad I did. I could still peek into Excel B for inspiration. Macros are just a way to get things done but formulae can get you there too!
I was given multiple data sources - and I needed to collate all the various worksheets to one master one that brought together key pieces of information.
Source #1: Physical inventory from last year. So this is out of date static data but a good start
Source #2: Discovery Tool output. Valid data within the last 24 hours, excellent source but as I'll explain not as easy to use
Source #3: Application Catalog. They want us to link Apps with Servers. Easy? Not so much!
Source #4: Previous Audit done a few years ago for the Datacenter. If not much has changed could be useful. We got lucky even knowing this existed due to a cracking good program manager on the customer side who had been around a long while.
Never under estimate the people issue, if you get a temporary person on the customer side for this kind of task you will probably never even find out that Source #4 or something like it exists making your job 10 times harder. Also we got a lead for paperwork that might show what servers were retired, again gold for mining to bring other data up to date.
At some point you have to cut off the number of data sources; ip ping sweeps, dns lookups etc the list goes on. Try to get at least two good data sources to correlate as one will never be enough. Two gives you better odds at finding the gaps you don't know about or to prompt questions about kit or subnets that may have been missed. Especially if it's behind a Firewall!
I ended up using the discovery data at the main source, and use the physical inventory to validate it. The discovery is not complete and only shows a fraction of the servers I expected but it's work in progress and using a graph to calculate the difference will be valuable when I speak to management about progress etc. So it will take a while for the discovery tool to get everything, also spend time tuning the output rather than doing it in Excel. We're seeing 500 application lines per server which adds up to 100,000's of excel lines and makes filtering there way too slow to be useful. Dirty in, dirty out; clean in, clean out. Get the Discovery people on your side and do the filtering of data on their side! Do you need a list of windows patches or unix device files for every server?!!
Remember, every time they find new servers you may need to reload that worksheet in your Excel file. Or ask them to supply changes ONLY. Ensure any other worksheets that reference the new data are looking at the full list, so if you start with 50 discovered objects, make sure any lookups reference 1000 lines should they end up finding that many. That way you don't need to update your formulae again. Otherwise you might start accidentally excluding new data if it flows beyond earlier guesses.
Now for the Spreadsheet - here are some headings I ended up with:
Index - list of each worksheet and hyperlinks to them with explanation as to what each is for
Goldmine - this is the worksheet where you gather all the data to give you a total view. Call it what you will!
Source #1 - so here I copy the contents of source #1 after trimming out any fat. Now I have a local copy of the data to look up separate to the original source file. This way I can keep all the data contained in my file without having to link to it and risk breaking anything.
Source #2 - see above
Source #3 - etc
Source #4 - etc
Reports - Using the Calculations worksheet I generated the most interesting graphs to me based on the data I had so far plus later ones I would know I'd need and use, even though the data points were empty at that time. I still had to work out the formulae but once done it's easy to edit later.
Calculations - You can do calculations at the end of any worksheet but it gets messy after a bit as the column widths are based on the data and don't suit the analysis. You can simply just cut and paste the calculations however to a central worksheet. This keep the data sources clean and allows you to keep all calculations grouped so now you can even link one to another easily. I didn't start this way but after all my calcs ended up on my physical inventory data source I decided to migrate them elsewhere to keep that source from becoming messed up any further.
List - Used to create pre-populated drop down lists for any of the other worksheets. I'll hide this later.
Save your file often but also create a new file by copying the old one and incrementing the version number. This protects you against data corruption later. It's like an old style backup. Do this every day and keep the last 1 or 2 versions only. Also kick up the latest copy to Sharepoint or some other backed up repository as a safeguard and to share with colleagues.
Most of the formulae I use are simple lookups and countifs statements; count the number of servers running windows, unix etc. I won't delve onto those here but you can use google which is where I learnt most of the tricks I use today. Take the time to learn this yourself and understand how they work as there's no substitute here for experience. Don't always take the first google result either, later Excel versions have better ranges of formulae so you may find more than one way to get what you need that is simpler and faster. This may break however if the customer wants an export to a much older version. Know your audience!
That's all for now. There's lots more you can do of course but mainly be organized. The worst job I ever gave myself was updating a data source line by line and square by square when I didn't think through how I would incorporate future changes! And as always, enjoy!!
