Most Popular Blog Posts of 2021

Here’s a look back at the most popular blog posts of 2021 according to the number of times readers opened those posts. It’s been a long time since I’ve done a best blogs post…

Some of these posts are oldies, and yet they are still pulling in plenty of traffic. Check out the list, and see if you missed any of them, especially new readers.

Advertisement

Shatter Silos to Identify More Risk

If you want to increase the effectiveness of your audits and find risks that haven’t been identified before, you need to shatter your silos so you can identify more risk.

Too often, audits are performed on one process, one category, or one system: Earning Commissions, Windows Servers, or Wire Transfer. Each one of those is a separate silo (one for oats, one for corn, one for rice).

ACL Table Already Open error message

Oh, I hate the Table Already Open ACL error message with a passion.

And the Command Cancelled message (see the end of this post regarding that message).

Usually, it means I did something stupid, and I can figure out what, and fix it pretty fast.

Sometimes I have to scratch my head for quite some time before I figure it out.

I wrote a post in 2017 about Deleting ACL Table Covers A Multitude of Sins. This post is an expansion of that post, but mainly focuses on the “Table Already Open” error.

My Favorite ACL Tricks

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.

Table History

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.

How to get a Data Science job with little or no experience

When you’re trying to get a data science job, you need experience, but to get experience, you need a job, right? Not always, and this is the case for many jobs, not just data science.

But in data science, you can generate the experience you need yourself.

You might have seen one of my earlier posts, How to get an IT Audit job with little or no experience. Let me say from the beginning that getting an IT audit job with no experience is easier than a data science job with no experience. But according to an article from KDnuggets, it can be done. And like everything else, it takes hard work.

The article defines data science as “an interdisciplinary field that focuses on solving problems and gathering information.” 

Create a Help Desk for Data

Companies need to create a help desk for data, similar to the help desk they created for hardware, software, application, network, and user problems.

Can you imagine if companies didn’t have a computer help desk and each department had figure out their own computer issues? If each department had to find, load, configure, and troubleshoot their own hardware and software?

But isn’t that how most companies operate when it comes to data and data projects?

Before You Analyze Data

Before you start analyzing data, you need to 1) know you have the right data, and 2) understand the data and the process that produced it.

This post assumes, of course, that you already accomplished some of the hardest tasks already: figuring out what data you need, where to get it, and actually getting the data. Good luck with that. :)

This post is part of the Excel: Basic Data Analytic series.

How to Profile Data

Before you analyze data, you should profile it.

Otherwise, your analysis may not be too broad, too narrow, or you may miss some important insights or errors.

This post is part of the Excel: Basic Data Analytic series.

Data profiling is developing a profile of your data, just as facial profiles of a person, taken from various angles, helps you size up a person’s nose, identify whether his chin is sagging, and how far apart the person’s eyes are.

Quote of the Weak – Clean Data Manually

If you are in IT, audit, or security (or any other job requiring data analysis), you should NOT be cleaning data manually.

Let me share a recent experience with you….

A young IT auditor texted me at work and asked for some Active Directory user account data that I capture automatically every week, using some scheduled ACL scripts.

If you’re not familiar with my ‘Quote of the Weak’ series, I described it briefly in About. For a list of posts in this series, see here.

ACL Tip: Be Careful when Renaming Tables

When you need to rename ACL tables, be careful to also rename the associated .fil file also.

Otherwise, you (or your ACL script) might get confused. You might delete the wrong table or .fil file, and create a head-scratching problem.

I know because I confused myself.

Steal from Agile to Increase Audit Analytics

To increase the amount and depth of the analytics performed, steal some agile methods, and apply them to your audits.

If you’re not familiar with agile methods, check out the first 5 topics listed here (just click Next at the bottom of each page; the topics are quick to the point and full of pictures).

Briefly, agile projects are performed in cycles, or iterations, rather than in a long, linear-waterfall fashion, which is: do all planning, then field work, then reporting. Each iteration of the project creates some value and includes feedback, which is used in the next iteration to increase the value of the project.

Create a Team for Audit Analytics? Part 3

In the previous post, Create a Team for Audit Analytics? Part 2, I explored the pros and cons of expecting all auditors to develop a level of data and analytic proficiency.

These auditors would continue to do audit testing that involves analytics as well as testing that does not involve analytics. In addition to keeping up their business skills, they would be learning and upgrading their data analytic skills.

