While Microsoft Excel was originally intended for performing financial, mathematical or statistical calculations, it’s often “abused” as a database as well. Despite ongoing controversy about whether using Excel as information storage is a good idea, it’s evident that Excel spreadsheets are used for storage in organizations of all sizes.
No matter how large or small your organization is, there are always a bunch of Excel spreadsheets, where data is displayed as simple tables or even as graphs. The data usually has different sources, such as manual input or extraction through OPAL or RDBM, and it can be operative or analytic data.
Since these spreadsheets are usually shared with a selected group of collaborators, you need to implement some kind of administration policies. In this article, we’ll show how you can use a monitoring tool to implement and monitor these policies.
Before we start, keep in mind that the users of these spreadsheets often include directors or high level managers, so be sure that you double check if they are okay with being monitored (this can save you some trouble in the long run).
Our following examples will assume that the organization has a file server which is used to share the Excel files.
The spreadsheet administrative policies should include the following metrics:
- Availability
- Reachability
- Version Control
- Access Control
- Backups
Our PRTG Network Monitor software offers two powerful sensors that can be implemented with a few clicks, and that allow you to check that the users can access the excel files. Before we go into further detail about the sensors, I’d like to explain the scenario, so that you understand where it makes sense to implement the sensors.
It’s common practice for admins to share the spreadsheets in a Windows environment using a set of network-wide shared folders. Using the security functions of Windows (e.g. Active Directory) the admins can manage the access rights for everyone individually.
iActive Directory (AD) is a directory service created by Microsoft for use in a Windows Server environment. It provides authentication and authorization functions, as well as providing a framework for other such services. The directory itself is an LDAP database that contains networked objects. Active Directory uses the Windows Server operating system. Read more ...
The File Sensor helps the sysadmins to check if the spreadsheets are always available. The sensor checks if a file exists in the folder and will provide useful information for proactive administration.
When you select the parameter “Sensor Behavior” while creating the sensor, the sensor will immediately switch to the error state if the spreadsheet does not exist, so that the admin can take corrective action. To inform your admins about any critical situation you can set up an alarm, which can be a visual/audible alarm, email, SMS, and so on. Since you will be the one creating the notifications and setting the thresholds, you can tailor the alarms to fulfill your needs.
In addition, the File Sensor offers the “File Content Verification” feature, which gives the administrators more control of the spreadsheets. The File Content Verification is based on the checksum function and will check if the file content has been changed.
If you need version control for the spreadsheets, then you can also select the option “Monitor File Time Stamp”, when you create the sensor. This will inform the admin whenever a new version of the spreadsheet is created.
PRTG Shows When the File Was Changed
This is another powerful sensor that will help you with the administration of any shared folder in your network. If you use these folders to save spreadsheets, this sensor will help you keep control over them.
The sensor gives you the following data:
- Folder size in bytes
- Number of files in the folder
- Passed time since the last change to a file in the folder (“newest file”)
- Passed time since the oldest modification of a file in the folder (“oldest file”)
Usually there’s a procedure in place where an automatic copy of the spreadsheets is generated as a back-up version or for version control. In this case the Folder Sensor will also help you to monitor this.
Additionally, the Folder Sensor can periodically check the number of files in a folder, which enables us to see if the number of files has increased over time.
Another valuable piece of information is the creation time of the newest file. You can easily set up a PRTG notification to alert the admins if the copy of any file is older than 24 hours, meaning that the daily backup procedure has failed.