SQL and Powershell for the IT Auditors - UK-OAG216

Overview

CPE:24


Price: 1833.00


Overview

Evolving business risks have significantly increased expectations from today’s audit function. In this three-day hands-on course, IT Auditors will delve deep into MS-SQL and Windows PowerShell - two powerful tools that can open up a data-driven approach to technology audit delivery. The course will address practical aspects of applying auditing techniques based on these tools, across all phases of the IT audit lifecycle. IT Auditors will learn basic SQL and PowerShell scripting through this course and would be equipped to apply their new acquired skills and techniques upon course completion.

Who Should Attend

Audit Analysts, Assistant Managers and Managers if they have delivery responsibilities. Business Auditors at similar levels are also welcome to attend if they have had some experience with testing IT controls.


Prerequisites

  • 3-5 years of IT Audit Experience

What You’ll Learn

•Produce an enriched and powerful audit program that offers value and insights to Senior Management, not just conventional assurance. •Achieve budget and effort optimization by learning how to leverage data to assess control effectiveness and spot missing controls. •Develop basic scripting skills in SQL and Windows PowerShell and learn how these can be used to boost audit program execution. •Discover and avoid common mistakes and misconceptions with data. •Become an indispensable part of the internal audit function, by developing the foundation for SME-level expertise relevant to application and infrastructure audits, as well as data analytics.

Objectives

Session 1:

  • Ice-breaker and introductions: what do you hope to learn?
  • Course overview
  • Expectations

Session 2:

  • Refresher: What is the IT Audit Lifecycle?
  • Refresher: How do IT General Controls (ITGCs) and IT Application Controls (ITACs) interact and complement each other?
  • Historic audit approach and key weaknesses / challenges
  • Where do the opportunities lie?
  • How does Data help?

Session 3:

  • Introduction to SQL
  • Basics of SQL Scripting and key functions
  • DDLs, DMLs, DCLs and how these relate to the IT audit lifecycle
  • Primary keys, unique keys and foreign keys – and why these are critical for an auditor to understand and visualize using a database diagram1
  • Scalar and Aggregate functions – using these to retrieve the data you want
  • Afternoon Quiz

Session 4:

  • SQL Scripting – writing basic SQL scripts
  • The concept of JOINS – how these can be used to powerfully combine different datasets and manipulate data for review in a conventional audit program

Session 5:

  • Documenting scripts as part of audit workpapers to demonstrate assurance over key risks noted
  • Maintaining an Internal Audit repository for real-time data retrieval to drive a continuous auditing approach
  • End of day quiz

Session 6:

  • Key takeaway from day 1
  • Reviewing scripts provided by Management
  • Key documentation principles and best practices

Session 7:

  • Introduction to ACL for Windows
  • Using SQL mode to write basic SQL scripts on ACL
  • Difference between ACL scripting and SQL – related, but quite different!
  • ACL Connectors and how to use them
  • Afternoon Quiz

Session 8:

  • Windows PowerShell – what is it?
  • Introduction to Windows Active Directory – and how this directly links to every ITGC program
  • Group Policy Objects (GPOs) – and why these are important to understand
  • Domain Controllers, Member Servers and Client Workstations – key relationships

Session 9:

  • Key takeaway from day 2
  • Pre-requisites for Windows PowerShell – Active Directory Add-onli>>
  • What do you ask your Domain Administrator?
  • Connecting ACL to Windows Active Directory

Session 10:

  • PowerShell scripting – Access Management testing
  • ITGC Access Management work programs using PowerShell reports
  • ARS console, and generic and service accounts – how to review privileged access as part of either ITGCs or Application Control reviews
  • PowerShell scripting – GPO Ownership
  • Change Management using Windows Event Server Logs
  • Afternoon Quiz

Session 11:

  • Case Study: Designing a work program using SQL and PowerShell
  • Measuring value from a data driven approach – suggested principles

Session 12:

  • Q&A
  • Review of course objectives
  • End of course quiz
ENROLL IN THIS COURSE

Tuition cost

$1,833

NASBA Certified CPE

24 Credits

Auditing

Who should attend

Audit Analysts, Assistant Managers and Managers if they have delivery responsibilities. Business Auditors at similar levels are also welcome to attend if they have had some experience with testing IT controls.

What You'll learn

•Produce an enriched and powerful audit program that offers value and insights to Senior Management, not just conventional assurance. •Achieve budget and effort optimization by learning how to leverage data to assess control effectiveness and spot missing

Prerequisites

  • 3-5 years of IT Audit Experience

Objectives

Session 1:

  • Ice-breaker and introductions: what do you hope to learn?
  • Course overview
  • Expectations

Session 2:

  • Refresher: What is the IT Audit Lifecycle?
  • Refresher: How do IT General Controls (ITGCs) and IT Application Controls (ITACs) interact and complement each other?
  • Historic audit approach and key weaknesses / challenges
  • Where do the opportunities lie?
  • How does Data help?

Session 3:

  • Introduction to SQL
  • Basics of SQL Scripting and key functions
  • DDLs, DMLs, DCLs and how these relate to the IT audit lifecycle
  • Primary keys, unique keys and foreign keys – and why these are critical for an auditor to understand and visualize using a database diagram1
  • Scalar and Aggregate functions – using these to retrieve the data you want
  • Afternoon Quiz

Session 4:

  • SQL Scripting – writing basic SQL scripts
  • The concept of JOINS – how these can be used to powerfully combine different datasets and manipulate data for review in a conventional audit program

Session 5:

  • Documenting scripts as part of audit workpapers to demonstrate assurance over key risks noted
  • Maintaining an Internal Audit repository for real-time data retrieval to drive a continuous auditing approach
  • End of day quiz

Session 6:

  • Key takeaway from day 1
  • Reviewing scripts provided by Management
  • Key documentation principles and best practices

Session 7:

  • Introduction to ACL for Windows
  • Using SQL mode to write basic SQL scripts on ACL
  • Difference between ACL scripting and SQL – related, but quite different!
  • ACL Connectors and how to use them
  • Afternoon Quiz

Session 8:

  • Windows PowerShell – what is it?
  • Introduction to Windows Active Directory – and how this directly links to every ITGC program
  • Group Policy Objects (GPOs) – and why these are important to understand
  • Domain Controllers, Member Servers and Client Workstations – key relationships

Session 9:

  • Key takeaway from day 2
  • Pre-requisites for Windows PowerShell – Active Directory Add-onli>>
  • What do you ask your Domain Administrator?
  • Connecting ACL to Windows Active Directory

Session 10:

  • PowerShell scripting – Access Management testing
  • ITGC Access Management work programs using PowerShell reports
  • ARS console, and generic and service accounts – how to review privileged access as part of either ITGCs or Application Control reviews
  • PowerShell scripting – GPO Ownership
  • Change Management using Windows Event Server Logs
  • Afternoon Quiz

Session 11:

  • Case Study: Designing a work program using SQL and PowerShell
  • Measuring value from a data driven approach – suggested principles

Session 12:

  • Q&A
  • Review of course objectives
  • End of course quiz
ENROLL IN THIS COURSE

Schedule your course

Use the table below to select the time and location that works best for you.

We don't currently have any dates scheduled for this course.

ENROLL IN THIS COURSE

ACI Learning is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.