In the first post of this series, I reviewed some of the pluses and minuses of creating a dedicated analytics team.

However, a third option exists, which is sort of a hybrid between having dedicated analytic auditors doing all the analytic work and requiring everyone to increase and develop their data and analytic skills.

Let’s explore the hybrid method in this post, and wrap up the series with a few final thoughts.

This is the third post of a 3-part series…

Create a Team for Audit Analytics? Part 2

In the previous post, Create a Team for Audit Analytics? Part 1, I explored the pros and cons of developing an analytics team.

This team consists of analytic auditors who are dedicated to analytic projects; they would NOT typically manage audits or testing that did not include analytics.

In this post, let’s explore another option for managing and growing analytics in an audit department — expecting all auditors to develop a level of data and analytic proficiency.

This is the second post of a 3-part series…

 

Create a Team for Audit Analytics? Part 1

Once your audit team has proven the value of doing analytics consistently, the next question is: Do we create an analytics team and have the team do all (or the majority) of the analytics?

Or should we expect all auditors to develop some levels of analytics proficiency?

Of course, this question often comes a bit further down the trail on the analytics journey, but I think the sooner it is decided, the better.

This is the first post of a 3-part series…

The Analytic Staircase for Auditors

Building a successful audit analytics program is like climbing a staircase.

The staircase is a set of steps that consist of several items having increasing levels of maturity.

The staircase steps not only help you build your program, but enable you to measure that maturity.

As you view the staircase graphic, mentally insert the word “analytics” before each step.

Download SharePoint Data w/o Rights

At a company I worked at recently, I ran across a Sharepoint site and wondered whether I could download data that I wasn’t supposed to see.

Now I understand the purpose of SharePoint and company intranets is to share data, but even then, some data should be restricted to a limited number of people.

So I decided to check (before doing things like this, you better know How to Stay Out of Jail).

No Metrics, Little Analytics

If your department doesn’t track metrics on your analytics, you are probably not doing analytics or you are making little progress in analytics.

In either case, its obvious that analytics isn’t very important to your management.

Which is one of the points I made in my post, 10 Signs Mgmt Doesn’t Really Support Analytics.

So far, I have encountered very few audit departments that track meaningful metrics about their analytics.

Counting the number of projects that include analytics isn’t enough.

CISA Does NOT an IT Auditor Make

Passing the CISA exam does not make you a good IT auditor anymore than passing a driving test makes you a good driver.
Passing either exam says that you know the basics, but you still have a lot to learn.

Most likely, you still don’t know how and when to use what you know and apply it to the current situation. That’s why experience is necessary. Lots of it.

I’m going on a rant here, so reader beware. If you read on, make sure you hang in there until I make my main point in the end.

You just won’t feel the love right away…

Deleting ACL Table Covers A Multitude of Sins

I’m not sure why, but sometimes deleting an ACL table or two covers a multitude of sins, errors, or just plain weird behavior.

No, I don’t get any error messages. That’s the strange part.

I’m talking about strange ACL behavior that you can’t troubleshoot by reviewing the log.

Require Analytic Skills to Hire and Promote

Unless your department is still in the early stages of your analytics journey, analytic skills should be one of your hiring and promotion criteria.

In an earlier post I outlined 10 Signs Mgmt Doesn’t Really Support Analytics.

One of the signs is that hiring and promotion decisions are made without reference to a person’s analytic skills.

Which Way is Analytic North?

To create a successful analytics program in internal audit, you must have a plan. A plan that points to analytic North.

That requires WRITTEN goals.

In an earlier post I outlined 10 Signs Mgmt Doesn’t Really Support Analytics.

One of the signs that indicates management isn’t really serious about analytics is that management does not require every staff member to have measurable analytic goals.

10+ Signs Mgmt Doesn’t Really Support Analytics

Your management says it wants more analytics, but does it really support analytics? Here’s 10+ signs that indicate that your mgmt:

• Does NOT knows what it takes to get analytics off the ground
• Believes that analytics multiply like rabbits, naturally
• Is NOT willing to make the adjustments required to deliver and sustain real value.

New IT Auditor (and WannaBEs) Master List

Here’s a list of all my posts to-date related to becoming or growing as an IT Auditor, all in one place for easy reference.
I’ll add other posts as they are written.

Use LinkedIn to get an IT Audit job

If you’re looking for an IT Audit job, here’s how to use LinkedIn to get noticed.

