Pro-Tips 54 LsAgent Script Compliance Report

Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
  icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname As OS,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssetCustom.manufacturer,
  tblAssetCustom.model,
  tsysIPLocations.IPLocation,
  tbladcomputers.OU,
  tblAssets.lastseen,
  tblAssets.lastlsagent As LastLSAgentScan,
  Case
    When checkforlsagent.AssetID Is Null Then 'No'
    Else 'Yes'
  End As lsagent,
  Case
    When hasLSAgentScript.Found = 1 Then 'Yes'
    Else 'No'
  End As LSAgent_Script,
  Case
    When hasLSAgentScriptErrorFile.Found = 1 Then 'Yes'
    Else 'No'
  End As LSAgent_ScriptErrorFile,
  hasLSAgentScriptErrorFile.lastmodified As ErrorFile_LastError,
  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
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 Top 1000000 tblServices.AssetID
    From tblServices
      Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
          tblServices.ServiceuniqueID
    Where tblServicesUni.Name = 'LansweeperAgentService') As checkforlsagent On
      tblAssets.AssetID = checkforlsagent.AssetID
  Left Join (Select tblFileVersions.AssetID,
      tblFileVersions.Found,
      tblFileVersions.FilePathfull,
      tblFileVersions.FileVersion,
      tblFileVersions.CompanyName,
      tblFileVersions.Filesize,
      tblFileVersions.Lastchanged
    From tblFileVersions
    Where
      tblFileVersions.FilePathfull = 'C:\Scripts\LansweeperServiceManager.ps1')
  hasLSAgentScript On tblAssets.AssetID = hasLSAgentScript.AssetID
  Left Join (Select tblFileVersions.AssetID,
      tblFileVersions.Found,
      tblFileVersions.FilePathfull,
      tblFileVersions.FileVersion,
      tblFileVersions.CompanyName,
      tblFileVersions.Filesize,
      tblFileVersions.Lastchanged,
      tblFileVersions.LastModified
    From tblFileVersions
    Where
      tblFileVersions.FilePathfull =
      'c:\scripts\lansweeperservicemanager_errorlog.txt')
  hasLSAgentScriptErrorFile On
      tblAssets.AssetID = hasLSAgentScriptErrorFile.AssetID
  Left Join (Select Top 1000000 unioned.assetid,
      unioned.software,
      unioned.version,
      unioned.Enabled,
      unioned.Uptodate
    From ((Select a.assetid As assetid,
          a.software As software,
          a.version As version,
          'software comparison' As RetrievedFrom,
          '' As Enabled,
          '' As Uptodate
        From (Select tblSoftware.AssetID As assetid,
              tblSoftwareUni.softwareName As software,
              tblSoftware.softwareVersion As version
            From tblSoftware
              Inner Join tblSoftwareUni On tblSoftware.softID =
                  tblSoftwareUni.SoftID
              Inner Join tsysantivirus On tblSoftwareUni.softwareName =
                  tsysantivirus.Software) a)
        Union
        (Select tblAntivirus.AssetID As assetid,
          tblAntivirus.DisplayName As software,
          Null As version,
          'WMI' As RetrievedFrom,
          Case
            When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
            Else 'No'
          End As Enabled,
          Case
            When tblAntivirus.productUpToDate = 1 Then 'Yes'
            Else 'No'
          End As Uptodate
        From tblAntivirus)) unioned
      Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
      Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
      Inner Join tblComputersystem On
          tblAssets.AssetID = tblComputersystem.AssetID
    Where tblAssetCustom.State = 1) As antiviruscheck On tblAssets.assetid =
      antiviruscheck.assetid
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName