Hello there,  so the situation we have is we want to lock down accessing any of our o365 apps from only approved devices.  I assume the only/best way to do this is via Intune.  What we want to do is have it setup where users can enroll devices or do auto enrollment as long as we can approve devices coming in.  We had a breach where someone was able to login to a users account that had MFA enabled still so the thought here is we would only allow access on devices we have approved.  Microsoft KB articles on enrollment seem a little dry on examples and instructions.  Does anyone have any good info on how to set something like that up if possible?   

​Hello there,  so the situation we have is we want to lock down accessing any of our o365 apps from only approved devices.  I assume the only/best way to do this is via Intune.  What we want to do is have it setup where users can enroll devices or do auto enrollment as long as we can approve devices coming in.  We had a breach where someone was able to login to a users account that had MFA enabled still so the thought here is we would only allow access on devices we have approved.  Microsoft KB articles on enrollment seem a little dry on examples and instructions.  Does anyone have any good info on how to set something like that up if possible?     Read More

​

I am currently facing an issue with rendering a contentURL in the modal view within the multistage view of my bot. I have implemented the following Adaptive Card, but the contentURL is not being rendered as expected. Instead, I’m getting the error message: “There was a problem reaching this app.” The same URL works correctly in a browser window.

Here is the sample Adaptive Card:

{
“type”: “Image”,
“url”: “ImageURL”,
“altText”: “Insights”,
“selectAction”: {
“type”: “Action.Submit”,
“title”: “Open”,
“data”: {
“msteams”: {
“type”: “invoke”,
“value”: {
“type”: “tab/tabInfoAction”,
“tabInfo”: {
“contentUrl”: “https://apiserver.ngrok.app/test”,
“websiteUrl”: “https://apiserver.ngrok.app/test”,
“name”: “utterance”,
“entityId”: “entityId”,
“openMode”: “modal”
}
}
}
}
}
}

​I am currently facing an issue with rendering a contentURL in the modal view within the multistage view of my bot. I have implemented the following Adaptive Card, but the contentURL is not being rendered as expected. Instead, I’m getting the error message: “There was a problem reaching this app.” The same URL works correctly in a browser window. Here is the sample Adaptive Card:    {
“type”: “Image”,
“url”: “ImageURL”,
“altText”: “Insights”,
“selectAction”: {
“type”: “Action.Submit”,
“title”: “Open”,
“data”: {
“msteams”: {
“type”: “invoke”,
“value”: {
“type”: “tab/tabInfoAction”,
“tabInfo”: {
“contentUrl”: “https://apiserver.ngrok.app/test”,
“websiteUrl”: “https://apiserver.ngrok.app/test”,
“name”: “utterance”,
“entityId”: “entityId”,
“openMode”: “modal”
}
}
}
}
}
}           Read More

​

Hi everyone, I am trying to execute a macro for formatting some data I have as well as creating a workbook. If I am being honest, I do not have much experience with macros and used chatgpt as a reference. Chatgpt gave me the below code, which did work at first, but now I am having the error I mentioned above. The purpose of this macro is to do some formatting first, then rename that sheet to whatever today’s date is “mmddyy.” Then it should add a new sheet and name it “Data Pivot” which should then be moved to the beginning so it is first. At cell A1 there should be a pivot table which references the data in my previous sheet. My data is located at row 3 from cells A to I. I need my pivot table to select all filled cells from A3 down and up until column I. The issue I am having is that my macro is giving me an error once it is about to insert the pivot table at cell A1.

When I click debug this is where the error is:

Set pivotTbl = pivotWs.PivotTables.Add(PivotCache:=ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=dataRange), TableDestination:=pivotWs.Cells(1, 1))

Please help!

This is my code:

Sub CreateReport()
Dim ws As Worksheet
Dim pivotWs As Worksheet
Dim pivotTbl As PivotTable
Dim dataRange As Range
Dim todaysDate As String

‘ Step 1: Delete column J
Columns(“J:J”).Delete

‘ Step 2: Add 2 new rows to the top
Rows(“1:2”).Insert Shift:=xlDown

‘ Step 3: Type “PB Charge Review by WQ” in cell A1
Range(“A1”).Value = “PB Charge Review by WQ”

