Category Archives: How to…

Excel: Identify Unique Values

Tobasic data analytics1 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.

Continue reading

1 Comment

Filed under Audit, Data Analytics, Excel, How to..., Technology

Excel: Identify Duplicates

While thbasic data analytics1e 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.

Continue reading

22 Comments

Filed under Audit, Data Analytics, Excel, How to..., Technology

Excel: Remove Duplicates

basic data analytics1To 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.

Continue reading

9 Comments

Filed under Audit, Data Analytics, Excel, How to..., Technology

Hiring Auditors Who Can Think

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

You should too.

How does he do it?

Continue reading

5 Comments

Filed under Audit, Employment, How to...

Server Audit for the Dauntless

dauntless server auditIf 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.

Continue reading

4 Comments

Filed under Audit, How to..., Security, Technology

ACL Tip: Beware of ORs and ANDs

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

Continue reading

Leave a comment

Filed under ACL, Audit, Data Analytics, How to..., Scripting (ACL), Written by Skyyler

Free File-Splitter Program

Splits text filesWhen 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.

Continue reading

5 Comments

Filed under Audit, Free, How to..., Technology, Written by Skyyler

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?

Continue reading

Leave a comment

Filed under ACL, Audit, Data Analytics, How to..., Scripting (ACL), Written by Skyyler

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.

Continue reading

Leave a comment

Filed under ACL, Audit, Data Analytics, Excel, How to..., Written by Skyyler

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.

Continue reading

6 Comments

Filed under ACL, Data Analytics, How to..., Scripting (ACL), Written by Skyyler

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…

Continue reading

9 Comments

Filed under Audit, How to...

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!

Continue reading

8 Comments

Filed under Audit, How to..., Poll, Security, Technology

ACL: Edit Scripts Easily

As soon as you create an ACL script, you often have to add to it or edit it. There’s an easy way to do it.

Continue reading

6 Comments

Filed under ACL, Data Analytics, How to..., Scripting (ACL), Written by Skyyler

ACL: Add a Custom View to a Table

Adding a custom view to an ACL table comes in handy when you want to 1) change the order of the fields in an ACL table, or 2) view a select number of fields.

You can add a custom view manually or via script. We’ll tackle the script version first.

This post is in response to Les’ question about reordering fields in a table.

Continue reading

4 Comments

Filed under ACL, Data Analytics, How to..., Scripting (ACL), Written by Skyyler

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!

Continue reading

2 Comments

Filed under Audit, How to..., Humor/Irony

How to Audit User Access

How to Audit User AccessWhen 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)

Continue reading

7 Comments

Filed under Audit, How to..., Security, Technology

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.

Continue reading

12 Comments

Filed under Audit, Data Analytics, Excel, How to...

ACL: How to Add a Computed Field

If you’ve been wondering how to add a computed field to an existing ACL table, you’re at the right place. I’ll take you through it step-by-step.

In ACL tip: What is a Computed Field?, I defined computed fields and provided 2 examples. I suggest you read that post before you dive into this one.

That post also explains expressions and functions, which you need to understand when creating computed fields. Both that post and this one are long ones, complete with graphics. You might want to print them both out first…

In this post, I’ll show you how to add the c_Region field that is described in the computed field post. It’s not as hard as it looks.

Continue reading

18 Comments

Filed under ACL, Data Analytics, Free, Free Download, How to..., Written by Skyyler

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!

Leave a comment

Filed under Audit, How to..., Security, Technology

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

Continue reading

Leave a comment

Filed under Audit, Free, How to..., Security

ACL tip: Create a File Import Script

File Import Script VacuumDid you know that you can create a script to import a file into ACL? That you can automate loading a table?

I’m talking about the File > New > Table command in ACL, also known as the Data Definition Wizard. Yes, you can create such a script, and I’m going to teach you how!

The good news is that it’s so much easier than you think. The bad news is that it doesn’t APPEAR easy, but it really is, because ACL does the heavy lifting for you. I promise that if you hang in there, you’ll so be a pro. Just try it once, and you’ll be hooked!

Continue reading

22 Comments

Filed under ACL, Data Analytics, How to..., Scripting (ACL), Written by Skyyler

ACL Error: Working Directory Does Not Have Write Access

On occasion, I have received the following ACL error: The working directory does not have write access permission (see below).

Simply said, it means: the working directory is not working; something is not write. :)

Seriously, the working directory is the directory in which the application wants to start, which is why it is also called the starting directory. This is the directory to which ACL expects you to save your ACL projects. That’s why ACL needs write access to that directory.

Continue reading

6 Comments

Filed under ACL, Data Analytics, How to..., Written by Skyyler

Audit and IT Audit for Dummies

Here’s some links for Audit and IT Audit for dummies, one from the IIA, the other from ISACA. Most of them do not require being a member or logging in.

