Script to identify largest gap(s) in content entry over a period of time

Question

One of our clients has reported an issue wherein after our most recent deployment some of their content appears to have gone missing and/or been reverted. I'm not looking for a way to find out the cause of or a fix for the issue just yet, but rather I am looking for a way to confirm that it occurred and - if confirmed - the time period(s) that have been affected.

What I am wondering is if knows if a SQL or SPE script could be used to iterate through all of the content items and use item statistics (specifically last updated time stamps) to identify the greatest periods of time within the last month when no content was edited.

Answer 1

As an alternative to Michael West's solution, I've written a quick script that should identify the actual ranges you're looking for.

$dates = @()

Get-ChildItem -recurse master:\sitecore\content\Home | ForEach-Object {
    $date = $_.Statistics.Updated.Date

    if (!$dates.Contains($date)) {
        $dates += $date
    }
}

$dates = $dates | Sort-Object

$results = @{}

For ($i = 1; $i -le $dates.Count; $i++) {
    $prevDate = $dates[$i - 1]
    $currentDate = $dates[$i]

    $diff = ($currentDate - $prevDate).TotalDays

    if ($results.ContainsKey($diff)) {
        $results[$diff] += @($prevDate, $currentDate)
    }
    else {
        $results[$diff] = ,@($prevDate, $currentDate)
    }
}

$result = $results.GetEnumerator() | Sort-Object -Descending { $_.Key }

$largestGap = $result | Select-Object -First 1

Write-Host "Largest Gap: " + $largestGap.Key
Write-Host "Date Ranges: "

$largestGap.Value | ForEach-Object {
    Write-Host $_[0].ToString("D") "to" $_[1].ToString("D")
}

What this script does is get all of the unique updated dates and sorts them ascending, then iterates through each group of 2 dates in the list. It uses the difference in dates as the key in a hash and adds the date range to it. Then, it just has to get the largest key in that hash and retrieve the date ranges. I tested this on an instance I have and it works fine. Note as well that this only covers the most recent version of the items. Iterating through the versions is also possible, but it would be a much more intense load.

I don't know what formatting you're looking for, but this should do the job if you're just looking for something to pass back to your client.

Answer 2

I would think SPE can handle the job.

There is a bundled report called Items last updated before date which may meet part of the requirements.

filter Where-LastUpdated {
    param(
        [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
        [Sitecore.Data.Items.Item]$Item,

        [datetime]$Before=([datetime]::Today)
    )

    if($Item."__Updated" -le $Before) {
        $Item
    }
}

$before = [System.DateTime]::Now
$items = Get-ChildItem -Path "master:\content\home" -Recurse | 
    Where-LastUpdated -Before $before

You may need to further explore the versions of each item to see those dates.

Use the Get-Member command to help determine fields available on the item.

Update

Here are some more ideas to consider.

1. Scan over all the items. This is not too expensive, plus you can run this during low usage time.
2. Create an array and tally up all the days in which content has been updated. Essentially a histogram.
3. Render a line chart with this information and let someone interpret the data. Use the SPE Web API to build HTML reports.

Update 2

You can find the above report here (screenshot from v 4.6):

Answer 3

I have faced such situation when someone did something and we need to figure out what went wrong and root cause analysis.

So in Sitecore, you have Updated Date and Updated which gives you the required information but those are also fields in the end and can be updated by any module running in Sitecore, so I had to find out something more reliable.

And here is the SQL script which stores the history (at high-level such as who deleted, created, added, saved, added version or removed version, etc for the item in question or missing)

SELECT [Id]
      ,[Category]
      ,[Action]
      ,[ItemId]
      ,[ItemLanguage]
      ,[ItemVersion]
      ,[ItemPath]
      ,[UserName]
      ,[TaskStack]
      ,[AdditionalInfo]
      ,[Created]
  FROM [dbo].[History]
  WHERE [ItemId] = '{Guide of Item}'
  ORDER BY [Created] DESC

and if you don't know the ItemId you can query by ItemPath

SELECT [Id]
      ,[Category]
      ,[Action]
      ,[ItemId]
      ,[ItemLanguage]
      ,[ItemVersion]
      ,[ItemPath]
      ,[UserName]
      ,[TaskStack]
      ,[AdditionalInfo]
      ,[Created]
  FROM [dbo].[History]
  WHERE [ItemPath] LIKE '/sitecore/content/home/products%' -- An Example. And here '%' symbol is wildcard character
  ORDER BY [Created] DESC

SQL result example:

I hope it helps.