What matters most on your IBM i are the files—more precisely, the data in those files—it contains. All the other parts of your IBM i, even the complex assembly of hardware, firmware, middleware, applications, user profiles networking and the rest, are just infrastructure designed to make access to those files possible, secure, and relatively convenient.
Think of QSystem Monitor (QSM) and QMessage Monitor (QMM) as casting a large sensor network over your IBM i environment. With files at the heart of the system, it follows naturally that both products should be able to monitor files. Here are two examples of the kinds of monitoring available, and how you can benefit.
#1: Database Server Monitoring with QMessage Monitor
The question “Who is accessing that file?” is the most important one to ask when considering file security. In an ideal world, file access would only be granted to responsible users with good intentions and a clear understanding of what they are doing. But in the real world:
- Users with bad intentions may read information from sensitive files
- Users with bad intentions may write faulty information into critical files
- Users with the best of intentions (and sufficient object authority) may accidentally overwrite information in a critical file, making that file useless or even dangerous
These sensitive files could be anything from payroll files in financial institutions to customer records for logistics companies, maybe even files representing the contents of an entire warehouse. In any case, reading or writing to these files has the potential to create a serious issue for your business.
Good news! QMessage Monitor can track this kind of unwanted access to files. QMM’s database server monitor functionality tracks accesses to any file on the system and generates a message for any kind of access that matches one of the database server filters that you have configured. The database server monitor also includes a rich set of filtering capabilities, allowing you to tailor which file accesses should be logged.
So, how would this work for you? Let’s say a file named QGPL/PAYROLL is accessed through the Run SQL Statement feature of iSeries Navigator (the official successor to dear old STRSQL).
Figure 1: Accessing the PAYROLL file through SQL
Information of this nature is highly sensitive and should be given top secret status. When configured to monitor this file, QMM brings up a message instantly.
Figure 2: Alert sent in response to PAYROLL file access
Second-level message text provides more detail and context, like the name of the accessing user, the IP address where the access was performed, the kind of access, and the text of the SQL statement.
Figure 3: User access details
Once the access has been turned into a message, you can use QMessage Monitor and QRemote Control (QRC) to escalate it out via SMS and email. Alternatively, you can use the QMM Activity Log to check which user profile accesses a particular set of files, and how often. A great tool for audits!
Database server monitoring offers rich functionality and direct operational advantages where access to files needs to be tracked. In many cases, these operational advantages translate directly to business advantages, and could include commercial saving by avoiding regulation compliance infringements or even reducing lengthy and costly security analysis for suspicious activity, especially for environments working with third parties where access needs to be highly transparent.
#2: SQL-Based Monitoring with QSystem Monitor
Since QSystem Monitor version 12, users have been able to use Object Count, Object Size, Number of Records, and Number of Deleted Records file-related metrics. QSM installations generally have at least a few monitors set up for those metrics. Now, QSM V13 takes what you can monitor to a whole new level.
SQL-based monitoring works like this: You take an SQL “SELECT” statement (the kind you use to pull information out of a file), configure it in QSM, and then you sit back and relax while QSM runs the statement for you at regular intervals, using the output of the statement as the value of a monitor.
It’s a simple and straightforward, yet extremely powerful way to get information into QSM. Want to track how many orders are delayed in your orders pipeline? Set up an SQL-based monitor for your orders file. Want to track if your banking application is configured to allow bank accounts to be overdrawn? Set up an SQL-based monitor for your banking application’s configuration files. Want to track how many accounts are already overdrawn? Set up an SQL statement. You get the idea.
As an example from the real world, we recently showed this new capability in a demo. We set up a monitor that checked for the number of overdrawn accounts in a file. Then, during the demo, we added a record to the file with a negative account balance. BAM! The threshold was exceeded and QSM showed the overdrawn monitor in bright red—pretty hard to ignore.
Figure 4: SQL statement alerting for overdrawn accounts
At the same time, an email went out via QMM and QRC warning that more than “X” number of accounts were overdrawn. The person receiving the demo—and the email from QRC—was positively surprised. He hadn’t realized that QSM is now capable of this level of application monitoring.
SQL-based monitoring can be used to get any kind of information that SQL can get. Think about it. You could probably come up with a few good SQL queries to plug into QSM right now. If you have a DB admin there, why not let them write the actual SQL statement to be plugged into QSM for you?
We expect the majority of QSM users to take advantage of this great functionality in the future. We hope it will be used for all kinds of wild and wonderful things we can’t yet imagine!