While these articles are not extensive, they will point new auditors in the right direction, and provide a refresher for the rest of us. Continue reading

36 Comments

Filed under Audit, How to...

Easiest Way to Steal Confidential Data

A lot of company data is lying around unprotected, making it very easy to steal. No, I’m not talking about picking up other people’s documents at the printer. Stealing printouts isn’t hard, but it can be risky, especially if the printer is a busy one. Besides, it has 2 other problems:

  • Your chances of picking up confidential data are low at any given time.
  • The person will look for the printout and wonder what happened to it.

There’s a much better way that is fast, easy, simple, raises no suspicion, and is basically impossible to detect, if you do it correctly. Can you think of what it is?

Continue reading

6 Comments

Filed under How to..., Security

Master List of Blogging Tips and Articles

I occasionally blog about blogging, so to make these posts easier to find (and link to), here’s a list (index) of all the blogging posts on this blog, in alphabetical order, and by most popular.
I’ll add other posts as they are written.

Continue reading

2 Comments

Filed under Blogging, How to..., Technology, Top 10

For Easier Reading and Linking, click PRINT

Click Print for easy reading and linkingTo make it easier to read articles on the Internet that span multiple pages, look for a Print button or link on the page.

Most of the time, the link is at the bottom of the page, but sometimes the Print link is at the top (of course, not all web sites offer this, but most of the online magazines do).

Continue reading

Leave a comment

Filed under How to..., Written by Skyyler

ACL tip: Quickly View/Print Table Layout

To quickly view or print the table layout in ACL, type dis in the ACL command line (dis is short for display).

The table layout contains information about the fields in a table, such as field names, field type (ASCII, Date, Numeric), field length, and most importantly, the expressions used to create computed fields. For more info on computed fields, see What is a Computed Field?

Continue reading

Leave a comment

Filed under ACL, Audit, Data Analytics, How to...

How Virtualization Changes Audits

If you haven’t determined how server virtualization changes your audit plans, you better get moving. I’m not just talking about a virtualization audit (more on that later), but the audits that you typically do every year or on a multi-year cycle.

For example, if every year you do an audit on all networks, servers, applications, and databases that host your key financial reporting or PHI systems, you’re looking at policies and procedures, configuration management, security (including patching), user access, logging, and so on. But do you first consider whether those assets run on virtualized servers?

Continue reading

2 Comments

Filed under Audit, How to..., Security, Technology

ACL tip: What is a Computed Field?

A computed field is a field in an ACL table that you create using expressions.

An ACL expression is similar to a Microsoft Excel formula [e.g., =SUM(A1:A2)] in that it contains at least one function [like SUM]. Excel formulas operate on cells (like A1 and A2), but computed fields operate on fields.

Continue reading

3 Comments

Filed under ACL, Audit, Data Analytics, How to..., Written by Skyyler

Convert Report Headings into List

Occasionally you might need to convert the headings from an Microsoft Excel spreadsheet (which are horizontal) into a list (which is vertical) so you can email it to someone or include the list in documentation.

You don’t need to retype the list or even cut and paste each heading, cell by cell. My method takes a few steps, but I think it’s a lot faster and certainly more accurate. It also works with other applications than Excel. (You can also use the same method to copy a horizontal row of cell values into a vertical row (convert a row into a column), but more on that later.)

Update: Actually, I found a MUCH easier way to do it–convert rows into columns and vice versa. See these simple instructions at http://www.howtogeek.com/howto/12366/convert-a-row-to-a-column-in-excel-the-easy-way/ Continue reading

Leave a comment

Filed under Excel, How to...

What IT Auditors Ought to Know – and Don’t!

Here’s my list of IT/security basics that I think IT auditors ought to know. If you can’t understand and audit these items, you do not know enough about technology to avoid having the wool pulled over your irises (not matter how good an auditor you are). The list is in no particular order.

If you’re a CISA or CISSP and you don’t know the following, I think you have some work to do.

Continue reading

39 Comments

Filed under Audit, How to..., Security, Technology

Shine Your Shoes Like a Pro

Open letter to you-know-who: Shine your shoes.

I know lots of things don’t matter much in life, and this might be one of them, but it doesn’t take much effort to put some polish on your shoes. It makes your shoes happy, it helps them last longer, and you look better too, which can translate into more confidence on your part. And being taken more seriously by others, especially people older than you.

Continue reading

2 Comments

Filed under Employment, How to...

Are U Fooled by This Spam Technique?

Akismet flagged a comment as spam and I had not seen the spam technique used before, so I was fooled. I reviewed the comment and approved it, but the name, Michael G. Redmond, made me wonder a bit more (Redmond, WA is the home of Microsoft). So I goggled it. Oops. I remarked the comment as spam. Why?

Continue reading

5 Comments

Filed under Blogging, How to..., Security