cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
abetzold
Engaged Sweeper III
I have a report I use to show the execs the patch status of our servers. The problem is, prior to 2008 R2 Microsoft returned the Win32_QuickFixEngineering.InstalledOn as Hex so my 2008 servers do not come back in this report. I would like to shake the hand of the man or women who decided to change that in 2008 R2 but alas I need some help with my query. How can I convert this data (LastPatchDate) to datetime? I also want to make sure this column still contains the data post 2008 server.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As LastPatchDate,
SubQuery1.Value As 'Last Auto Update',
tblAssets.Lastseen,
tblAssetCustom.Custom1 As 'Patching Comments',
tblAssetCustom.Custom2 As 'Patching Schedule',
tblAssetCustom.Custom3 As 'Technical Contact'
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Value
From tblRegistry
Where
tblRegistry.Regkey Like
'%SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install') SubQuery1 On SubQuery1.AssetID = tblAssets.AssetID
Where (tblQuickFixEngineering.InstalledOn Like '%/%' or tblQuickFixEngineering.InstalledOn = '') And
tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
SubQuery1.Value,
tblAssets.Lastseen,
tblAssetCustom.Custom1,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssets.Description
Having tblAssets.AssetName Not Like 'TS%'
Order By LastPatchDate
0 REPLIES 0

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now