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 |
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 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:
|
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:
|
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: {"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:
|
AccountHolderName | The account holder's name. |
ServiceType | Id of the service. The number can be one of the following:
|
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:
|
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: [{"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: [{"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:
|
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:
|
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:
|
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: [{"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. |