‎11-07-2019 05:22 PM
‎11-07-2019 10:05 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now