How I saved time with automation

Automation has been very helpful at every stage of my career, which spans 18 years of working with customers across multiple continents. In the early stages, Bash and AWK were the tools of choice, and nowadays, the toolset also includes Ansible and Jenkins.

Background

In my role as an IT Analyst, my responsibilities included reconciling SMS data on a per-customer basis for a Telecom VAS (Value Added Services) provider. The company had hundreds of customers, including leading private (banks, airlines, etc.) and public sector companies, and several hundred thousand messages were sent daily. Records for the data were stored as entries in a SQL database and several SMS gateway log files were stored across multiple Linux servers. Manually reconciling this data daily was extremely difficult, and the customers also wanted daily usage reports.

Revenue from this service was one of the key growth drivers for the company. Data accuracy was critical for customers as the SMS messages were related to banking transactions or other essential service alerts meant for the end-users. The company also had to maintain these records for seven years as per the law.

[ A free guide from Red Hat: 5 steps to automate your business. ]

How we used automation

In order to help reconcile the data between the two systems, we utilized automation (shell and AWK scripting) to query the databases and prepare a daily report for the number of messages sent by each customer. We also combined the logs of all the SMS gateways. Next, we used AWK to parse through the relevant entries in the logs and prepare a summarized report (similar to a pivot table) in the desired format and exported it into a CSV file.

This file could be easily imported into a worksheet by the MIS reporting team, and data from both sources was validated in a few minutes by utilizing macros. Thus, we were able to meet the dual demands of faster data access for both internal and external teams and ensure data consistency. This led to fewer customers contesting billing data and streamlined the company's revenue.

To further speed up the reporting, we also started creating database partitions by dates and migrating data more than two months old into an archive database. This process was also fully automated using shell scripting.

Conclusion

One of the major highlights of this initiative was resolving billing disputes with a major public sector entity for the data pertaining to the past two years and fast-tracking the payments for the same. Another added advantage was better compliance with legal requirements. We were able to effectively respond to a request from the local law enforcement to provide data regarding specific numbers in a day compared to several days in the past.

This is but one example of how automation has improved business processes and aided me in achieving organizational goals. There are many ways in which even simple automation tasks can result in significant wins.

[ Looking for more on system automation? Get started with The Automated Enterprise, a free book from Red Hat. ]