‘ Step 4: Merge cells A1 and B1
Range(“A1:B1”).Merge

‘ Step 5: Bold cell A1 and make the font 12 pt
With Range(“A1”)
.Font.Bold = True
.Font.Size = 12
End With

‘ Step 6: Align the text in cell A1 to the left
Range(“A1”).HorizontalAlignment = xlLeft

‘ Step 7: Underline cells A3:I3 (headers)
Range(“A3:I3”).Font.Underline = xlUnderlineStyleSingle

‘ Step 8: Autofit columns A-I
Columns(“A:I”).AutoFit

‘ Step 9: Rename the sheet to today’s date (mmddyy)
todaysDate = Format(Date, “mmddyy”)
ActiveSheet.Name = todaysDate

‘ Step 10: Add a new sheet and move it to the beginning
Set pivotWs = Sheets.Add(Before:=Sheets(1))

‘ Step 11: Rename this new sheet to “Data Pivot”
pivotWs.Name = “Data Pivot”

‘ Step 12: Set the data range for the pivot table
Set ws = Sheets(todaysDate)
Set dataRange = ws.Range(“A3:I” & ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row)

‘ Step 13: Create the pivot table in the new sheet
Set pivotTbl = pivotWs.PivotTables.Add(PivotCache:=ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=dataRange), TableDestination:=pivotWs.Cells(1, 1))

‘ Step 14-19: Add fields to the pivot table and format them
With pivotTbl
‘ Add “Owning Area” to rows
.PivotFields(“Owning Area”).Orientation = xlRowField

‘ Add “Num of Chg Sess” to values as a sum
With .PivotFields(“Num of Chg Sess”)
.Orientation = xlDataField
.Function = xlSum
End With

‘ Add “Amt on Chg Rvw” to values as a sum
With .PivotFields(“Amt on Chg Rvw”)
.Orientation = xlDataField
.Function = xlSum
End With

‘ Add “Avg Svc Dt Age” to values as an average
With .PivotFields(“Avg Svc Dt Age”)
.Orientation = xlDataField
.Function = xlAverage
End With

‘ Add “Avg Age” to values as an average
With .PivotFields(“Avg Age”)
.Orientation = xlDataField
.Function = xlAverage
End With
End With

‘ Step 20: Format columns B, D, and E with custom number format to show dashes instead of zeros
pivotWs.Columns(2).NumberFormat = “#,##0;-#,##0;–”
pivotWs.Columns(4).NumberFormat = “#,##0;-#,##0;–”
pivotWs.Columns(5).NumberFormat = “#,##0;-#,##0;–”

‘ Step 21: Format column C as currency with no decimals
pivotWs.Columns(3).NumberFormat = “$#,##0”

End Sub

​Hi everyone, I am trying to execute a macro for formatting some data I have as well as creating a workbook. If I am being honest, I do not have much experience with macros and used chatgpt as a reference. Chatgpt gave me the below code, which did work at first, but now I am having the error I mentioned above. The purpose of this macro is to do some formatting first, then rename that sheet to whatever today’s date is “mmddyy.” Then it should add a new sheet and name it “Data Pivot” which should then be moved to the beginning so it is first. At cell A1 there should be a pivot table which references the data in my previous sheet. My data is located at row 3 from cells A to I. I need my pivot table to select all filled cells from A3 down and up until column I. The issue I am having is that my macro is giving me an error once it is about to insert the pivot table at cell A1. When I click debug this is where the error is: Set pivotTbl = pivotWs.PivotTables.Add(PivotCache:=ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=dataRange), TableDestination:=pivotWs.Cells(1, 1))  Please help! This is my code: Sub CreateReport()
Dim ws As Worksheet
Dim pivotWs As Worksheet
Dim pivotTbl As PivotTable
Dim dataRange As Range
Dim todaysDate As String

‘ Step 1: Delete column J
Columns(“J:J”).Delete

‘ Step 2: Add 2 new rows to the top
Rows(“1:2”).Insert Shift:=xlDown

‘ Step 3: Type “PB Charge Review by WQ” in cell A1
Range(“A1”).Value = “PB Charge Review by WQ”

‘ Step 4: Merge cells A1 and B1
Range(“A1:B1”).Merge

