Software : changes last 24 hours Enhancement

[Dass]

Hi All

I'm trying to use the software changes last 24 hours report and noticed although its tracking windows software it doesn't track the operating system when that updates. Is there a way to report on this either with all the other software changes which would be most preferable or as a separate report?

If i go into the history of a machine i can see at a KB level certain patches with the plus/minus so its being tracked i just don't know how to get this incorporated in the software report?

[RCorbeil]

Software changes and KB updates are tracked in two different tables. You can either run two separate reports or use a bit of sticky tape (a Union) and combine them.

e.g.

Select Top 1000000
  tsysOS.Image As icon,
  tsysOS.OSname,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Description,
  Case tblSoftwareHist.Action
    When 1 Then 'Ins >>>'
    When 2 Then '<<< Rmv'
    Else '???'
  End As [+/-],
  tblSoftwareUni.softwareName As Software,
  tblSoftwareHist.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Convert(Char(10),tblSoftwareHist.Installdate,120) As InstallDate,
  tblSoftwareHist.Lastchanged
From
  tblAssets
  Inner Join tblSoftwareHist On tblAssets.AssetID = tblSoftwareHist.AssetID
  Inner Join tblSoftwareUni On tblSoftwareHist.softid = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where
  tblSoftwareHist.Lastchanged > GetDate() - 1
  And tblAssetCustom.State = 1

Union

Select Top 1000000
  tsysOS.Image As icon,
  tsysOS.OSname,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Description,
  Case tblQuickFixEngineeringHist.Action
    When '1' Then 'Ins >>>'
    When '2' Then '<<< Rmv'
    Else '???'
  End As [+/-],
  tblQuickFixEngineeringUni.HotFixID As Software,
  Null As Version,
  tblQuickFixEngineeringUni.Description As Publisher,
  Case
    When IsDate(tblQuickFixEngineeringHist.InstalledOn) = 1
    Then Convert(Char(10),Convert(DateTime,tblQuickFixEngineeringHist.InstalledOn),120)
    Else Null
  End As InstallDate,
  tblQuickFixEngineeringHist.Lastchanged
From
  tblAssets
  Inner Join tblQuickFixEngineeringHist On tblAssets.AssetID = tblQuickFixEngineeringHist.AssetID
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where
  tblQuickFixEngineeringHist.Lastchanged > GetDate() - 1
  And tblAssetCustom.State = 1

Order By
  AssetName,
  Lastchanged Desc,
  InstallDate Desc,
  Software