Edit

User lifetime representation in the Microsoft Intune Data Warehouse

  • Article

You can use the month of data snapshots stored in the Intune Data Warehouse to answer questions about time-based trends. For example, you can look at the number of users being added over a month. You might also ask about the number of users who have been removed from the system.

To provide this type insight, the data warehouse stores historical information. The data warehouse can track the lifetime of an entity. The warehouse records when an entity was created, when the state of the entity changes, and when an entity is deleted. With the history captured with daily snapshots of quantitative measurements, you can compare one day to the previous day, and so on.

Working with entity lifetimes can be confusing since your entities are changing state. That means if you look at a snapshot on day 30, a user record may not exist in an active state in the data. On day 29 the entity record may exist as active. And then before day 28, the user didn't exist at all.

This scenario may be clearer if you walk through the lifetime of an entity.

Assume a user, John Smith, gets assigned a license on 06/01/2017, then the User table would have the following entry:

DisplayName IsDeleted StartDateInclusiveUTC EndDateExclusiveUTC IsCurrent
John Smith FALSE 06/01/2017 12/31/9999 TRUE

John Smith gives up his license on 07/25/2017. The User table has the following entries. Changes in existing records are marked.

DisplayName IsDeleted StartDateInclusiveUTC EndDateExclusiveUTC IsCurrent
John Smith FALSE 06/01/2017 07/26/2017 FALSE
John Smith TRUE 07/26/2017 12/31/9999 TRUE

The first row indicates John Smith existed in Intune from 06/01/2017 to 07/25/2017. The second record indicates that the user was deleted on 07/25/2017 and is no longer present in Intune.

Now let assume John Smith gets a new license assigned on 08/31/2017, then the User table would have the following entries:

DisplayName IsDeleted StartDateInclusiveUTC EndDateExclusiveUTC IsCurrent
John Smith FALSE 06/01/2017 07/26/2017 FALSE
John Smith TRUE 07/26/2017 08/31/2017 FALSE
John Smith FALSE 08/31/2017 12/31/9999 TRUE

A person wanting to see the current state of all users would want to apply a filter where IsCurrent = TRUE.

A person wanting to see only existing users would want to apply a filter where IsCurrent = TRUE AND IsDeleted = FALSE.

Dimension tables in the entity lifetime

In order to store the history of state changes in entities, Intune doesn't delete records. Instead it marks the record as deleted. This is called a soft-delete. The dimension tables use various metadata columns to track the lifetime of records.

The most commonly used metadata columns are:

Metadata Property Name Interpretation of Values
IsDeleted Indicates whether the entity was deleted in Intune.
StartDateInclusiveUTC The UTC date the entity was loaded into the Intune Data Warehouse. The entity may have been created before it was imported into the Intune Data Warehouse.
DeletedDateUTC The UTC date that the entity was deleted in Intune.

Any metadata column starting with the prefix Row, such as RowLastModifiedDateTimeUTC, indicates when a record was created or modified in the Intune Data Warehouse. The warehouse is downstream from the data in Intune. This value has no relationship to the lifetime of the entity in Intune.

Any person wanting to see only those dimension entities that currently exist would want to apply a filter where IsDeleted = FALSE.

Next steps