‘ Step 5: Bold cell A1 and make the font 12 pt
With Range(“A1”)
.Font.Bold = True
.Font.Size = 12
End With

‘ Step 6: Align the text in cell A1 to the left
Range(“A1”).HorizontalAlignment = xlLeft

‘ Step 7: Underline cells A3:I3 (headers)
Range(“A3:I3”).Font.Underline = xlUnderlineStyleSingle

‘ Step 8: Autofit columns A-I
Columns(“A:I”).AutoFit

‘ Step 9: Rename the sheet to today’s date (mmddyy)
todaysDate = Format(Date, “mmddyy”)
ActiveSheet.Name = todaysDate

‘ Step 10: Add a new sheet and move it to the beginning
Set pivotWs = Sheets.Add(Before:=Sheets(1))

‘ Step 11: Rename this new sheet to “Data Pivot”
pivotWs.Name = “Data Pivot”

‘ Step 12: Set the data range for the pivot table
Set ws = Sheets(todaysDate)
Set dataRange = ws.Range(“A3:I” & ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row)

‘ Step 13: Create the pivot table in the new sheet
Set pivotTbl = pivotWs.PivotTables.Add(PivotCache:=ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=dataRange), TableDestination:=pivotWs.Cells(1, 1))

‘ Step 14-19: Add fields to the pivot table and format them
With pivotTbl
‘ Add “Owning Area” to rows
.PivotFields(“Owning Area”).Orientation = xlRowField

‘ Add “Num of Chg Sess” to values as a sum
With .PivotFields(“Num of Chg Sess”)
.Orientation = xlDataField
.Function = xlSum
End With

‘ Add “Amt on Chg Rvw” to values as a sum
With .PivotFields(“Amt on Chg Rvw”)
.Orientation = xlDataField
.Function = xlSum
End With

‘ Add “Avg Svc Dt Age” to values as an average
With .PivotFields(“Avg Svc Dt Age”)
.Orientation = xlDataField
.Function = xlAverage
End With

‘ Add “Avg Age” to values as an average
With .PivotFields(“Avg Age”)
.Orientation = xlDataField
.Function = xlAverage
End With
End With

‘ Step 20: Format columns B, D, and E with custom number format to show dashes instead of zeros
pivotWs.Columns(2).NumberFormat = “#,##0;-#,##0;–”
pivotWs.Columns(4).NumberFormat = “#,##0;-#,##0;–”
pivotWs.Columns(5).NumberFormat = “#,##0;-#,##0;–”

‘ Step 21: Format column C as currency with no decimals
pivotWs.Columns(3).NumberFormat = “$#,##0”

End Sub
    Read More

​

We are excited to announce a new Microsoft Defender for Identity sensor for Entra Connect servers. This addition is a significant step in our ongoing commitment to expanding Defender for Identity’s coverage across hybrid identity environments. It reinforces our vision of overseeing and protecting the entire identity fabric, greatly enhancing the SOC’s visibility and protections for these complex environments.

Identities are one of, if not the most targeted attack vector and cyber-criminals are always evolving their strategies to exploit new vulnerabilities or gaps in protection. Many organizations today manage hybrid identity environments, with an on-premises Active Directory footprint along with a deployment of Entra ID in the cloud. The gaps between these two elements present ample opportunities for bad actors and as the primary bridge between them, Entra Connect servers can be classified as tier-0 level assets.  

To stay ahead of emerging threats and deliver powerful security solutions, our team is continuously evolving and updating our offerings, and the primary objective of this new sensor is to help our customers better prevent, detect, and remediate credential theft and privilege escalation attacks commonly initiated against Entra Connect.

What is Entra Connect? What security value does the new sensor provide?

Entra Connect (previously known as Azure AD Connect or AAD Connect) is a Microsoft service used to synchronize on-premises Active Directory environments with Entra ID (formerly Azure Active Directory). Entra Connect facilitates identity management and provides single sign-on capabilities for users across on-premises and cloud resources by creating a common identity. This synchronization is essential for maintaining consistent and secure access across different platforms.

The new Microsoft Defender for Identity sensor for Entra Connect servers provides comprehensive monitoring of synchronization activities between Entra Connect and Active Directory, offering crucial insights into potential security threats and unusual activities. With this enhanced visibility across hybrid identity environments Defender for Identity can now provide new Entra Connect specific security alerts and posture recommendations, as detailed below.

