Site report with CLI for Microsoft 365 working with dates

Site report with CLI for Microsoft 365 working with dates header image

I love the Microsoft 365 ‘dev’ community, or probably should call it the Tech Community. After posting about Migration report with CLI for Microsoft 365 I got a comment from Barry Bokdam that he would love to get the last content modified date into the report. Now that looks easy enough as that information is present, but there are a few caveats. The result is returned looking a bit off.

Last Content Modified

Each site you create will get a LastContentModifiedDate property. So if list them all using m365 spo site classic list -o json you do get that value back. The returned value however is returned as follows,

"LastContentModifiedDate": "/Date(2021,2,6,4,22,56,230)/",

If you use PowerShell that value is parsed as a string and not as a date. So, if you would return that into your CSV report there is no nice way of sorting or displaying that date. Unfortunately that date is coming directly from the SharePoint APIs, the CLI does not do any magic with it. If you would like to parse it, you need to do it yourselves.

Parsing the LastContentModifiedDate as a date

You can try to parse the date with [Datetime]::ParseExact but that won’t get you far. The date is either written with one or two digits for day of the month, hour of the day and minutes of the day. So depending on what the date returns you would need the following:

[Datetime]::ParseExact('/Date(2021,1,28,5,46,5,963)/', '/Date(yyyy,M,dd,H,mm,s,fff)/', $null)
[Datetime]::ParseExact('/Date(2021,1,8,5,5,5,963)/', '/Date(yyyy,M,d,H,m,s,fff)/', $null)

There is no common denominator you can put into place. The only way to work with the date is to split on the comma, and rebuild the date yourself.

# assuming you have the $site object mapped already
# Format will be /Date(2021,2,6,4,22,56,230)/
# Substring to split out the first 6 characters
$dateArray = $site.LastContentModifiedDate.Substring(6).Split(",");
[DateTime]$lastChanged = "{0}/{1}/{2} {3}:{4}" -f $dateArray[0], $dateArray[1], $dateArray[2], $dateArray[3], $dateArray[4]

You can use the Substring to split out the first 6 characters. That removes the /Date(. That way you can split by , and construct your own date object. If you would put it in the migration report script it would looks as follows:

$fileExportPath = "<PUTYOURPATHHERE.csv>"

$m365Status = m365 status

if ($m365Status -eq "Logged Out") {
  # Connection to Microsoft 365
  m365 login
}

$results = @()
Write-host "Retrieving all sites..."
$allSPOSites = m365 spo site classic list -o json | ConvertFrom-Json
$siteCount = $allSPOSites.Count

Write-Host "Processing $siteCount sites..."
#Loop through each site
$siteCounter = 0

foreach ($site in $allSPOSites) {
  $siteCounter++
  Write-Host "Processing $($site.Url)... ($siteCounter/$siteCount)"

  $dateArray = $site.LastContentModifiedDate.Substring(6).Split(",");

  $results += [pscustomobject][ordered]@{
    Type         = "site"
    Title        = $site.Title
    Url          = $site.Url
    StorageUsage = $site.StorageUsage
    Template     = $site.Template
    LastChanged  = [DateTime]$("{0}/{1}/{2} {3}:{4}" -f $dateArray[0], $dateArray[1], $dateArray[2], $dateArray[3], $dateArray[4])
  }

  Write-host "Retrieving all lists..."

  $allLists = m365 spo list list -u $site.url -o json | ConvertFrom-Json
  foreach ($list in $allLists) {

    $results += [pscustomobject][ordered]@{
      Type     = "list"
      Title    = $list.Title
      Url      = $list.Url
      Template = $list.BaseTemplate
    }
  }
}

Write-Host "Exporting file to $fileExportPath..."
$results | Export-Csv -Path $fileExportPath -NoTypeInformation
Write-Host "Completed."

Remarks

The above is a great way to get insights into how active the site collections are. While it might take a few additional lines to get the date into a proper format, it still feels straight forward. So thanks to Barry for pointing that out. There is however one caveat. If you look at the SharePoint Admin center you will most likely see that the date that is show for last activity is different. In all my tenants I did see a date difference of a few days. The Admin Center always shows a date that is a few days into the future compared to what the API returns.

Different dates in the UI and calling the APIs

I couldn’t write it off to timezones, and for my demo purposes it didn’t really matter if the date was off one or two days, but if you are looking at the exact date time you might have to look at other options.

Loading comments…