These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 11, 2014

Automated Report Audit for OIT

Executive Summary

I work for Brigham Young University’s (BYU) Office of Information Technology (OIT).  A major task for BYU, and OIT specifically, is to keep track of assets across all of BYU campus.  This includes thousands of networking assets alone, which equates to millions of dollars in funds from the Church of Jesus Christ of Latter-day Saints.  However, before the Church approves those funds for future maintenance, improvements, and investments, OIT must provide an accurate accounting of current assets.  Where are they?  What are they?  What is the status of that asset?

One tool that OIT leverages to track these assets is CAIMS, an internet-accessible, internal database.  Given the shear number of assets entered and changed in the database in a single day, a large amount of human error creeps into each entry.  So, before the Church audits OIT’s Network Solution team, the student employees are required to follow check lists and identify errors.  Any entries that have issues are then forwarded to their supervisor, who then corrects the database with the corresponding changes. 

This is a long and tedious process.  Many errors are missed, resulting in few improvements to the database.  To help alleviate this process, I have created an Excel file that includes VBA code to perform the checking, highlighting, and emailing of these reports.  Each audit is executed with a simple button click that will fetch the data, analyze it, and produce the ideal report to quickly and efficiently make changes to the database.

Excel Project
Write Up (Currently not up due to server errors)

No comments:

Post a Comment

Blog Archive