Entra Connect sensor on Identities Settings page

New Detections (in Public Preview):

Suspicious Interactive Logon to the Entra Connect Server:

Direct logins to Entra Connect servers are highly unusual and potentially malicious. Attackers often target these servers to steal credentials for broader network access. Microsoft Defender for Identity can now detect abnormal logins to Entra Connect servers, helping you identify and respond to these potential threats faster. It is specifically applicable when the Entra Connect server is a standalone server and not operating as a Domain Controller.

Pre-requisite: Ensure that the 4624 logon event is enabled on the Entra Connect server. This step is necessary only if the Entra Connect server is not functioning as a Domain Controller.

User Password Reset by Entra Connect Account:
The Entra Connect connector account often holds high privileges, including the ability to reset user’s passwords. Microsoft Defender for Identity now has visibility into those actions and will detect any usage of those permissions that were identified as malicious and non-legitimate. This alert will be triggered only if the password writeback feature is disabled.

Suspicious writeback by Entra Connect on a sensitive user:

While Entra Connect already prevents writeback for users in privileged groups, Microsoft Defender for Identity expands this protection by identifying additional types of sensitive accounts. This enhanced detection helps prevent unauthorized password resets on critical accounts, which can be a crucial step in advanced attacks targeting both cloud and on-premises environments.

Additional improvements and capabilities:

New activity of any failed password reset on a sensitive account available in the ‘IdentityDirectoryEvents’ table in Advanced Hunting. This can help customers track failed password reset events and create custom detection based on this data.
Enhanced accuracy for the DC sync attack detection.
New health alert for cases where the sensor is unable to retrieve the configuration from the Entra Connect service.
Extended monitoring for security alerts, such as PowerShell Remote Execution Detector, by enabling the new sensor on Entra Connect servers.

New posture recommendations in Microsoft Secure Score (Identity security assessment):

Rotate password for Entra Connect connector account:
A compromised Entra Connect connector account (AD DS connector account, commonly shown as MSOL_XXXXXXXX) can grant access to high-privilege functions like replication and password resets, allowing attackers to modify synchronization settings and compromise security in both cloud and on-premises environments as well as offering several paths for compromising the entire domain. In this assessment we recommend customers change the password of MSOL accounts with the password last set over 90 days ago.

Remove unnecessary replication permissions for Entra Connect Account:
By default, the Entra Connect connector account has extensive permissions to ensure proper synchronization (even if they are not actually required). If Password Hash Sync is not configured, it’s important to remove unnecessary permissions to reduce the potential attack surface.

Change password for Entra seamless SSO account configuration:
This report lists all Entra seamless SSO computer accounts with password last set over 90 days ago. The password for the Azure SSO computer account is not automatically changed every 30 days. If an attacker compromises this account, they can generate service tickets for the AZUREADSSOACC account on behalf of any user and impersonate any user in the Entra tenant that is synchronized from Active Directory. An attacker can use this to move laterally from Active Directory into Entra ID.

Remove Resource Based Constrained Delegation for Entra seamless SSO account:
If resource-based constrained delegation is configured on the AZUREADSSOACC computer account, an account with the delegation would be able to generate service tickets for the AZUREADSSOACC account on behalf of any user and impersonate any user in the Entra tenant that is synchronized from AD.

All new recommendations require a sensor installed on servers running Entra Connect services.
The recommendations related to the Entra seamless SSO account will be available only if Defender for Identity can detect this type of computer account, while the recommendations related to the connector account will be available if our sensor recognized a retrieve configuration data from the Entra Connect services.

New Entra Connect recommendations in Microsoft Secure Score

As cyber threats become more sophisticated, the need for advanced security solutions is more pressing than ever. The new sensor for Entra Connect Server within Microsoft Defender for Identity represents a significant advancement in protecting and detecting threats within the identity fabric. By adopting this powerful tool, organizations can enhance their security posture, safeguard their identity infrastructure, and maintain the trust of their users.

We highly recommend customers install a sensor on any Domain controller, AD CS, AD FS, or Entra Connect servers. In the upcoming weeks, our team will delve deeper into potential enhancements for the Entra Connect support to better help organizations stay secure and protected.

