This article is contributed. See the original author and article here.
In this post, we’ll cover some details on how to track the lifecycle of a SharePoint Site in the Microsoft Graph Data Connect (MGDC), using the date columns in the SharePoint Site dataset. If you’re not familiar with MGDC for SharePoint, start with https://aka.ms/SharePointData.
All Dates in the Sites Dataset
One of the most common scenarios in MGDC for SharePoint is tracking the lifecycle of a site, which includes understanding when the site was created, how it grows over time, when it stops growing and when it becomes inactive or abandoned.
The SharePoint Sites dataset includes several columns that can be used to understand the site lifecycle in general. For instance, here are the datetime columns available:
Site Created
Creation date is straightforward. There is a column (CreatedTime) with the date when the site was created. As with all other dates, it uses the UTC time zone.
Last Modified
In the Sites dataset, you also have the date and time when any items under the root web were last modified (RootWeb.LastItemModifiedDate). This includes the last time when files were created, updated or deleted. This is a great indication that the Site is still in active use.
You also have the date the site security was last modified (LastSecurityModifiedDate). This shows when permissions were granted, updated or revoked. That includes permissions granted through the manage access interface and permissions granted through sharing links.
Last Accessed
Last access is available at the site level (LastUserAccessDate). This shows when an item in the site was last accessed (this includes simply reading the file). This is an important indicator to help understand when the site is becoming inactive or abandoned.
Note that, while there is an effort to identify here only access performed directly by users, this date might also include automated actions by applications, including internal SharePoint applications.
Snapshot Date
Please note that there is one more date (SnapshotDate), but that one is not relevant to the site lifecycle. The snapshot date simple tracks when the data was retrieved by MGDC.
File Actions
Besides what’s captured in these datetime columns in the Sites dataset, you also have the option to capture detailed file activity in the site using the SharePoint File Actions and accumulate those over time.
Keep in mind that MGDC for SharePoint only keeps actions for the last 21 days due to compliance issues. More specifically, you can get file actions between today minus 2 days and today minus 23 days. For instance, if today is June 30th, you can get file actions between June 8th and June 28th.
If you query this information daily, you could build a longer history of file actions over time. For instance, you could keep the last 90 days of data from the File Actions dataset. With that you could find recent access or otherwise say “no access in the last 90 days”.
This would also let you know more details about recent file activities, like who last accessed the site, which file or extension was last accessed, what was the last action, etc. You need to decide if you can rely solely on the date columns provided in the Sites dataset or if it is useful to keep these additional details.
Please do check with the compliance team in your company to make sure there are no restrictions on keeping this information for longer periods of time in your country. There might be regulatory restrictions on how long you can keep this type of personally identifiable information.
Calculated Columns
Keep in mind that these dates use a datetime data type, so grouping by one of them can sometimes be a challenge. If you’re using Power BI, you can show them as a date hierarchy and get a summary by year, quarter, month or day.
It might also be useful to create calculated columns to help with grouping and visualization. For instance, you can create a new date column (without the time portion) for daily summaries. Here’s how to calculate that in Power BI:
CreatedDay = DATE(YEAR(Sites[CreatedTime]),MONTH(Sites[CreatedTime]), DAY(Sites[CreatedTime]))
You could also create a column for monthly summaries like this:
CreatedMonth = FORMAT(Sites[CreatedTime], "yyyy-MM")
You can read more about calculated columns in Power BI at MGDC for SharePoint FAQ: How to create custom columns in Power BI.
Conclusion
I hope this clarifies what is available in MGDC for SharePoint to track the lifecycle of a SharePoint site.
Let us know in the comments if you think we should consider additional lifecycle information.
For further details about the schema of all SharePoint datasets in MGDC, including SharePoint Sites and SharePoint File Actions, see https://aka.ms/SharePointDatasets.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments