Upcoming Dates & Locations
SQL and Powershell for the IT Auditors - UK-OAG216
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 AttendAudit 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.
- 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.
- Ice-breaker and introductions: what do you hope to learn?
- Course overview
- 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?
- 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
- 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
- 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
- Key takeaway from day 1
- Reviewing scripts provided by Management
- Key documentation principles and best practices
- 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
- 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
- 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
- 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
- Case Study: Designing a work program using SQL and PowerShell
- Measuring value from a data driven approach – suggested principles
- Review of course objectives
- End of course quiz