Learn more about the new sensor in our documentation here and stay tuned for more updates and insights on how MDI continues to innovate in the realm of cybersecurity, ensuring that your organization remains secure in an ever-changing digital world.

​Microsoft Tech Community – Latest Blogs –Read More 

Join the Microsoft Planner product team on Tuesday, September 17 at 9:00 AM Pacific to learn about recent updates to the Planner app, and get a sneak peek at what’s coming next, followed by a live Ask Microsoft Anything. Our product experts will showcase key new features and improvements of the new experience, including demonstrations of how to leverage Copilot in Planner, new Baselines to stay on track, web app updates, integration with Whiteboard, and more. The event includes a live Q&A session where you can ask the team any questions about these updates.

The AMA event on Tuesday is your chance to see the latest features of Planner and hear directly from the product team. This 60-minute session is an opportunity to ask open questions and provide feedback about the new Microsoft Planner. Register today!

Please note, if you cannot attend the live AMA, you can ask questions at any time via the event page—see Comments section. Our team will address all questions during the event, so check back for answers.

​Microsoft Tech Community – Latest Blogs –Read More 

In this blog post I am going to talk about splitting logs to multiple tables and opting for basic tier to save cost in Microsoft Sentinel. Before we delve into the details, let’s try to understand what problem we are going to solve with this approach.

Azure Monitor offers several log plans which our customers can opt for depending on their use cases. These log plans include:

Analytics Logs – This plan is designed for frequent, concurrent access and supports interactive usage by multiple users. This plan drives the features in Azure Monitor Insights and powers Microsoft Sentinel. It is designed to manage critical and frequently accessed logs optimized for dashboards, alerts, and business advanced queries.
Basic Logs – Improved to support even richer troubleshooting and incident response with fast queries while saving costs. Now available with a longer retention period and the addition of KQL operators to aggregate and lookup.
Auxiliary Logs – Our new, inexpensive log plan that enables ingestion and management of verbose logs needed for auditing and compliance scenarios. These may be queried with KQL on an infrequent basis and used to generate summaries.

Following diagram provides detailed information about the log plans and their use cases:

I would also recommend going through our public documentation for detailed insights about feature-wise comparison for the log plans which should help you in taking right decisions for choosing the correct log plans.

**Note** Auxiliary logs are out of scope for this blog post, I will write a separate blog on the Auxiliary logs later.

So far, we know about different log plans available and their use cases.

 The next question is which tables support Analytics and Basic log plan?

Analytics Logs: All tables support the Analytics plan.
Basic Logs: All DCR-based custom tables and some Azure tables support the Basic log plan.

You can switch between the Analytics and Basic plans; the change takes effect on existing data in the table immediately.

When you change a table’s plan from Analytics to Basic, Azure monitor treats any data that’s older than 30 days as long-term retention data based on the total retention period set for the table. In other words, the total retention period of the table remains unchanged, unless you explicitly modify the long-term retention period.

Check our public documentation for more information on setting the table plan.

I will focus on splitting Syslog table and setting up the DCR-based table to Basic tier in this blog.

Typically Firewall logs contribute to high volume of log ingestion to a SIEM solution.

In order to manage cost in Microsoft Sentinel its highly recommended to thoroughly review the logs and identify which logs can be moved to Basic log plan.

At a high level, the following steps should be enough to achieve this task:

Ingest Firewall logs to Microsoft Sentinel with the help of Linux Log Forwarder via Azure Monitor Agent.
Assuming the log is getting ingested in Syslog table, create a custom table with same schema as Syslog table.
Update the DCR template to split the logs.
Set the table plan to Basic for the identified DCR-based custom table.
Set the required retention period of the table.

At this point, I anticipate you already have log forwarder set up and able to ingest Firewall logs to Microsoft Sentinel’s workspace.

Let’s focus on creating a custom table now

This part used to be cumbersome but not anymore, thanks to my colleague Marko Lauren who has done a fantastic job in creating this PowerShell Script which can create a custom table easily. All you need to do is to enter the pre-existing table name and the script will create a new DCR-Based custom table with same schema.

Let’s see it in action:

