Microsoft 365 data tables

This section lists all relational and event-sourced data tables in the Microsoft 365 data model and schema changes introduced in different versions.

Schema changes

The table shows the schema changes in Snow License Manager version 9.6.2 and 9.12, as compared to version 9.6.1.

Table name

Status

[o365].[Portal]

Added in version 9.6.2

[o365].[SubscriptionPlan]

Added in version 9.6.2

[o365].[User]

Added in version 9.6.2

[ew].[TenantSource]

Added in version 9.6.2

[ew].[Event]

Added in version 9.6.2

[o365].[Projections]

Removed in version 9.6.2

[o365].[ProjectionTracking]

Removed in version 9.6.2

[o365].[ReadModelActivityPerService]

Renamed in version 9.12

[o365].[ReadModelMonthlyServiceActivityBreakdown]

Added in version 9.12

[o365].[ReadModelMonthlySubscriptionPlanActivityBreakdown]

Added in version 9.12

[o365].[ReadModelMonthlyUserBreakdown]

Added in version 9.12

[o365].[Country]

Added in version 9.12

[o365].[CountryList]

Added in version 9.12

[o365].[CountryMap]

Added in version 9.12

[o365].[Department]

Added in version 9.12

[o365].[UserFilterConfiguration]

Added in version 9.12

O365.PlansWithMonitoring

The table O365.PlansWithMonitoring contains Microsoft 365 subscription plans that have been monitored by Snow License Manager Microsoft 365 module. Both paid and unpaid subscriptions are included.

Column name

Description

PlanID

The identity of the subscription plan.

AccountSKU

AccountSKU of the subscription plan.

Metric

Metric type (PerUser, Credit, Runs, or Storage) of the subscription plan.

FriendlyName

The friendly name of the subscription plan.

IsPaid

Identifies if the subscription plan is free of charge or not.

IsMonitored

Identifies if the subscription plan is monitored or not by Snow License Manager.

Added in Snow License Manager 9.14.0.

O365.RelevantApplications

The table O365.RelevantApplications contains the Microsoft 365 applications that Snow License Manager 365 module measures and evaluates user activities for. These applications are various versions of the following Microsoft 365 products: Teams, OneDrive, and SharePoint.

Column name

Description

ApplicationId

The identity of the application.

The same guid comes from dbo.tblApplication table.

ApplicationName

The friendly name of the application.

AccountSKU

AccountSKU of the subscription plan.

ServiceId

The identity of the service/product.

ServiceName

The friendly name of the service/product.

ServiceType

Id of the service.

The number can be one of the following:

  1. Microsoft Exchange

  2. Microsoft Skype for Business

  3. Microsoft Project

  4. Microsoft 365 Apps for Enterprise

  5. Microsoft Visio

  6. Microsoft OneDrive

  7. Microsoft SharePoint

  8. Microsoft Yammer

  9. Microsoft Teams

O365.MigrationProgress

The table O365.MigrationProgress contains the information related to the event migration from EventStore database to MSSQL database.

The migration takes place on Snow License Manager 9.6.2 from EventStore db to SnowLicenseManager’s ew schema: TenantSource and Event tables.

Column name

Description

Data

Not completed migration data.

If the Microsoft 365 service shuts down during migration, the process will start from where it stopped in the next run.

SequenceNumber

The sequence number of the latest migrated event.

The sequence number is -1 if there is no event to migrate.

IsCompleted

The status of the migration.

Saved

The date when the migration was completed and all events was successfully saved to ew.Event table.

O365.Import

The table O365.Import contains queued aggregations from Microsoft 365 Integration Connector.

Column name

Description

TenantID

The identity of the tenant.

ImportID

The identity of the import.

AddedDate

The saved date of the aggregation.

AccountPayload

Account payload.

Portal data.

AccountSkusPayload

Account SKU payload.

Subscription plans data.

UsersPayload

User payload.

Portal user data.

Processed

The status of the import.

O365.Portal

The table O365.Portal contains all Microsoft 365 portals of the tenant.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

AccountName

Account holder name.

Company

The company name of the tenant.

Vendor

Vendor, for example Microsoft.

ServiceName

Service name, for example Microsoft 365.

LastImportDate

Last aggregation date.

O365.SubscriptionPlan

The table O365.SubscriptionPlan contains the portal subscription plans of the tenant.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

Name

The name of the subscription plan (AccountSKU).

ActiveUnits

Active units (purchased licenses).

WarningUnits

Warning units (licenses about to expire).

ConsumedUnits

Consumed units.

IsPaid

Identifies if the subscription plan is free of charge or not.

Licenses

The licences included in the subscription plan.

The column contains historical data.

Prices

The price change of the subscription plan.

The column contains historical data.

Services

JSON data

