cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Dass
Engaged Sweeper
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?
1 REPLY 1
RCorbeil
Honored Sweeper II
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