Here’s a couple of my favorite ACL tricks & treats that I use frequently to get me through the day a little faster and a little less frustrated.
These tricks are the kind that they don’t teach you in class or in tutorials (at least I’ve never learned any of them there; maybe I was in the bathroom during that session); I either figured them out on my own or had someone say, “Let me show you something.”
The Command Line
When I train someone in ACL, the command line is one of the first bonus items to which I draw their attention. The command line allows you to run individual ACL commands without using the ACL menu or scripts.
To open the command line: in the menu bar, click Window, Command Line. This will appear:
You can run most ACL commands from the command line, such as OPEN a table, ASSIGN a variable value, and lots more (the commands can be entered in lower/upper/camel case, but I use uppercase in this post to help them stand out).
My 2 most frequently used command are listed below.
DISPLAY – list the fields in a table, along with their start position, length, and more.
To run this command, 1) open the table you want to run this command against, and 2) enter the command in yellow in the command line, and press Enter.
Note that the last line shows you a computed field and the formula behind it.
DISPLAY VARIABLES – list all currently active variables, their type/format, and their values.
To run the following command, just enter it in the command line, and press Enter.
Note that user-defined variables (v_record and v_table) are shown, along with system variables (OUTPUTFOLDER and WRITE1). If you’re not familiar with ACL system variables, look them up in the ACL help file (it will be worth your time).
Note that 2 of the variables are character (C) type and 2 are numeric (N).
This command is extremely helpful when you are troubleshooting variables.
Bonus: Instead of DISPLAY, you can type DIS; instead of DISPLAY VARIABLES, you can type DIS VAR. Much shorter!
Bonus #2: Another useful use of the command line is to enter variable values. For example, if you have a NOTIFY command at the end of a script that will send an email if v_Run_Notify = “Y”, you can enter v_Run_Notify = “N” in the command line and press Enter to change the variable value and prevent the NOTIFY command from running while you test changes to your script.
Open a Table You Can’t Find
Sometimes I can’t find a table because I don’t remember (or know) which ACL folder it is hiding in (the folder in your project, not a Windows folder on your hard drive).
If you know the name of the table, you can just type OPEN <tablename> and press Enter (where <tablename> is the name of the table you want to open). When I don’t remember the table name or I’m too lazy to type it out, I copy the name from the ACL log or a script that uses it, and copy it to the command line.
When the table opens, you can then see what folder the table was hiding in (the folder is not shown in screenshot below).
Clear the Command Line
When you use the command line a lot, you have to clear it before entering another command. Instead of backspacing and deleting the text, or highlighting and deleting the text, just click the X at the far right.
Likewise, instead of pressing Enter after entering a command, you can click the checkmark.
When you’re working on a big project that contains many different tables, sometimes it’s hard to remember how that table was created. Or you haven’t opened the ACL project in a while, or you have to troubleshoot or review a project someone else created.
So what table(s) were used to create that table, and what filters/joins were used to create it? How many records did the original table contain?
I used to hunt through the ACL log or the scripts to find all that info, but for the most part, it’s all in the table history.
To access a table’s history, 1) open the table you’re interested in, and 2) from the menu bar, select Tools, Table History. You’ll see something like this:
The first line shows the original table (PcardTransactions) and the FILTER used. The second line shows the filtered data (all fields) was extracted to a new table (PCardUSA).
The third line shows number of records in the original table (Input) and the fourth line shows the number of resulting records (Output) in the extracted table.
If a JOIN was used, the table history would list the primary and secondary tables as well as the JOIN command parameters used.
The other nice thing is that you can take a screenshot of the table history and use it for documentation or evidence.
Bonus: Instead of selecting Tools, History from the menu, you can type DIS HIS in the command line, and press ENTER. Same results!
If you have some ACL tricks up your sleeve, let me know.
6 responses to “My Favorite ACL Tricks”
I thought you dropped ACL for an alternative. Did you stop looking for alternatives?
I was checking KNIME and it looks promising cause you can execute workflows without opening the GUI and has some good nodes for anomaly detection, like the H2O integration to apply isolation forest for anomaly.
LikeLiked by 1 person
No, I haven’t dropped ACL, and neither has skyyler. We have lots of automated ACL scripts running to support in the businesses we support.
And while we are using other tools also, in some cases, we still use ACL as the other tools either can’t handle what we need to do OR we don’t have the time to figure it out in other tools with current deadlines.
As our experience in other tools matures, we’ll do less and less new development in ACL.
One of the issues I’ve had with with R is that everything loads in memory, and some of the files we use are beyond the RAM we have available. We will have to reconfigure our machines for greater RAM eventually.
I’ve read about KNIME, but haven’t kicked the tires yet. As usual, I have way to much work to do. While management wants us to transition from ACL, they can’t afford to take us off our projects to so that we can do what’s needed to do just that.
As usual, management makes demands, but doesn’t count the cost of making it happen, and they seldom want to provide the time that’s required. And they don’t understand what’s required either, and don’t want to know.
Go figure. (That’s another blog post or 2.)
Thanks for your question, Angel. I’d like to hear more about your journey.
Did I miss something? Is ACL no longer available stand alone? I just went to the Galvanize site and couldn’t see anything obvious related to data analytics/forensics. I assume there is still a way to get just the analytics tool.
If you happen to know the new name/s, perhaps include it in your posts.
I’ve responded in the past that we moved away from ACL, but the WE is used pretty loosely. While there are 3 of us in our audit department, as far as ACL is concerned, it was an I. “We” moved partly because “I” didn’t want to be pigeoned as the only person that could read write and understand the scripts (as well as the crappy subscription models they kept pushing).
there are a ton of audit shops that have built some insanely awesome tools on ACL (Tools being used by the business as well). Tools that would be costly to replace and ACL is still excellent. I suspect even if it does “die” the skill will be needed for a long time to come. Not sure if it would live as long as fortran but if it ain’t broke why pay to fix it. … Although auditor’s shouldn’t be trusting a black box and I often go back through old scripts and discover logic errors.
Some of those same shops have lost their Champion to retirement or they simply moved on. Hopefully the people running the scripts truly understand them.
P.S. I keep my parallel port key for nostalgia.
LikeLiked by 1 person
Yes, ACL Analytics is still available in standalone/desktop. ACL’s website has become more confusing since the Galvanize thing happened. I think you can only see Analytics if you’re logged in with a subscription.
We have some really good analytics that run daily, weekly, and monthly, so ACL will be around for a while. No one else in the company is doing the kind of work we’re doing, and we’re getting senior mgmt attention.
However, ACL is text-based, slow, has limits, and can’t write to a database, so we’re moving to other tools for some new development. Some of our projects do a 2-year rolling analysis, which requires us to keep 2 years of data all the time. Each month the oldest month is purged and the latest month is added to the analysis. Our security team doesn’t like all those ACL data files sitting on the LAN, and would rather lock that data in a database where it can be monitored more closely.
While I agree with the security team, they tend to forget most of our company data is all over the LAN in Excel files, Power BI files, and files.
I also remind them that I have scripts that delete files regularly and also sweep all our folders for files that get missed, or added during an adhoc analysis that people forget to delete.
I also chuckle because I’ve configured all our traffic going into our databases and out of our databases encrypted in transit, which many other teams in the company haven’t done. Are they going after those other teams for that? Not really (but we are).
I guess they are just paying me back for all the audit issues I wrote against them so far…
On the ACl side, we have a few people who script, and they are getting better, so I’m not totally alone. The problem is that I’m the only one who knows SQL, databases, and a number of other tools. So ACL skills aren’t our only problem.
Thanks for your input.
Pingback: Master List of ACL Articles and Tips | ITauditSecurity
Pingback: Couple of Favorite Posts | ITauditSecurity