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).

Why You Should Run ACL in a Virtual Machine

Running the desktop version of ACL in a virtual machine* (VM) has so many advantages, but I haven’t heard anyone else doing it.

Consider the following advantages, listed in order of importance (to me):

#1 Reason for NOT Doing Data Analytics

Do you know the #1 reason auditors don’t do data analytics (DA) much?

It is so simple, so obvious, I hesitated to blog about it. Let me know if you agree.

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.

Reader Poll: Who R U?

I’d like to get a better feel for my readers, so please take the following poll.

I’ve leaving this poll open throughout 2015, so no matter when you see this, please vote. Thanks!

Poor Testing Leads to Complicated Coffee

Poor testing and a seemingly simple process conspired against me when I tried to make 36 delicious cups of coffee.

Since I make coffee every morning in my own unit at home, and the instructions were posted next to the coffee maker, I was confident.

But I had never made that many cups of coffee or used a commercial coffee maker. And this machine had a thermos-like carafe that held the coffee and kept it hot.

The instructions said the first step was to turn the coffee maker on. It went down the drain from there.

SONY stored Passwords in Password Directory

And in unprotected documents.

Lots of passwords. Lots of documents. Lots of easy access.

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?

Don’t Use GRC app to do Workpapers!

I consulted with a company that implemented a new GRC package, and unfortunately they are using an application designed for GRC to do audit workpapers.

That wasn’t the only move that was questionable…

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.

Bank’s Change Management Troubles

AuditMonkey has written about the Royal Bank of Scotland’s change management troubles.

Too Few GOOD IT Audit Blogs

As we all know, too few good IT audit blogs exist.

So it’s exciting when a new one is launched that shows promise.

Review of ACL Excel Add-in, Now FREE! (NOT)

In case you missed it, ACL released the next version of their Acerno product, renamed it ACL Excel Add-in, and made it FREE!  2021 UPDATE – it doesn’t look like it’s free any more; requires ACL subscription.

UPDATE – I’m guessing that since this product never caught on, they only give it away to subscribers – go figure.

So I thought I’d update my review.

For my original review of Acerno, see A Review of ACL Acerno. It still seems that I’m the only one who ever took the time to review the product (versus marketing blurbs, which are all over the ‘net), which appears to be a statement regarding its popularity.

Despite the poor popularity, since they updated it AND made it free, I decided to dive in for another look.

Note: This add-in is not just for auditors! Any one who regularly reviews data should consider using this simple, EASY-to-use software.

Please take the new & improved poll at the bottom of this post (also free).

Data Center Failure

One company I worked at had a sad data center failure, and I’m not talking a power outage or a fire or theft.

When I arrived at this company, it had no security department. Few security processes. Little security.

And the company also made two interesting mistakes when it hired me.

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%.

You Might be an ACL Freak if…

You might be an Audit Command Language (ACL) freak if more than 2 of the following are true:

• At work, you have a second computer (or virtual machine) just for running ACL.

Infographic: Profile of a Fraudster

The Association of Certified Fraud Examiners recently posted an infographic entitled: Profile of a Fraudster.

FREE Frank (Catch Me If You Can) Abagnale video

Frank Abagnale, the real-life con artist depicted in the Catch Me if You Can movie, talks about his life as a fraudster in a free video.

Back in the 1960s, Abagnale posed as an Pan Am airline pilot, a pediatrician, an FBI agent, and a lawyer. He was a master at conning people and passing bad checks. He even conned his dad (see ‘First Con’ heading).

Jacka’s Most Interesting and Geeky Auditor

If you’re in the mood for auditor humor (is that an oxymoron?), the IIA’s Mike Jacka has something for you.

Periodic Access Review Problems

One of my current clients is trying really hard to do periodic access reviews.

They know that mistakes are made in granting access, that users get access and eventually don’t need it anymore, but don’t tell anyone, and that some users leave the company without their manager’s knowledge (I never have understood how that happens, but it does; it has happened in every Fortune 500 company in which I’ve worked).

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.

2014 Top Paying Certs (United States)

Below is a list of the top paying certs for 2014 (including average salary amount).

The list is based on the 2014 IT Skills and Salary Survey conducted by Global Knowledge and Penton, completed in October 2013.

After the list, I offer a few comments on some of the certs and the salaries.

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?

5 Things I Hate About ACL

I have 5 things I really hate about ACL. 

No, these aren’t critical issues, but I deal with them constantly, and they waste my time. All of them deal with the user interface.

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.

FREE Global Security Resource Guide

ISC2.org, the organization that grants the CISSP certification, has a great, online, FREE global security resource guide.

No membership, certification, or log-in required!

Update 1-11-14: See Kim White’s comment below about availability of this resource. If it is made public, I will link to the new version. The “remove this post now” comment makes me wonder if it’s coming back for public consumption*. – Mack

How to Perform Population Validation

Do you perform appropriate population validation of the data you rely on in an audit?

Population validation is simply gaining confidence that the data you are using in your audit contains all the appropriate data for your audit objectives (e.g., your server list includes all the SOX servers).

For the difference between population validation and data validation, see Why You Must Validate Data.

So how do you do population validation? Let’s look at an example…

Auditing is a Noble Profession

While commenting on AuditMonkey’s blog, I noted that because companies often don’t do the right thing, auditing is a noble profession.

Mainly because we can right some of those wrongs.

Then I said…

How to Ping a Server

If you’re an IT auditor or security analyst and you don’t know how to ping a server, then I have some words for you:

LEARN HOW!

So let’s do it.

I’m assuming most of my readers already know how to do this. If so, please answer the poll question at the bottom. If not, please read on, then answer the poll question. Thanks!

Free CISA Prep: Self-Assessment Exam

If you’re planning to take the CISA exam, you need to take ISACA‘s own CISA Self-Assessment exam (get it here).

The exam consists of 50 questions that allow exam candidates to “assess their knowledge of the CISA job practice areas and determine in which information security areas they may have strengths and weaknesses.”

FREE Infosec & Web Pentesting Education

Security Monkey posted that PentesterLab has some great resources that provide training on pentesting, like:

• Basics of Web
• Basics of HTTP
• Detection of common web vulnerabilities:
• Basics of fingerprinting
• and more! (like Linux Host Review)

Ask a Question

This post is the parking lot for questions that don’t necessarily relate to one of my posts.

If you want to ask a question, post it here.

Why CISSP?

This post answers these questions: Why get the CISSP certification? What has it done for me? What else do I need to know?

Charles, one of my readers, asked me, “Do you have postings related to CISSP?” Not many, but here’s one….

Evaluating Risk in the Dark

When you evaluate the risk of a vulnerability, do you do it in the dark?

Or do you take into account other factors that might affect the risk?

What if one of the factors is an existing audit issue that has not been remediated?

Master List of CISA Articles

To make these posts easier to find (and link to), here’s a list of all the CISA-related posts on this blog, in alphabetical order.
I’ll add other CISA posts as they are written.

How to be an Irritating Auditor

If you need to read about how to be an irritating auditor, you obviously haven’t been auditing very long. According to most auditees, that quality comes with the territory, right? I hope not!

FREE CISA Glossary

ISACA has a free glossary of IT, audit, and security terms that is not only helpful in studying for the CISA exam, but is a good reference guide for new and experienced auditors.

How to Audit User Access

When checking system access, make sure you look at all the different items that affect the user’s access. For example, the user might need one or more of the following:

• Application ID
• Application role or group
• Membership in an local server group, Active Directory (AD) group, or UNIX Group
• Access to the application’s share and/or folder on the server
• Database ID
• Database role, including access permissions (read/write)
• Other permission (from a home-grown application code or enterprise identify management system)

CISA vs. CIA Certification

If you’re an IT auditor (or want to be one) and don’t have any audit certifications, which certification should you get, the CISA or the CIA? If you want to get both, which one do you get first?

Full disclosure: I have the CISA, but not the CIA. Back when the CIA was 4 exams, I studied for all the CIA exams except the financial exam, but ended up not taking any of the exams. I also have the CISSP.

IT Admin vs. IT Auditor

IT admins and IT auditors often don’t see eye-to-eye, and they don’t usually think their goals are similar.

The IT auditor just has to work a little harder to convince the IT admin of that. I’ve worn both hats, so I know it can be done.

Compare Multiple Fields with Excel vlookup (Easy)

When you need to determine whether several fields in 2 Excel documents (or tabs) match, all you need to do is combine the fields in each document into one value and then compare the 2 values using vlookup.

You could do this many ways, but if you’re new to Excel formulas, I think this way is easier to configure and understand. I’m assuming you’re familar with the basics of Excel and vlookup already.

If you are not familiar with vlookup, you might want to review this first, as my post does not teach you vlookup, just another way to use it.

New IT Auditor Needs Help!

A new IT auditor needs some help dealing with database patching issues and how far you need to dive into technology during an IT audit.

Take a moment to read his comment and add your thoughts. I’ve put in my 2 cents. Let’s get a good discussion going.

I think any auditor can chime in, as audit scope and audit limitations are not unique to IT audit.

Dinesh’s comment appears in What IT Auditors Ought to Know – and Don’t!

PSPad: Great Text File Audit Tool

PSPad is a great text editor and search tool, so by default, it’s a great audit tool, and it’s free. It can also handle a million lines of text–literally. Are you interested yet? It is also a great file diff/compare tool I’ve ever seen.

PSPad works with text files, such as those ending in TXT or CSV, or any text-based file (like an ini file). It works with DOC files too.

I’ll explain how to do the following with PSPad:

• Search a file (find all lines containing X)
• List all occurrences/matches of a search term
• Export a list of occurrences
• Compare 2 documents (diff)
• Download & install PSPad

Biggest Problem in Computer Security

What’s the biggest problem in computer security, according to valsmith at carnal0wnage.attackresearch.com? Well, it’s…

Staffing.

As the author admits, the post leans toward self-promotion of the company, but it makes many good points and deserves a read and a good pondering.