I am hoping this is possible. I am trying to generate a report that shows all asset state changes within the past 24 hours. More specifically I am looking at assets that have moved from an "Active" status to anything else. This will be used to auto email our asset manager of things such as an item that was active and has now been recycled. I was trying to use tblAssets.Lastchanged as the date to reference against, but it didn't update when I changed the state. Any help would be appreciated. See code below:
Select Top 1000000 tblSystemEnclosure.SMBIOSAssetTag As [Asset Tag],
tblSystemEnclosure.SerialNumber As [Serial Number],
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblState.Statename As Status,
tblAssetCustom.Location,
tblAssets.LastChanged As [Date Changed]
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Where tblAssets.LastChanged >= GetDate() - 1 And tblAssets.AssetUnique
Like '%\D___-%' And tblAssetCustom.State <> 1 And
tblDiskdrives.Caption = 'c:'
