Report request: Computers recently upgraded from XP to 7

[stahly8]

just a simple request to show computers that were recently upgraded from Windows XP to Windows 7 in the last 24 hours. thank you.

[Susan_A · Accepted solution]

A sample report can be seen below. You can replace the number I've highlighted (= 1 day) to change the time frame. Keep in mind that this report will only work if the History checkbox of the OS item is ticked under Configuration\Scanning Setup\Item Wait Time. (Ticking the box now will only enable tracking of future OS changes; it will not allow you to detect changes that already occurred.)

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblOperatingsystem.Caption As CurrentOS,
  tblAssets.SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssets.AssetID In (Select Top 1000000 tblOperatingsystemHist.AssetID
  From tblOperatingsystemHist
  Where tblOperatingsystemHist.Caption Like '%windows 7%' And
    tblOperatingsystemHist.Action = 1 And tblOperatingsystemHist.Lastchanged >
    GetDate() - 1) And tblAssets.AssetID In (Select Top 1000000
    tblOperatingsystemHist.AssetID From tblOperatingsystemHist
  Where tblOperatingsystemHist.Caption Like '%windows xp%' And
    tblOperatingsystemHist.Action = 2 And tblOperatingsystemHist.Lastchanged >
    GetDate() - 1) And tblOperatingsystem.Caption Like '%windows 7%' And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName