Thanks, this is helpful!
I started working today to add the collection and reporting of configuration data. I created a query that will report all changes within the last week. One nice thing is that I can run it without needing to supply parameters.
select * , rank() over (partition by configurationid order by capturedate) as rank
a.CaptureDate as BeforeCaptureDate
, a.Value as BeforeValue
, a.ValueInUse as BeforeValueInUse
, b.CaptureDate as AfterCaptureDate
, b.Value as AfterValue
, b.ValueInUse as AfterValueInUse
inner join ranked b on a.ConfigurationID = b.ConfigurationID and a.rank = b.rank - 1
a.CaptureDate >= dateadd(week, -1, getdate()) -- Look for changes in the last week
and ((a.Value <> b.Value) or (b.ValueInUse <> b.ValueInUse)) -- A change is where one of the values doesn't match for consecutive captures