Saving 1000 Hours On Storage Management
Has your supervisor ever asked you a question, and you responded with, “I track that in a spreadsheet…”? He or she then reviews what you have created, and overnight, that spreadsheet magically becomes critical to your organization’s IT decision making process.
What started out as you updating a small spreadsheet file 1-2 times per month, grows and grows into something much larger and consumes a large part of your time each week.
This past spring, a Headwaters Client found themselves in this very same situation and decided to do something about it.
Their mission was to create chargeback reports reflecting the cost and usage of storage resources grouped by department (in this case departmental LAN shares). But, what started out as a small spreadsheet updated 1-2 times per month, evolved into something much larger as the chargeback program began to expand and take hold.
During some initial discovery sessions, the Client revealed to our team that one of their key employees spent nearly 40% of his time each week (roughly 16 hours) gathering, organizing, tidying, and compiling data and then entering the figures into spreadsheet reports. These reports would be published and distributed so that end-user departments could remit their financial share of the consumption.
Sometimes the end-users would challenge and dispute his figures. Even worse, occasionally he he would make an error which resulted in corrections, re-work, and accounting havoc. Luckily for him, his supervisors and management team saw the benefits of automating these tasks and freeing this key employee to work on more productive and business essential tasks.
After we were invited to propose a solution, the Client began to share some of their requirements. They were clear that the software and reporting solution would need to meet the following requirements:
- Conduct scheduled scans of the CIFS shares in the environment
- Collect the meta-data about each share (size allocated, size consumed, owner, creation date, access date, etc.)
- Populate a relational database schema
- Allow users to run custom reports via a web portal
- Allow for extension and customization to the database schema to define department and share names
- Inexpensive and billed as an operating expense
Complicating matters, this Client has some complex rules how LAN shares are grouped together, and a complicated algorithm for determining how much each internal department is charged per TB for storage consumption.
The Proposed Solution
After a Proof of Concept period, the Client eventually elected to deploy the StorageStream RaaS (Reporting as a Service) service (powered by Aptare), to meet their needs.
StorageStream and Aptare fit the requirements well for this project:
- Automated collection of File Analytics
- Ability to create custom tables and SQL queries (allows handling of customer’s complicated chargeback algorithm)
- In-expensive when purchased “as a service”
How We Did It
The tasks boiled down to basically automating the spreadsheet-based method of reporting. It took us 2-3 iterations, but once we had the the database tables populated, the SQL queries developed, and the business logic defined, the Client was able to transition to this automated solution. The feedback was very positive from the outset.
The trickiest part was the creation of an independent supplemental database schema that was formed to hold a collection of tables containing additional information needed for enhanced chargeback reporting. The tables were populated with data provided by the Client, which included:
- Division and departmental names
- Personnel contact information
- Host names of the CIFS and NFS servers
- Share names
- Billing codes used by the finance department
- Unit costs (per terabyte) for billing
These tables were linked to the Aptare file analytics database tables using common keys. SQL queries were then written, which rendered html-based reports that met the requirements set by the client.
If your organization is looking to automate the creation of a series of spreadsheets that track usage of key resources in your IT environment, you should consider speaking to the experts at Headwaters about how StorageStream could be a fit.