Skip to main content

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 nameStatus
[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

Temporary tables

These tables are used temporarily during the migration between Snow License Manager 9.10 and 9.12:

  • O365.MigrationHistoricalActivitiesServiceActivity

  • O365.MigrationHistoricalActivitiesSubscriptionPlanActivity

  • O365.MigrationHistoricalActivitiesUserActivity

See Migrate user activity historic data for more information on the migration.

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 nameDescription
PlanIDThe identity of the subscription plan.
AccountSKUAccountSKU of the subscription plan.
MetricMetric type (PerUser, Credit, Runs, or Storage) of the subscription plan.
FriendlyNameThe friendly name of the subscription plan.
IsPaidIdentifies if the subscription plan is free of charge or not.
IsMonitoredIdentifies 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 nameDescription
ApplicationIdThe identity of the application.
The same guid comes from dbo.tblApplication table.
ApplicationNameThe friendly name of the application.
AccountSKUAccountSKU of the subscription plan.
ServiceIdThe identity of the service/product.
ServiceNameThe friendly name of the service/product.
ServiceTypeId 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 nameDescription
DataNot completed migration data.
If the Microsoft 365 service shuts down during migration, the process will start from where it stopped in the next run.
SequenceNumberThe sequence number of the latest migrated event.
The sequence number is -1 if there is no event to migrate.
IsCompletedThe status of the migration.
SavedThe 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 nameDescription
TenantIDThe identity of the tenant.
ImportIDThe identity of the import.
AddedDateThe saved date of the aggregation.
AccountPayloadAccount payload.
Portal data.
AccountSkusPayloadAccount SKU payload.
Subscription plans data.
UsersPayloadUser payload.
Portal user data.
ProcessedThe status of the import.

O365.Portal

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

Column nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
AccountNameAccount holder name.
CompanyThe company name of the tenant.
VendorVendor, for example Microsoft.
ServiceNameService name, for example Microsoft 365.
LastImportDateLast aggregation date.

O365.SubscriptionPlan

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

Column nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
NameThe name of the subscription plan (AccountSKU).
ActiveUnitsActive units (purchased licenses).
WarningUnitsWarning units (licenses about to expire).
ConsumedUnitsConsumed units.
IsPaidIdentifies if the subscription plan is free of charge or not.
LicensesThe licences included in the subscription plan.
The column contains historical data.
PricesThe price change of the subscription plan.
The column contains historical data.
ServicesJSON 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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
UserPrincipalNameThe user’s principal name (email address from Microsoft 365 portal).
DataJSON data, including:
  • OnlineOnly
  • LicenseManagerId
  • ConsolidationRule
LicensesThe user's licenses.
ActivitiesThe user's activities.
CountryThe user’s country.
DepartmentThe user’s department.
OrgChecksumThe user’s organization checksum.
OrgIdId of the organization.
OrgNameType of the organization.
SlmUserIdThe user’s Snow LicenseManager identity.
OrganizationName of the organization.
OnlineOnlyIs inventoried user.
IdThe identity of the user.
ImmutableDataJSON data.
All user data comes from Microsoft 365 portal.
BlockedIf 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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
SubscriptionPlanNameName of subscription plan (AccountSKU).
YearYear of breakdown.
MonthMonth of breakdown.
AccountHolderNameThe account holder's name.
DataJSON data.
Example:
[{"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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
ValidFromThe date from when the price change is valid from.
SubscriptionPlanNameName of subscription plan (AccountSKU).
PriceThe price for the subscription plan.
CurrencyCurrency of the price information.
Note: Microsoft 365 does not support multiple currencies.
AccountHolderNameThe account holder's name.

O365.ReadModelCurrentPrices

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

Column nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
AccountSkuName of subscription plan (AccountSKU).
PriceThe price for the subscription plan.
CurrencyCurrency of the price information.
Note: Microsoft 365 does not support multiple currencies.
AccountHolderNameThe 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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
SlmIdThe user’s Snow License Manager identity.
UserPrincipalNameThe user’s principal name (email address from Microsoft 365 portal).
LastActivityStatusLast activity status. The status can be one of the following:
  • Unknown = 0
  • WithinAWeek = 1
  • WithinAMonth = 2
  • WithinThreeMonths = 3
  • WithinSixMonths = 4
  • WithinOneYear = 5
  • MoreThanAYearAgo = 6
AccountHolderNameThe account holder's name.
ServiceTypeId 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
UserActivitySourcePortal = 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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
AccountSKUThe name of the subscription plan (AccountSKU).
UserPrincipalNameThe user’s principal name (email address from Microsoft 365 portal).
AccountHolderNameThe account holder's name.
SubscriptionPlanNameThe friendly name of the subscription plan.
UserNameThe user name in Snow License Manager.
DisplayNameThe user’s display name. The display name is used in Microsoft 365 portal.
LastActivityStatusThe 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
LastExchangeActivityStatusThe last activity status for Exchange.
LastSkypeActivityStatusThe last activity status for Skype.
LastProjectActivityStatusThe last activity status for Project.
LastVisioActivityStatusThe last activity status for Visio.
LicenseManagerIdThe user’s Snow License Manager identity.
CreatedDateThe date when the user was created on Microsoft 365 portal.
SyncDateThe date when the user’s Active Directory last synchronized.
The date is equivalent to LastDirSyncTime in Snow Integration Manager.
LastOnedriveActivityStatusThe last activity status for OneDrive.
LastSharepointActivityStatusThe last activity status for SharePoint.
LastTeamsActivityStatusThe last activity status for Teams.
LastYammerActivityStatusThe last activity status for Yammer.
LastDynamics365ActivityStatusThe last activity status for Dynamics365.
LastPowerBIActivityStatusThe last activity status for PowerBI.
LastEnterpriseActivityStatusThe 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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
TotalUsersThe total number of portal users, that is, the total number of users who have been assigned at least one subscription plan.
TotalAssignedSubscriptionsThe total assigned subscription plans (consumed units).
TotalSubscriptionsThe total number of assigned and unassigned subscription plans. (Sum of Active and Warning units)
AccountHolderNameThe account holder's name.
LastUpdatedThe last import date.

O365.ReadModelOverviewSubscriptionPlan

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

Column nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
SubscriptionPlanNameThe name of the subscription plan (AccountSKU).
FriendlyNameThe friendly name of the subscription plan.
TotalUsersThe total users of the subscription plan.
ActivityJSON string of user count per activity type.
Example:
[{"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}] 
IsPaidIdentifies 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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
MonthThe month for the cost period. The column contains historical data.
TotalUsersThe 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 nameDescription
UserEmailThe user’s email address
UserPrincipalNameThe user’s principal name (email address from Microsoft 365 portal).
TenantIdThe identity of the tenant.
FirstNameThe user’s first name.
LastNameThe user’s last name.
SuggestedSlmUsersSuggested Snow License Manager users. The column contains Snow License Manager data (from RsCloudUserConsolidation view) JSON.
Example:
[{"LicenseManagerId":126,"Username":"EUSE\\niclinsrv","Name":"Nicklas Lindell","Organization":"","Email":null (SLM email)}]
PortalIdThe identity of the portal.

O365.ReportSubscriptionPlanCost

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

Column nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
AccountHolderNameThe account holder's name.
AccountSkuThe AccountSKU of the subscription plan.
SubscriptionNameThe friendly name of the subscription plan.
AvailableLicensesThe number of available licences.
Available licenses = Total licenses* – Consumed units
*Total licenses = Active units + warning units
AssignedLicensesThe number of assigned licenses (consumed units).
MonthlyCostPerUserThe current monthly cost per user.
MonthlyCostAvailableLicensesThe current monthly cost of all available licenses.
MonthlyCostAssignedLicensesThe current monthly cost of all assigned licenses.
TotalCostPerMonthThe total cost of available and assigned licenses per month.
AccumulatedTotalCostThe accumulated total cost for the last 12 months.
CurrencyCurrency

O365.ReportUnlinkedUser

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

Column nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
FullNameThe user’s full name.
UserCreatedDateThe date when the user was created on Microsoft 365 portal.
SubscriptionPlanThe user’s assigned subscription plans. Use a comma to separate the subscriptions.
UserPrincipalNameThe user’s principal name (email address from Microsoft 365 portal).

O365.UserApplicationDoubleLicensing

The table O365. UserApplicationDoubleLicensing contains double licensed application.

Column nameDescription
ApplicationIdThe identity of the application.
SlmIdThe user’s Snow License Manager identity.
LicenseIdThe license identity of the application. (UserLicenseTracking)
PlanIdThe identity of the subscription plan.
TenantIdThe identity of the tenant.
OrgChecksumThe user’s organization checksum.

O365.AutomationQueue

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

Column nameDescription
RequestIdThe identity of the request.
AccountHolderNameThe account holder's name.
UserPrincipalNameThe user’s principal name (email address from Microsoft 365 portal).
ObjectIdThe identity of the user’s object.
AccountSKUAccountSKU of the subscription plan.
StatusCodeThe status code is be one of the following:
  • New = 0
  • Received = 1
  • Invoked = 2
  • Completed = 3
  • Failed = 4
StatusMessageStatus message.
If failed, an error message is shown. The field is empty by default.
RequestTypeThe 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 nameDescription
AccountHolderNameThe account holder's name.
LastRequestDateThe date for the last automation request.

O365.Country

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

Column nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
CountryNameThe country names.
CountryCodeA2The country international code.

O365.CountryList

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

Column nameDescription
CountryNameThe country names.
CountryCodeA2The country international code.

O365.CountryMap

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

Column nameDescription
CountryNameThe country names.
CountryCodeA2The country international code.

O365.Department

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

Column nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
DepartmentIdThe identity of the department in a given portal.
DepartmentNameDepartment 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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
ServiceTypeId 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
MonthThe month for the cost period. The column contains historical data.
YearThe year for the cost period. The column contains historical data.
TotalUsersThe total users that have at least one subscription plan assigned to them.
ActiveUsersThe total users that have at least one subscription plan assigned to them and are active.
UnlinkedUsersThe 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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
SubscriptionPlanNameAccount SKU of the subscription plan.
MonthThe month for the cost period. The column contains historical data.
YearThe year for the cost period. The column contains historical data.
TotalUsersThe total users that have at least one subscription plan assigned to them.
ActivityJSON string of user count per activity type.
Example:
[{"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}] 
IsPaidIdentifies if the subscription plan is free of charge or not.
UnlinkedUsersThe 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 nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
MonthThe month for the cost period. The column contains historical data.
YearThe year for the cost period. The column contains historical data.
TotalUsersThe total users in the portal.
UnlinkedUsersThe total unlinked users.
AssignedUsersThe total users that have at least one subscription plan assigned to them.
AssignedActiveUsersThe 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 nameDescription
FilterIdThe identity of the filter.
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
UserIdThe user’s Snow License Manager identity.
IsDefaultIs default filter.
FilterNameFilter name.
CountryThe country names in filter.
DepartmentThe department names in filter.
CreatedByIdThe user’s Snow License Manager identity.
CreatedDateFilter creation date.

O365.MigrationHistoricalActivitiesProgress

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

Column nameDescription
TenantIdThe identity of the tenant.
PortalIdThe identity of the portal.
CorrelationIdMigration process identifier.
YearYear of data period.
MonthMonth of data period.
PageNumberNumber of last processed pages.
SequenceNumberNumber of last processed sequence number.
IsCompletedStatus of import process.
SavedStatus time stamp.