Here’s how to cut through the clutter in QAUDJRN and learn more

You hear the same complaints again and again: The security audit journal, QAUDJRN, contains too many entries. It’s too hard to make sense of all the details. Do you really care that you have 1592 program adopt entries one day, and only 450 the next? Do you need to copy the journal receiver to a database file every day? What exactly do all the different entry types mean?

The System i operating system provides commands that allow you to see the contents of the audit journal. The Display Audit Journal Entry (DSPAUDJRNE) command copies the entries to a display or report. However, the command has not been updated for a few years and it misses some of the newer entry types and formats. The Display Journal (DSPJRN) command can dump any journal receiver to an output file (database file). In both cases, the end result is an unformatted file that you can massage using your favorite query tool, such as SEQUEL.

The problem with both of these approaches is that they do not include other sources on the system that track jobs or users and their associated entries to create a complete audit trail.

To fully investigate a security event, you may need to look for information in QSYSOPR and QHST. In addition, the exit point history for critical servers such as FTP, ODBC, and JDBC, can provide valuable information. With all of these sources, you can more accurately piece together the trail for a job or user on the system.

Robot/SECURITY uncovers the transactions
Robot/SECURITY can take the security-related events for a date range, job, or user from multiple sources on the system and build exactly the output you desire. You can select from a list of potential system sources, such as QAUDJRN, QHST, and QSYSOPR (see Figure 1).

In addition, the Robot/SECURITY database contains usage data for common communication server exit points. It also maintains detailed log entries for user actions during profile exchange operations. You select the sources of information to include and your auditor gets a clear picture of what was accessed on the system.

From security sources to the detail
When you click Run, Robot/SECURITY interrogates your system to obtain the detailed entries. After all sources are checked, it displays the information in a graphical display directly on your PC. The entries are summarized by source type, including a counter of the number of entries from each source (see Figure 2).

(Click on screenshot above to see larger image.)

QAUDJRN is broken down by entry type for each audited area, such as PW (Password Failure) or AF (Authority Failure). Robot/SECURITY’s Forensics Analysis Utility offers a data filter that lets you limit the data shown to the entries that meet your criteria. For example, you can display only the entries related to a specific file or library.

Five-in-one convenience
Robot/SECURITY is really five security products in one package—forensics is just one part of the product. Give it a trial to see the other components: exit point monitoring, QAUDJRN monitoring, security auditing, and profile exchange.

Contributed by Tom Huntington, Vice President of Technical Services

When you use SEQUEL ViewPoint on your system, you can have it track who is using what and when. You can see if access paths are created, which files are queried, and how many records are retrieved. You can do all this with SEQUEL auditing.

What is SEQUEL auditing?
You access auditing through the ViewPoint Administrator. Auditing allows you to monitor your investment in SEQUEL. Its inquiries and reports help you get the most out of SEQUEL by showing you where SEQUEL is used most heavily and how it affects your system’s resources. Using the auditor’s inquiry and analysis tools, you can effectively manage the query environment.

There are three phases to the auditing process: collection, distribution, and analysis. SEQUEL collects data in a System i journal and its attached receiver. You turn on the auditing process through each user’s default data area, to collect auditing data selectively. After you collect some data, you can run the “Manage Audit Data” process to extract data from the receiver and distribute it to files in the audit database. Then, you can analyze the data using summary and detail inquiries and graphs.

Getting started
Data collection and distribution takes place “behind the scenes” and must be done before data analysis. To begin, go into the ViewPoint Administrator and select the SEQUEL Auditing option. Then, follow this easy, three-step process:

1. Choose the Set Audit Default option, to decide which users to audit. Check the box next to each user ID you want to audit.

2. Wait a day or two to allow information to collect.

3. Select the Manage Audit Data option (available only to administrators) to move the data from the journal receiver to the database. (In the future, you can schedule this option to run automatically.)

Once the submitted job completes, you are ready to analyze SEQUEL’s use.

Analysis
You can view the collected data as either summary or detailed information. To display SEQUEL requests by user, object, job name, and access path, choose Show Audit Data. Click User to display summary values of SEQUEL activity for each user you are auditing. Double-click a user to display detailed information about that user.

You can analyze information by object or job name; display a list of files, views, and reports used; or create a list by job name. To display detailed information, just double-click the item. The detail inquiry also shows a command summary that includes a graph.

The Access Path Detail window displays summary-level information about which access paths are used, by whom, and how often. You can drill into the access paths to show the specifics of when access paths were used.

Summary
The SEQUEL ViewPoint Administrator lets you access auditing features that help you keep track of the SEQUEL requests run on your system. It’s an easy way to show your management the importance of your investment in SEQUEL.

Contributed by Sheryl Quinlan, Technical Consultant

Not many companies can say they manufacture fun, but that’s exactly what Polaris Industries, Inc., does. A leader in the motor-sports industry, Polaris designs, engineers, and manufactures ATVs, snowmobiles, RANGER utility vehicles, and Victory motorcycles. The common denominator for these vehicles is that they let people escape from their busy lives to a world of freedom and fun. Since 1954, when the first Polaris snowmobile made its appearance, Polaris riders have been finding “The Way Out.”

Polaris is headquartered in Medina, Minnesota; with manufacturing facilities in Roseau, Minnesota; Spirit Lake, Iowa; Osceola, Wisconsin; and a distribution facility in Vermillion, South Dakota. Mark Haanpaa, the Lead System Administrator for Polaris, explains a little of their IT history. “In the early 1990s, Polaris brought in an IBM AS/400 and installed MAPICS. At about the same time we started using Help/Systems’ Robot products. Today, we’re on the IBM System i, running two systems with many partitions—including two production partitions that are replicated. One system houses our manufacturing Enterprise Resource Planning (ERP) Solution, and the other one houses our distribution facility.”

