heb1014 (11/28/2012)

---

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.

with ranked

as

(

select * , rank() over (partition by configurationid order by capturedate) as rank

from config.ConfigData

)

select

a.CaptureDate as BeforeCaptureDate

, a.ConfigurationID

, a.Name

, a.Value as BeforeValue

, a.ValueInUse as BeforeValueInUse

, b.CaptureDate as AfterCaptureDate

, b.Value as AfterValue

, b.ValueInUse as AfterValueInUse

from

ranked a

inner join ranked b on a.ConfigurationID = b.ConfigurationID and a.rank = b.rank - 1

where

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

order by

a.ConfigurationID, a.CaptureDate