List of Windows Updates installed for past 7 days on Windows Servers only

[danielk]

I cannot figure out how to create a report that list of Windows Updates installed for past 7 days on Windows Servers only. Please advise. Thanks.

[Esben_D]

I tested the report on my local installation and it worked without displaying an error. My local installation is on the latest Lansweeper release 6.0.100.94

I added a domainrole criteria for you to the report to make sure it only displays server.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblQuickFixEngineeringUni.HotFixID,
  tblQuickFixEngineeringUni.Description,
  tblQuickFixEngineeringUni.FixComments,
  tblQuickFixEngineeringInstalledBy.InstalledBy,
  tblQuickFixEngineering.InstalledOn,
  tblQuickFixEngineering.Lastchanged As LastChanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblQuickFixEngineering On tblQuickFixEngineering.AssetID =
    tblAssets.AssetID
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
    tblQuickFixEngineering.QFEID
  Left Join tblQuickFixEngineeringInstalledBy
    On tblQuickFixEngineeringInstalledBy.InstalledByID =
    tblQuickFixEngineering.InstalledByID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblQuickFixEngineering.InstalledOn >= GetDate() - 7 And
  tblState.Statename = 'Active' And tblComputersystem.Domainrole > 1
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblQuickFixEngineeringUni.HotFixID

[danielk]

Hi Ian,
Thanks for the reply. But got the following error and how do I target Windows Servers only.

Error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

[Ian_F]

You need to add "tblQuickFixEngineering.InstalledOn >= GetDate() - 7" to the where statement of your query.

I have altered the built-in report "Windows: installed Windows updates" to list only the assets on which a Windows update was installed during the last 7 days:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblQuickFixEngineeringUni.HotFixID,
  tblQuickFixEngineeringUni.Description,
  tblQuickFixEngineeringUni.FixComments,
  tblQuickFixEngineeringInstalledBy.InstalledBy,
  tblQuickFixEngineering.InstalledOn,
  tblQuickFixEngineering.Lastchanged As LastChanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblQuickFixEngineering On tblQuickFixEngineering.AssetID =
    tblAssets.AssetID
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
    tblQuickFixEngineering.QFEID
  Left Join tblQuickFixEngineeringInstalledBy
    On tblQuickFixEngineeringInstalledBy.InstalledByID =
    tblQuickFixEngineering.InstalledByID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblQuickFixEngineering.InstalledOn >= GetDate() - 7 And
  tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblQuickFixEngineeringUni.HotFixID