Pepuri Server world Eng Version

Recently, there have been significant changes in Copilot in Excel.

It seems to be related to the updates below, after all.

What’s new in Copilot | June 2024 - Microsoft Community Hub

Expanded data structure support and comprehensive answers in Copilot in Excel

Users are no longer limited to using Copilot in Excel only in Excel tables, because Copilot in Excel now works on data ranges resembling tables with a single row of headers on top. This saves time by eliminating the need to format data. so users can start analyzing with Copilot right away. This feature started rolling out this month. 

In addition, the edit box is now available on any Excel worksheet, regardless of the selected cell. Copilot will reason over the nearest table, or data range resembling a table, to the user’s selected grid area on the same worksheet. This enables users to interact with Copilot immediately, regardless of their position in the worksheet, saving time and increasing productivity. This feature started rolling out this month.

Copilot in Excel now also provides more conversational and comprehensive answers to a wide array of Excel-related questions. When prompted, users can now receive step-by-step instructions including formula examples, or can see corrections and explanations of formula errors. This feature started rolling out this month.

In my personal opinion, I think it has now... become somewhat useful. I’ll show you how you can utilize it to some extent with a specific prompt pattern.

https://youtu.be/GkGa20qCxwg

Auto-Save Activation

To use Copilot in Excel, the file needs to be saved on OneDrive or SharePoint, and auto-save must be activated. For collaborative editing, auto-save must be enabled. In a way, this is similar to the concept of how Copilot works in conjunction with Pilot for collaborative editing.

Inserting Tables

Copilot operates based on tables. Initially, it only worked within a table, but now it can reference and operate using other tables like a database.

Insert - Table

OK

Prompt 1. Calculate A based on B

Let's calculate the year based on the date.

Calculate Year Based on Date.

Insert column

Confirm result

Prompt 2. Calculate A based on B. If A is null, null

It's similar to prompt 1, but with an additional condition. If A is null, it should be displayed as null.

If this condition is not met, and the date is null, it will be displayed in the following format:

This time, we'll calculate the month based on the date.

Calculate Month based on Date. If Date is null, null

Insert column

The month is calculated as shown below. You can see that if the date is null, it is displayed as null.

If you click on "Give me another column suggestion" instead of "Insert column," it will provide an alternative approach using a different formula.

It suggests a different formula as shown below.

Using the same approach, let’s calculate the week number.

Calculate Week of Month based on Date. If Date is null, null.

Insert column

Confirm result

Prompt 3. Calculate A, Matching B based on C

This time, let's retrieve the department corresponding to the name based on Table2, as shown below.

(This can be done even if it’s not on the same sheet.)

After selecting the first table, enter the following prompt:

Calculate Department, Matching Name based on Table2

Insert column

You can see that the department corresponding to the name is retrieved using the XLOOKUP function.

Prompt 4. Calculate Method Z, X, Q, Y  Matching Name, Month based on Table1

Table1 is a performance table by sales representatives.

Let’s use a prompt in Table3 to retrieve the relevant performance data.

By entering the prompt below, you can calculate the monthly performance of sales representatives by method.

Calculate Method W, X, Y, Z.  Matching Name, Month based on Table1

It suggests four formulas at once, as shown below.

As of July 27, 2024, you can add formula-based aggregation by up to four different attributes.

You can calculate the total monthly sales performance using the following prompt.

Calculate "Total Sales"  Matching Name, Month based on Table1

Insert column

Table1의 정보를 기준으로 Month의 값을 참고하여 각각의 실적을 계산합니다.

Based on the information in Table1, calculate the performance for each month by referencing the values in the Month column.

Calculate Method W, X, Y, Z.  Matching Name, Quarter based on Table1

Insert column

A complex formula is completed to calculate the quarter.

Using the same approach, let’s calculate the total sales by quarter.

Calculate "Quarter sales".  Matching Name, Quarter based on Table1

Insert column

Confirm the result

Using the same approach, let’s calculate the semi-annual performance.

Calculate Method W, X, Y, Z.  Matching Name, Half year based on Table1

Insert column

Confirm the result

Calculate "Half year sales".  Matching Name, Half year based on Table1

Insert column

Calculate Method W, X, Y, Z.  Matching Name, Full year based on Table1

Insert column

Calculate "Full year sales".  Matching Name, Full year based on Table1.

Insert column

Confirm the result

With a simple prompt, you can see that complex formulas are completed.

It seems that the barrier to using Excel functions has been lowered compared to before.

I will continue to post about effective ways to utilize these features in the future.

Last post

2024.07.06 - [Microsoft 365/Entra ID] - Microsoft Entra ID. Set up tenant restrictions v2 by GPO (English)

Continuing from the previous post, this time we will proceed with setting tenant restrictions using GSA.

Youtube (English)

https://youtu.be/PIfHu4yPjN4

Step 1 is the same process as in the previous post.

The client PC has already been joined to Entra ID in advance.

Step 1: Configure default tenant restrictions v2

Entra Admin Center > Cross-tenant access settings > cross-tenant access settings > Default settings

Edit tenant restrictions defaults

Create Policy

The Policy ID is generated as shown below. Make sure to copy each value and keep them.

To set up a blocking policy for external accounts, configure it as shown below (default settings).

To block all external apps, configure the settings as shown below.

Step 2: Configure GSA

Click on Global Secure Access -> Activate to enable it.

Connect -> Traffic forwarding -> Activate each profile.

Proceed with assigning users and groups.

Assign to all users -> Yes

Secure -> Security profiles -> Create profile

Enter the profile name.

Link policy -> Existing policy

Link the default policy -> Proceed with the profile creation process.

Baseline profile

Change to Enabled status.

Step 3: Install GSA Client

Connect -> Client download

Download client (When deploying to actual users, Intune can be utilized.)

Proceed with the installation process of the GSA Client.

Sign in

Verify the connection status as shown below.

When logging in to a different tenant in Chrome, you can confirm that it is blocked as shown below.

The downside of the preview version is that the client has a Pause button.

Once officially released, it is expected to be built into the Windows service, similar to MDE.

I will discuss Tenant restriction settings.

The primary purpose of Conditional Access is to prevent company accounts from being accessed on personal devices. However, Conditional Access cannot prevent other company accounts from being accessed on company devices.

Of course, if a company device can access Naver Mail and Google Drive, it means the company is not very concerned about data leakage, and you may disregard this post.

To use M365, you need to open MS-related URLs such as office.com. Tenant Restriction is a concept used to prevent access with other company or personal accounts (such as outlook.com) during this time.

Youtube (English)

https://youtu.be/z-sVlZoz3y8

Technical article

Configure tenant restrictions - Microsoft Entra ID - Microsoft Entra External ID | Microsoft Learn

There are three main methods:

1. GSA
2. Company Proxy Equipment
3. GPO

The method using GSA requires a prior understanding of GSA.

I will cover that part separately later.

In this post, I will apply tenant restrictions using the third option, GPO.

Step 1: Configure default tenant restrictions v2

Entra Admin Center > Cross-tenant access settings > cross-tenant access settings > Default settings

Edit tenant restrictions defaults

Create Policy

The Policy ID is generated as shown below. Make sure to copy each value and keep them.

To set up a blocking policy for external accounts, configure it as shown below (default settings).

To block all external apps, configure the settings as shown below.

Step 2: Enable tenant restrictions on Windows managed devices (preview)

In the technical documentation, there are guidelines as shown below.

Tenant restrictions V2 on Windows is a partial solution that protects the authentication and data planes for some scenarios. It works on managed Windows devices and does not protect .NET stack, Chrome, or Firefox. The Windows solution provides a temporary solution until general availability of Universal tenant restrictions in Microsoft Entra Global Secure Access (preview).

-> Although the content is difficult to understand, it can be interpreted as indicating that the feature will be provided in a different way in the future. Currently, it is in the preview stage.

Download the ADMX files for the latest Windows GPO policies.

Download Administrative Templates (.admx) for Windows 11 2023 Update (23H2) from Official Microsoft Download Center

Once installed, the policy files will be saved to the following location.

Depending on the method of policy deployment in AD, copy the PolicyDefinitions folder to the appropriate location with only the necessary languages. (This part of the policy is related to AD, so we will not cover it here.)

Run gpmc.msc on the Domain Controller (DC).

Create a policy in the Organizational Unit (OU) that you will use for testing. Right-click and select "Edit".

Computer Configuration > Administrative Templates > Windows Components > Tenant Restrictions

Configure the settings as shown below.

Attempt to log in with a personal account on Edge.

Verify that access is blocked as shown below.

You can also see that access is blocked when attempting to log in with another tenant account.

There has always been a need to synchronize address books (GAL) between companies in scenarios such as M&A, affiliated companies, or group companies, where using a single tenant is not possible. Traditionally, this was achieved by setting up servers like Microsoft Identity Manager (MIM) on an On-Premise Exchange Server, creating objects between ADs to synchronize address books. Alternatively, it could be implemented through HR integration solutions.

However, adopting MIM or HR integration solutions can be prohibitively expensive and requires specialized knowledge for management, making it very burdensome.

Recently, it has become possible to synchronize address books with Cross-tenant Synchronization. Specifically, this functionality automates the invitation of Guests.

https://youtu.be/-HtT_uuDul0

The following content was written based on the technical documentation below.

Configure cross-tenant synchronization - Microsoft Entra ID | Microsoft Learn

Settings are configured separately for the Source Tenant and the Target Tenant.

Step 1: Plan your provisioning deployment

First, collect the information for the Source Tenant and the Target Tenant.

Source Tenant

Domain: Contoso.kr

Tenant ID: a0c898ca-2445-4e74-ab4b-afd7916549a6

Target Tenant

Domain: fabrikam.kr

Tenant ID: afab079d-1f08-4de3-881e-435e497f923f

Step 2: Enable user synchronization in the target tenant

Entra Admin Center > External Identities > Organizational settings > Add organization

Enter the Source Tenant ID information. > Add

Inbound access > Inherited from default

Allow users sync into this tenant > Check

Step 3: Automatically redeem invitations in the target tenant

Trust settings > Automatically redeem invitations with the tenant [Tenant Name] > Check > Save

Step 4: Automatically redeem invitations in the source tenant

Entra Admin Center > External Identities > Cross-tenant access settings

Add organization

Enter Target Tenant ID > Add

Outbound access > Inherited from default

Trust settings > Automatically redeem invitations with the tenant Fabrikam > Check > Save

Step 5: Create a configuration in the source tenant

Cross-tenant synchronization

Configurations > New configuration

Specify the configuration name. > Create

Step 6: Test the connection to the target tenant

Get started

Provisioning Mode: Automatic > Admin Credentials > Tenant Id: Target Tenant ID > Test Connection > Save

Step 7: Define who is in scope for provisioning (Source Tenant)

Provisioning > Settings > Confirm Scope  > Sync only assinged users and groups:

This means specifying only certain users or groups to synchronize.

Users and groups  -> Add user/group

None Selected

Specify the target. > Select > Assign

Step 9: Review attribute mappings

If, for various reasons, you do not want to synchronize specific attributes, proceed as follows.

Provisioning > Mappings > Provision Microsoft Entra ID Users

You can remove some items except for the required fields.

Step 10: Start the provisioning job

Start provisioning

Target Tenant > Entra admin center > Users > All Users

You can verify that they are added as guests as shown below.

You can also verify this in the Exchange Admin Center as shown below.

You can also verify this in the address book as shown below.

Tenant-to-tenant synchronization settings are configured as follows: In the Source Tenant, set up the Outbound settings, and in the Target Tenant, set up the Inbound settings. This synchronization process results in Guest accounts. Since Guest accounts have Mail User attributes, they can be verified in the address book.

I am starting my blog in English for the first time.

The purpose is to make it easier to use commands or scripts provided in the videos on YouTube.

The topic for this week is Cross-tenant Mailbox Migration.

I have carried out the process in the simplest Only Cloud environment, and I will cover Azure AD Sync and Exchange Hybrid scenarios later. To understand the principles of Migration, you need to understand the principles of Migration in Exchange Server. I will update this part later.

https://youtu.be/dNLLk-WNu24

I have referred to the following technical documentation to write this.

Cross-tenant mailbox migration - Microsoft 365 Enterprise | Microsoft Learn

Migration Scenario Diagram

[Test Environment]

Source Tenant

Tenant: M365x47686041.onmicrosoft.com

Custom domain: wingtiptoys.kr

Target tenant

Tenant: M365x79002307.onmicrosoft.com

Custom domain: tailspintoys.kr

Since it is a tenant environment, there is no process for assigning cross-tenant migration licenses.

Without the appropriate license, migration is not possible, so we conducted the test using a shared mailbox.

Step 1. Prepare the target (destination) tenant by creating the migration application and secret

Access https://entra.microsoft.com (Target Tenant) -> search for "app registrations" -> click

New Registration

Enter the information as shown below and then click Register

Record it as the AppID of the Target Tenant.

API Permissions -> Add a permission

APIs my organization uses -> Office 365 Exchange Online -> Office 365 Exchange Online

Application permissions -> Mailbox.Migration -> Add permission

Confirm

Certificates & secrets -> New client secret

Description -> Add

Copy & Record the Value

Enterprise Application -> Click the migration app

Permissions -> Grant admin for Tenant name

Accept

Confirm

After opening a new browser window, access the following URL: (Source Tenant + App ID)

https://login.microsoftonline.com/contoso.onmicrosoft.com/adminconsent?client_id=[application_id_of_the_app_you_just_created]&redirect_uri=https://office.com

#Example
https://login.microsoftonline.com/M365x85148890.onmicrosoft.com/adminconsent?client_id=d8afba35-2ae3-4b42-89f2-8511bfb42bd2&redirect_uri=https://office.com

Accept

Step 2. Prepare the target tenant by creating the Exchange Online migration endpoint and organization relationship

Connect Exchange Online Powershell (Target Tenant)

#Enable customization if tenant is dehydrated
Get-OrganizationConfig | select isdehydrated
Enable-OrganizationCustomization

$AppId = "[guid copied from the migrations app]"

$AppId = "d8afba35-2ae3-4b42-89f2-8511bfb42bd2"

#Create Migration Endpoint

$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $AppId, (ConvertTo-SecureString -String "[this is your secret password you saved in the previous steps]" -AsPlainText -Force)
New-MigrationEndpoint -RemoteServer outlook.office.com -RemoteTenant "sourcetenant" -Credentials $Credential -ExchangeRemoteMove:$true -Name "[the name of your migration endpoint]" -ApplicationId $AppId

#Sample
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $AppId, (ConvertTo-SecureString -String "1x38Q~7-d-hdD92Ue9Or5A2ilTkO-n7C1p2raaWX" -AsPlainText -Force)
New-MigrationEndpoint -RemoteServer outlook.office.com -RemoteTenant "M365x85148890.onmicrosoft.com" -Credentials $Credential -ExchangeRemoteMove:$true -Name "wingtiptoys" -ApplicationId $AppId

Looking at the command structure, you can think of the created Migration Application as being connected as follows.

The endpoint is connected by designating the Remote tenant as the Source tenant.

#Create Organization Relationship

$sourceTenantId="[tenant id of your trusted partner, where the source mailboxes are]"
$orgrels=Get-OrganizationRelationship
$existingOrgRel = $orgrels | ?{$_.DomainNames -like $sourceTenantId}
If ($null -ne $existingOrgRel)
{
    Set-OrganizationRelationship $existingOrgRel.Name -Enabled:$true -MailboxMoveEnabled:$true -MailboxMoveCapability Inbound
}
If ($null -eq $existingOrgRel)
{
    New-OrganizationRelationship "[name of the new organization relationship]" -Enabled:$true -MailboxMoveEnabled:$true -MailboxMoveCapability Inbound -DomainNames $sourceTenantId
}
---------------------------------------------------------------
$sourceTenantId="a18c909b-006a-404f-8666-c2ccae261bcd"
$orgrels=Get-OrganizationRelationship
$existingOrgRel = $orgrels | ?{$_.DomainNames -like $sourceTenantId}
If ($null -ne $existingOrgRel)
{
    Set-OrganizationRelationship $existingOrgRel.Name -Enabled:$true -MailboxMoveEnabled:$true -MailboxMoveCapability Inbound
}
If ($null -eq $existingOrgRel)
{
    New-OrganizationRelationship "wingtiptoys" -Enabled:$true -MailboxMoveEnabled:$true -MailboxMoveCapability Inbound -DomainNames $sourceTenantId
}

MailboxMoveCapability is understood as specifying the direction of Cross-Tenant Mailbox Migration.

Copy the Tenant ID from the Source Tenant

$sourceTenantId="a18c909b-006a-404f-8666-c2ccae261bcd"

It appears that the migration direction has been enabled as shown below.

Step3. Prepare the source (current mailbox location) tenant by accepting the migration application and configuring the organization relationship

It can be understood as granting permissions related to app usage in the Source Tenant as shown below.

