📑 Table of Contents

  1. Overview & Purpose 🚀
  2. Dependencies & Technologies 🛠️
  3. Migration Structure 🏗️
  4. Intune Table Schema 📊
  5. Integration & Usage 🔄
  6. UML Diagram 📈
  7. Conclusion ✅

1. Overview & Purpose 🚀

The 20250320211658_AddIntuneTable.cs file is an Entity Framework Core migration that introduces a new table called Intune under the irby schema. This table is designed to store rich telemetry and compliance data for devices managed via Microsoft Intune.

Key goals:

  • Centralize Intune device metadata in a relational database.
  • Track device compliance, enrollment, OS details, user assignment, security posture, and more.
  • Enable further querying/reporting and join operations with existing asset tables via AssetId.

2. Dependencies & Technologies 🛠️

Technology Version/Details
.NET 6.0+ (or compatible EF Core version)
Entity Framework Core Migrations, MigrationBuilder, SQL Server
C# Language for migration class
SQL Server (or Azure) Target relational database

Note: Make sure your project references the Microsoft.EntityFrameworkCore.Design package to run migrations.


3. Migration Structure 🏗️

The migration class AddIntuneTable derives from Migration and overrides two methods:

Up Method

  • Purpose: Create the Intune table with a comprehensive set of columns.

  • Signature:

    protected override void Up(MigrationBuilder migrationBuilder)
    
  • Key Call:

    migrationBuilder.CreateTable(
        name: "Intune",
        schema: "irby",
        columns: table => new
        {
            IntuneId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            AssetId  = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            // ... (many other columns)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Intune", x => x.IntuneId);
        });
    

Down Method

  • Purpose: Roll back the changes by dropping the Intune table.

  • Signature:

    protected override void Down(MigrationBuilder migrationBuilder)
    
  • Key Call:

    migrationBuilder.DropTable(
        name: "Intune",
        schema: "irby");
    

4. Intune Table Schema 📊

Column Definitions

Below is a detailed breakdown of each column in the irby.Intune table:

Column SQL Type Nullable Description
IntuneId uniqueidentifier No Primary Key (GUID)
AssetId uniqueidentifier No FK to an asset record in your asset management table
DeviceId nvarchar(max) Yes Intune device identifier
DeviceNameManagedByOwnership nvarchar(max) Yes Device name as managed in Intune
Compliance nvarchar(max) Yes Compliance status (e.g., Compliant, NonCompliant)
ComplianceGracePeriodExpiration datetime2 No Date/time when compliance grace period ends
OS nvarchar(max) Yes Operating system name
OSVersion nvarchar(max) Yes OS version string (e.g., 10.0.19043)
DeviceState nvarchar(max) Yes State of device (e.g., active, inactive)
PrimaryUserEmailAddress nvarchar(max) Yes Email of the primary user
PrimaryUserUPN nvarchar(max) Yes User Principal Name of the assigned user
LastCheckIn datetime2 No Last check-in timestamp
EnrollmentDateIMEI nvarchar(max) Yes Enrollment/IMEI info
EASActivated bit No Boolean flag if EAS is activated
EASActivationId nvarchar(max) Yes Exchange ActiveSync activation ID
IntuneRegistered nvarchar(max) Yes Registration status
JailBroken nvarchar(max) Yes Jailbreak detection (Yes/No/Unknown)
Supervised nvarchar(max) Yes Supervision status
Category nvarchar(max) Yes Device category
LastEASSyncTime datetime2 No Timestamp of last EAS sync
EASStatus nvarchar(max) Yes EAS status detail
EASReason nvarchar(max) Yes Reason for EAS status
Encrypted nvarchar(max) Yes Encryption status
Model nvarchar(max) Yes Device model
Manufacturer nvarchar(max) Yes Device manufacturer
SerialNumber nvarchar(max) Yes Serial number
PhoneNumber nvarchar(max) Yes Associated phone number
PrimaryUserDisplayName nvarchar(max) Yes Display name of primary user
SecurityPatchLevel nvarchar(max) Yes Security patch level
WiFiMAC nvarchar(max) Yes Wi-Fi MAC address
MEID nvarchar(max) Yes Mobile Equipment Identifier
SubscriberCarrier nvarchar(max) Yes Cellular carrier
TotalStorage nvarchar(max) Yes Total storage size (e.g., 64 GB)
FreeStorage nvarchar(max) Yes Free storage available
ManagementName nvarchar(max) Yes Management profile name
AzureADDeviceId nvarchar(max) Yes Azure AD device ID
AzureADRegistered nvarchar(max) Yes Azure AD registration status
JoinType nvarchar(max) Yes Azure AD join type (e.g., AzureADJoined, HybridJoined)
SkuFamily nvarchar(max) Yes SKU family for Intune license
ManagementCertificateExpirationDate datetime2 No Expiration date of management certificate