Download the script locally.
Open the script in PowerShell ISE and update workspace ID & resource ID details as shown below.

Save it locally and upload to Azure PowerShell.
Load the file and enter the table name from which you wish to copy the schema.
Provide the new table name as per your wish, ensure the name has suffix “_CL” as shown below:

This should create a new DCR-based custom table which you can check in Log Analytics Workspace > Table blade as shown below:

**Note** We highly recommend you should review the PowerShell script thoroughly and do proper testing before executing it in production. We don’t take any responsibility for the script.

The next step is to update the Data Collection Rule template to split the logs

Since we already created custom table, we should create a transformation logic to split the logs and send less relevant log to the custom table which we are going to set to Basic log tier.

For demo purposes, I’m going to split logs based on SeverityLevel. I will drop “info” logs from Syslog table and stream it to Syslog_CL table.

Let’s see how it works:

Browse to Data Collection Rule blade.
Open the DCR for Syslog table, click on Export template > Deploy > Edit Template as shown below:

In the dataFlows section, I’ve created 2 streams for splitting the logs. Details about the streams as follows:

1st Stream: It’s going to drop the Syslog messages where SeverityLevel is “info” and send the logs to Syslog table.
2nd Stream: It’s going to capture all Syslog messages where SeverityLevel is “info” and send the logs to Syslog_CL table.

Save and deploy.

Let’s validate if it really works

Go to the Log Analytics Workspace > Logs and check if the tables contains the data which we have defined it for.

In my case as we can see, Syslog table contains all logs except those where SeverityLevel is “info”

Additionally, our custom table: Syslog_CL contains those Syslog data where SeverityLevel is “info”

Now the next part is to set the Syslog_CL table to Basic log plan

Since Syslog_CL is a DCR-based custom table, we can set it to Basic log plan. Steps are straightforward:

Go to the Log Analytics Workspace > Tables
Search for the table: Syslog_CL
Click on the ellipsis on the right side and click on Manage table as shown below:

Select the table plan to Basic and set desired retention period

Save the settings.

Now you can enjoy some cost benefits, hope this helps.

​Microsoft Tech Community – Latest Blogs –Read More 

Overview 

In this blog post we will see demonstration and detailed walk through of how Generative AI capabilities of GitHub Copilot can work together with SQL Server Migration Assistant (SSMA) for Oracle and accelerate code conversion from PL/SQL to T-SQL and simplify Oracle migration journey to Azure SQL. Before we delve into how GitHub Copilot can accelerate your code conversion journey, lets get a brief overview of GitHub Copilot, SSMA for Oracle, Database migrations and the criticality of code conversion in the migration process.  

What is GitHub Copilot? 

GitHub Copilot is an AI coding assistant that helps developers write code faster with less effort, allowing developers to focus on problem solving and collaboration. It improves developer productivity by doing completions, answering coding questions, fix issues, generate unit test cases, jumpstarting your project and much more. GitHub Copilot also has Language Translation ability that can translate code from one Programming language to another, for example: Python to JavaScript or HTML to Markdown, PL/SQL to T-SQL etc. In this demo we will see how GitHub Copilot Language translation capability can simplify your Oracle to SQL Server database migration by automatically converting PL/SQL into T-SQL. 

GitHub Copilot is available as an extension in IDEs, GitHub Mobile as a chat interface, on command line as GitHub CLI and more. In this demo we are going to use Visual Studio Code extension of GitHub Copilot.  

What is SQL Server Migration Assistant (SSMA) for Oracle 

Microsoft SQL Server Migration Assistant (SSMA) for Oracle is a desktop tool to automate migration from Oracle database(s) to SQL Server, Azure SQL Database, Azure SQL Database Managed Instance and Azure SQL Data Warehouse. SSMA for Oracle converts Oracle database objects and loads those objects into SQL Server or Azure SQL, and then migrates data. For more information on how to use SSMA for Oracle, please refer to  SQL Server Migration Assistant for Oracle.

Database Migrations overview 