[
  {
    "Name": "EXCHANGE_S_FOUNDATION",
    "Status": 1,
    "Id": "113feb6c-3fe4-4440-bddc-54d774bf0318",
    "Type": "exchange",
    "Consumer": 2
  }
]

O365.User

The table O365.User contains the portal users of the tenant.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

UserPrincipalName

The user’s principal name (email address from Microsoft 365 portal).

Data

JSON data, including:

  • OnlineOnly

  • LicenseManagerId

  • ConsolidationRule

Licenses

The user's licenses.

Activities

The user's activities.

Country

The user’s country.

Department

The user’s department.

OrgChecksum

The user’s organization checksum.

OrgId

Id of the organization.

OrgName

Type of the organization.

SlmUserId

The user’s Snow LicenseManager identity.

Organization

Name of the organization.

OnlineOnly

Is inventoried user.

Id

The identity of the user.

ImmutableData

JSON data.

All user data comes from Microsoft 365 portal.

Blocked

If the user is blocked or not.

Added in Snow License Manager 9.20.0.

O365.ReadModelMonthlySubscriptionPlanBreakdown

The table O365.ReadModelMonthlySubscriptionPlanBreakdown table contains the number of active, warning, assigned and total license information of each cost period’s subscription plans. The table contains historical data.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

SubscriptionPlanName

Name of subscription plan (AccountSKU).

Year

Year of breakdown.

Month

Month of breakdown.

AccountHolderName

The account holder's name.

Data

JSON data.

Example 33.
{"SubscriptionPlanName":"EMS",
"AccountHolderName":"snowsoft",
"SubscriptionPlanFriendlyName":"Microsoft Enterprise Mobility + Security E3",
"NumberOfTotalLicenses":781,
"NumberOfAssignedLicenses":745,
"NumberOfUnassignedLicenses":36,
"NumberOfWarningLicenses":0,
"NumberOfActiveLicenses":781,
"IsPaid":true,
"IsRemoved":false} 


O365.ReadModelCost

The table O365.ReadModelCost contains the cost information for the subscription plans. The table contains historical data.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

ValidFrom

The date from when the price change is valid from.

SubscriptionPlanName

Name of subscription plan (AccountSKU).

Price

The price for the subscription plan.

Currency

Currency of the price information.

Note

Microsoft 365 does not support multiple currencies.

AccountHolderName

The account holder's name.

O365.ReadModelCurrentPrices

The table O365.ReadModelCurrentPrices contains the current prices for the subscription plans.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

AccountSku

Name of subscription plan (AccountSKU).

Price

The price for the subscription plan.

Currency

Currency of the price information.

Note

Microsoft 365 does not support multiple currencies

AccountHolderName

The account holder's name.

O365.ReadModelActivityPerService

The table O365.ReadModelActivityPerService contains user activity information that comes from Snow License Manager per service type (Teams, OneDrive, SharePoint etc.).

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

SlmId

The user’s Snow License Manager identity.

UserPrincipalName

The user’s principal name (email address from Microsoft 365 portal).

LastActivityStatus

Last activity status. The status can be one of the following:

  • Unknown = 0

  • WithinAWeek = 1

  • WithinAMonth = 2

  • WithinThreeMonths = 3

  • WithinSixMonths = 4

  • WithinOneYear = 5

  • MoreThanAYearAgo = 6

AccountHolderName

The account holder's name.

ServiceType

Id of the service.

The number can be one of the following:

  1. Microsoft Exchange

  2. Microsoft Skype for Business

  3. Microsoft Project

  4. Microsoft 365 Apps for Enterprise

  5. Microsoft Visio

  6. Microsoft OneDrive

  7. Microsoft SharePoint

  8. Microsoft Yammer

  9. Microsoft Teams

UserActivitySource

Portal = 0

Inventory = 1

O365.ReadModelUsersPerSubscriptionPlan

The table O365.ReadModelUsersPerSubscriptionPlan contains a list of subscription plans that are assigned to a Microsoft 365 user and the user's last activity information.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

AccountSKU

The name of the subscription plan (AccountSKU).

UserPrincipalName

The user’s principal name (email address from Microsoft 365 portal).

AccountHolderName

The account holder's name.

SubscriptionPlanName

The friendly name of the subscription plan.

UserName

The user name in Snow License Manager.

DisplayName

The user’s display name. The display name is used in Microsoft 365 portal.

LastActivityStatus

The user’s last activity status. The status can be one of the following:

  • Unknown = 0

  • WithinAWeek = 1

  • WithinAMonth = 2

  • WithinThreeMonths = 3

  • WithinSixMonths = 4

  • WithinOneYear = 5

  • MoreThanAYearAgo = 6

LastExchangeActivityStatus

The last activity status for Exchange.

LastSkypeActivityStatus

The last activity status for Skype.

LastProjectActivityStatus

The last activity status for Project.

LastVisioActivityStatus