5. Integration & Usage 🔄

Applying the Migration

  1. Add the migration (already created in your case):
    dotnet ef migrations add AddIntuneTable
    
  2. Apply to database:
    dotnet ef database update
    
  3. Verify: Ensure the irby.Intune table appears in your SQL Server / Azure SQL instance.

Configuring the DbContext

In your IrbyImagingDbContext (or similar), add a DbSet:

public class IrbyImagingDbContext : DbContext
{
    public IrbyImagingDbContext(DbContextOptions options) 
        : base(options) { }

    // ✨ New DbSet for Intune telemetry
    public DbSet<Intune> Intune { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("irby");

        modelBuilder.Entity<Intune>(entity =>
        {
            entity.HasKey(e => e.IntuneId);
            entity.ToTable("Intune");
            // Additional configuration if needed...
        });
    }
}

Tip: You may create an Intune entity class mirroring the migration columns for strong typing.

Sample CRUD Operations

// 1. Insert a new Intune record
var newDevice = new Intune
{
    IntuneId = Guid.NewGuid(),
    AssetId  = existingAssetId,
    DeviceId = "ABC-123",
    OS       = "iOS",
    // ... populate other fields
};
dbContext.Intune.Add(newDevice);
await dbContext.SaveChangesAsync();

// 2. Query non-compliant devices
var nonCompliant = await dbContext.Intune
    .Where(i => i.Compliance == "NonCompliant")
    .ToListAsync();

// 3. Update compliance
var device = await dbContext.Intune.FindAsync(deviceId);
device.Compliance = "Compliant";
await dbContext.SaveChangesAsync();

// 4. Delete obsolete record
dbContext.Intune.Remove(device);
await dbContext.SaveChangesAsync();

6. UML Diagram 📈

Below is a Mermaid class diagram representing the Intune entity:

classDiagram
    class Intune {
        +Guid IntuneId
        +Guid AssetId
        +string? DeviceId
        +string? DeviceNameManagedByOwnership
        +string? Compliance
        +DateTime ComplianceGracePeriodExpiration
        +string? OS
        +string? OSVersion
        +string? DeviceState
        +string? PrimaryUserEmailAddress
        +string? PrimaryUserUPN
        +DateTime LastCheckIn
        +string? EnrollmentDateIMEI
        +bool EASActivated
        +string? EASActivationId
        +string? IntuneRegistered
        +string? JailBroken
        +string? Supervised
        +string? Category
        +DateTime LastEASSyncTime
        +string? EASStatus
        +string? EASReason
        +string? Encrypted
        +string? Model
        +string? Manufacturer
        +string? SerialNumber
        +string? PhoneNumber
        +string? PrimaryUserDisplayName
        +string? SecurityPatchLevel
        +string? WiFiMAC
        +string? MEID
        +string? SubscriberCarrier
        +string? TotalStorage
        +string? FreeStorage
        +string? ManagementName
        +string? AzureADDeviceId
        +string? AzureADRegistered
        +string? JoinType
        +string? SkuFamily
        +DateTime ManagementCertificateExpirationDate
    }

7. Conclusion ✅

This migration adds vital Intune device telemetry data into your database, enabling:

  • 📊 Comprehensive reporting on device compliance and status.
  • 🔗 Integration between Intune and your asset-management workflows.
  • 🚀 Scalability to ingest and query thousands of devices with rich metadata.

With the irby.Intune table in place, you can now build dashboards, alerts, and advanced analytics on device health, security posture, and lifecycle management.

Next Steps:

  1. Create the corresponding Intune entity class in your Domain layer.
  2. Seed sample data or build a data ingestion pipeline from Microsoft Graph API.
  3. Develop your reporting/UI layer to visualize Intune metrics to stakeholders.

Happy coding! 😊🚀