In a nutshell, you need to enhance your LinkedIn profile so that everyone knows you’re working hard at learning IT auditor skills.

If you’re already working as an IT auditor, use these suggestions to get noticed more and move ahead (or into another company with more opportunities).

Why Internal Auditors Should Care about Robotic Process Automation

What is Robotics Process Automation (RPA) and why should internal auditors care about it?

RPA is software that imitates human interaction with computer systems to accomplish tasks.

RPA can log into multiple systems, navigate through user interfaces or command line functions to add, update, or download data, create tickets, place orders, or basically anything else a human can do.

Except think (and some vendors would disagree with that).

How to get an IT Audit job with little or no experience

I get asked all the time, “How do I get a job in IT audit with little or no experience?”

When Michael Onuoha asked me this question (see here), I thought I’d share my response with my readers.

You’ll find these same answers scattered around the blog as I answered people in the past, but I thought I’d pull it all together into one place.

Breaking into any field can be difficult, but it can be done. Especially when the demand for IT auditors is so high.

Careers After IT Auditing

Recently, a reader named Porak asked me what careers IT auditors can move to when they leave auditing (see the original question here).

I couldn’t find much on the Internet on this topic, but there’s a lot of options.

I’ve actually worked in quite a few of the areas mentioned below…

New IT Auditors Should Start Here

If you’re a new IT auditor or want to become one, I’ve listed a number of my earlier posts for your consideration. If you’re an experienced auditor, here’s an overview of the profession through my eyes.

These posts will:

1. Provide basic information regarding IT audit and security and links to other sources.
2. Help you avoid some of the hidden pitfalls that control owners and auditors face.
3. Give you ideas and approaches for some common and uncommon audits.
4. Give you a few chuckles.

If you start at the top and read through each post, you’ll get a good taste of the positives and negatives of IT auditing. Since you can’t do it in one sitting, you could bookmark the list and work your way through it as you have time.

Some of my Favorites

Since some of you are newer to the blog, I thought I’d bring a couple of my favorite posts to your attention.

How to Review Your ACL Log

Whether you script your projects or use menu commands, you need to review your ACL log carefully.

Good analysts review their results and the log as they work in ACL, after they think they are done, and have others review their log before the ACL project is relied upon.

(You can’t imagine the dumb mistakes my team and I found that saved us a lot of embarrassment later.)

Transform Data Fast with Excel Flash Fill

You can easily use Excel’s Flash Fill tool to transform data fast, without formulas.

Did you catch that? Without formulas!

Flash Fill has been around a few years, but few people, including auditors, seem to be aware of it.

This tool is so easy to use, you could learn it AND teach it to your mom in 4 minutes. Really.

Safely Check Bad URLs

If you’re looking for a way to safely check URLs for bad content, Lenny Zeltser had a great list of free online tools for you.

Check Excel Data for Blank and Invalid Values (Part 2 – Sort)

As I explained in the last Excel post, you can check for blank and invalid data in Excel several ways.

In this post, I will focus on the insights and issues encountered by sorting each column from A to Z and then Z to A.

Sounds pretty simple, but I’m willing to bet you will be surprised to learn a thing or two…

For a list of the reasons why you must validate data before analyzing it, see Why You Must Validate Data.

Check Excel Data for Blank and Invalid Values (Part 1 – Dropdown)

You can check for blank and invalid data in Excel several ways.

Depending on the size of the file and your preferences, you can either scroll through the dropdown list, sort each column from A to Z and then Z to A, or apply a filter.

Sometimes, you need to use a combination of these methods.

It’s important to know how these methods treat data differently and to be aware of their limitations.

Why You Must Validate Data

Before you analyze data, you must first validate it.

Otherwise, your analysis may not be accurate, and you may miss some important insights or errors.

This post is part of the Excel: Basic Data Analytic series.

Before analyzing your data, you need to check the following:

• Duplicate transactions do not exist.
• Required fields/key fields do not contain blanks, spaces, zeroes, unprintable characters, or other invalid data.
• Date fields contain real dates, and the range of dates is appropriate.
• Amount fields don’t contain inappropriate zero, positive, or negative amounts, and the range of values is appropriate.
• Each field is stored in the correct format. This prevents data from being converted on the fly into something else unexpectantly (e.g., user ID JUL15 becomes 15-Jul).

Recover Pictures from SD Card

Recently a friend of mine went to Europe and took almost a 1000 pictures that she saved on 2 SD cards.

