Business Problem

You are a Security Analyst for Microsoft's Azure Active Directory team. For a security compliance audit, you need to generate a report of all active users who have MFA enabled and belong to an active organization. The report must include standardized user and organization details for the auditors.

Your task is to join the users and organizations tables and produce a clean list that meets the following criteria:

Both the user and their organization must be active.
The user must have mfa_enabled.
The user's email, their extracted email domain, the organization's primary domain, and the user's role must all be standardized to a consistent case.

The final report should be sorted by the organization's primary domain to group users from the same company.

Schema: users

Business Problem

Your task is to join the users and organizations tables and produce a clean list that meets the following criteria:

Both the user and their organization must be active.
The user must have mfa_enabled.
The user's email, their extracted email domain, the organization's primary domain, and the user's role must all be standardized to a consistent case.

The final report should be sorted by the organization's primary domain to group users from the same company.

Schema: users

Column Name	Type
user_id	INTEGER
org_id	INTEGER
email	TEXT
full_name	TEXT
role	TEXT
seat_type	TEXT
status	TEXT
mfa_enabled	INTEGER

Column Name	Type
user_id	INTEGER
org_id	INTEGER
email	TEXT
full_name	TEXT
role	TEXT
seat_type	TEXT
status	TEXT
mfa_enabled	INTEGER

Column Name	Type
org_id	INTEGER
org_code	TEXT
org_name	TEXT
industry	TEXT
size_bucket	TEXT
country	TEXT

Column Name	Type
org_id	INTEGER
org_code	TEXT
org_name	TEXT
industry	TEXT
size_bucket	TEXT
country	TEXT

user_id	org_id	email_lower	email_domain	org_domain_lower	role_upper
94	23	sebastian.park@coralindustries.tech	coralindustries.tech	coralindustries.tech	MEMBER
95	23	hana.wilson@yahoo.com	yahoo.com	coralindustries.tech	MEMBER
96

2508. Active Users with MFA and Org Domain (Normalized)

Relevant companies

Tags

2508. Active Users with MFA and Org Domain (Normalized)

Relevant companies

Tags

Sign in to Practice