Database migration is a process of moving data from one or more source platforms to the desired target platforms. Data migration can happen between databases of the same database management system (DBMS) from the same provider or between databases from different database management system (DBMS) providers. For example, Migration of SQL Servers from on-premises infrastructure or non-Azure cloud platforms to Azure SQL (which includes following three products: Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure VM) is called Homogenous Migrations and Migration of non-SQL server databases like Oracle, DB2, Sybase etc. to Microsoft SQL Server or Azure SQL is called Heterogenous migrations. Both homogenous and heterogenous are a multi-phase journey with the following phases: 

Discovery: Users must first Discover their entire source database estate either in on-prem or in other clouds and determine which of these databases need to be migrated. 

Total Cost of Ownership (TCO) comparison analysis between source and target platforms to quantify the potential cost savings by migrating the databases.  

Assess the source databases to understand the workload patterns and determine the right configuration of the target database and provision the target. 

Convert the code and other source database objects to make them target compatible.  

Migrate the data from source to target databases either.  

Conversion using SQL Server Migration Assistant for Oracle 

SQL Server Migration Assistant for Oracle provides extensive conversion rule set engine that converts most of your Oracle objects into PL/SQL code into SQL Server compatible objects and T-SQL with 100% accuracy. Additionally, SSMA provides multiple reusable customization options for mapping datatypes and extending inbuilt rule engine that help you accelerate the overall code conversion process. High-level steps for conversion in SSMA are: 

Mapping Oracle and SQL Server data types: SSMA for Oracle offers a default set of type mappings, which meets common conversion requirements in most of the cases. This data type mapping is inherited by default at project level for all the underlying object categories and object types. Users can customize them as needed at object category level and create exceptions 

Assessing Oracle Schemas for conversion: Before loading objects and migrate data to SQL Server, you should determine how complex the migration will be and how much time the migration will take. SSMA for Oracle creates an assessment report that shows the percentage of objects that will be successfully converted, and it also lets you view the specific issues that cause conversion failures. Additionally, SSMA also tells you the amount of manual effort required in hours to convert the objects that could not be automatically converted. 

Converting Oracle Schemas into SQL Server Schemas: Converting database objects takes the object definitions from Oracle, converts them to similar SQL Server objects, and then loads this information into the SSMA metadata. It does not load the information into the instance of SQL Server. You can then view the objects and their properties by using the SQL Server Metadata Explorer. During the conversion, SSMA prints output messages to the Output pane and error messages to the Error List pane. Use the output and error information to determine whether you have to modify your Oracle databases or your conversion process to obtain the desired conversion results. 

Loading converted database objects into SQL Server: To load the converted database objects into SQL Server without modification, you can have SSMA directly create or recreate the database objects. To modify the Transact-SQL that is used to create objects for more control over object creation, use SSMA to create scripts. You can then modify those scripts to create each object individually, and even use SQL Server Agent to schedule creating those objects. To secure the converted database objects in SQL Server, you can grant and deny permissions on those objects. It is recommended to set the security permissions before performing data migration. 

More details about the Oracle to SQL Server migration and the conversion process can be found in Migrate Oracle to SQL Server (OracleToSQL) 

GitHub Copilot, a great companion to SSMA in code conversion 

SSMA for Oracle provides comprehensive conversion rule engine that converts majority of the datatypes and objects into SQL Server compatible type with 100 % accuracy. Objects that could not be converted automatically by SSMA, need to be converted manually and this can take multiple hours of manual effort. Users can leverage the full power of Generative AI capabilities available in GitHub Copilot to automate the conversion of Oracle database objects that could not be converted by SSMA for Oracle. GitHub Copilot is available as a Visual Studio extension, which facilitates conversion of large and complex Oracle procedures and functions to T-SQL procedures and functions with few clicks. Here is a step-by-step guide on how to leverage GitHub Copilot VS Code extension to automate the conversion: 

Create and view the conversion assessment report generated by SSMA to know the list of all objects that could not be automatically converted by SSMA into SQL Server compatible objects. Here is a screenshot that shows how the assessment report would look like capturing details on number of objects and actual objects that could not be converted (Pie chart on the left) and the amount of manual effort required to convert these objects (Pie chart on the right): 

Select an object (ex: a PL/SQL procedure or function) that could not be successfully converted by SSMA. As shown the screenshot below, we have selected get_employee_info () procedure which has ref cursor as a return type which is not supported directly in T-SQL 

