Course description
SQL and Powershell for the IT Auditors
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.
Do you work at this company and want to update this page?
Is there out-of-date information about your company or courses published here? Fill out this form to get in touch with us.
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
Training content
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
Certification / Credits
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.
ACI Learning
At ACI Learning, we train leaders in Cybersecurity, Audit, and Information Technology. Whether you're starting your IT career, mastering your profession, or developing your team, we're with you every step of the way. We believe that training is not a...