The last activity status for Visio.

LicenseManagerId

The user’s Snow License Manager identity.

CreatedDate

The date when the user was created on Microsoft 365 portal.

SyncDate

The date when the user’s Active Directory last synchronized.

The date is equivalent to LastDirSyncTime in Snow Integration Manager.

LastOnedriveActivityStatus

The last activity status for OneDrive.

LastSharepointActivityStatus

The last activity status for SharePoint.

LastTeamsActivityStatus

The last activity status for Teams.

LastYammerActivityStatus

The last activity status for Yammer.

LastDynamics365ActivityStatus

The last activity status for Dynamics365.

LastPowerBIActivityStatus

The last activity status for PowerBI.

LastEnterpriseActivityStatus

The last activity status for Enterprise.

O365.ReadModelOverview

The table O365.ReadModelOverview contains the overview of each cost period: assigned plans, total plans, total users, and last import date. The table contains historical data.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

TotalUsers

The total number of portal users, that is, the total number of users who have been assigned at least one subscription plan.

TotalAssignedSubscriptions

The total assigned subscription plans (consumed units).

TotalSubscriptions

The total number of assigned and unassigned subscription plans. (Sum of Active and Warning units)

AccountHolderName

The account holder's name.

LastUpdated

The last import date.

O365.ReadModelOverviewSubscriptionPlan

The table O365.ReadModelOverviewSubscriptionPlan contains the current state of subscription plan activities.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

SubscriptionPlanName

The name of the subscription plan (AccountSKU).

FriendlyName

The friendly name of the subscription plan.

TotalUsers

The total users of the subscription plan.

Activity

JSON string of user count per activity type.

Example 34.
[{"Status":0,"TotalCount":0},
{"Status":1,"TotalCount":696},
{"Status":2,"TotalCount":150},
{"Status":3,"TotalCount":103},
{"Status":4,"TotalCount":65},
{"Status":5,"TotalCount":108},
{"Status":6,"TotalCount":63}] 


IsPaid

Identifies if the subscription plan is free of charge or not.

O365.ReadModelOverviewUsersUserHistory

The table O365.ReadModelOverviewUsersUserHistory contains the total number of users per cost period. The table contains historical data.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

Month

The month for the cost period. The column contains historical data.

TotalUsers

The total users that have at least one subscription plan assigned to them.

O365.ReadModelUserLinkingSuggestion

The table O365.ReadModelUserLinkingSuggestion contains the list of users that haven't been linked to a Snow License Manager user and the list of their potential Snow License Manager user matchings.

Column name

Description

UserEmail

The user’s email address

UserPrincipalName

The user’s principal name (email address from Microsoft 365 portal).

TenantId

The identity of the tenant.

FirstName

The user’s first name.

LastName

The user’s last name.

SuggestedSlmUsers

Suggested Snow License Manager users. The column contains Snow License Manager data (from RsCloudUserConsolidation view) JSON.

Example 35.
[{"LicenseManagerId":126,
"Username":"EUSE\\niclinsrv",
"Name":"Nicklas Lindell",
"Organization":"",
"Email":null (SLM email)}]


PortalId

The identity of the portal.

O365.ReportSubscriptionPlanCost

The table O365.ReportSubscriptionPlanCost contains the data from the report Microsoft 365 subscription plan cost.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

AccountHolderName

The account holder's name.

AccountSku

The AccountSKU of the subscription plan.

SubscriptionName

The friendly name of the subscription plan.

AvailableLicenses

The number of available licences.

Available licenses = Total licenses* – Consumed units

*Total licenses = Active units + warning units

AssignedLicenses

The number of assigned licenses (consumed units).

MonthlyCostPerUser

The current monthly cost per user.

MonthlyCostAvailableLicenses

The current monthly cost of all available licenses.

MonthlyCostAssignedLicenses

The current monthly cost of all assigned licenses.

TotalCostPerMonth

The total cost of available and assigned licenses per month.

AccumulatedTotalCost

The accumulated total cost for the last 12 months.

Currency

Currency

O365.ReportUnlinkedUser

The table O365.ReportUnlinkedUser contains the data from the report Microsoft 365 unlinked users.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

FullName

The user’s full name.

UserCreatedDate

The date when the user was created on Microsoft 365 portal.

SubscriptionPlan

The user’s assigned subscription plans. Use a comma to separate the subscriptions.

UserPrincipalName

The user’s principal name (email address from Microsoft 365 portal).

O365.UserApplicationDoubleLicensing

The table O365. UserApplicationDoubleLicensing contains double licensed application.

Column name

Description

ApplicationId

The identity of the application.

SlmId

The user’s Snow License Manager identity.

LicenseId

The license identity of the application. (UserLicenseTracking)

PlanId

The identity of the subscription plan.

TenantId

The identity of the tenant.