As a next step, copy the query, open GitHub Copilot extension in VSCode and paste it into a new file which is saved with .sql extension. In this case, I saved the file with the PL/SQL code as ora2sql.sql.  

To setup GitHub Copilot in Visual Studio Code follow the instructions in: GitHub Copilot setup in VSCode. 

After pasting the PL/SQL procedure in VS Code, hit Ctrl+I to invoke the GitHub Copilot inline chat that lets you ask questions or give specific commands in Natural Language. In the chat interface please type convert PL/SQL to T-SQL and hit enter.  

In few seconds the entire PL/SQL code is rewritten to convert the return cursor type to a table type and the generated T-SQL function is also correct. We can either Accept or Discard the changes. In this case I will go ahead and accept the suggestion: 

As a quick check, will copy the generated query into SSMS and see if it can be validated and run successfully: 

With the T-SQL procedure validated, you can copy the generated T-SQL procedure into the SSMA project and synchronize/load it along with the other converted Oracle objects. 

 Here is the demo video that captures this entire scenario end to end: 

GitHub Copilot can handle even more complex conversion scenarios and help you save lot of manual effort and time by converting them to correct T-SQL syntax with few clicks. Here is another demo video showcasing a complex PL/SQL package with inbuilt PL/SQL procedures and a user defined data type being converted to T-SQL automatically using GitHub Copilot: 

To summarize in this blog post we have seen how the combination of SSMA’s rule-based conversion and GitHub Copilot’s AI-driven approach could significantly accelerate code conversion, potentially achieving high conversion success rates in the late 80s to 90s percentage range. 

​Microsoft Tech Community – Latest Blogs –Read More 

EmailEvents table in the advanced hunting schema – Microsoft Defender XDR | Microsoft Learn – this page tells us:

Note

* The LatestDeliveryLocation and LatestDeliveryAction columns are not available in the Streaming API.

I’ve found that a lot of my queries come back with blank LatestDeliveryLocation.  This means I’m searching via the streaming API.  But I don’t want to do that, I want to search the live EmailEvents table and even want to filter based on LatestDeliveryLocation.  I am working in Defender portal, within the Advanced Hunting section.  Example query:

// Works (time range set in UI dropdown):
EmailEvents
| where LatestDeliveryLocation in~ (‘Quarantine’, ‘Junk folder’) and DeliveryLocation =~ ‘Inbox/folder’

// Does NOT work:
EmailEvents
| where TimeGenerated >= ago(1d)
| where LatestDeliveryLocation in~ (‘Quarantine’, ‘Junk folder’) and DeliveryLocation =~ ‘Inbox/folder’

So it seems as though if your query sets the time range, you’re searching the streaming API.  Can anyone please confirm I have this understood correctly?  My next question would be, can I add something else to my query to ensure I’ll be searching the live table?

Microsoft 365 Defender Streaming API: Identity and CloudApp Events in General Availability – Microsoft Community Hub – I asked this in the comments over there too.

​EmailEvents table in the advanced hunting schema – Microsoft Defender XDR | Microsoft Learn – this page tells us:Note* The LatestDeliveryLocation and LatestDeliveryAction columns are not available in the Streaming API. I’ve found that a lot of my queries come back with blank LatestDeliveryLocation.  This means I’m searching via the streaming API.  But I don’t want to do that, I want to search the live EmailEvents table and even want to filter based on LatestDeliveryLocation.  I am working in Defender portal, within the Advanced Hunting section.  Example query:   // Works (time range set in UI dropdown):
EmailEvents
| where LatestDeliveryLocation in~ (‘Quarantine’, ‘Junk folder’) and DeliveryLocation =~ ‘Inbox/folder’

// Does NOT work:
EmailEvents
| where TimeGenerated >= ago(1d)
| where LatestDeliveryLocation in~ (‘Quarantine’, ‘Junk folder’) and DeliveryLocation =~ ‘Inbox/folder’    So it seems as though if your query sets the time range, you’re searching the streaming API.  Can anyone please confirm I have this understood correctly?  My next question would be, can I add something else to my query to ensure I’ll be searching the live table? Microsoft 365 Defender Streaming API: Identity and CloudApp Events in General Availability – Microsoft Community Hub – I asked this in the comments over there too.  Read More

​