When she arrived back in the states, one of the cards could not be read by her camera or her PC. The card was corrupted.

ACL: Automate Active Directory Downloads

Here’s a way to automate the download of data from Active Directory (AD), specifically group members, into ACL using adfind and the ACL Execute command.

I’ll walk you through it step-by-step.

This was posted before ACL released their own Active Directory driver, which I still haven’t figured out.

Even if you don’t use ACL, you might gain a better understanding of AD and LDAP in general….

Excel: Basic Data Analytics

Here’s a list of my basic data analytic procedures for Excel.

As I add more posts to the series, I’ll update this list.

I created this series because:

1) I often get asked by new AND EXPERIENCED auditors how to do these tasks,

2) when I review workpapers, I realize too many auditors are not aware of these functions,

Excel: Text to Columns

Excel’s Text to Columns function allows you to separate pieces of data in a single column into multiple columns.

This function helps when key data is buried in a field with other information and you need to extract the key data into a separate column before you can analyze it.

For example, you obtain a list of email addresses, and all you want are the user IDs. Or you get a list of servers, and the server name is server.domain.com, and you need just the “server” name. Or you need to separate LastName, First Name into separate columns. That’s where Text to Columns saves the day.

This article is the fourth post in the Excel basic data analytic series.

Easy! Insert Screenshot in MS Office

With just a few clicks, you can insert a screenshot into Microsoft Word, Excel, or Powerpoint from inside the application.

Excel: Identify Unique Values

To identify unique values in an Excel table, follow the steps below.

This article is the third post in the Excel basic data analytic series, which starts here.

The steps for identifying unique values are similar to identifying duplicates. The first difference shows up in step 3 below.

Excel: Identify Duplicates

While the previous post in this series described how to remove duplicate values in Excel, this post describes how to identify duplicates.

The remove duplicates function doesn’t tell you which values are duplicates, it just removes them. Sometimes you need a list of the duplicates so you can review them in detail or include them in your workpapers.

So we’ll look at how to create a list of duplicates across all values/columns and in specific columns.

Excel: Remove Duplicates

To remove duplicate values in Excel, follow the steps below.

This is the first post in a series of basic data analytic procedures using Excel. If you work with data regularly, these procedures will help you understand your data better and analyze it faster.

I started this series because I am asked how to do these tasks, sometimes by experienced contractors and auditors.

Hiring Auditors Who Can Think

Norman Marks, of the Institute of Internal Auditors, likes to hire auditors who can think.

You should too.

How does he do it?

Server Audit for the Dauntless

If you’re looking for an insightful server audit, and you’re dauntless, you might want to jump on this train.

First, why do you need to be dauntless?

Because you’re going to need to obtain your data from a number of different sources; the bigger your company, the more likely you’ll need to call on and question more than a handful of people.

Because comparing and tracking all the servers that are on one list, but not another can be a challenge.

Because it his highly LIKELY that you WILL find something and the server team will not be happy.

ACL Tip: Beware of ORs and ANDs

Whenever you use OR and AND operators in ACL (or other software, for that matter), be careful to ensure that you receive the results that you are looking for.

Assume you have Table1, which contains 100 loan transactions. 10 of those transactions have a loan rate of 5% and 10 transactions have a rate of 6%. The remaining transactions have rates above 10%.

Free File-Splitter Program

When I ‘m trying to work with text files that are so big I can’t even open them with programs like Excel, Notepad, or PSPad, I reach for the FREE file-splitter program.

ACL: How to Add a Conditional Computed Field

In ACL, a conditional computed field (CCF), is basically a regular computed field with some fireworks.

It looks and acts much like a regular computed field, but has some extra parts that do some extra work. Fortunately, the extras are NOT complicated, and after reading this post, you will find that will you use CCFs frequently.

So what’s the difference?

ACL Error: Cannot Export to Excel

Next time you get the cannot perform export to Excel error in ACL, try one of the 3 solutions described below.  The full text of the error is:

 Cannot perform the export.
You can export fields with maximum of 254 characters to Excel.

ACL: How to Add Computed Fields via Script

Once you’ve mastered creating computed fields, you’re ready to add computed fields to a table via script. It is easier than it sounds.

If you need some background on computed fields, see my previous posts, What is a Computed Field? and How to Add a Computed Field (manually). Now let’s explore writing a script that adds computed fields to a table.