Robot/SCHEDULE, Help/Systems’ job scheduler and batch management system, was the first Robot product Polaris implemented. When they started using Robot/SCHEDULE, they used it primarily as a placeholder for all of their jobs. Mark explains, “With MAPICS, you have an ERP manufacturing environment that involves lots of Material Resources Planning (MRP) runs and forecasting, taking place weekly. It seemed to us that that kind of setup needed to be manual. Our operators would see the job, plug in a lot of values, and kick off the job manually.”

Mark admits they are a bit more savvy with their operations now. “We completely automated those jobs, both on the regular weekly schedule, and ad hoc in the middle of the week. We just select one job to run and, using group and reactive jobs, we run the entire MRP process, taking advantage of the event-driven scheduling available in Robot/SCHEDULE.”

By Mark’s calculation, Polaris has 350 to 400 jobs in Robot/SCHEDULE, including nightly, weekly, and end-of-the-month jobs. How automated does he feel their schedule is? Mark explains, “When I first came to work at Polaris, we had four operators at our manufacturing facility and two operators at our distribution facility. When we upgraded the system to a more robust infrastructure and got some operators more involved with the Robot tools, we eliminated four operators and moved them to a different part of the company. We now run those two systems with just two operators.”

For the last few years, Polaris has been running without a night operator. Mark explains, “We work a schedule from 5:30 a.m. to 5:00 p.m.; but jobs run around the clock. Using Robot/SCHEDULE, Robot/REPLAY, Robot/CONSOLE, and Robot/ALERT, we run our jobs and let our programmers know if something is wrong, so they can sign on to the System i and make corrections. If there is a standard solution for an error, we program the solution in Robot/CONSOLE to automate the response.”

Another benefit of automation was the ability to easily page Polaris programmers for problems without involving anybody else. “You don’t like to use your operators or your on-call staff as an answering service for your programmers.” Mark explains, “In the past, a problem would display a message on the QSYSOPR message queue, the operators would see it and call the programmers. Then, the programmers would tell the operators what to do. It was terribly inefficient and a waste of time. Now, with Robot/ALERT and Robot/CONSOLE, we go right to the source of the problem—the code.”

From an IT management perspective, moving operators into different roles is a positive step. Mark explains, “As the company expanded—since 2000, we have added more than 400 employees—administering our desktop systems and the more sophisticated equipment on the floors in the manufacturing facility has absorbed several of our former System i operators. We have reduced our System i administration overhead and redeployed our operations support to the desktop and the shop floor without having to increase our head count.”

In addition, errors from manual entries are down now that Polaris has automated their schedule. As Mark points out, “Certainly, there is always some human error when people manually enter values, variables, and so forth. By using Robot/REPLAY and Robot/SCHEDULE, we have been able to be more consistent and not have those errors.”

When asked what features of Robot/SCHEDULE they use the most, Marks thinks of group jobs, reactive jobs, and override codes. “As things juggle around on the weekends—a manufacturing line is going to be offline—using Robot/SCHEDULE’s Schedule Override Codes to omit and hold jobs is really useful. We use that a lot.”

As a publicly traded company, Polaris must deal with the compliance requirements of the Sarbanes-Oxley (SOX) Act, and Robot/SCHEDULE helps with that also. “I am the Sarbanes administrator for the System i and for a lot of the other IT functions,” Mark says. “We use Robot/SCHEDULE to run our daily Sarbanes matching report. We run a program that looks for changed objects and produces a report every day at 7:00. We match that against what should have changed based on our programmers’ project completion forms. If they don’t match, we have to reconcile the differences.”

As a final note, Mark encourages everyone to visit their Web site, www.polarisindustries.com, to look at the new Polaris products. “We’re very proud of our Victory motorcycle. JD Powers recently released their ratings for motorcycle manufacturers and the Victory ranks in the top five in every category listed.” It’s the latest in a line of products that help Polaris provide an escape into a world of freedom and fun. A line of products that offers ‘The Way Out’, courtesy of Polaris, Help/Systems, and Robot/SCHEDULE.

By Cheryl Lewis

I have two files on my System i. One is a parts list that includes prices. The other contains new prices. Is there an easy way to get the new prices into the parts list?

Use SEQUEL to do a JOIN UPDATE. When you do a JOIN UPDATE you automatically update a System i file with data from another System i file! (We believe this is unique in the System i world.) Here’s an example:

UPDATE SET((LSTPC.1 NEW_PRICE.2))
SQL(’FROM PARTMAST, NEWPRICE JOIN PRDNO.1 = PRDNO.2′)

Important note: The file you are updating must be the primary (first) file in the SQL statement.

The first parameter in the SET clause is the column name (field) to be updated. In this example, it is the LSTPC (List Price) field. The second parameter in the SET clause assigns the new value to the column name. In this example, we specify that another field, NEW_PRICE, will supply the value. Alternatively, the value parameter could contain a literal value or a derived character or numeric value, depending on your requirements. For instance, you might want to multiply LSTPC by a numeric value to come up with a new List Price.

The SQL FROM clause specifies the two database files required for the operation and the JOIN identifies the fields used to join the files together. The SQL statement in this example is fairly basic. It updates all LSTPC fields in the PARTMAST file. You also could use WHERE and CASE statements to apply conditional updates in your target file.

When you do a profile exchange in Robot/SECURITY, does the Security Audit Journal (QAUDJRN) show actions taken as being carried out by the “original” user profile or the “exchanged to” user profile?

Any changes made by a person using profile exchange show up in QAUDJRN as being carried out by the “exchanged to” or alternate user profile. If you need to investigate further, check the Robot/SECURITY Profile Exchange Activity List to determine who was using the alternate user profile at that time. The activity list also shows actions performed during the exchange.