Source Tenant -> Exchange Admin Center -> Create Mail-enabled security

Enter the name

Add the mailboxes to be migrated to the specified group.

Assign address -> Complete creation

Connect Exchange Online Powershell (Source Tenant)

Create Organization Relationship for the Source Tenant

$targetTenantId="[tenant id of your trusted partner, where the mailboxes are being moved to]"
$appId="[application id of the mailbox migration app you consented to]"
$scope="[name of the mail enabled security group that contains the list of users who are allowed to migrate]"
   $orgrels=Get-OrganizationRelationship
$existingOrgRel = $orgrels | ?{$_.DomainNames -like $targetTenantId}
If ($null -ne $existingOrgRel)
{
    Set-OrganizationRelationship $existingOrgRel.Name -Enabled:$true -MailboxMoveEnabled:$true -MailboxMoveCapability RemoteOutbound -OAuthApplicationId $appId -MailboxMovePublishedScopes $scope
}
If ($null -eq $existingOrgRel)
{
    New-OrganizationRelationship "[name of your organization relationship]" -Enabled:$true -MailboxMoveEnabled:$true -MailboxMoveCapability RemoteOutbound -DomainNames $targetTenantId -OAuthApplicationId $appId -MailboxMovePublishedScopes $scope
}

Example

$targetTenantId="27359b9a-645f-424d-b4f2-526903be2546"
$appId="d8afba35-2ae3-4b42-89f2-8511bfb42bd2"
$scope="Migrationgroup"
   $orgrels=Get-OrganizationRelationship
$existingOrgRel = $orgrels | ?{$_.DomainNames -like $targetTenantId}
If ($null -ne $existingOrgRel)
{
    Set-OrganizationRelationship $existingOrgRel.Name -Enabled:$true -MailboxMoveEnabled:$true -MailboxMoveCapability RemoteOutbound -OAuthApplicationId $appId -MailboxMovePublishedScopes $scope
}
If ($null -eq $existingOrgRel)
{
    New-OrganizationRelationship "ToTargetTenant" -Enabled:$true -MailboxMoveEnabled:$true -MailboxMoveCapability RemoteOutbound -DomainNames $targetTenantId -OAuthApplicationId $appId -MailboxMovePublishedScopes $scope
}

The RemoteOutbound and Inbound relationship settings have been completed through the Organization Relationship settings of each tenant.

Step 4.  Create MailUser

Check the properties of the migration mailbox in the Source Tenant

Get-Mailbox -Identity user01 |Select-Object PrimarySMTPAddress,Alias,SamAccountName,FirstName,LastName,DisplayName,Name,ExchangeGuid,ArchiveGuid,LegacyExchangeDn,EmailAddresses

Create a Mail User in the Target Tenant

New-MailUser -MicrosoftOnlineServicesID User01@tailspintoys.kr -PrimarySmtpAddress User01@tailspintoys.kr -ExternalEmailAddress user01@wingtiptoys.kr -Name User01 -DisplayName User01 -Alias User01 

Set-MailUser -Identity User01 -EmailAddresses @{add="X500:Type the LegacyExchangeDN"} -ExchangeGuid "Type the ExchangeGuid"

#In scenarios where the existing domain needs to be completely removed, enter the onmicrosoft.com address and designate it as the target delivery domain.
Set-MailUser -Identity User01 -EmailAddresses @{add="smtp:user01@M365x47686041.onmicrosoft.com"}

The attributes were created to map as follows.

Check the migration connection status with the following command.

Test-MigrationServerAvailability -Endpoint "wingtiptoys" -TestMailbox "user01@tailspintoys.kr"

Step 5. Migration

Migration -> Add Migration batch

Migration to Exchange Online -> Next

Cross tenant migration -> Next

Next

Select migration endpoint ->Next

Import CSV file

Create a CSV with the Target Email Address and proceed with the import.

Enter target delivery domain

Save

Synchronization proceeds as shown below.

After checking the license assignment status, click Complete migration batch. ->

If the migration is complete, remove the batch.

You can confirm the migrated mailboxes as shown below.

And the existing Source Mailbox is changed to a Mail User.

Since the External Address is the Target Tenant address, any emails received after the transition will be forwarded to the Target Tenant.

The overall migration flow is not significantly different from Exchange hybrid or Cross-Forest.