OrgChecksum

The user’s organization checksum.

O365.AutomationQueue

The table O365.AutomationQueue contains the Automation Queue from Automation Platform.

Column name

Description

RequestId

The identity of the request.

AccountHolderName

The account holder's name.

UserPrincipalName

The user’s principal name (email address from Microsoft 365 portal).

ObjectId

The identity of the user’s object.

AccountSKU

AccountSKU of the subscription plan.

StatusCode

The status code is be one of the following:

  • New = 0

  • Received = 1

  • Invoked = 2

  • Completed = 3

  • Failed = 4

StatusMessage

Status message.

If failed, an error message is shown. The field is empty by default.

RequestType

The request type is one of the following:

  • RemovePlan = 1

  • AddPlan = 2

O365.AutomationStatus

The table O365.AutomationStatus contains the Automation Status from Automation Platform.

Column name

Description

AccountHolderName

The account holder's name.

LastRequestDate

The date for the last automation request.

O365.Country

The table O365.Country contains the list of countries that portal users belong to.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

CountryName

The country names.

CountryCodeA2

The country international code.

O365.CountryList

The table O365.CountryList contains a static list of all countries.

Column name

Description

CountryName

The country names.

CountryCodeA2

The country international code.

O365.CountryMap

The table O365.CountryMap contains a map between country names from microsoft api and international code.

Column name

Description

CountryName

The country names.

CountryCodeA2

The country international code.

O365.Department

The table O365.Department contains the list of departments that portal users belong to.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

DepartmentId

The identity of the department in a given portal.

DepartmentName

Department name.

O365.ReadModelMonthlyServiceActivityBreakdown

The table O365.ReadModelMonthlyServiceActivityBreakdown contains number of active, unlinked and total number of users for each cost period’s service type. The table contains historical data.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

ServiceType

Id of the service.

The number can be one of the following:

  1. Microsoft Exchange

  2. Microsoft Skype for Business

  3. Microsoft Project

  4. Microsoft 365 Apps for Enterprise

  5. Microsoft Visio

  6. Microsoft OneDrive

  7. Microsoft SharePoint

  8. Microsoft Yammer

  9. Microsoft Teams

Month

The month for the cost period. The column contains historical data.

Year

The year for the cost period. The column contains historical data.

TotalUsers

The total users that have at least one subscription plan assigned to them.

ActiveUsers

The total users that have at least one subscription plan assigned to them and are active.

UnlinkedUsers

The total unlinked users.

O365.ReadModelMonthlySubscriptionPlanActivityBreakdown

The table O365.ReadModelMonthlySubscriptionPlanActivityBreakdown contains total number of users and user activity information of each cost period’s subscription plans. The table contains historical data.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

SubscriptionPlanName

Account SKU of the subscription plan.

Month

The month for the cost period. The column contains historical data.

Year

The year for the cost period. The column contains historical data.

TotalUsers

The total users that have at least one subscription plan assigned to them.

Activity

JSON string of user count per activity type.

Example 36.
[{"Status":0,"TotalCount":0},
{"Status":1,"TotalCount":696},
{"Status":2,"TotalCount":150},
{"Status":3,"TotalCount":103},
{"Status":4,"TotalCount":65},
{"Status":5,"TotalCount":108},
{"Status":6,"TotalCount":63}] 


IsPaid

Identifies if the subscription plan is free of charge or not.

UnlinkedUsers

The total unlinked users.

O365.ReadModelMonthlyUserBreakdown

The table O365.ReadModelMonthlyUserBreakdown contains the unlinked, active, total number of users per cost period. The table contains historical data.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

Month

The month for the cost period. The column contains historical data.

Year

The year for the cost period. The column contains historical data.

TotalUsers

The total users in the portal.

UnlinkedUsers

The total unlinked users.

AssignedUsers

The total users that have at least one subscription plan assigned to them.

AssignedActiveUsers

The total users that have at least one subscription plan assigned to them and are active.

O365.UserFilterConfiguration

The table O365.UserFilterConfiguration contains filters configuration per user.

Column name

Description

FilterId

The identity of the filter.

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

UserId

The user’s Snow License Manager identity.

IsDefault

Is default filter.

FilterName

Filter name.

Country

The country names in filter.

Department

The department names in filter.

CreatedById

The user’s Snow License Manager identity.

CreatedDate

Filter creation date.

O365.MigrationHistoricalActivitiesProgress

The table O365.MigrationHistoricalActivitiesProgress contains user activity migration per portal.

Column name

Description

TenantId

The identity of the tenant.

PortalId

The identity of the portal.

CorrelationId

Migration process identifier.

Year

Year of data period.

Month

Month of data period.

PageNumber

Number of last processed pages.

SequenceNumber

Number of last processed sequence number.

IsCompleted

Status of import process.

Saved

Status time stamp.