Missing json field `GetMailboxSettings` on table `azuread_user`

[MarkusGnigler]

I need the property 'userPurpose' to distinguish whether the user is a shared mailbox.

@ParthaI maybe you know a better api for that usecase?

Every idea is very welcome 😃

[ParthaI]

Hello @MarkusGnigler, we’ve already added this to the Microsoft 365 plugin and there’s an open PR for it.

@khushboo9024 / @misraved, could you please add the proposed column to this plugin as well?

[MarkusGnigler]

Should i make a extra join for one field in another plugin right?
To check inactive guest users and discard shared mailboxes. I need the sign_in_activity field and the userPurpose field.

Example query that returns also shared-mailbox principals:

WITH last_signed_in_user AS (
	SELECT
		id,
		user_principal_name,
		user_type,
		created_date_time,
		external_user_state,
		sign_in_activity
	FROM
		azuread_user
	WHERE
		user_type = 'Guest' AND external_user_state = 'Accepted'
)

SELECT
	id AS identifier,
	user_principal_name
FROM
	last_signed_in_user
WHERE
	sign_in_activity IS NOT NULL
	AND
	(sign_in_activity ->> 'LastSignInDateTime')::TIMESTAMPTZ < (now() AT TIME ZONE 'UTC' - INTERVAL '30 days')
	OR
	(sign_in_activity ->> 'LastNonInteractiveSignInDateTime')::TIMESTAMPTZ < (now() AT TIME ZONE 'UTC' - INTERVAL '30 days')
;