Pro-Tips 57 Consolidated Compliance Report

Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
  icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tbladcomputers.OU,
  Case
    When hasinternetexplorer.Found = 1 Then 'Yes'
    Else 'No'
  End As hasinternetexplorer,
  Case
    When lsagentcheck.AssetID Is Null Then 'No'
    Else 'Yes'
  End As hasLsAgent,
  Case
    When checkfordefender.AssetID Is Null Then 'No'
    Else 'Yes'
  End As [Has Defender Service],
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When (tblErrors.ErrorText Is Not Null Or
      tblErrors.ErrorText != '') And tsysasseterrortypes.ErrorMsg Not Like
      '%PrinterSpoolDisabledError%' Then 'Scanning Error: ' +
      tsysasseterrortypes.ErrorMsg
    Else ''
  End As ScanningErrors,
  Case
    When (tblErrors.ErrorText Is Not Null Or
      tblErrors.ErrorText != '') And tsysasseterrortypes.ErrorMsg Not Like
      '%PrinterSpoolDisabledError%' Then '#ffadad'
    Else '#ffffff'
  End As backgroundcolor,
  tblAssetCustom.Comments
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tbladcomputers On tblAssets.AssetID = tbladcomputers.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
      Max(tblErrors.Teller) As ErrorID
    From tblErrors
    Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
      ScanningError.ID
  Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
  Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
      tblErrors.ErrorType
  Left Join (Select Distinct tblAssets.AssetID
    From tblAssets
      Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
      Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
      Left Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
      Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
          tblVmwareInfo.AssetID
      Inner Join tblState On tblState.State = tblAssetCustom.State
      Inner Join tblVmwareGuestSnapShots On tblVmwareGuest.GuestID =
          tblVmwareGuestSnapShots.GuestID) As hassnapshot On tblAssets.AssetID =
      hassnapshot.AssetID
  Left Join (Select Top 1000000 tblServices.AssetID
    From tblServices
      Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
          tblServices.ServiceuniqueID
    Where tblServicesUni.Caption = 'Windows Defender Antivirus Service') As
  checkfordefender On tblAssets.AssetID = checkfordefender.AssetID
  Left Join (Select Distinct tblSoftware.AssetID,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.SoftID
    Where tblSoftwareUni.softwareName Like '%LsAgent%') As lsagentcheck On
      tblAssets.AssetID = lsagentcheck.AssetID
  Left Join (Select tblFileVersions.AssetID,
      tblFileVersions.Found,
      tblFileVersions.FilePathfull,
      tblFileVersions.FileVersion,
      tblFileVersions.CompanyName,
      tblFileVersions.Filesize,
      tblFileVersions.Lastchanged
    From tblFileVersions
    Where tblFileVersions.FilePathfull Like '%iexplore.exe%')
  hasinternetexplorer On tblAssets.AssetID = hasinternetexplorer.AssetID
Where tsysOS.OSname Like '%win 2%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName